连接三个表并使用左侧外部连接

Question

我有三个表。他们中的两人平等加入，但其中一人需要加入左翼。我在linq中找到了很多这样做的代码，但只在两个表之间。

以下是我试图在LINQ中重新编写的SQL代码。

   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;

下面是一个LINQ代码片段，我正在使用它作为基础。我只是无法通过LINQ和左外部联接将第三个表（示例SQL中的产品）拼凑在一起。样本位于两张表之间。谢谢你的提示。

   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

这个怎么样：

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

编辑：如果你想对所有表进行左外联接，你可以这样做：

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

从另一个Stackoverflow线程中提取，有一种更清晰的方法：

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

这混合使用了linq查询语法和lambda语法，得到了（我认为）最好的结果。标识符没有大量的重新别名，这是我见过的最简洁的方法。