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

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