c# - MVC - Joining multiple table using LINQ / lambda - Using EF -

i implementing controller , need staff members have risktypeid, selected user when click on navigation item.

here how create joins in sql

sql

select  rthg.risktypeid, sm.fullname  risktypehasgroup rthg inner join riskgroup rg on rthg.riskgroupid = rg.id inner join riskgrouphasgroupmembers rghgm on rg.id = rghgm.riskgroupid inner join groupmember gm on rghgm.groupmemberid = gm.id inner join groupmemberhasstaffmember gmhsm on gm.id = gmhsm.groupmemberid inner join staffmember sm on gmhsm.staffmemberid = sm.id  rthg.risktypeid = 1 

i’ve pulled data before using linq , lambda using simple expressions, need able make call bring same data sql outlined above, i’ve searched online can’t find similar requirement.

here controller, placed comments inside guidance

controller

public actionresult viewrisktypes(int selectedrisktypeid) {     var risktypes = _dbcontext.risktypes.tolist(); // of current items held in risktypes tables, store them list in var risktypes     var viewmodel = new list<risktypewithdetails>(); // create colletion holds instances of risktypewithdetails , pass them viewmodel     var details = new risktypewithdetails(); // create new instance of risktype details , store instance in var details      foreach (var risktype in risktypes) // loop through each item held in var risktypes     {         details.risktypes.add(new risktypesitem { id = risktype.id, description = risktype.description }); // assign each items id & description same feilds in new         // instance of risktypeitems (which property of risktypewithdetails)     }      foreach (var risktype in risktypes)  // loop through each item in risktypes     {         if (risktype.id == selectedrisktypeid) // check item id matches selectedrisktypeid value         {             //var details = new risktypewithdetails();             details.risktypedescription = risktype.description;  //assign risk type descripton risktypewithdetails risktypedescription property             details.riskdetails = _dbcontext                 .risktypehasgroups                 //.groupmembertypehasgroupmembers                 .where(r => r.risktypeid == selectedrisktypeid) // risktypeid matches selected id bring following data db                 .select(r => new riskdetails                 {                     riskgroupdescription = r.riskgroup.description,                     groupmembers = r.riskgroup.riskgrouphasgroupmembers                             .select(v => v.groupmember).tolist(),                     //staffmembers = r.riskgroup.risktypehasgroups                     //              .join(r.riskgroup.risktypehasgroups,                     //              => a.riskgroupid , b => b.riskgroup.id,                     //              (a, b) => new {a, b})                     //              .join(r.riskgroup.riskgrouphasgroupmembers,                     //              c => c.) // dosent join expect... no idea here                 }).tolist();              viewmodel.add(details); //add data retrieved viewmodel (this creates 1 item in collection)         }     }     return view(viewmodel); } 

as see want staff members match selected risktypeid. need assistance in converting above sql work within controller lambda expression

thanks in advance

you on right track commented out code! starters, linq has 2 different sytaxes: query , method chain. using method chain syntax , can unmaintainable quickly.

for instance this, query syntax it's at.

here's result:

from rhtg in _dbcontext.risktypehasgroup  rhtg.risktypeid == 1  join rg in _dbcontext.riskgroup    on rhtg.riskgroupid equals rg.id  join rghgm in _dbcontext.riskgrouphasgroupmembers    on rg.id equals rhtg.id  join gm in _dbcontext.groupmember   on rg.id equals gm.id  join gmhsm in _dbcontext.groupmemberhasstaffmember   on gm.id equals gmhsm.groupmemberid  join sm in _dbcontext.staffmember    on gmhsm.staffmemberid equals sm.id  select new  {   rhtg.risktypeid,   sm.fullname  }; 

do note, used .net conventions different variables.

here's documentation on query syntax: https://msdn.microsoft.com/en-us/library/gg509017.aspx