none
Nested Linq Queries RRS feed

  • Question

  • So I am trying to write something like this:

    SELECT  s.CompanyID, 
            s
    .ShareDate,
            s
    .OutstandingShares,
            s
    .ControlBlock
    FROM
    (
        SELECT MAX
    (ShareDate) AS Sharedate,
               
    CompanyID
        FROM
    ShareInfo
        WHERE
    (ShareDate <= @filter_date)
        GROUP BY
    CompanyID
     
    ) AS si
     INNER JOIN
     tblShareInfo AS s ON s
    .ShareDate = si.Sharedate AND s.CompanyID = si.CompanyID

    Essentially this is trying to return the most recent Share Information, we keep a running history. Now I am trying to write something similar to this in LINQ.

    Here was my closest attempt:

    From a _
    In db_context.ShareInfos _
    Where a.ShareDate <= filter_date _
    Group a By a.CompanyID Into Group _
    Select CompanyID, MostRecentShareDate = Group.Max(Function(a) a.ShareDate) _
    Join b In db_context.ShareInfos On b.CompanyID Equals a.CompanyID _
    Select b.CompanyID, b.ShareDate, b.OS, b.CB()

    Unfortunately this does not compile. The part that does not compile is the line with the Join on it. The 'a.CompanyID' at the very end of the line gives the error "Name 'a' is not declared or not in current scope".  Obviously I'm not understanding the LINQ syntax somehow. Can anyone steer me in the right direction?

    Thanks.


    You are free to do what you want, but you are not free to want what you want. -- Arthur Schopenhauer
    Thursday, January 14, 2010 4:50 PM

Answers

  • Okay so looks like this needs to be done using two statements: 

    Dim MostRecentShareDates = _ 
    From s2 In query_collection.DBContext.ShareInfos _ 
    Where s2.ShareDate <= filter_date _ 
    Group s2 By s2.CompanyID Into Group _ 
    Select New With { _ 
                       .CompanyID = CompanyID, _ 
                       .MostRecentShareDate = Group.Max(Function(s3) s3.ShareDate) _ 
                    } 
    
    Return From s In query_collection.DBContext.ShareInfos _ 
           Join s1 In MostRecentShareDates On s.CompanyID Equals s1.CompanyID And s.ShareDate Equals s1.MostRecentShareDate _ 
           Select New With { _ 
                            .CompanyID = s.CompanyID, _ 
                            .ShareDate = s.ShareDate, _ 
                            .OS = s.OS, _ 
                            .CB = s.CB _ 
                           }
    I tried using the 'Let' keyword to embed the first statement into the second, but that would not compile either. Now the nice thing about this is the Linq has delayed execution, so until you traverse the collection returned by the second statement, no SQL gets generated. Linq is then smart enough to combine the two code fragments into one SQL statement, essentially exactly the same statement as I wrote in my original SQL above.
    You are free to do what you want, but you are not free to want what you want. -- Arthur Schopenhauer
    • Marked as answer by Filisophilese Friday, January 15, 2010 12:48 AM
    Thursday, January 14, 2010 11:01 PM

All replies

  • Hi fili,
    i am not quite sure why you are selecting the mostrecentsharedate because it's not used further and joining on the same table, however you can try the let clause and see if it helps.

    From a _
    In db_context.ShareInfos _
    Where a.ShareDate <= filter_date _
    Group a By a.CompanyID Into Group _
    Let Companys = Group _
    From b In Companys _
    Join c In db_context.ShareInfos _
    On b.CompanyID Equals c.CompanyID _
    Select c.CompanyID, c.ShareDate, c.OS, c.CB()

    regards,
    kashif
    • Edited by Kashif Pervaiz Thursday, January 14, 2010 8:51 PM fixed line spacing
    Thursday, January 14, 2010 8:50 PM
  • The linq is not correct (the T-SQL more accurately represents what I am trying to do).  A closer attempt might be:

    From b In query_collection.DBContext.ShareInfos _
    Let MostRecentShareDates = From a _
    		         In query_collection.DBContext.ShareInfos _
    		         Where a.ShareDate <= filter_date _
    		         Group a By a.CompanyID Into Group _
    		         Select CompanyId = CompanyID, MostRecentShareDate = Group.Max(Function(a) a.ShareDate) _
    Join c In MostRecentShareDates On b.CompanyID Equals c.CompanyID And b.ShareDate Equals c.MostRecentShareDate _
    Select b.CompanyID, b.ShareDate, b.OS, b.CB
    

    Again, this does not compile.  It says MostRecentShareDates is not declared or in current scope right after "Join c In ..."
    Thursday, January 14, 2010 10:30 PM
  • Okay so looks like this needs to be done using two statements: 

    Dim MostRecentShareDates = _ 
    From s2 In query_collection.DBContext.ShareInfos _ 
    Where s2.ShareDate <= filter_date _ 
    Group s2 By s2.CompanyID Into Group _ 
    Select New With { _ 
                       .CompanyID = CompanyID, _ 
                       .MostRecentShareDate = Group.Max(Function(s3) s3.ShareDate) _ 
                    } 
    
    Return From s In query_collection.DBContext.ShareInfos _ 
           Join s1 In MostRecentShareDates On s.CompanyID Equals s1.CompanyID And s.ShareDate Equals s1.MostRecentShareDate _ 
           Select New With { _ 
                            .CompanyID = s.CompanyID, _ 
                            .ShareDate = s.ShareDate, _ 
                            .OS = s.OS, _ 
                            .CB = s.CB _ 
                           }
    I tried using the 'Let' keyword to embed the first statement into the second, but that would not compile either. Now the nice thing about this is the Linq has delayed execution, so until you traverse the collection returned by the second statement, no SQL gets generated. Linq is then smart enough to combine the two code fragments into one SQL statement, essentially exactly the same statement as I wrote in my original SQL above.
    You are free to do what you want, but you are not free to want what you want. -- Arthur Schopenhauer
    • Marked as answer by Filisophilese Friday, January 15, 2010 12:48 AM
    Thursday, January 14, 2010 11:01 PM
  • Won't this work?

    filter_date => ShareInfo
      .Where(s => s.ShareDate <= filter_date)  
      .GroupBy(s => s.CompanyID, (s,items) => new {Sharedate = items.Max(item => item.ShareDate), s.CompanyID})
      .Join(ShareInfo, si => new {si.Sharedate, si.CompanyID}, 
            s => new {s.ShareDate, s.CompanyID}, (si, s) => new {s.CompanyID, s.ShareDate, s.OutstandingShares, s.ControlBlock})

    Regards
    Friday, January 15, 2010 12:58 PM