CODEWITHSUNDEEP
c#linq
Benk
Benk

Reputation: 1312

c# Understanding AsEnumerable in LINQ query

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

Upvotes: 4

Views: 2574

Answers (2)

ocuenca
ocuenca

Reputation: 39376

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

Upvotes: 2

Jon
Jon

Reputation: 437704

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

Upvotes: 4

Related Questions