c# Understanding AsEnumerable in LINQ query

Question

Below code:

var mids = _db.Members
     .GroupBy(m => new { m.MemberID, m.CreatedDate })
     .Where(m => m.All(s => s.Status == 1) && m.Key.CreatedDate.Date == DateTime.Today)
     .Select(m=>m);

I get a run-time error: The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported

When I add _db.Members.AsEnumerable() to the first line it works.

My understanding was that .AsEnumerable() forces the query to execute on the client side. So in the above code AsEnumerable operator break query into 2 parts select on server side and rest on the client side(group by,where).

Can someone validate if my understanding is correct? and why the code failed without .AsEnumerable()?

Yes, you're correct. As a side not you can't compare dates like that using linq to sql syntax — Vsevolod Goloviznin, Jan 17 '15 at 20:16

Jon · Accepted Answer · 2015-01-17T20:25:52.030

4

You understanding is correct. After calling AsEnumerable the data source can no longer be queried and it falls back to a simplistic "give me everything" mode, which means all the data is transferred to the client and any further operations are done locally.

That's also the reason why the query doesn't work as written: in order for it to work, all the expressions you use in LINQ methods must be translatable to whatever language is understood by your data source -- and since it is the query provider's responsibility to do the translation, you will also be constrained by whatever it is programmed to support.

In this specific case (assuming EF) the query can be fixed to work in queryable mode by manually substituting property accesses to the canonical function TruncateTime:

.Where(m => m.All(s => s.Status == 1) 
    && EntityFunctions.TruncateTime(m.Key.CreatedDate) ==
       EntityFunctions.TruncateTime(CurrentDateTime()))

edited Jan 17 '15 at 20:25

answered Jan 17 '15 at 20:18

Jon

428,835
81
738
806

Isn't it LINQ2Entities? It's mentioned in Ben's post. – Arkadiusz Kałkus Jan 17 '15 at 20:36
Thanks Jon for the explanation. So if using EF, you would recommend to use your approach since the condition would be executed on the data source, it would execute on server side? correct? and If using AsEnumerable it returns all the information and would return additional information..more data that might not useful. – Benk Jan 17 '15 at 20:51
@Landeeyo `EntityFunctions` is for any "Entity" based system which Linq2Entitys and EF both are. All that matters is your provider knows how to understand the function calls (which the default provider does). – Scott Chamberlain Jan 17 '15 at 21:09
@Ben yes, and executing on the server is what you always want to do if you can make it happen. – Jon Jan 17 '15 at 21:11
Would you be able to share a scenario where I would use .AsEnumerable? If you know a good article, please share. Appreciate your help. – Benk Jan 17 '15 at 21:20
@Ben: You would use `AsEnumerable` if you wanted to e.g. run your own arbitrary code at some point. For example, `db.Members.Select(m => MyOwnCode(m))` is obviously never going to be translatable into a store expression -- `MyOwnCode` could do *anything* and that's obviously not translatable to SQL. In this case you have to use `AsEnumerable` to tell LINQ "I know you can't do this, so don't even try". – Jon Jan 17 '15 at 21:26

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

AsEnumerable is frequently used when you want to switch to LINQ to Objects, mostly because you want to use some functionalities that Linq to Entities doesn't support. When you use AsEnumerable(), you are casting to IEnumerable<T> and it basically moves processing from the Data Source (which has all the data and indexes etc.) to your application. In other words, if you don't use AsEnumerable(), LINQ to Entities will go to translate your query to SQL and it will be executed remotely in your Data Source. If you want to see more info about this subject, I recommend you read this post.

As Jon said, Entity Framework provides a set of functions for working with dates that can be converted directly to SQL, and these are in the EntityFunctions namespace. These map to so-called "canonical functions" which just means that there are 1:1 translations to SQL

c# Understanding AsEnumerable in LINQ query

2 Answers2