C# 如何将 LINQ 左外连接限制为一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/492683/
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
How to limit a LINQ left outer join to one row
提问by ahmed
I have a left outer join (below) returning results as expected. I need to limit the results from the 'right' table to the 'first' hit. Can I do that somehow? Currently, I get a result for every record in both tables, I only want to see one result from the table on the left (items) no matter how many results I have in the right table (photos).
我有一个左外连接（下面）按预期返回结果。我需要将“正确”表中的结果限制为“第一个”命中。我能以某种方式做到这一点吗？目前，我为两个表中的每条记录都得到了一个结果，我只想从左侧的表（项目）中看到一个结果，无论我在右侧表中有多少结果（照片）。
var query = from i in db.items
join p in db.photos
on i.id equals p.item_id into tempPhoto
from tp in tempPhoto.DefaultIfEmpty()
orderby i.date descending
select new
{
itemName = i.name,
itemID = i.id,
id = i.id,
photoID = tp.PhotoID.ToString()
};
GridView1.DataSource = query;
GridView1.DataBind();
采纳答案by Amy B
This will do the job for you.
这将为您完成这项工作。
from i in db.items
let p = db.photos.Where(p2 => i.id == p2.item_id).FirstOrDefault()
orderby i.date descending
select new
{
itemName = i.name,
itemID = i.id,
id = i.id,
photoID = p == null ? null : p.PhotoID.ToString();
}
I got this sql when I generated it against my own model (and without the name and second id columns in the projection).
当我针对我自己的模型生成它时（并且在投影中没有名称和第二个 id 列），我得到了这个 sql。
SELECT [t0].[Id] AS [Id], CONVERT(NVarChar,(
SELECT [t2].[PhotoId]
FROM (
SELECT TOP (1) [t1].[PhotoId]
FROM [dbo].[Photos] AS [t1]
WHERE [t1].[Item_Id] = ([t0].[Id])
) AS [t2]
)) AS [PhotoId]
FROM [dbo].[Items] AS [t0]
ORDER BY [t0].[Id] DESC
When I asked for the plan, it showed that the subquery is implemented by this join:
当我询问计划时，它显示子查询是由这个连接实现的：
<RelOp LogicalOp="Left Outer Join" PhysicalOp="Nested Loops">
回答by Nick Berardi
What you want to do is group the table. The best way to do this is:
您要做的是将表格分组。最好的方法是：
var query = from i in db.items
join p in (from p in db.photos
group p by p.item_id into gp
where gp.Count() > 0
select new { item_id = g.Key, Photo = g.First() })
on i.id equals p.item_id into tempPhoto
from tp in tempPhoto.DefaultIfEmpty()
orderby i.date descending
select new
{
itemName = i.name,
itemID = i.id,
id = i.id,
photoID = tp.Photo.PhotoID.ToString()
};
Edit: This is Amy B speaking. I'm only doing this because Nick asked me to. Nick, please modify or remove this section as you feel is appropriate.
编辑：这是 Amy B 的发言。我这样做只是因为尼克要求我这样做。Nick，请根据您的需要修改或删除此部分。
The SQL generated is quite large. The int 0 (to be compared with the count) is passed in via parameter.
生成的 SQL 非常大。int 0（要与计数进行比较）通过参数传入。
SELECT [t0].X AS [id], CONVERT(NVarChar(MAX),(
SELECT [t6].Y
FROM (
SELECT TOP (1) [t5].Y
FROM [dbo].[Photos] AS [t5]
WHERE (([t4].Y IS NULL) AND ([t5].Y IS NULL)) OR (([t4].Y IS NOT NULL) AND ([t5].Y IS NOT NULL) AND ([t4].Y = [t5].Y))
) AS [t6]
)) AS [PhotoId]
FROM [dbo].[Items] AS [t0]
CROSS APPLY ((
SELECT NULL AS [EMPTY]
) AS [t1]
OUTER APPLY (
SELECT [t3].Y
FROM (
SELECT COUNT(*) AS [value], [t2].Y
FROM [dbo].[Photos] AS [t2]
GROUP BY [t2].Y
) AS [t3]
WHERE (([t0].X) = [t3].Y) AND ([t3].[value] > @p0)
) AS [t4])
ORDER BY [t0].Z DESC
The execution plan reveals three left joins. At least one is trivial and should not be counted (it brings in the zero). There is enough complexity here that I cannot clearly point to any problem for efficiency. It might run great.
执行计划显示了三个左连接。至少有一个是微不足道的，不应该被计算在内（它带来了零）。这里有足够的复杂性，我无法明确指出任何效率问题。它可能运行得很好。
回答by leppie
You could do something like:
你可以这样做：
var q = from c in
(from s in args
select s).First()
select c;
Around the last part of the query. Not sure if it will work or what kind of wack SQL it will produce :)
围绕查询的最后一部分。不确定它是否会起作用或它会产生什么样的古怪 SQL :)
回答by Edward Brey
Use an inner query. Include DefaultIfEmptyfor the case of no photo and orderbyfor the case of more than one. The following example takes the photo with the greatest id.
使用内部查询。包括DefaultIfEmpty没有照片orderby的情况和多于一张的情况。下面的例子拍摄了最大的照片id。
var query =
from i in db.items
let p = from p in db.photos where i.id == p.item_id orderby p.id select p).DefaultIfEmpty().Last()
orderby i.date descending
select new {
itemName = i.name,
itemID = i.id,
id = i.id,
photoID = p.PhotoID
};
If you need to handle the case of no photo specially, you can omit DefaultIfEmptyand use FirstOrDefault/LastOrDefaultinstead.
如果需要特殊处理没有照片的情况，可以省略DefaultIfEmpty，用FirstOrDefault/LastOrDefault代替。
