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
– Vsevolod Goloviznin, Commented Jan 17, 2015 at 20:16

Jon · Accepted Answer · 2015-01-17 20:25:52Z

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

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.
@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).
@Ben yes, and executing on the server is what you always want to do if you can make it happen.
Would you be able to share a scenario where I would use .AsEnumerable? If you know a good article, please share. Appreciate your help.

Community · Accepted Answer · 2017-05-23 12:01:02Z

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

Collectives™ on Stack Overflow

c# Understanding AsEnumerable in LINQ query

2 Answers 2

6 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Linked

Related