如何将LINQ左外连接限制为一行

Question

我有一个左外连接(如下)，可以返回预期的结果。我需要将“右”表中的结果限制为“第一个”命中。我能做到吗？目前，我会得到两个表中每条记录的结果，但不管右表(photos)中有多少结果，我只想看到左表(items)中的一个结果。

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

- ahmed

Answer 1

这会为您完成工作。

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

当我根据自己的模型生成它（在投影中没有名称和第二个 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

当我要求计划时，它显示该子查询是通过这个连接实现的：

<RelOp LogicalOp="Left Outer Join" PhysicalOp="Nested Loops">

- David B

---

我喜欢这个解决方案的优雅性，但是我认为由于子查询的原因，这可能会创建一个更难被 SQL 优化的查询。 - Nick Berardi

有没有可能把SQL代码发出来，我很想看看。 - Nick Berardi

不幸的是，它生成子查询，而不是LEFT JOIN...这对性能不利。 - vk_muse

@vk_muse：“当我要求计划时，它显示子查询是通过这个连接实现的。” - Amy B

这正是我在寻找的，但我仍需要获取子查询（在此情况下为照片）按其中一个字段排序的ordery by case语句。你能帮我吗？因为当我执行.Where(p2 => i.id == p2.item_id).OrderBy(p2 => p2.FILELD == VALUE ? 1 : 0).FirstOrDefault()时，会出现错误... - John Mathison

Answer 2

你想要做的是对表格进行分组。最好的方法是:

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

---

编辑：我是Amy B。我之所以这样做，是因为Nick让我这样做。Nick，请根据你认为合适的方式修改或删除此部分。

生成的SQL语句非常庞大。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

执行计划显示了三个左连接，其中至少有一个是微不足道的，并且不应计算（它带来的是零）。这里有足够的复杂性，我无法明确指出任何效率问题。它可能运行得很好。

- Nick Berardi

---

这可能不是 Linq to SQL 的答案，但它确实解决了 Entity Framework 的问题，并产生了类似的 SQL。现在正在寻找更好的解决方案。 - Daniel Harvey

Answer 3

你可以尝试这样做： ```

你的代码

```

var q = from c in
          (from s in args
           select s).First()
        select c;

关于查询的最后一部分。不确定它是否有效或者会产生什么奇怪的SQL语句 :)

- leppie

Answer 4

使用内部查询。在没有照片的情况下包括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
    };

如果您需要特别处理没有照片的情况，可以省略DefaultIfEmpty并改用FirstOrDefault/LastOrDefault。

- Edward Brey