CODEWITHSUNDEEP
entity-frameworkentity-framework-6
Naeem Sarfraz
Naeem Sarfraz

Reputation: 7430

Why is Entity Framework choosing in-memory aggregation instead of sql?

I started with this query:

var results = 
    context.Set<Record>()
        .GroupBy(r => r.Number)
        .Select(g => new { g.Key, Count = g.Count() })
        .ToList();

Whilst refactoring I extracted this query into a method so the GroupBy takes a delegate. This change has resulted in the aggregation now being carried out in memory.

Is this behaviour by design? I've just lost out on benefits of aggregating in SQL.

Here's an example of the in-memory aggregation:

Func<Record, int> func = r => r.Number;
var results = 
    context.Set<Record>()
        .GroupBy(func)
        .Select(g => new { g.Key, Count = g.Count() })
        .ToList();

Upvotes: 2

Views: 52

Answers (2)

Naeem Sarfraz
Naeem Sarfraz

Reputation: 7430

Very simply changing Func<Record, int> func to Expression<Func<Record, int>> func fixed my issue.

Upvotes: 0

Harald Coppoolse
Harald Coppoolse

Reputation: 30474

Be aware of the differences between IQueryable and IEnumerable.

See the most voted answer in What is the difference between IQueryable and IEnumerable

The major difference is that IQueryable keeps an Expression and a Provider on which the expression will be executed. If you want the query to be executed by the provider (probably in SQL), you'll have to make sure your results object is an IQueryable.

However, the Provider does not know your delegates. Those delegates are not on your provider, but locally in memory. Hence the Provider does not know how to do this, hence it has to be done locally.

Understanding IQueryable and IEnumerable helped me to understand what happens when you use IQueryables

Upvotes: 2

Related Questions