How to flatten result of 1 to Many relationship join

Question

I have a join

var result = from a in dbContext.As
   join b in dbContext.Bs on a.Id equals b.A.Id
   select { A = a, Ds = b.AList.Select(listItem => listItem.ObjectOfTypeD) }

how can I flatten the result so instead of list of Ds I get list of a single D linked with single A ?

Answer 1

You can try adding another from:

var queryable = from a in dbContext.As
   join b in dbContext.Bs on a.Id equals b.A.Id
   from c in b.Children
   select new { a, c };

Or using SelectMany:

var queryable = (from a in dbContext.As
    join b in dbContext.Bs on a.Id equals b.A.Id)
    .SelectMany(t => t.b.Children.Select(c => new {t.a, c}));

Note that if you have correctly set up the relationships between the entities in the model usually there is no need to use manual joins, so you can try something like:

dbContext.Bs
   .SelectMany(b => b.AList
        .Select(li => new { b.A, D = li.ObjectOfTypeD }));