Linq Lambda to SQL Sub Query Conversion

c# entity-framework linq linq-to-entities sql

Question

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  

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

1
3
4/3/2011 6:40:00 PM

Accepted Answer

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

5
4/3/2011 1:50:00 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow