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
Comments
Post a Comment