SQL Group By column value in LINQ

Question

Hello all, I am trying to translate this:

SELECT IDNum, Max(Convert(varchar(20), (TimeOut - TimeIn), 108)) 
       FROM IO_Times Group by IDNum 
       order by Max(Convert(varchar(20), (TimeOut - TimeIn), 108)) desc";

Into LINQ in C#. The above returns a list of the maximum value of (TimeOut-TimeIn) that corresponds to each unique IDNum (There are multiple TimeOut-TimeIn values for every IDNum).

Here is what I am trying now:

from IO_Time in db.IO_Times
orderby SqlMethods.DateDiffMinute(IO_Time.TimeIn, IO_Time.TimeOut) descending
group IO_Time by IO_Time.IDNum into list
select new
{
  ID = list.Key,
  Time = list

});

This returns the correct IDs in the correct order, but the "list" contains everything in the database entry (which makes sense). I have also tried group by IDNum and Time, but then I get unique time entries and multiple IDs returned.

I could do a foreach loop and manually sort it all out, but I would really rather not do that.

Any suggestions?

Answer 1

select new
{
  ID = list.Key,
  Time = list.Max(i => <operation>)

});

Is that what you want to do?

Answer 2

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

And since you want to use Lambdas more often:

var results = 
    dbo.IO_Times
        .OrderByDescending(i => SqlMethods.DateDiffMinutes(i.TimeIn, i.TimeOut))
        .GroupBy(i => i.IDNum)
        .Select(g => new 
            { 
                ID = g.Key, 
                Time = g.Max(t => t.TimeOut - t.TimeIn)
            });