Linq-to-sql One-To-Many with a max

Question

I'm having a heck of a time with this one. Might be the lack of sleep... or maybe I'm just getting dumb.

I have 2 tables:
a) Person {Key, Name, LastName}
b) LogEntry {Key, PersonKey, Log, EntryTime}

I'm trying to get a join of Person and LogEntry where LogEntry is the latest LogEntry for the given Person.

I hope I don't go "duh..." in five minutes when the answers hits me hard.

Answer 1

On a partial class for Person, create a new property similar to this:

public partial Person
{
    public LogEntry RecentLogEntry
    {
     get
     {
      return LogEntries.OrderByDescending(l => l.EntryTime).FirstOrDefault();
     }
    }
}

I believe this (or something similar) should work, though this is from memory. This, of course, relies on your relationships being present.

If you would prefer to do this when selecting your records, you could try this:

var people = from p in context.People
             select new
             {
                 Person = p,
                 LatestLogEntry = p.LogEntries.OrderByDescending(l => l.EntryTime).FirstOrDefault()
             };

Answer 2

If you have the Relations in SQLServer you should have in the class Person something like Person.LogEntries. You can do like this:

   LogEntry logEntry = 
Person.LogEntries.OrderByDescending(p => p.EntryTime).FirstOrDefault();

Then you can check if logEntry is null if not I should have the last log entry for the person.

Answer 3

If one does not have relationships setup, the accepted answer can be done by:

var r = Person
    .GroupJoin(
        LogEntry,
        o => o.Key,
        i => i.PersonKey,
        (o,i) => new {o, Entry = i.OrderByDescending(x => x.EntryTime).First()});