Aggregate Function in LINQ expression throws error. (cannot be translated into a store expression.)

Question

Error: LINQ to Entities does not recognize the method 'System.String Aggregate[String,String](System.Collections.Generic.IEnumerable1[System.String], System.String, System.Func3[System.String,System.String,System.String])' method, and this method cannot be translated into a store expression.

Linq Expression:

      Items = context.TESTANSWER.Where(x => x.ID == 6729223232)
            .Join(context.QUESTIONREPOs, x => x.QUESTIONID, y => y.ID, (x, y) => new { x = x, y = y })
            .Join(context.OPTIONREPOs, p => p.x.QUESTIONID, q => q.QUESTIONID, (p, q) => new { p = p, q = q }).Where(p => p.p.x.RESPONSEID == p.q.ID)
            .GroupJoin(context.TESTANSWERASSOCIATION, c => c.p.x.ID, b => b.TESTANSWERID, (c, b) => new { c = c, b = b })
            .SelectMany(
                n => n.b.DefaultIfEmpty(),
                    (n, b) =>
                        new QuestListItemObj
                        {
                            State = n.c.p.x.STATE,
                            Association = n.b.Select(l => l.ASSOCIATION.TITLE).ToList().Aggregate((s, t) => s + ", " + t),
                            Description = n.c.p.y.DESCRIPTION,
                            Question = n.c.p.y.QUESTION,
                            Answer = n.c.q.OPTIONTEXT,
                        }).ToList();

I also just tried SelectMany but got same error..

 Affiliaiton = n.b.SelectMany(l => l.AFFILIATION.TITLE).Aggregate(string.Empty, (s, t) => s + ", " + t),

score 6 · Accepted Answer · answered Oct 18 '13 at 15:53

You're having an IQueryable that translates to SQL. Your Aggregate is a method that is unknown to SQL, so there is no way to translate it and you get your exception.

A possible way is to call AsEnumerable() before. This will cause the query to execute and get the data from your SQL server, and the remaining actions are executed in memory (and not on your SQL Server).

myQuery.AsEnumerable().Aggregate(...)

score 3 · Answer 2 · answered Oct 18 '13 at 15:54

As the error message is telling you, the database doesn't know how to translate that code into SQL.

Fortunately, there really isn't any need for it to do so. Rather than putting the data into a comma delimited string on the DB end, just pull down the pieces and make a string out of it in C#. It's pulling the same amount of data, so there's no real reason to use a database.

You can use AsEnumerable to ensure that the following operation is one in linq to object, not on the DB end, but in this case Aggreagte is a poor tool to use for appending values to a string. Just use String.Join.

var query = n.b.SelectMany(l => l.AFFILIATION.TITLE);


//not very efficient option, but will work
string data1 = query.AsEnumerable().
    .Aggregate(string.Empty, (s, t) => s + ", " + t);

//faster, more efficient, simpler to write, and clearer to the reader.
string data2 = string.Join(", ", query);

Aggregate Function in LINQ expression throws error. (cannot be translated into a store expression.)

2 Answers2

Linked

Related