Finding minimum time from record and splitting it in LINQ

We have a database of swimmers with their times. To create a ranking we want to get the fastest time of each athlete.

var rankings = (  
    from r in _db.Results  
        orderby r.swimtime  
        group r by r.athleteid into rg  
        select new  
        {
            AthleteId = rg.Key,  
            FirstName = rg.Min(f2 => f2.Athlete.firstname),  
            Swimtime = rg.Min(f8 => f8.swimtime),  
            hours = rg.Min(f9 => f9.swimtime.Hours),  
            minutes = rg.Min(f10 => ("00" + f10.swimtime.Minutes.ToString()).Substring(("00" + f10.swimtime.Minutes.ToString()).Length - 2)),    // to get 2 digits in minutes  
            seconds = rg.Min(f11 => ("00" + f11.swimtime.Seconds.ToString()).Substring(("00" + f11.swimtime.Seconds.ToString()).Length - 2)),    // to get 2 digits in seconds  
            milliseconds = rg.Min(f12 => (f12.swimtime.Milliseconds.ToString() + "00").Substring(0, 2)),    // because miliseconds are not always filled  
        }
    );

Now the ranking is created correctly, however the time shown is not.

I know what the problem is, but don't know how to fix it:

In the database we have a swimmer that has 2 times : 00:01:02:10 (1min2sec10) and 00:00:56:95 (56sec95)

The result we get is the minim开发者_如何学编程um for the minutes (=00), the minimum for the seconds (=02) and the minimum for the milliseconds (=10)

Resulting in a time of 00:00:02:10.

What we should get is the hours,minutes,seconds and milliseconds of the fastest time (=00:00:56:95)

Anyone any ideas on how to fix this ?

---

This should do the trick:

from result in db.Results
group result by result.AthleteId into g
let bestResult = (
    from athleteResult in g
    orderby athleteResult.SwimTime
    select athleteResult).First()
orderby bestResult.SwimTime
select new
{
    AthleteId = bestResult.Athlete.Id,
    FirstName = bestResult.Athlete.FirstName,  
    BestTime = bestResult.SwimTime,  
}

The query fetches the best result from a group (all results from a single athelete), orders by that result, and uses that result to populate the final result.

---

Stay away from calling .First on groups, as that can cause automatic requerying due to the difference between LINQ's group (key and elements) vs SQL's group (key and aggregates).

Instead, get the minSwimTime once and let it be.

var rankings =
  from r in _db.Results
  group r by r.athleteid into rg
  let minSwimTime = rg.Min(x => x.swimtime)
  select new
  {
    AthleteId = rg.Key,
    FirstName = rg.Min(f2 => f2.Athlete.firstname),
    Swimtime = minSwimTime,
    hours = minSwimTime.Hours,
    minutes = ("00" + minSwimTime.Minutes.ToString()).Substring(("00" + minSwimTime.Minutes.ToString()).Length - 2),    // to get 2 digits in minutes
    seconds = ("00" + minSwimTime.Seconds.ToString()).Substring(("00" + minSwimTime.Seconds.ToString()).Length - 2),    // to get 2 digits in seconds
    milliseconds = minSwimTime.Milliseconds.ToString() + "00").Substring(0, 2),    // because miliseconds are not always filled
  };

Also - don't do string formatting in the database. Database servers have better things to do than turn datetimes into text.