c# - Converting a SQL join into LINQ with EF with filtering -
i'm learning entity framework , attempting convert existing sql query linq query, struggling convert it.
select taskitems.description,taskitemresponses.iscompleted,taskitemresponses.userid,taskitemresponses.notes tasklists left join taskitems on tasklists.tasklistid = taskitems.tasklistid left join taskitemresponses on taskitemresponses.taskitemid = taskitems.taskitemid , taskitemresponses.userid = '1'
this works fine me, brings following data, showing list of tasks, , if user has responded of them, if they've completed , notes they've added.
description iscompleted userid notes task null null null task b null null null task c null null null task d 1 1 i've done now. task e null null null
but when i'm trying convert linq query within c# can't figure out syntax, far i've got
var query = t in dbcontext.tasklist join ti in dbcontext.taskitem on t.tasklistid equals ti.tasklistid join tr in dbcontext.taskitemresponse on ti.taskitemid equals tr.taskitemid trj x in trj.defaultifempty() x.userid == userid select t;
but isn't filtering particular userid, , instead returns
description iscompleted userid notes task 0 2 great task b 1 2 okay task c 1 3 nope task d 1 1 i've done now. task e 0 5 ok.
the correct way convert sql left join
right side filter linq apply right side filter before join
operator.
here linq equivalent of sql query (of course can correct field names/types if needed):
var query = t in dbcontext.tasklist join ti in dbcontext.taskitem on t.tasklistid equals ti.tasklistid tij ti in tij.defaultifempty() // left join join tr in dbcontext.taskitemresponse.where(x => x.userid == userid) // filter on ti.taskitemid equals tr.taskitemid trj tr in trj.defaultifempty() // left join select new { ti.description, iscompleted = (bool?)tr.iscompleted, userid = (int?)tr.userid, tr.notes };
Comments
Post a Comment