c# - How to Left join with 2 subselects in Entity Framework Linq? -

how write query in c# entity framework?

select id, name, deliveryadress, invoiceadress company  left join (select address.companyid companyid, street + ' ' + zipcode + '     ' + city deliveryadress address  join addresstype on addresstype.addresstypeid = address.addresstypeid addresstype.name = 'delivery') sub1 on sub1.companyid = company.id left join (select address.companyid companyid, street + ' ' + zipcode + ' ' + city invoiceadress address  join addresstype on addresstype.addresstypeid = address.addresstypeid addresstype.name = 'invoice') sub2 on sub2.companyid = company.id 

in fact have 3 tables: company, address , adresstype , in query have select id , name company , 2 adresses selected typename...

first list of deliveryaddresses joining address , addresstypes

var deliveryaddresses= addr in db.addresses join (addrtype in     db.addresstypes.where(at=>at.name=="delivery")) on addr.addresstypeid equals  addrtype.addresstypeid select new {companyid=addr.companyid, deliveryaddress= addr.street + " " + zipcode + " " + city} 

next list of invoice addresses joining address , addresstypes

var invoiceaddresses= addr in db.addresses join (addrtype in     db.addresstypes.where(at=>at.name=="invoice")) on addr.addresstypeid equals  addrtype.addresstypeid select new {companyid=addr.companyid, invoiceaddress= addr.street + " " + zipcode + " " + city} 

then perform left outer join on deliveryaddresses , invoiceaddresses

var deliveryandinvoiceaddresses=from da in deliveryaddresses join ia in     invoiceaddresses on da.companyid equals ia.companyid addrgroup ag      in addrgroup.defaultifempty() select new    {companyid=da.companyid,deliveryaddress=da.deliveryaddress,invoiceaddress=  (ag==null?string.empty:ag.invoiceaddress)} 

finally perform left outer join on company , result of outer join of delivery , invoice addresses

var completelist=from c in db.companys join d in deliveryandinvoiceaddresses    on c.id equals d.companyid compgroup cg in  companygroup.defaultifempty() select new {companyid=c.id,  name=c.name,deliveryaddress=(cg==null?string.empty:cg.deliveryaddress),  invoiceaddress=(cg==null?string.empty:cg.invoiceaddress)} 

hope solves problem.