LINQ to SQL左外连接

此查询是否等同于LEFT OUTER连接？

//assuming that I have a parameter named 'invoiceId' of type int
from c in SupportCases
let invoice = c.Invoices.FirstOrDefault(i=> i.Id == invoiceId)
where (invoiceId == 0 || invoice != null)    
select new 
{
      Id = c.Id
      , InvoiceId = invoice == null ? 0 : invoice.Id
}

Amir.. 205

您不需要into语句:

var query = 
    from customer in dc.Customers
    from order in dc.Orders
         .Where(o => customer.CustomerId == o.CustomerId)
         .DefaultIfEmpty()
    select new { Customer = customer, Order = order } 
    //Order will be null if the left join is null

是的,上面的查询确实创建了一个LEFT OUTER连接.

链接到处理多个左连接的类似问题: Linq到Sql:多个左外连接

1> Amir..：

---

您不需要into语句:

var query = 
    from customer in dc.Customers
    from order in dc.Orders
         .Where(o => customer.CustomerId == o.CustomerId)
         .DefaultIfEmpty()
    select new { Customer = customer, Order = order } 
    //Order will be null if the left join is null

是的,上面的查询确实创建了一个LEFT OUTER连接.

链接到处理多个左连接的类似问题: Linq到Sql:多个左外连接

---

虽然我知道@Marc Gravvel的答案确实有效,但我真的更喜欢这种方法,因为IMO感觉更符合左连接应该是什么样子.

---

对于像我刚才那样发现这个的其他人来说,这会产生一个LEFT OUTER JOIN*里面*一个CROSS APPLY,这意味着如果在连接的右侧有多个匹配,你将获得重复.Marc Gravell的解决方案,虽然不是"漂亮",但却给了我正确的SQL输出和结果集.

---

@Jim谢谢:-)我很高兴开发者仍然可以从这个答案中获得好处.我完全同意DefaultIfEmpty()比使用into语句更自然.

2> Marc Gravell..：

---

不完全 - 因为左外连接中的每个"左"行将匹配0-n"右"行(在第二个表中),其中 - 您的匹配仅为0-1.做一个左外连接,你需要SelectMany和DefaultIfEmpty,例如:

var query = from c in db.Customers
            join o in db.Orders
               on c.CustomerID equals o.CustomerID into sr
            from x in sr.DefaultIfEmpty()
            select new {
               CustomerID= c.CustomerID, ContactName=c.ContactName,
               OrderID = x.OrderID == null ? -1 : x.OrderID};   

(或通过扩展方法)

---

有人可以解释这种疯狂语法是如何工作的 我没有看到这些关键字如何神奇地使它成为左连接."进入sr"有什么作用？Linq有时会让我感到沮丧:)

3> Krishnaraj B..：

---

Public Sub LinqToSqlJoin07()
Dim q = From e In db.Employees _
        Group Join o In db.Orders On e Equals o.Employee Into ords = Group _
        From o In ords.DefaultIfEmpty _
        Select New With {e.FirstName, e.LastName, .Order = o}

ObjectDumper.Write(q) End Sub

请访问http://msdn.microsoft.com/en-us/vbasic/bb737929.aspx

---

+1只是因为这是一个很好的vb.net示例

4> 小智..：

---

我找到了1个解决方案 如果想将这种SQL(左连接)翻译成Linq实体...

SQL:

SELECT * FROM [JOBBOOKING] AS [t0]
LEFT OUTER JOIN [REFTABLE] AS [t1] ON ([t0].[trxtype] = [t1].[code])
                                  AND ([t1]. [reftype] = "TRX")

LINQ:

from job in JOBBOOKINGs
join r in (from r1 in REFTABLEs where r1.Reftype=="TRX" select r1) 
          on job.Trxtype equals r.Code into join1
from j in join1.DefaultIfEmpty()
select new
{
   //cols...
}