How to get fastest time for each Member-CodePudding

I Know how to produce a list by fastest time by Member (list displays by fastest time). I've also excluded records where time hasn't been entered as per below. But how do I display only the best time for each member? For instance the member named Trevor has the fastest 3 times, but I only want to show the fastest time for each member?

        public async Task<IActionResult> IndexFastestTimes()
        {
            var fastesttimes = await _context.MemberData.Where(x => x.Time.Minutes > 0).OrderBy(x => x.Time).ToListAsync();
            return View(fastesttimes);
        }

For Example the above will return

Trevor 24:00,
Trevor 24:05,
Trevor 24:20,
Paul 24:30,
Paul 24:40,
Tim 25:10,
Tim 25:20

but I want it to return

Trevor 24:00,
Paul 24:30,
Tim 25:10

CodePudding user response：

For first for each member, you need to use group by as (change time to your member field in group by)

 var fastesttimes = await _context.MemberData.Where(x => x.Time.Minutes > 0).GroupBy(x => x.F1, (key,g) => g.OrderBy(e => e.Time).First()).ToListAsync();

The above will first grouping based on condition then .First() will give the first entry of each group

see other options

How to get first record in each group using Linq

Linq Query Group By and Selecting First Items

CodePudding user response：

Thank you for your help @Ajay2707 you got me on the right track. I had to split it into two linq statements as below:

        public async Task<IActionResult> IndexFastestTimes()
        {
            var times = await _context.MemberData.Where(x => x.Time.Minutes > 0).OrderBy(x=>x.Time).ToListAsync();
            var result = times.GroupBy(x => x.MemberId, (key, g) => g.OrderBy(e => e.Time).First()).ToList();
            return View(result);
        }

CodePudding user response：

private class Runner
{
    public string Name { get; set; }
    public double Time { get; set; }
}

public MainWindow()
{
    InitializeComponent();
    List<Runner> runners = new()
    {
        new Runner() { Name = "Trevor", Time = 41 },
        new Runner() { Name = "Trevor", Time = 15 },
        new Runner() { Name = "Trevor", Time = 16 },
        new Runner() { Name = "Paul", Time = 22 },
        new Runner() { Name = "Paul", Time = 12 },
        new Runner() { Name = "Tim", Time = 5 },
        new Runner() { Name = "Tim", Time = 150 }
    };

    Mylist.ItemsSource = runners.Where(x => x.Time > 5).GroupBy(x => x.Name, (x, key) => key.OrderByDescending(x => x.Time).First()).OrderByDescending(x => x.Time);
}