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

Question

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();

Hint: Hover the `Select` in VS editor to see the type of the result in both cases and you'll get the answer. Another hint: change `Func func` to `Expression> func`. — Ivan Stoev, Aug 10 '16 at 11:46
@IvanStoev that was it, once I changed it to an expression it's back to aggregating in SQL. Can you please add this as an answer and I'll mark it as the answer. — Naeem Sarfraz, Aug 10 '16 at 13:24
You are welcome. Thank you for the kind proposition, really appreciate! But I wouldn't commented if I was planning to put an answer. Please feel free to accept the Harald's answer. — Ivan Stoev, Aug 10 '16 at 13:36

score 2 · Answer 1 · edited May 23 '17 at 12:08

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

The last link you provided explain it very well. Sweet! – jpgrassi Aug 10 '16 at 15:18 — jpgrassi, Aug 10 '16 at 15:18

score 0 · Accepted Answer · answered Aug 23 '16 at 12:30

0

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

answered Aug 23 '16 at 12:30

Naeem Sarfraz

7,360
5
37
63

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

2 Answers2