.net LINQ to SQL 左外连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/700523/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ to SQL Left Outer Join
提问by Ali Kazmi
Is this query equivalent to a LEFT OUTERjoin?
此查询是否等同于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
}
回答by Amir
You don't need the into statements:
您不需要 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
And yes, the query above does indeed create a LEFT OUTER join.
是的，上面的查询确实创建了一个 LEFT OUTER 连接。
Link to a similar question that handles multiple left joins: Linq to Sql: Multiple left outer joins
链接到处理多个左连接的类似问题： Linq to Sql: Multiple left outer joins
回答by Marc Gravell
Not quite - since each "left" row in a left-outer-join will match 0-n "right" rows (in the second table), where-as yours matches only 0-1. To do a left outer join, you need SelectManyand DefaultIfEmpty, for example:
不完全 - 因为左外连接中的每个“左”行将匹配 0-n 个“右”行（在第二个表中），而您的仅匹配 0-1。要进行左外连接，您需要SelectManyand 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};
(or via the extension methods)
（或通过扩展方法）
回答by Krishnaraj Barvathaya
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
回答by mokth
I found 1 solution. if want to translate this kind of SQL (left join) into Linq Entity...
我找到了 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...
}
回答by Brian Kraemer
I'd like to add one more thing. In LINQ to SQL if your DB is properly built and your tables are related through foreign key constraints, then you do not need to do a join at all.
我还想补充一件事。在 LINQ to SQL 中，如果您的数据库构建正确并且您的表通过外键约束相关联，那么您根本不需要进行连接。
Using LINQPad I created the following LINQ query:
使用 LINQPad 我创建了以下 LINQ 查询：
//Querying from both the CustomerInfo table and OrderInfo table
from cust in CustomerInfo
where cust.CustomerID == 123456
select new {cust, cust.OrderInfo}
Which was translated to the (slightly truncated) query below
这被翻译成下面的（稍微被截断的）查询
-- 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]
Notice the LEFT OUTER JOINabove.
注意LEFT OUTER JOIN以上。
回答by Andreas Schütz
Take care of performance:
注意性能：
I experienced that at least with EF Corethe different answers given here might result in different performance. I'm aware that the OP asked about Linq to SQL, but it seems to me that the same questions occur also with EF Core.
我的经验是，至少对于EF Core，此处给出的不同答案可能会导致不同的性能。我知道 OP 询问了 Linq to SQL，但在我看来，EF Core 也会出现同样的问题。
In a specific case I had to handle, the (syntactically nicer) suggestion by Marc Gravell resulted in left joins inside a cross apply -- similarly to what Mike U described -- which had the result that the estimated costs for this specific query were two times as high compared to a query with no cross joins. The server execution times differed by a factor of 3. [1]
在我必须处理的特定情况下，Marc Gravell 的（语法上更好的）建议导致交叉应用内的左连接——类似于 Mike U 所描述的——结果是这个特定查询的估计成本是两个与没有交叉连接的查询相比要高出数倍。服务器执行时间相差 3倍。[1]
The solution by Marc Gravell resulted in a query without cross joins.
Marc Gravell 的解决方案导致查询没有交叉连接。
Context:I essentially needed to perform two left joins on two tables each of which again required a join to another table. Furthermore, there I had to specify other where-conditions on the tables on which I needed to apply the left join. In addition, I had two inner joins on the main table.
上下文：我基本上需要在两个表上执行两个左连接，每个都需要连接到另一个表。此外，我必须在需要应用左连接的表上指定其他 where-condition。此外，我在主表上有两个内部联接。
Estimated operator costs:
预计运营商成本：
- with cross apply: 0.2534
- without cross apply: 0.0991.
- 交叉应用：0.2534
- 没有交叉应用：0.0991。
Server execution times in ms (queries executed 10 times; measured using SET STATISTICS TIME ON):
服务器执行时间以毫秒为单位（查询执行 10 次；使用 SET STATISTICS TIME ON 测量）：
- with cross apply: 5, 6, 6, 6, 6, 6, 6, 6, 6, 6
- without cross apply: 2, 2, 2, 2, 2, 2, 2, 2, 2, 2
- 交叉应用：5, 6, 6, 6, 6, 6, 6, 6, 6, 6
- 没有交叉应用：2, 2, 2, 2, 2, 2, 2, 2, 2, 2
(The very first run was slower for both queries; seems that something is cached.)
（两个查询的第一次运行都较慢；似乎缓存了一些东西。）
Table sizes:
桌子尺寸：
- main table: 87 rows,
- first table for left join: 179 rows;
- second table for left join: 7 rows.
- 主表：87 行，
- 左连接的第一个表：179 行；
- 左连接的第二个表：7 行。
EF Core version: 2.2.1.
EF 核心版本：2.2.1。
SQL Server version: MS SQL Server 2017 - 14... (on Windows 10).
SQL Server 版本：MS SQL Server 2017 - 14...（在 Windows 10 上）。
All relevant tables had indexes on the primary keys only.
所有相关的表都只在主键上有索引。
My conclusion: it's always recommended to look at the generated SQL since it can really differ.
我的结论：始终建议查看生成的 SQL，因为它确实可能有所不同。
[1] Interestingly enough, when setting the 'Client statistics' in MS SQL Server Management Studio on, I could see an opposite trend; namely that last run of the solution without cross apply took more than 1s. I suppose that something was going wrong here - maybe with my setup.
[1] 有趣的是，当在 MS SQL Server Management Studio 中设置“客户端统计”时，我可以看到相反的趋势；即没有交叉应用的解决方案的最后一次运行需要超过 1 秒。我想这里出了点问题 - 也许是我的设置。
