.net LINQ将多个列表合并为一个列表

sg3maiej 于 2023-04-22 发布在 .NET

关注(0)|答案(2)|浏览(277)

假设我有多个db表，为了方便起见，我将它们表示为列表：

EntitySource {
 public int Id {get; set;}
 public ICollection<Entity_1> table_1 { get; set }
 public ICollection<Entity_2> table_2 { get; set }
 public ICollection<Entity_3> table_3 { get; set }
}

Entity_1/Entity_2/Entity_3 {
   public int Id { get; set; }
   public string Name { get; set; }
}

List<Entity_1> table1 = new List<Entity_1>() {new Entity_1{Id = 1, Name = "First"}, new Entity_1{Id = 2, Name = "Second"}

List<Entity_2> table2 = new List<Entity_2>() {new Entity_2{Id = 3, Name = "First"}, new Entity_2{Id = 4, Name = "Second"}

List<Entity_3> table3 = new List<Entity_3>() {new Entity_3{Id = 5, Name = "First"}, new Entity_3{Id = 6, Name = "Second"}

我正在查询EntitySource，它包含对多个集合的引用，我想查询这些集合并Map到MergedList类，MergedList类包含两个属性，实体源的Id和一个包含EntitySource的所有合并集合的集合。
我想实现的是只对id进行查询，并将它们Map到单个整数列表。
就像这样：

var entities = await entitySource.Queryable()
          .Select(e => new MergedList()
          {
              PrincipalId = e.Id,
              CombinedIds = e.table1.Select(e => e.Id)
                .Concat(e.table2.Select(e => e.Id)
                .Concat(e.table3.Select(e => e.Id)
          })
          .ToListAsync(cancellationToken);

public class MergedList {
  public int PrincipalId {get;set;}
  public IEnumerable<int> CombinedIds {get;set;}
}

但是显然上面的语句是不起作用的，表达式无法解析。
无法转换投影中的集合子查询，因为父查询或子查询未投影唯一标识它并在客户端正确生成结果所需的必要信息。
使用Entity Framework Core v6.0

.net

来源：https://stackoverflow.com/questions/72908412/linq-merge-multiple-lists-to-one-list

2条答案

按热度按时间

k5hmc34c1#

您可以在客户端将它们组合起来。

var filtered = entitySource.Queryable()
    .Where(ent => input.Id == ent.Id);

var rawData = await 
    filtered.SelectMany(e => e.table1.Select(t => new { e.Id, SubId = t.Id } ))
    .Concat(filtered.SelectMany(e => e.table2.Select(t => new { e.Id, SubId = t.Id } ))
    .Concat(filtered.SelectMany(e => e.table3.Select(t => new { e.Id, SubId = t.Id } ))
    .ToListAsync(cancellationToken);

var entities = rawData.GroupBy(x => x.Id)
    .Select(g =>  new MergedList()
    {
        PrincipalId = g.Key,
        CombinedIds = g.Select(x => x.SubId).ToList()
    })
    .ToList();

赞(0）回复(0）举报 2023-04-22

ccrfmcuu2#

鉴于您的班级：

public class MergedList {
  public int PrincipalId {get;set;}
  public IEnumerable<int> CombinedIds {get;set;}
}

如果你只需要唯一的值，最简单的方法可能是联合。
https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.union?view=net-8.0

var entitySource = _dBcontext.EntitySource.Select(e => new MergedList()
{
    PrincipalId = e.Id,
    CombinedIds = e.table1.Select(e => e.Id).Union(e.table2.Select(e => e.Id)).Union(e.table3.Select(e => e.Id))
}).FirstOrDefault();

使用我的DbContext代码如下：

UnresolvedThreatAndCountermeasures =
    product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
        vuln => new ProductSummaryVulnerabilityDtoHelper()
        {
            AllExternalLinkIds = vuln.ExternalLinks.Select(x => x.Id).Union(vuln.Threat.ExternalLinks.Select(x => x.Id)).Union(vuln.Threat.Fork.ExternalLinks.Select(x => x.Id))
        })

生成的SQL看起来像这样：

SELECT [t0].[Id], [t1].[Id] AS [Id0], [t2].[Id] AS [Id1], [t3].[Id] AS [Id2], [t0].[ProductId]
FROM [ThreatAndCountermeasure] AS [t0]
INNER JOIN [Threats] AS [t1] ON [t0].[ThreatId] = [t1].[Id]
LEFT JOIN [Threats] AS [t2] ON [t1].[ForkId] = [t2].[Id]
OUTER APPLY (
    SELECT [e].[Id]
    FROM [ExternalLink] AS [e]
    WHERE [t0].[Id] = [e].[ThreatAndCountermeasureId]
    UNION
    SELECT [e0].[Id]
    FROM [ExternalLink] AS [e0]
    WHERE [t1].[Id] = [e0].[ThreatId]
    UNION
    SELECT [e1].[Id]
    FROM [ExternalLink] AS [e1]
    WHERE ([t2].[Id] IS NOT NULL) AND [t2].[Id] = [e1].[ThreatId]
) AS [t3]
WHERE [t0].[NotApplicable] = CAST(0 AS bit) AND ([t0].[Verified] = CAST(0 AS bit) OR [t0].[Implemented] = CAST(0 AS bit))

如果出于某种原因实际上需要重复，您也可以这样做：

public class MergedList {
  public int PrincipalId {get;set;}
  public IEnumerable<int> CombinedIds {get;set;}
}

var entitySource = _dBcontext.EntitySource.Select(e => new MergedList()
{
    PrincipalId = e.Id,
    CombinedIds = CombineLists<int>(e.table1.Select(e => e.Id), e.table2.Select(e => e.Id), e.table3.Select(e => e.Id))
}).FirstOrDefault();

private static IEnumerable<T> CombineLists<T>(params IEnumerable<T>[] lists)
{
    List<T> result = new();

    foreach (var list in lists.Where(l => l != null))
    {
        result.AddRange(list);
    }

    return result;
}

我用我的DbContext测试了这个：

UnresolvedThreatAndCountermeasures =
    product.ThreatAndCountermeasures.Where(tac => !tac.NotApplicable && !(tac.Verified && tac.Implemented)).Select(
        vuln => new ProductSummaryVulnerabilityDtoHelper()
        {
            AllExternalLinkIds = CombineLists<int>(vuln.ExternalLinks.Select(x => x.Id), vuln.Threat.ExternalLinks.Select(x => x.Id), vuln.Threat.Fork.ExternalLinks.Select(x => x.Id))
        })

这是用预期结果生成的查询：

SELECT [t0].[Id], [t1].[Id] AS [Id0], [t3].[Id] AS [Id1], [e].[Id] AS [Id2], [e0].[Id] AS [Id3], [e1].[Id] AS [Id4], [t0].[ProductId]
FROM [ThreatAndCountermeasure] AS [t0]
INNER JOIN [Threats] AS [t1] ON [t0].[ThreatId] = [t1].[Id]
LEFT JOIN [Threats] AS [t3] ON [t1].[ForkId] = [t3].[Id]
LEFT JOIN [ExternalLink] AS [e] ON [t0].[Id] = [e].[ThreatAndCountermeasureId]
LEFT JOIN [ExternalLink] AS [e0] ON [t1].[Id] = [e0].[ThreatId]
LEFT JOIN [ExternalLink] AS [e1] ON [t3].[Id] = [e1].[ThreatId]
WHERE [t0].[NotApplicable] = CAST(0 AS bit) AND ([t0].[Verified] = CAST(0 AS bit) OR [t0].[Implemented] = CAST(0 AS bit)

这样使用Concat：

var entitySource = _dBcontext.EntitySource.Select(e => new MergedList()
{
    PrincipalId = e.Id,
    CombinedIds = e.table1.Select(e => e.Id).Concat(e.table2.Select(e => e.Id)).Concat(e.table3.Select(e => e.Id))
}).FirstOrDefault();

或者像这样：

CombinedValues = e.table1.Concat(e.table2).Concat(e.table3)

会导致EF Core 7出现如下异常：
System.InvalidOperationException：'无法转换投影中的集合子查询，因为父查询或子查询未投影唯一标识集合子查询并在客户端正确生成结果所需的必要信息。尝试关联无键实体类型时可能会发生这种情况。对于“Distinct”之前的某些投影情况或“GroupBy”情况下的分组键的某些形状，也可能发生这种情况。这些属性应该包含操作所应用的实体的所有键属性，或者只包含简单的属性访问表达式。
即使你尝试投影列表的可能性也很高，你最终会得到这样的Concat：

System.InvalidOperationException: 'The LINQ expression 'MaterializeCollectionNavigation(
    Navigation: MyObject.MyList,
    subquery: DbSet<MyListObject>()
        .Where(e => EF.Property<int?>(t.Outer.Outer, "Id") != null && object.Equals(
            objA: (object)EF.Property<int?>(t.Outer.Outer, "Id"), 
            objB: (object)EF.Property<int?>(e, "MyObjectId")))
        .Where(i => EF.Property<int?>(t.Outer.Outer, "Id") != null && object.Equals(
            objA: (object)EF.Property<int?>(t.Outer.Outer, "Id"), 
            objB: (object)EF.Property<int?>(i, "MyObjectId"))))
    .AsQueryable()
    .Concat(MaterializeCollectionNavigation(
        Navigation: MyObject2.MyList,
        subquery: DbSet<MyListObject>()
            .Where(e0 => EF.Property<int?>(t.Outer.Inner, "Id") != null && object.Equals(
                objA: (object)EF.Property<int?>(t.Outer.Inner, "Id"), 
                objB: (object)EF.Property<int?>(e0, "MyObject2Id")))
            .Where(i => EF.Property<int?>(t.Outer.Inner, "Id") != null && object.Equals(
                objA: (object)EF.Property<int?>(t.Outer.Inner, "Id"), 
                objB: (object)EF.Property<int?>(i, "MyObject2Id")))))
    .Concat(t.Outer.Inner.ForkId != null ? MaterializeCollectionNavigation(
        Navigation: MyObject2.MyList,
        subquery: DbSet<MyListObject>()
            .Where(e1 => EF.Property<int?>(t.Inner, "Id") != null && object.Equals(
                objA: (object)EF.Property<int?>(t.Inner, "Id"), 
                objB: (object)EF.Property<int?>(e1, "MyObject2Id")))
            .Where(i => EF.Property<int?>(t.Inner, "Id") != null && object.Equals(
                objA: (object)EF.Property<int?>(t.Inner, "Id"), 
                objB: (object)EF.Property<int?>(i, "MyObject2Id")))) : new List<MyListObject>())' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.'

查看此帖子以获取更多信息：
https://github.com/dotnet/efcore/issues/26703#issuecomment-981843751

赞(0）回复(0）举报 2023-04-22

我来回答

.net LINQ将多个列表合并为一个列表

2条答案

相关问题

热门标签

最新问答