Convert SQL Sub Query to In to Linq Lambda

How do I convert the following SQL statement into Lambda Expression or Linq Que开发者_运维问答ry?

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  

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;

This results in a CROSS JOIN in the generated SQL

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;

This results in a INNER JOIN in the SQL

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

You could use a let statement to select the first answer per QuestionID group:

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

EDIT: Linq2Sql translates the above query into a N+1 database calls. This query gets translated to just one SQL query:

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

Makes me wonder in what way Linq2Sql is supposed to be simpler than SQL.

Try to use this query:

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

I just checked the query in profiler and it produces single SQL query (the ugly one):

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