LINQ, can't join to string

Question

I have a list of users, each user has list of questions. In my model list of questions should be in string via comma. I try:

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 error:

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

How to get it correctly?

instead of joining in `linq`, just return the collection and do the joining after retrieval — Kris Ivanov, Mar 10 '12 at 14:07

Jon Skeet · Accepted Answer · 2012-03-10T14:23:30.197

19

I would suggest doing the string.Join operation locally instead 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();

edited Mar 10 '12 at 14:23

answered Mar 10 '12 at 14:07

Jon Skeet

1,421,763
867
9,128
9,194

Question4 has type String, your sentense with select has type IQuerable... – John Mar 10 '12 at 14:17
1

@John: That's fine - because in `q` I'm only creating an `IQueryable` for an anonymous type `T`. I only convert it to an `AdminSurveyReportModel` in the second part. I'll rename the property to make this clearer. – Jon Skeet Mar 10 '12 at 14:23
I need to add additional filter for `Question4Parts` before calling `.ToList()`, so I'm wondering about the performance of this query. Does it get the data only when it hits `.ToList()`? Or does it query as soon as it hits `.AsEnumerable()`? – Hp93 Dec 08 '17 at 08:54
1

@Hp93: It doesn't get the data until it's requested (which `ToList` will do) - but if you specify a filter after `AsEnumerable`, the filtering will be performed locally rather than at the database. – Jon Skeet Dec 08 '17 at 08:55
So if I want to optimize for the speed, I should write query to get `Question4` at the DB level, not by using `Join` in .NET, so that I can continue to filter on the DB after that. Got it. Thanks. – Hp93 Dec 08 '17 at 09:09
@Hp93: We don't really have enough context to know. You should do as much filtering as you before `AsEnumerable`, but you could then still do the `string.Join` locally. If that's not enough information, I suggest you ask a new question with concrete details. – Jon Skeet Dec 08 '17 at 09:21

score 0 · Answer 2 · answered Mar 13 '12 at 09:48

0

can't include the string.Join() in the initial projection, because the LINQ translator doesn't support it. Can I write a custom translator for it?

answered Mar 13 '12 at 09:48

john js

1

score -6 · Answer 3 · answered Mar 10 '12 at 14:09

-6

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)

Have not tested

answered Mar 10 '12 at 14:09

Adam Driscoll

9,395
9
61
104

no, it does not work: LINQ to Entities does not recognize the method 'System.String Aggregate[String](System.Collections.Generic.IEnumerable`1[System.String], System.Func`3[System.String,System.String,System.String])' method, and this method cannot be translated into a store expression. – John Mar 10 '12 at 14:15

LINQ, can't join to string

3 Answers3

Linked

Related