How to get average, minimum and maximum with fewer database round-trips?

Question

In an ASP.NET Core Razor Pages application, using Entity Framework Core, I'm using the code below to populate a list with average, minimum and maximum values from a database. While this works OK with the relatively small volume of data recorded so far, I don't think it's going to scale well as the volume of data grows. Is there a more efficient way to do this in the application, or should I forget about doing it in the application code and get it from a stored procedure instead?

ServerStatsList = _context
    .PerfLog
    .Select(l => new ServerStats {
        CompanyName = l.CompanyName,
        ServerName = l.ServerName,
        Average = 0,
        Minimum = 0,
        Maximum = 0
    })
    .Distinct()
    .ToList();

foreach (ServerStats s in ServerStatsList)
{
    s.Average = _context
        .PerfLog
        .Where(l => l.CompanyName == s.CompanyName && l.ServerName == s.ServerName)
        .Average(l => l.MemoryAvailableMBytes);
    s.Minimum = _context
        .PerfLog
        .Where(l => l.CompanyName == s.CompanyName && l.ServerName == s.ServerName)
        .Min(l => l.MemoryAvailableMBytes);
    s.Maximum = _context
        .PerfLog
        .Where(l => l.CompanyName == s.CompanyName && l.ServerName == s.ServerName)
        .Max(l => l.MemoryAvailableMBytes);
}

Answer 1

Try with the following:

var statsPerfLog = _context
    .PerfLog
    // group logs by the tuple [CompanyName, ServerName]
    // and select the `MemoryAvailableMBytes` from every log
    .GroupBy(l => new {l.CompanyName, l.ServerName}, l => l.MemoryAvailableMBytes)
    .Select(group => new ServerStats
    {
        CompanyName = group.Key.CompanyName,
        ServerName = group.Key.ServerName,
        Average = group.Average(),
        Minimum = group.Min(),
        Maximum = group.Max()
    })
    .ToList();