LINQ to SQL 左外连接

Question

这个查询语句与 LEFT OUTER join 相等吗？

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

- Ali Kazmi

Answer 1

您不需要使用"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

是的，上面的查询确实创建了一个左外连接。

类似的问题链接，处理多个左外连接： Linq to Sql: Multiple left outer joins

- Amir

---

我知道@Marc Gravvel的回答是可行的，但我更喜欢这种方法，因为在我看来，它更符合左连接的本质。 - llaughlin

非常好的答案。在谷歌搜索了超过5个小时，这是唯一能得到带有左连接的SQL的方法。 - Faisal Mq

非常感谢你...我整个下午都在寻找解决方案，而你的代码解决了我的问题（而且感觉很自然）。真希望我能多次点赞。 - Jim

@Jim 谢谢 :-) 我很高兴开发人员仍然从这个答案中获得了收益。我完全同意 DefaultIfEmpty() 比使用 into 语句更自然。 - Amir

对于其他像我一样找到这篇文章的人，这里有一个提示：这将导致在CROSS APPLY内部进行LEFT OUTER JOIN，这意味着如果在连接的右侧存在多个匹配项，则会出现重复。尽管Marc Gravell的解决方案不够“美观”，但给了我正确的SQL输出和结果集。 - Mike U

Answer 2

并不完全正确 - 因为左外连接中的每个“左”行将与第二个表中的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 == null ? -1 : x.OrderID };   

(或者通过扩展方法)

- Marc Gravell

---

有人能解释一下这个疯狂的语法是如何工作的吗？我看不出来这些关键词中任何一个是如何神奇地使它成为左连接。"into sr" 是什么意思？有时候 Linq 让我感到沮丧 :)。 - Joe Phillips

@JoePhillips 我有丰富的SQL经验，但尝试学习LINQ就像在泥潭中前行一样。我同意这绝对是疯狂的。 - Nick.McDermaid

@MarcGravell：实际上我们正在使用LINQ查询来处理我们的MVC RAZOR项目，相比之下我更擅长SQL。我在编写LINQ查询时遇到了困难，而在SQL中我可以轻松编写此查询。由于我们项目中的所有查询都是LINQ，因此我正在尝试使用LINQ编写。 - Vishal I P

@VishalIPatil 那么...为什么要这样做呢？几乎每个LINQ工具都包括运行手写SQL的功能。为什么不直接这样做呢？ - Marc Gravell

@JoePhillips，我认为在LINQ术语中，“into sq”被称为分组。它是连接操作的虚拟结果，由所有其他连接对象组成的集合，但似乎也具有右侧连接的属性，例如“x.Customer”和“x.Order”，还有“x.AllPropertiesOfOrder”（因为他们正在使用“from x in sr.DefaultIfEmpty（）”遍历“sr”）。如果连接产生null，则“DefaultIfEmpty”只会初始化默认的“Order”。 - Paul-Sebastian Manole

Answer 3

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

- Krishnaraj Barvathaya

---

不错的尝试，但看起来OP正在使用C#。VB语法有些奇怪不同。 - Levitikon

Answer 4

我可以为您提供翻译帮助。以下是需要翻译的内容:

我找到了一种解决方案。如果想将这种类型的SQL（左连接）转换为Linq Entity ...

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

- mokth

---

请参见此评论，Linq-to-SQL实体不支持DefaultIfEmpty。 - T.J. Crowder

Answer 5

我想再补充一点。在LINQ to SQL中，如果您的数据库构建正确，并且表通过外键约束相关联，则根本不需要进行联接操作。

使用LINQPad，我创建了以下LINQ查询：

//Querying from both the CustomerInfo table and OrderInfo table
from cust in CustomerInfo
where cust.CustomerID == 123456
select new {cust, cust.OrderInfo}

这被翻译成了以下略微缩短的查询

 -- Region Parameters
 DECLARE @p0 Int = 123456
-- EndRegion
SELECT [t0].[CustomerID], [t0].[AlternateCustomerID],  [t1].[OrderID], [t1].[OnlineOrderID], (
    SELECT COUNT(*)
    FROM [OrderInfo] AS [t2]
    WHERE [t2].[CustomerID] = [t0].[CustomerID]
    ) AS [value]
FROM [CustomerInfo] AS [t0]
LEFT OUTER JOIN [OrderInfo] AS [t1] ON [t1].[CustomerID] = [t0].[CustomerID]
WHERE [t0].[CustomerID] = @p0
ORDER BY [t0].[CustomerID], [t1].[OrderID]

请注意上面的LEFT OUTER JOIN。

- Brian Kraemer

Answer 6

关注性能：

我发现至少在EF Core中，这里给出的不同答案可能导致不同的性能。我知道OP问的是Linq to SQL，但我觉得在EF Core中也会遇到相同的问题。

在我处理的一个特定情况中，Marc Gravell提出的（语法更好）建议导致了左连接内部交叉应用--类似于Mike U所描述的情况--这导致此特定查询的预估成本比没有交叉连接的查询高两倍。服务器执行时间相差3倍。 [1]

Marc Gravell的解决方案导致查询没有交叉连接。

上下文：我基本上需要在两个表上执行两个左连接，每个表都需要再次连接另一个表。此外，我还必须在需要应用左连接的表上指定其他where条件。 此外，我在主表上有两个inner join。

预估操作符成本：

• 使用交叉应用：0.2534
• 不使用交叉应用：0.0991。

以毫秒为单位的服务器执行时间（查询执行了10次；使用SET STATISTICS TIME ON进行测量）：

• 使用cross apply：5、6、6、6、6、6、6、6、6、6
• 不使用cross apply：2、2、2、2、2、2、2、2、2、2

（两个查询的第一次运行都比较慢；似乎有些东西被缓存了。）

表大小：

• 主表：87行，
• 左连接的第一个表：179行；
• 左连接的第二个表：7行。

EF Core版本：2.2.1。

SQL Server版本： MS SQL Server 2017 - 14...（在Windows 10上）。

所有相关表只有主键索引。

我的结论：始终建议查看生成的SQL，因为它可能会非常不同。

---

[1] 有趣的是，当我在MS SQL Server Management Studio中将“客户端统计信息”设置为开启时，我可以看到一个相反的趋势；也就是说，在没有使用cross apply的情况下运行解决方案的最后一次运行超过了1秒。我想这里可能出现了一些问题 - 可能是我的设置出了问题。

- Andreas Schütz