Linq string.Join group values

Question

I have the following query and I would like to get a comma seperated list of Locs in the property LValues.

from sci in sit

  join i in it on sci.ItemId equals i.Id into it
    from i in it.DefaultIfEmpty()
  join sc in sc on sci.ISId equals sc.Id
  join l in Loc on sc.Id equals l.ESId

  group new { l, sci, i } by new { i.Code, i.Name  } into g
  select new 
  {
      Code = g.Key.Code,
      Name = g.Key.Name ?? "UNKNOWN",
      LValues = string.Join(',', g.Select(x=>x.l.LValue).Distinct()), // This is not working
      Qty = g.Sum(x => x.sci.Qty)
  }

I'm using EF Core 3.1.3 and Linq says

InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: new { Code = (i.Code), Name = (i.Name), }, ElementSelector:new { l = (EntityShaperExpression: EntityType: Loc ValueBufferExpression: (ProjectionBindingExpression: l) IsNullable: False ), sci = (EntityShaperExpression: EntityType: Sit ValueBufferExpression: (ProjectionBindingExpression: sci) IsNullable: False ), i = (EntityShaperExpression: EntityType: It ValueBufferExpression: (ProjectionBindingExpression: i) IsNullable: True ) } ) .Select(x => x.l.LValue)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

If I use an aggregate function like g.Max(x=>x.l.LValue) it gives one value as expected. Is there any other way than using client side evaluation? However I have tried string.Join(',', g.Select(x=>x.l.LValue).ToList().Distinct()) but that didn't work either, throws the same error.

Update

It throws the same error even with LValues = g.Select(x=>x.l.LValue) or LValues = g. There must be something in my data or the group by that makes it fail.

Generated SQL (Sql Server) looks like this using LValues = g.Max(x=>x.l.LValue) in linq:

SELECT [i0].[Code], COALESCE([i0].[Name], N'UNKNOWN') AS [Name],
    MAX([l].[LValue]) AS [LValues], SUM([i].[Qty]) AS [Qty]
FROM [Sit] AS [i]
LEFT JOIN [It] AS [i0] ON [i].[ItemId] = [i0].[Id]
INNER JOIN [Sc] AS [i1] ON [i].[ISId] = [i1].[Id]
INNER JOIN [Loc] AS [l] ON [i1].[Id] = [l].[ESId]
GROUP BY [i0].[Code], [i0].[Name]

I hope this helps understand what is causing the error.

Answer 1

I would say you need to materialize your data and concatenate string on client side, cause EF can not translate string.Join into proper SQL:

var query = from sci in sit

  join i in it on sci.ItemId equals i.Id into it
    from i in it.DefaultIfEmpty()
  join sc in sc on sci.ISId equals sc.Id
  join l in Loc on sc.Id equals l.ESId

  group new { l, sci, i } by new { i.Code, i.Name  } into g
  select new 
  {
      Code = g.Key.Code,
      Name = g.Key.Name ?? "UNKNOWN",
      LValues = g.Select(x=>x.l.LValue).Distinct(), 
      Qty = g.Sum(x => x.sci.Qty)
  };

var result = query.ToList()
  .Select(o => new
  {
      LValues = string.Join(",", o.LValues)
  });

Answer 2

I think you'll have to do the grouping in memory as EF Core can only do basic aggregate functions in a select when using a group by

(from sci in sit
join i in it on sci.ItemId equals i.Id into it
from i in it.DefaultIfEmpty()
join sc in sc on sci.ISId equals sc.Id
join l in Loc on sc.Id equals l.ESId
select new 
{
    i.Code,
    i.Name,
    l.LValue,
    sci.Qty
})
.AsEnumerable()
.GroupBy(x => new { x.Code, x.Name })
.Select(grp => new
{
    Code = grp.Key.Code,
    Name = grp.Key.Name ?? "UNKNOWN",
    LValues = string.Join(',', grp.Select(x=>x.LValue).Distinct()), 
    Qty = g.Sum(x => x.Qty)
});

I switched from query syntax to method to better show what is done in the DB and what is done in memory but you can use either syntax through out.