21

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

BizApps
  • 6,048
  • 9
  • 40
  • 62
MdeVera
  • 647
  • 5
  • 8
  • 22

2 Answers2

46

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 })
Beno
  • 4,633
  • 1
  • 27
  • 26
  • 1
    no worries, mate. I had the same problem recently... took a while to figure it out. happy to share :) – Beno May 28 '11 at 10:39
  • @Beno what do you do if you want to use left outer join on each table instead one inner and one outer join? – mko Nov 12 '12 at 21:29
  • If second JOIN contain to db.Companies, how to refer to "co"? – Fame th Nov 01 '17 at 06:28
6

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.

bwerks
  • 8,651
  • 14
  • 68
  • 100