将SQL子查询转换为In到Linq Lambda [英] Convert SQL Sub Query to In to Linq Lambda

问题描述

如何将以下SQL语句转换为Lambda Expression或Linq Query?

How do I convert the following SQL statement into Lambda Expression or Linq Query?

以下查询获取每个问题的单个最新答案.或者用另一种方式表达，用最新的答案获得每个问题.

The following query get the single most recent Answer for each Question. Or to phrase it another way, get each Question with the newest Answer.

这也将由实体框架执行.

Also this will be execute by Entity Framework.

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

这是使用内部联接查看上一个查询的另一种方法

Here another way to look at the previous query using an 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的查询而言是次优的.

I have read that the LINQ Contains method is sub optimal for queries that access SQL.
LINQ to Sql and .Contains() trap.

推荐答案

我认为您可以使用以下方法来做到这一点:

I think you could do this using something like:

 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

This results in a CROSS JOIN in the generated SQL

此外，您可以在查询的第二部分中使用join:

Also, you could use join in the second part of the query:

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

这将导致SQL中的INNER JOIN

This results in a INNER JOIN in the SQL

注意其他情况-以上答案合理地假设AnswerID是Answers的主键-如果您碰巧有一个以(AnswerID，QuestionID)为键的表设计，那么您将需要通过AnswerID和QuestionID进行加入，例如:

Note for side cases - the above answers make the reasonable assumption that AnswerID is the primary key of Answers - if you happen to have instead a table design which is keyed on (AnswerID, QuestionID) then you will need to join by both AnswerID and QuestionID like:

 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;

有关此备用表设计的更多讨论，请参见评论线索...

See the comment trail for more discussion on this alternate table design...

这篇关于将SQL子查询转换为In到Linq Lambda的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！