LINQ merge multiple lists to one list

Question

Let's assume I have multiple db tables, I'll just represent them as lists for convenience:

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

I'm querying against EntitySource which contains references to multiple collections that I want to query against and map to MergedList class that contains two properties, Id of the entity source and one collection containing all merged collections of the EntitySource.

What I want to achieve is query for only id's and map them to single list of integers.

Something like this:

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

But apparently the above statement is not working, expression could not be parsed.

Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side.

I'm using Entity Framework Core v6.0

Marillin

Looks like you forgot to mention EF Core and it's version. – Aspersorium

It's EF Core 6.0 – Marillin

Looks like you're missing some parentheses there – Turki

This is just a mock up of the expression I am using, I probably forgot some parentheses accidentally here. – Marillin

Surely the Where should be before the Select. – Web

You're making vague approximations that turn your question into nonsense. That query seems to suggest that you have one parent entity that has three types of child entities but you don't mention anything about a parent in the question. Please provide a FULL and CLEAR explanation of the problem. – Web

I have modified the question, I'm querying against EntitySource which contains references to multiple collections that I want to query against and map to MergedList class that contains two properties, Id of the entity source and one collection containing all merged collections of the EntitySource. Also I have removed .Where() completely as it is not relevant to the problem. – Marillin

We need MergedList and Entity_1, Entity_2 and Entity_3 definitions... or remake the code for a better understanding please – Presurmise

Answer 1

You can combine them on the client side.

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

Aspersorium

This is nice, but is it possible to do it in a single expressions, similar to what I've already started. It suits my needs better. – Marillin

It is almost most performant variant that i can suggest. Other queries will be slow even we make them translatable to SQL. – Aspersorium

Answer 2

Given your class:

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

The simplest way to do this would probably be a Union if you only need unique values.

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

Using my DbContext with code like this:

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 generated looks like this:

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

You could also do it like this if you for some reason actually want duplicates:

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

I tested this using my DbContext like this:

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

and this is the query generated with expected results:

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)

Using Concat like this:

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

Or like this:

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

Would cause an exception like this for EF Core 7:

System.InvalidOperationException: 'Unable to translate a collection subquery in a projection since either parent or the subquery doesn't project necessary information required to uniquely identify it and correctly generate results on the client side. This can happen when trying to correlate on keyless entity type. This can also happen for some cases of projection before 'Distinct' or some shapes of grouping key in case of 'GroupBy'. These should either contain all key properties of the entity that the operation is applied on, or only contain simple property access expressions.'

Even if you try to project the list chances are quite high you would end up with something like this for 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.'

See this post for more info:

https://github.com/dotnet/efcore/issues/26703#issuecomment-981843751

Portraiture