Let's have 3 entities implementing the same interface for ID's:

public interface IEntity
{
  int Id { get; set; }
}

public class Company : IEntity
{
  public int Id { get; set; }
  public string Brand { get; set; }
  public List Factories { get; set; }
}

public class Factory : IEntity
{
  public int Id { get; set; }
  public string Location { get; set; }
  public Company Company { get; set; }
  public List Products { get; set; }
}

public class Product : IEntity
{
  public int Id { get; set; }
  public string Name { get; set; }
  public Factory Factory { get; set; }
}

From the code above, you can see the relations:

• Company to Factory: one-to-many
• Factory to Product: one-to-many

Now, I want to get a simple list of id pairs: <CompanyId, ProductId>

For example: <3, 5>, <3, 6>, <7, 2> Means:

• The company with ID=3 provides products with ID=5 and ID=6.
• The company with ID=7 provides a product with ID=2

I can think of this LINQ expression (suppose we use EF Core):

var companiesProducts = dbContext.Set
  .SelectMany(c => c.Factories, (c, f) => new { Company = c, Factory = f })
  .SelectMany(cf => cf.Factory.Products, (cf, p) => new { CompanyId = cf.Company.Id, ProductId = p.Id })
  .ToList();

Well, I would like to make this a little bit "C# generic". In my real project, there are many entities with similar relations. I do not want to write this complex query multiple times.

If there were just one one-to-many relation, say between Factory and Product, I can imagine a method like this:

public List<(int EntityId, int SubEntityId)> GetIdPairs(
  Expression>> subEntitySelector
) where TEntity : IEntity where TSubEntity : IEntity
{
  var entitiesSubEntities = dbContext.Set
    .SelectMany(subEntitySelector, (e, s) => new { EntityId = e.Id, SubEntity = s.Id })
    .ToList();

  // Transformation from the  anonymous type to (int EntityId, int SubEntityId) tuple
  var idPairs = ...

  return idPairs;
}

For the sake of simplicity, I left out a transformation to the correct return type of the method.

Now that we have the method ready, we can call it like this:

var factoryAndProductIds = GetIdPairs(f => f.Products);

I would like to do the same for the 2-level one-to-many relationship but I really don't know how.

Any suggestions?