none
Linq outer join with Max help! RRS feed

  • Question

  • Hi there. I am trying to write a Linq query to do the following:

    I have 3 tables
    PersonContactInfo - Links a person to a company
    Person - Has lastname, first name etc
    Report_Hist - Has a record for everytime a report is run for a PersonContactInfo

    I am trying to populate a grid with a list of employees (PersonContactInfos) at a company and also show their date that a report was last run for each employee

    What I am struggling with is that a report may never have been run for a particular employee, so I still need to employee record, but the date would be null/empty

    For example:

    Let's say I have 2 employees at a company - Bob and Sally

    In the Report_Hist table, Bob has 3 records - meaning that Bob has had 3 reports run for him and Sally has 0 records - meaning Sally has never had a report run for her.

    Person:
    PersonId, Lastname, Firstname
    1, Smith, Bob
    2, Jones, Sally

    PersonContactInfo:
    PersonContactInfoId, PersonId, CompanyId
    1, 1, 1 - Ties Bob to Company #1
    2, 2, 1 - Ties Sally to Company1

    Report_Hist
    ReportHistID, PersonContactInfoId, RunDate
    1, 1, 1/1/2010 - Bob's report run in Jan
    2, 1, 2/1/2010 - Bob's report run in Feb
    3, 1, 3/1/2010 - Bob's Report run in March

    I want my results to be such:
    Bob Smith 3/1/2010 - latest report for Bob
    Sally Jones - Sally doesn't have any reports run yet, so show nothing

    This is what I have so far:

    from pci in PersonContactInfo
    from rh in Report_Hists.Where(rh => rh.ReportFor_PersonContactInfoId == pci.PersonContactInfoId).DefaultIfEmpty()
    where pci.CompanyId == 37
    group rh by rh.ReportFor_PersonContactInfoId into grp
    let MaxRunDate = grp.Max(g => g.RunDate)

    from p in grp 
    where p.RunDate == MaxRunDate || p.RunDate == null
    select p

    This gives me all of the max rundates and a null record for Sally, but I don't know how to get other fields from the person table like Firstname and Lastname and also how do I get the null record joined in correctly?

    Seems like I almost have it, but could use some help.

    Thanks, Mike

    Thursday, March 18, 2010 12:43 AM

Answers

  • Hi Mike,

    Typically, such scenarios can be tackled with an anonymous type containing all the table records , here's an example:

    from pci in PersonContactInfo
    join p in Person on pci.PersonID equals p.PersonID
    from rh in Report_Hists.Where(rh => rh.ReportFor_PersonContactInfoId == pci.PersonContactInfoId).DefaultIfEmpty()
    where pci.CompanyId == 37
    group new {p, rh} by rh.ReportFor_PersonContactInfoId into grp
    let MaxRunDate = grp.Max(g => g.rh .RunDate)

    from p in grp
    where p.rh. RunDate == MaxRunDate || p.rh. RunDate == null
    select new { p.p, p.rh }
    //or select new { p.p.FirstName, p.rh.RunDate }

    Such scenarios are discussed in this post as well:

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, March 18, 2010 5:35 AM

All replies

  • Hi Mike,

    Typically, such scenarios can be tackled with an anonymous type containing all the table records , here's an example:

    from pci in PersonContactInfo
    join p in Person on pci.PersonID equals p.PersonID
    from rh in Report_Hists.Where(rh => rh.ReportFor_PersonContactInfoId == pci.PersonContactInfoId).DefaultIfEmpty()
    where pci.CompanyId == 37
    group new {p, rh} by rh.ReportFor_PersonContactInfoId into grp
    let MaxRunDate = grp.Max(g => g.rh .RunDate)

    from p in grp
    where p.rh. RunDate == MaxRunDate || p.rh. RunDate == null
    select new { p.p, p.rh }
    //or select new { p.p.FirstName, p.rh.RunDate }

    Such scenarios are discussed in this post as well:

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Thursday, March 18, 2010 5:35 AM
  • That worked perfectly!

    Thanks so much for your help.

    Mike

    Monday, March 22, 2010 4:20 PM
  • Hi Mike,

    Typically, such scenarios can be tackled with an anonymous type containing all the table records , here's an example:

    from pci in PersonContactInfo
    join p in Person on pci.PersonID equals p.PersonID
    from rh in Report_Hists.Where(rh => rh.ReportFor_PersonContactInfoId == pci.PersonContactInfoId).DefaultIfEmpty()
    where pci.CompanyId == 37
    group new {p, rh} by rh.ReportFor_PersonContactInfoId into grp
    let MaxRunDate = grp.Max(g => g.rh .RunDate)

    from p in grp
    where p.rh. RunDate == MaxRunDate || p.rh. RunDate == null
    select new { p.p, p.rh }
    //or select new { p.p.FirstName, p.rh.RunDate }

    Such scenarios are discussed in this post as well: LINQ: How to build complex queries utilizing deferred execution and anonymous types

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles

    I am a new hand and I have learned a lot from this sample. Your blog posts are so valuable. If I have question, can I leave a message on your blog?
    Thursday, July 1, 2010 9:56 AM