将 SQL 子查询转换为 In to Linq Lambda

Question

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

以下查询获取每个问题的最新答案。或者换句话说，为每个问题提供最新的答案。

这也将由实体框架执行。

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

这里是使用 Inner Join 查看上一个查询的另一种方法，

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() 陷阱。

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;

有关此替代表设计的更多讨论，请参阅评论线索...

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 更简单。

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