将SQL子查询转换为In运算符的Linq Lambda表达式

Question

如何将以下SQL语句转换为Lambda表达式或Linq查询？

以下查询获取每个问题的单个最新答案。或者换句话说，获取具有最新答案的每个问题。

此外，这将由Entity Framework执行。

SELECT Answers.*
FROM Answers
Where AnswerID IN
(
    SELECT Max(AnswerID) AnswerID
    FROM Answers
    GROUP BY QuestionID
)

这是使用内连接查看先前查询的另一种方式

SELECT answers.* 
FROM answers 
INNER JOIN  
(
     SELECT Max(answerID) answerID --,  QuestionSiteID
     FROM answers
     GROUP BY QuestionID 
) t ON
     answers.answerID = t.answerID  

我已经了解到，LINQ的Contains方法在访问SQL时查询效率不高。
LINQ to Sql和.Contains()的陷阱。

- Mike Barlow - BarDev

Answer 1

我认为你可以使用类似以下的方式实现：

我认为你可以使用类似以下的方式实现：

 var subQuery = from a in answers
                group a by a.QuestionID into grouping
                select new
                {
                    QuestionID = grouping.Key,
                    MaxAnswerID = grouping.Max(x => x.AnswerID)
                };

 var query = from a in answers
             from s in subQuery
             where a.AnswerID == s.MaxAnswerID
             select a;

这会导致生成的 SQL 中出现 CROSS JOIN。

---

另外，你可以在查询的第二部分使用 join：

 var query = from a in answers
             join s in subQuery on a.AnswerID equals s.MaxAnswerID
             select a;

这将在SQL中导致INNER JOIN。

---

对于特殊情况的说明 - 上述答案合理地假设AnswerID是Answers的主键 - 如果您的表设计代替为(AnswerID, QuestionID)，那么您需要通过AnswerID和QuestionID进行连接，如下：

 var subQuery = from a in answers
                group a by a.QuestionID into grouping
                select new
                {
                    QuestionID = grouping.Key,
                    MaxAnswerID = grouping.Max(x => x.AnswerID)
                };

 var query = from a in answers
             from s in subQuery
             where a.AnswerID == s.MaxAnswerID
             && a.QuestionID == s.QuestionID
             select a;

查看评论记录以获取有关此备选表设计的更多讨论...

- Stuart

---

@Stuart：根据您的评论“使用/不使用join”，我注意到您的第一个linq查询创建了一个SQL Cross Join，而您的第二个linq查询创建了一个SQL Inner Join。这两个查询都创建了相同的SQL执行计划。 - Mike Barlow - BarDev

刚刚测试了一下，对于我的4行测试表，它返回了7行。看起来你需要在底部查询中添加 where a.QuestionID == s.QuestionID。 - Andomar

@BarDeb - 有关交叉连接和内连接的观察很有趣 - 对于您的表结构，它们具有相同的执行计划 - 谢谢。 - Stuart

@Andomar - 你的表格中存在多个具有相同AnswerID的答案。你当然可以自由地给它们点踩，但我认为在OP中，“AnswerID”是Answer表的唯一标识符......因此，如果你的测试数据包含多个Answer ID为1的答案，那么我个人认为是你的测试数据出现了问题......我相信一个有效的测试数据集可能是(1,1),(2,2),(3,3),(4,1)。就像我说的，随意点踩-我知道我不能一直取悦所有人 :) - Stuart

@Stuart：看起来这个假设挺合理的，我会取消踩一下。只是当我输入第一个例子时，结果是错误的。 - Andomar

Answer 2

您可以使用let语句来选择每个QuestionID组的第一个答案：

from answer in Answers
group answer by answer.QuestionID into question
let firstAnswer = question.OrderByDescending(q => q.AnswerID).First()
select firstAnswer

编辑：Linq2Sql将上述查询转换为N+1个数据库调用。而这个查询只被转换为一个SQL查询：

from a in Answers
group a by a.QuestionID into grouping
join a2 in Answers on 
    new {AnswerID = grouping.Max(x => x.AnswerID), QuestionID = grouping.Key} 
    equals new {a2.AnswerID, a2.QuestionID}
select a2

让我想知道Linq2Sql在哪方面比SQL更简单。

