C# Anyway to Aggregate from IQueryable (Not AsEnumerable)

Question

Code is the best way to express.

IQueryable<Entity> Sample = dbContext.Entity.Where(condition);
var result = from s in Sample
              select new NewList
              {
                  value = s.ChildrenTable.Select(child => child.license)
                      .Aggregate("", (current => next) => current + "; " + next),
              }

And the error (Maybe you already known):

    LINQ to Entities does not recognize the method 'System.String 
Aggregate[String,String](System.Collections.Generic.IEnumerable`1[System.String], System.String, 
System.Func`3[System.String,System.String,System.String])' method, 
and this method cannot be translated into a store expression.

So please help me any solution for this case!

Answer 1

Entity Framework can't actually run your C# code as part of its query. It has to be able to convert the query to an actual SQL statement. In order for that to work, you will have to restructure your query expression into an expression that Entity Framework can handle

Answer 2

You should get all data from sql via linq-to-sql first.

After that, you can use Join() or Aggregate() to achieve it.

IQueryable<Entity> Sample = dbContext.Entity.Where(condition);
var data = from s in Sample
              select new 
              {
                  childlicense = s.ChildrenTable.Select(child => child.license)
              }.ToList();
var result = data.Select(p => new NewList { value  = string.Join(", ", p.childlicense) });