none
Only join on one record of a one-to-many relationship RRS feed

  • Question

  • I did some searching, but couldn't find what I'm looking for.  I have two tables that have a one-to-many relationship.  They're setup like this:

     

    PRSLoad

    LoadID

    LoadName

    <other fields>

     

    PRSLoadHistory

    LoadHistoryID

    LoadID

    LoadHistoryBegin

    LoadHistoryUserID

    <other fields>

     

    the Load table will have multiple records in the LoadHistory table, joined on LoadID.  What I am trying to do is return one row per LoadID that selects the data from Load and latest/newest record from History (based on the datetime field LoadHistoryBegin) for that particular LoadID.  In addition, I'd like to do the equivalent of an OUTER JOIN so that if a Load has no History, it will still be included in the result set.  Here is what I've come up so far (doesn't satisfy my "outer join" requirement), but I get the error: "The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

     

    Code Snippet

    e.Result = from h in db.PRSLoadHistories

    group h by h.LoadID into g

    let latest = g.OrderByDescending(h => h.LoadHistoryBegin).First()

    join l in db.PRSLoads on latest.LoadID equals l.LoadID

    select new { l.LoadID, l.LoadName, last.LoadHistoryBegin, last.LoadHistoryUserID };

     

    Wednesday, July 9, 2008 2:19 PM

Answers

  • I do this kind of thing all the time.  You should be able to do something like this:

     

    from l in db.PRSLoads

    join h in db.PRSLoadHistories

      on new {l.LoadID, LoadHistoryBegin =

          db.PRSLoadHistories.Where(o => o.LoadID == l.LoadID).Max(o => o.LoadHistoryBegin)}

      equals new {h.LoadID, h.LoadHistoryBegin} into z

    from h in z.DefaultIfEmpty()   // this does the left outer join

    select new {l, h}  // or some other such selection

     

    Let me know how this approach goes.

     

    -Larry

     

     

    Wednesday, July 9, 2008 4:43 PM

All replies

  • I do this kind of thing all the time.  You should be able to do something like this:

     

    from l in db.PRSLoads

    join h in db.PRSLoadHistories

      on new {l.LoadID, LoadHistoryBegin =

          db.PRSLoadHistories.Where(o => o.LoadID == l.LoadID).Max(o => o.LoadHistoryBegin)}

      equals new {h.LoadID, h.LoadHistoryBegin} into z

    from h in z.DefaultIfEmpty()   // this does the left outer join

    select new {l, h}  // or some other such selection

     

    Let me know how this approach goes.

     

    -Larry

     

     

    Wednesday, July 9, 2008 4:43 PM
  • Thanks!  I had a similar query (sans DefaultIfEmpty) working right before I saw your post, but this one does the trick and is much cleaner.

    Wednesday, July 9, 2008 5:55 PM
  • Glad to hear it.  One thing you might want to consider is putting an index on LoadHistoryBegin (if you don't already have it).  If PRSLoadHistory has a lot of records within a particular LoadID, it will help with performance for this particular query.

     

    -Larry

     

    Wednesday, July 9, 2008 6:07 PM
  • Hi there. I have a similar issue, but I can't quite make your example fit my needs. Here is what I have so far:

    from

     

     

    p in PersonContactInfo
    join r in Report_Hists
    on new {p.PersonContactInfoId, LastRunDate =
         Report_Hists.Where(o => o.ReportFor_PersonContactInfoId == p.PersonContactInfoId).Max(o => o.RunDate)}
    equals new {r.ReportFor_PersonContactInfoId,r.RunDate} into z
    from r in z.DefaultIfEmpty() // this does the left outer join
    select new {p, r} // or some other such selection

    And I get this error: The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'GroupJoin'.

    The only thing I see that is different is that foreign key column is not named the same.

    o.ReportFor_PersonContactInfoId == p.PersonContactInfoId

    Would that cause the issue?

    Tuesday, October 6, 2009 9:17 PM