- Andomar

---

应该使用 OrderByDescending 吗？ - Ladislav Mrnka

+1 对这个答案表示赞赏 - 我喜欢它的外观！我很想知道 - 它被翻译成什么样的 SQL - 是一个包含子查询的查询吗？ - Stuart

感谢您的回答。我在LinqPad中运行了该语句，并获得了预期的结果。我还注意到系统中为每个问题创建了一个SQL语句。如果我有100个问题，那么就会运行100个查询。 - Mike Barlow - BarDev

@Bar：尝试用Take(1)替换First()。 - Ladislav Mrnka

@Landislav：使用Take(1)创建相同的SQL；每个问题都需要一个查询。我不确定您希望我在哪里放置ToList()。即使我添加了ToList()，我认为它也不会有益，因为它会强制进行数据库调用。 - Mike Barlow - BarDev

Answer 3

尝试使用这个查询：

var query = from c in context.Childs
            group c by c.ParentEntityId into pc
            select pc.OrderByDescending(pcc => pcc.Id).Take(1);

我刚刚在分析器中检查了查询，它生成了单个SQL查询（丑陋的那一个）：

SELECT 
[Project3].[ParentEntityId] AS [ParentEntityId], 
[Project3].[C1] AS [C1], 
[Project3].[Id] AS [Id], 
[Project3].[Name] AS [Name], 
[Project3].[ParentEntityId1] AS [ParentEntityId1]
FROM ( SELECT 
    [Distinct1].[ParentEntityId] AS [ParentEntityId], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Name] AS [Name], 
    [Limit1].[ParentEntityId] AS [ParentEntityId1], 
    CASE WHEN ([Limit1].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM   (SELECT DISTINCT 
        [Extent1].[ParentEntityId] AS [ParentEntityId]
        FROM [dbo].[ChildEntities] AS [Extent1] ) AS [Distinct1]
    OUTER APPLY  (SELECT TOP (1) [Project2].[Id] AS [Id], [Project2].[Name] AS [Name], [Project2].[ParentEntityId] AS [ParentEntityId]
        FROM ( SELECT 
            [Extent2].[Id] AS [Id], 
            [Extent2].[Name] AS [Name], 
            [Extent2].[ParentEntityId] AS [ParentEntityId]
            FROM [dbo].[ChildEntities] AS [Extent2]
            WHERE ([Distinct1].[ParentEntityId] = [Extent2].[ParentEntityId]) OR (([Distinct1].[ParentEntityId] IS NULL) AND ([Extent2].[ParentEntityId] IS NULL))
        )  AS [Project2]
        ORDER BY [Project2].[Id] DESC ) AS [Limit1]
)  AS [Project3]
ORDER BY [Project3].[ParentEntityId] ASC, [Project3].[C1] ASC

- Ladislav Mrnka

---

还会导致N+1查询...很奇怪 - Andomar

对我来说，这个LINQ查询会返回多个查询结果，例如 SELECT TOP (1) [t0].[AnswerID], [t0].[QuestionID] FROM [Answers] AS [t0] WHERE ((@x1 IS NULL) AND ([t0].[QuestionID] IS NULL)) OR ((@x1 IS NOT NULL) AND ([t0].[QuestionID] IS NOT NULL) AND (@x1 = [t0].[QuestionID])) ORDER BY [t0].[AnswerID] DESC。 - Andomar

有趣。我正在使用Entity Framework对SQL Server 2008进行操作。你的配置是什么？我可以想象使用Linq-to-Sql会运行多个查询，但我在Linq-to-entities中没有看到这一点。 - Ladislav Mrnka

现在我看到你在回答中提到了Linq-to-Sql。这两者是不同的东西。Linq-to-Sql会优化查询，并在单个或多个查询应该运行时做出自己的决定。默认SQL提供程序的Linq-to-entities始终创建单个查询。 - Ladislav Mrnka

我正在使用LinqPad针对SQL2008R2，这可能解释了差异。一个带有top 1的outer apply也相当低效，但看起来EF至少避免了往返！ - Andomar

@Andomar：是的，这个肯定是效率低下的。我考虑过删除我的答案，但我让它存在，只是作为另一种不太好的方法。 - Ladislav Mrnka