I have three tables. Two of them join equally but one will need to join with a left. I'm finding a lot of code to do this in linq but between two tables only.
Here is the SQL code that I'm trying to re-code within LINQ.
SELECT PRSN.NAME
,CO.NAME
,PROD.NAME
FROM PERSON PRSN
INNER JOIN COMPANY CO ON PRSN.PERSON_ID = CO.PERSON_ID
LEFT OUTER JOIN PRODUCT PROD ON PROD.PERSON_ID = PROD.PERSON_ID;
Here is a snippet of LINQ code that I'm using as a base. I'm just not able to piece together the third table (product in my sample SQL) via LINQ and with a left outer join. The sample is between two tables. Than开发者_运维问答ks for any tips.
var leftOuterJoinQuery =
from category in categories
join prod in products on category.ID equals prod.CategoryID into prodGroup
from item in prodGroup.DefaultIfEmpty(new Product{Name = String.Empty, CategoryID = 0})
select new { CatName = category.Name, ProdName = item.Name };
Michael
How about this:
var loj = (from prsn in db.People
join co in db.Companies on prsn.Person_ID equals co.Person_ID
join prod in db.Products on prsn.Person_ID equals prod.Person_ID into prods
from x in prods.DefaultIfEmpty()
select new { Person = prsn.NAME, Company = co.NAME, Product = x.NAME })
EDIT: if you want to do a left outer join on all tables, you can do it like this:
var loj = (from prsn in db.People
join co in db.Companies on prsn.Person_ID equals co.Person_ID into comps
from y in comps.DefaultIfEmpty()
join prod in db.Products on prsn.Person_ID equals prod.Person_ID into prods
from x in prods.DefaultIfEmpty()
select new { Person = prsn.NAME, Company = y.NAME, Product = x.NAME })
Taken from another Stackoverflow thread somewhere, there's a more legible way to do this:
var loj = (from prsn in db.People
from co in db.Companies.Where(co => co.Person_ID == prsn.Person_ID).DefaultIfEmpty()
from prod in db.Products.Where(prod => prod.Person_ID == prsn.Person_ID).DefaultIfEmpty()
select new { Person = prsn.NAME, Company = co.NAME, Product = prod.NAME })
This uses a mix of linq query syntax and lambda syntax to what (I believe is) the best result. There's no copious re-aliasing of identifiers, and it's the most concise way to do this that I've seen.
精彩评论