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

问题描述

我有一个左外连接（如下图）返回结果符合预期。我需要从正确表中的结果限制为第一个打击。我可以这样做，不知怎的？目前，我得到两个表中的每个记录的结果，我只希望看到一个结果从表中左侧（项目）不管我有多少结果在右表（照片）。

  VAR的查询=从我在db.items
                加入db.photos p
                在i.id等于p.item_id到tempPhoto
                从TP在tempPhoto.DefaultIfEmpty（）
                排序依据i.date下降
                新选择
                {
                    ITEMNAME = i.name，
                    ITEMID = i.id，
                    ID = i.id，
                    PHOTOID = tp.PhotoID.ToString（）
                };
    GridView1.DataSource =查询;
    GridView1.DataBind（）;
 

解决方案

这将做的工作适合你。

 从我db.items
让P = db.photos.Where（P2 =＆GT; i.id == p2.item_id）.FirstOrDefault（）
排序依据i.date下降
新选择
{
  ITEMNAME = i.name，
  ITEMID = i.id，
  ID = i.id，
  PHOTOID = P == NULL？空：p.PhotoID.ToString（）;
}
 

当我创造它针对我自己的模式，我得到这个SQL（和不使用名称和投影第二ID列）。

  SELECT [T0]。[ID] AS [ID]，CONVERT（的NVarChar，（
    SELECT [T2]。[PHOTOID]
    FROM（
        SELECT TOP（1）[T1]。[PHOTOID]
        FROM [DBO]。[组图] AS [T1]
        WHERE [T1]。[ITEM_ID] =（T0]。[ID]）
        ）AS [T2]
    ））AS [PHOTOID]
FROM [DBO]。[项目] AS [T0]
ORDER BY [T0]。[ID] DESC
 

当我问这个计划，它表明，子查询是实现了这个连接：

 ＆LT; RelOp LogicalOp =左外部联接PhysicalOp =嵌套循环＆GT;
 

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

解决方案

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

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屋！