Optimizing orderby sum in LINQ and Entity Framework

Question

Optimizing orderby sum in LINQ and Entity Framework

.net-core entity-framework-core linq

Question

I wrote a LINQ query that performs an orderby on an Entity Framework Core (.NET Core 2.0.7) database context using the Sum extension method. It works fine on a small sample database, but when running against a larger database ~100,000 entries, it becomes significantly slower and uses more CPU. I have pasted the relevant code below. Is there a way to perform the Sum faster? (it's essentially a weighted average on an arbitrary number of tuples).

var iqClientIds = (from stat in context.Set<EFClientStatistics>()
    join client in context.Clients
    on stat.ClientId equals client.ClientId
    group stat by stat.ClientId into s
    orderby s.Sum(cs => (cs.Performance * cs.TimePlayed)) / s.Sum(cs => cs.TimePlayed) descending
    select new
    {
          s.First().ClientId,
    })
    .Skip(start)
    .Take(count);

Thanks!

RaidMax

View more on Stack Overflow

NetMage · Accepted Answer · 5/29/2018 9:26:02 PM

EF Core 2 handles GroupJoin with translation to SQL and your query can be converted to use this:

var iqClientIds = (from client in context.Clients 
                   join stat in context.Set<EFClientStatistics>() on client.ClientId equals stat.ClientId into sj
                   orderby sj.Sum(s => (s.Performance * s.TimePlayed)) / sj.Sum(s => s.TimePlayed) descending
                   select sj.First().ClientId
                  )
                  .Skip(start)
                  .Take(count);

NOTE: I simplified the result (select) to not create an anonymous object for a single value.

Optimizing orderby sum in LINQ and Entity Framework

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related