C
C#•3mo ago
A Ka Sup

Left join 2 or more tables with 1-n relationship

guys, i have tableA, B with relation 1 A - n B. so i can get the data from A with leftjoin like this 
context.TableA
       .GroupJoin(context.TableB,
           a => a.Id,
           b => b.IdA,
           (a, b) => new { a, b = b.DefaultIfEmpty() }
       )
       .SelectMany(s => s.b, (s, b) => {
           Stuff = b.Stuff,
       })
context.TableA
       .GroupJoin(context.TableB,
           a => a.Id,
           b => b.IdA,
           (a, b) => new { a, b = b.DefaultIfEmpty() }
       )
       .SelectMany(s => s.b, (s, b) => {
           Stuff = b.Stuff,
       })
but now i have other tableC, with relation 1 A - n C. so how do i get the data from A, with left join to B and C? the result i want 
{ Stuff = b.Stuff, Stuff2 = c.Stuff2 }
{ Stuff = b.Stuff, Stuff2 = c.Stuff2 }
4 Replies
millibyte
millibyte•3mo ago
If you’re using Entity Framework — the idiomatic way is to use navigation properties to implicitly do the joins 
context.TableC.Select(c => new { c.Stuff2, c.A.B.Stuff })
context.TableC.Select(c => new { c.Stuff2, c.A.B.Stuff })
Going up from the many side to the 1 side of a relationship like this is generally a lot easier to express than drilling down in LINQ. In SQL, it’s the same JOINs either way
millibyte
millibyte•3mo ago
A Ka Sup
A Ka SupOP•3mo ago
yeah, get the result from C side is easier than from A side, but the thing is A - B has 1 - n relationship, same with A - C
millibyte
millibyte•3mo ago
I misunderstood. What do you actually want the data coming out to look like? Do you want A x 1 result rows like { IEnumerable<B.Stuff>, IEnumerable<C.Stuff2> }, or do you want to actually join every B against every C with a shared A?

Did you find this page helpful?