Joining three tables and using a left outer join

Question

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. Thanks 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

Answer 1

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 }) 

Answer 2

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.