c# - LINQ Query for outerjoining Parent and child (nested) tables -

i have 3 levels of tables "directors", "managers" , "leaders" 1 many relationship between them.

i bring rows directors, managers , leaders separately along children relationship.(maybe leftouterjoin)

for example directors table have

directorid directorname

     1            director1  

managers

manager id director id manager name

  1              1                 manager 1   2              1                 manager 2 

leaders

leader id manager id leader name

  1                 1              leader 1     2                 1              leader 2 

i want query return

dirid directorname mgrid managername ldrid leadername

1 director1
1 director1 1 manager1
1 director1 1 manager1 1 leader1
1 director1 1 manager1 2 leader1
1 director1 2 manager2

my linq query results 3 rows

dirid directorname mgrid managername ldrid leadername

1 director1 1 manager1 1 leader1
1 director1 1 manager1 2 leader1
1 director1 2 manager2

             var query = d in db.directors                         join m in db.managers on d.directorid equals m.directorid directormgrgroup                         dmgr in directormgrgroup.defaultifempty()                         join l in db.leaders on dmgr.managerid equals l.managerid mgrleadergroup                         mlgr in mgrleadergroup.defaultifempty() 

it feels small tweak. can't right. can please me? thank you.

it feels small tweak

well, not exactly. result format in sql produced called rollup query not supported natively linq. groupjoin gather information needed, flattening way want quite hard may see below (combinations of singe item array concatenating group join groups). check out:

var query = db.directors.groupjoin(db.managers, director => director.directorid, manager => manager.directorid, (director, managergroup) => new {     id = director.directorid,     name = director.directorname,     managers = managergroup.groupjoin(db.leaders, manager => manager.managerid, leader => leader.managerid, (manager, leadergroup) => new     {         id = manager.managerid,         name = manager.managername,         leaders = leadergroup.select(leader => new         {             id = leader.leaderid,             name = leader.leadername         })     }) }) .selectmany(director =>       new[] { new     {         dirid = director.id, directorname = director.name,         mgrid = (int?)null, managername = (string)null,         ldrid = (int?)null, leadername = (string)null     } }     .concat(director.managers.selectmany(manager =>          new[] { new         {             dirid = director.id, directorname = director.name,             mgrid = (int?)manager.id, managername = manager.name,             ldrid = (int?)null, leadername = (string)null         } }         .concat(manager.leaders.select(leader => new         {             dirid = director.id, directorname = director.name,             mgrid = (int?)manager.id, managername = manager.name,             ldrid = (int?)leader.id, leadername = leader.name         }))     )) );