Geek asks and answers

Linq query with two joins in internal selection in EF

I am trying to create a Linq query for EF that combines 2 values โ€‹โ€‹from an internal selection. Below you will find an SQL query that does the trick; trying to do this in Linq is much more complicated.

I am using a POCO object and would like the request to return a list rather than an anonymous type. Is this possible from 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
+3
source share
1 answer

You can do this with Where or 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;
+2
source

More articles:


All Articles