LINQ, cannot join the line

Question

I have a list of users, each user has a list of questions. My model list of questions should have a comma separated line. I'm trying to:

public List<ITW2012Mobile.ViewModels.AdminSurveyReportModel> SurveyReportList() { var q = from i in _dbContext.Users where i.UserId != null select new ITW2012Mobile.ViewModels.AdminSurveyReportModel() { FirstName = i.FirstName, LastName = i.LastName, Question4 = String.Join(", " , (from a in _dbContext.MultipleQuestions where a.MultipleQuestionType.KEY == MultipleQuestionKeys.BENEFITS select a.Question).ToArray()) }; return q.ToList(); } public class AdminSurveyReportModel { public string FirstName { get; set; } public string LastName { get; set; } public string Question4 { get; set; } } 

Of course, I get an error:

LINQ to Entities does not recognize the 'System.String Join (System.String, System.String [])' method, and this method cannot be translated into a storage expression.

How to do it right?

Answer 1

I would suggest performing the string.Join operation locally using AsEnumerable :

 var q = from i in _dbContext.Users where i.UserId != null select new { FirstName = i.FirstName, LastName = i.LastName, Question4Parts = _dbContext.MultipleQuestions .Where(a => a.MultipleQuestionType.KEY == MultipleQuestionKeys.BENEFITS) .Select(a => a.Question) }; return q.AsEnumerable() .Select(x => new ITW2012Mobile.ViewModels.AdminSurveyReportModel { FirstName = x.FirstName, LastName = x.LastName, Question4 = string.Join(", ", x.Question4Parts) }) .ToList(); 

Answer 2

cannot contain string.Join () in the initial projection because the LINQ translator does not support it. Can I write my own translation for him?

Answer 3

Try using the Aggregate method.

 Question4 = (from a in _dbContext.MultipleQuestions where a.MultipleQuestionType.KEY == MultipleQuestionKeys.BENEFITS select a.Question).ToArray().Aggregate((x,y) => x + "," + y) 

Not tested