none
LINQ and "WITH AS" clause RRS feed

  • 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
    Sunday, February 14, 2010 10:07 PM

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


    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
    Monday, February 15, 2010 8:00 AM

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


    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
    Monday, February 15, 2010 8:00 AM
  • Thank you very much! This is working exactly as expected.
    Monday, February 15, 2010 3:06 PM