时间戳

首页 > 解决方案 > Linq 字符串。加入组值

c# - Linq 字符串。加入组值

问题描述

我有以下查询，我想在属性 LValues 中获取逗号分隔的 Locs 列表。

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

我正在使用 EF Core 3.1.3 和 Linq 说

InvalidOperationException: LINQ 表达式 '(GroupByShaperExpression: KeySelector: new { Code = (i.Code), Name = (i.Name), }, ElementSelector:new { l = (EntityShaperExpression: EntityType: Loc ValueBufferExpression: (ProjectionBindingExpression: l) IsNullable: False ), sci = (EntityShaperExpression: EntityType: 坐 ValueBufferExpression: (ProjectionBindingExpression: sci) IsNullable: False ), i = (EntityShaperExpression: EntityType: It ValueBufferExpression: (ProjectionBindingExpression: i) IsNullable: True ) } ) .Select(x => xlLValue)' 无法翻译。要么以可以翻译的形式重写查询，或通过插入对 AsEnumerable()、AsAsyncEnumerable()、ToList() 或 ToListAsync() 的调用显式切换到客户端评估。看 https://go.microsoft.com/fwlink/?linkid=2101038了解更多信息。

如果我使用像 g.Max(x=>xlLValue) 这样的聚合函数，它会按预期给出一个值。除了使用客户端评估之外，还有其他方法吗？但是我已经尝试过string.Join(',', g.Select(x=>x.l.LValue).ToList().Distinct())，但这也不起作用，抛出同样的错误。

更新

LValues = g.Select(x=>x.l.LValue)即使使用or也会引发相同的错误LValues = g。我的数据或组中必须有一些东西使它失败。

LValues = g.Max(x=>x.l.LValue)在 linq 中使用生成的 SQL（Sql Server）看起来像这样：

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]

我希望这有助于了解导致错误的原因。

标签： c#entity-frameworklinqentity-framework-core

解决方案

我会说您需要在客户端实现数据并连接字符串，因为 EF 无法转换string.Join为正确的 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)
  });

推荐阅读