none
Linq to SQL Max problem RRS feed

  • Question

  • Hi there.

    I am new to Linq and I am trying to write a linq statement that gives me the correct record with a max(RunDate). Here are my 2 tables:

    Table 1 - PersonContactInfo

    - PersonContactInfoId
    - LastName
    - FirstName
    etc

    Table 2 - Report_Hist
    - ReportHistId
    - ReportFor_PersonContactInfoId (FK back to PersonContactInfoId)
    - FilePath
    etc

    When a report is run for a particular person, a new record is added into the Report_Hist table that stores the FilePath of the newly created report. What I want is a linq statement that gives me back the PersonContactInfo data and the Report_Hist data for the given person with the max(RunDate). Also, the linq statement must be able to handle the fact that there may not be a corresponding record in Report_Hist.

    Here is what I have so far which gets me the correct data, but I need more related elements:

    from

     

     

    p in PersonContactInfo
    join r in Report_Hists on p.PersonContactInfoId equals r.ReportFor_PersonContactInfoId
    group new { p, r }
    by new { p.PersonContactInfoId} into combined
    select new
        {
            combined.Key.PersonContactInfoId,
            MaxRunDate = combined.Max(t => t.r.RunDate)
        }

     

     

     

    I need r.FilePath, p.LastName etc, but I cannot figure out how to get those from the select statement.

    Any help will be greatly appreciated.

    Thanks, Mike

     

    My sql query would look osmething like this:

    select

     

    pci.PersonId, r.RunDate, r.FilePath from PersonContactInfo pci left outer join

    Report_Hist r

    on pci.PersonContactInfoId = r.ReportFor_PersonContactInfoId join

     

     

    (select

    p

    .PersonContactInfoId,

     

    max(r.RunDate) as LastRunDate

     

    from PersonContactInfo p left outer join Report_Hist r on

    p

    .PersonContactInfoId = r.ReportFor_PersonContactInfoId

     

    group by p.PersonContactInfoId) sub on pci.PersonContactInfoId = sub.PersonContactInfoId and (r.RunDate = sub.LastRunDate or r.RunDate is null)

    Tuesday, October 6, 2009 9:41 PM

Answers

  • Hi

    Try this

    var jQuery = from pc in PersonContactInfo
        join r in Report_Hist on pc.PersonContactInfoId
        equals r.ReportFor_PersonContactInfoId into pcrJoin
        from pci in pcrJoin.DefaultIfEmpty()
        select new 
        { 
         Id = pcrJoin.Key, 
         pc.PersonId, 
         pci.RunDate, 
         pci.FilePath 
        };
    
    var gQuery = from p in jQuery 
                 group p.RunDate by p.Id into pGroup
                 select new { Id = pGroup.Key, LastRunDate = pGroupMax };
    
    var finalQuery = from p1 in jQuery
                     join p2 in gQuery on p1.Id equals p2.Id
                     where p1.RunDate == null || p1.RunDat == p2.LastRunDate
                     select p1
    The query will be sent to SQL server when you say finalQuery.ToList(). So it will execute in single batch processing

    Thanks and Regards,

    Vijay Pandurangan
    • Marked as answer by michaelloveusa Thursday, March 18, 2010 12:46 AM
    Friday, October 9, 2009 12:55 PM