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

Popular posts from this blog

Failed to execute goal org.apache.maven.plugins:maven-surefire-plugin:2.12:test (default-test) on project.Error occurred in starting fork -

windows - Debug iNetMgr.exe unhandle exception System.Management.Automation.CmdletInvocationException -

configurationsection - activeMq-5.13.3 setup configurations for wildfly 10.0.0 -