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!
Accepted Answer
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.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow