LINQ Join table then groupby master table

Question

Hello all, I am trying to output a table of IDNums + Name + Max(TimeOut-TimeIn). I only want the maximum time change value for each ID since there are multiple time entries per ID. I have that so far. However, the Name associated with the ID is in a seperate table. Here is what I tried:

from IO_Time in db.IO_Times
join NameTable in db.NamesTable on IO_Time.IDNum equals NameTable.IDNum
orderby SqlMethods.DateDiffMinute(IO_Time.TimeIn, IO_Time.TimeOut) descending
group IO_Time by IO_Time.IDNum into list
select new
{
  ID = list.Key,
  Name = NameTable.Name
  Time = list.Max(t=> t.TimeOut-t.TimeIn)

});

But of course, NameTable.Name does not exist in that context. How can I fix this?

Answer 1

I think this should work... basically it's changing the group key to include the name as well... which should be fine, given the join you've already got.

from IO_Time in db.IO_Times
join NameTable in db.NamesTable on IO_Time.IDNum equals NameTable.IDNum
orderby SqlMethods.DateDiffMinute(IO_Time.TimeIn, IO_Time.TimeOut) descending
group IO_Time by new { IO_Time.IDNum, NameTable.Name } into list
select new
{
  ID = list.Key.IDNum,
  Name = list.Key.Name
  Time = list.Max(t=> t.TimeOut-t.TimeIn)    
});