如何将 LINQ 左外连接限制为一行 [英] How to limit a LINQ left outer join to one row

问题描述

我有一个左外连接(如下)按预期返回结果.我需要将正确"表的结果限制为第一个"命中.我能以某种方式做到这一点吗?目前，我对两个表中的每条记录都得到一个结果，我只想从左边的表(项目)中看到一个结果，无论我在右边的表中有多少结果(照片).

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();

推荐答案

这将为您完成工作.

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

当我根据自己的模型生成它时得到了这个 sql(并且在投影中没有名称和第二个 id 列).

I got this sql when I generated it against my own model (and without the name and second id columns in the projection).

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">

这篇关于如何将 LINQ 左外连接限制为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！