Linq query with two joins on inner select in EF

Question

I'm trying to create a Linq query for EF that joins on 2 values from an inner select. Underneath you will find the SQL query that does the trick, things are much more tricky when trying to do so in Linq.

I use POCO object and would like the query to return the List and not an anonymous type. Is this possible with Linq to EF?

SELECT s1.* 
FROM [Statistics] s1
INNER JOIN
(
    SELECT  MAX(CreateDate) as createdate
    FROM [Statistics]
    GROUP BY UserId
) s2 
ON s1.UserId = s2.[UserId] and s1.CreateDate = s2.createdate
ORDER BY s1.Balance desc

This SQL isn't valid, because s2 only has the aggregate, createdate, and does not have a UserId column. Fix: SELECT s1.* FROM [Statistics] s1 INNER JOIN ( SELECT UserId, MAX(CreateDate) as createdate FROM [Statistics] GROUP BY UserId ) s2 ON s1.UserId = s2.UserId and s1.CreateDate = s2.createdate ORDER BY s1.Balance desc — Richard Anthony Freeman-Hein

Richard Anthony Freeman-Hein Richard Anthony Freeman-Hein · Accepted Answer · 2012-04-16T17:57:06

You can do this with a Where or a Join.

from s1 in Statistics
join s2 in (from s in Statistics group s by s.UserId into g
select new { UserId = g.Key, CreateDate = g.Max (s => s.CreateDate) })
on new { s1.UserId, s1.CreateDate } equals new { s2.UserId, s2.CreateDate }
orderby s1.Balance descending
select s1;

Or,

from s1 in Statistics
from s2 in (from s in Statistics group s by s.UserId into g
select new { UserId = g.Key, CreateDate = g.Max (s => s.CreateDate) })
where s1.UserId == s2.UserId && s1.CreateDate == s2.CreateDate
orderby s1.Balance descending
select s1;

Linq query with two joins on inner select in EF

1 Answers