Answered by:
LINQ and "WITH AS" clause
Question

Here is an example of SQL query, which works great but I can't figure out how to do the same thing in LINQ. Can someone help me?
WITH
Results AS
(
SELECT R.ClientId, R.Store, R.FileName, ROW_NUMBER() OVER(PARTITION BY R.ApplicationId ORDER BY R.DateCreated DESC) AS row
FROM Reports R
JOIN Rules PR On PR.ApplicationId = R.ApplicationId AND PR.Sequence = R.Sequence
WHERE R.ClientId = 1
)
SELECT ClientId, Store, [FileName] FROM Results WHERE Results.row = 1
Answers

Hi,
It seems you need to find the latest report record for each Application. Here's a blog post that describes how such queries can be written in LINQ using the let keyword: LINQ: How to get the latest/last record with a group by clause .
from r in Reports join pr in Rules on new {r.ApplicationID, r.Sequence} equals new {pr.ApplicationID, pr.Sequence} where r.ClientId ==1 group r by r.ApplicationID into grp let MaxDatePerApplication = grp.Max ( g=>g.DateCreated ) from r in grp where r.DateCreated == MaxDatePerApplication select r
Hope that helps.
Regards,
Syed Mehroz Alam
My Blog  My Articles Marked as answer by Artw07 Monday, February 15, 2010 3:06 PM
All replies

Hi,
It seems you need to find the latest report record for each Application. Here's a blog post that describes how such queries can be written in LINQ using the let keyword: LINQ: How to get the latest/last record with a group by clause .
from r in Reports join pr in Rules on new {r.ApplicationID, r.Sequence} equals new {pr.ApplicationID, pr.Sequence} where r.ClientId ==1 group r by r.ApplicationID into grp let MaxDatePerApplication = grp.Max ( g=>g.DateCreated ) from r in grp where r.DateCreated == MaxDatePerApplication select r
Hope that helps.
Regards,
Syed Mehroz Alam
My Blog  My Articles Marked as answer by Artw07 Monday, February 15, 2010 3:06 PM
