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),

Answer 1

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(...)