c# - From Sql to Linq-toSql -

i'm using sql server 2012 , .net 4

the sql i'd convert c# looks this:

select rd.name, rd.description, prdv.value roledetail rd      join role r on r.roleid = rd.roleid     join personrole pr on pr.roleid = r.roleid     left outer join personroledetailvalue prdv on prdv.roledetailid = rd.roledetailid pr.personid = 42 

after several attempts , time wasted, don't seem better off when started. appreciated.

i prefer method syntax, query syntax solutions more welcome too.

solutions (thanks david b's answer below)

below both working solutions. hope others can benefit...

method syntax:

var methodsyntax = db.personroles     .where(pr => pr.personid == 42)     .selectmany(pr => pr.role.roledetails)     .selectmany(rd => rd.personroledetailvalues.defaultifempty(), (rd, prdv) => new         {             name = rd.name,             description = rd.description,             value = prdv.value         }); 

query syntax:

var querysyntax = pr in db.personroles      pr.personid == 42      let r = pr.role      rd in r.roledetails      prdv in rd.personroledetailvalues.defaultifempty()      select new      {          name = rd.name,          description = rd.description,          value = prdv.value      }; 

thank you

if mapping set right, there should navigational properties between types. lets start query 1 type , include other types without specifying joined columns on , over.

from pr in datacontext.personroles pr.personid = 42   //navigation property many->one  queryable.select let r = pr.role     //navigation property one->many  queryable.selectmany rd in r.roledetails   //navigation property one->many, match null if none prdv = rd.personroledetailvalues.defaultifempty() select new {   name = rd.name,   description = rd.description,   value = prdv.value } 

and fun, method syntax terse can get:

datacontext.personroles   .where(pr => pr.personid = 42)   .selectmany(pr => pr.role.roledetails)   .selectmany(rd => rd.personroledetailvalues.defaultifempty(),     (rd, prdv) => new   {     name = rd.name,     description = rd.description,     value = prdv.value   });