What Is the Effect of Asenumerable() on a Linq Entity

What is the effect of AsEnumerable() on a LINQ Entity?

AsEnumerable() is effectively a cast to IEnumerable<T>, which makes member resolution find members of Enumerable instead of Queryable. It's usually used when you want to force part of a query to run as SQL (or similar), and the remainder to run using LINQ to Objects.

See my Edulinq blog post on it for more information.

Now you've actually got two calls to AsEnumerable. I can see how removing the first but not the second could cause problems, but have you tried removing both?

var results = from p in pollcards
              join s in spoils
                 on new { Ocr = p.OCR, fileName = p.PrintFilename } 
                 equals new { Ocr = s.seq, fileName = s.inputFileName }
              where p.Version == null
              orderby s.fileOrdering, s.seq
              select new ReportSpoilsEntity
              {
                  seq = s.seq,
                  fileOrdering = s.fileOrdering,
                  inputFileName = s.inputFileName,
                  Ocr = p.OCR,
                  ElectorName = p.ElectorName
              };

Understanding .AsEnumerable() in LINQ to SQL

The reason for AsEnumerable is to

AsEnumerable(TSource)(IEnumerable(TSource))
can be used to choose between query
implementations when a sequence
implements IEnumerable(T) but also has
a different set of public query
methods available

So when you were calling the Where method before, you were calling a different Where method from the IEnumerable.Where. That Where statement was for LINQ to convert to SQL, the new Where is the IEnumerable one that takes an IEnumerable, enumerates it and yields the matching items. Which explains why you see the different SQL being generated. The table will be taken in full from the database before the Where extension will be applied in your second version of the code. This could create a serious bottle neck, because the entire table has to be in memory, or worse the entire table would have to travel between servers. Allow SQL server to execute the Where and do what it does best.

What's the difference(s) between .ToList(), .AsEnumerable(), AsQueryable()?

There is a lot to say about this. Let me focus on AsEnumerable and AsQueryable and mention ToList() along the way.

What do these methods do?

AsEnumerable and AsQueryable cast or convert to IEnumerable or IQueryable, respectively. I say cast or convert with a reason:

When the source object already implements the target interface, the source object itself is returned but cast to the target interface. In other words: the type is not changed, but the compile-time type is.
When the source object does not implement the target interface, the source object is converted into an object that implements the target interface. So both the type and the compile-time type are changed.

Let me show this with some examples. I've got this little method that reports the compile-time type and the actual type of an object (courtesy Jon Skeet):

void ReportTypeProperties<T>(T obj)
{
    Console.WriteLine("Compile-time type: {0}", typeof(T).Name);
    Console.WriteLine("Actual type: {0}", obj.GetType().Name);
}

Let's try an arbitrary linq-to-sql Table<T>, which implements IQueryable:

ReportTypeProperties(context.Observations);
ReportTypeProperties(context.Observations.AsEnumerable());
ReportTypeProperties(context.Observations.AsQueryable());

The result:

Compile-time type: Table`1
Actual type: Table`1

Compile-time type: IEnumerable`1
Actual type: Table`1

Compile-time type: IQueryable`1
Actual type: Table`1

You see that the table class itself is always returned, but its representation changes.

Now an object that implements IEnumerable, not IQueryable:

var ints = new[] { 1, 2 };
ReportTypeProperties(ints);
ReportTypeProperties(ints.AsEnumerable());
ReportTypeProperties(ints.AsQueryable());

The results:

Compile-time type: Int32[]
Actual type: Int32[]

Compile-time type: IEnumerable`1
Actual type: Int32[]

Compile-time type: IQueryable`1
Actual type: EnumerableQuery`1

There it is. AsQueryable() has converted the array into an EnumerableQuery, which "represents an IEnumerable<T> collection as an IQueryable<T> data source." (MSDN).

What's the use?

AsEnumerable is frequently used to switch from any IQueryable implementation to LINQ to objects (L2O), mostly because the former does not support functions that L2O has. For more details see What is the effect of AsEnumerable() on a LINQ Entity?.

For example, in an Entity Framework query we can only use a restricted number of methods. So if, for example, we need to use one of our own methods in a query we would typically write something like

var query = context.Observations.Select(o => o.Id)
                   .AsEnumerable().Select(x => MySuperSmartMethod(x))

ToList – which converts an IEnumerable<T> to a List<T> – is often used for this purpose as well. The advantage of using AsEnumerable vs. ToList is that AsEnumerable does not execute the query. AsEnumerable preserves deferred execution and does not build an often useless intermediate list.

On the other hand, when forced execution of a LINQ query is desired, ToList can be a way to do that.

AsQueryable can be used to make an enumerable collection accept expressions in LINQ statements. See here for more details: Do i really need use AsQueryable() on collection?.

Note on substance abuse!

AsEnumerable works like a drug. It's a quick fix, but at a cost and it doesn't address the underlying problem.

In many Stack Overflow answers, I see people applying AsEnumerable to fix just about any problem with unsupported methods in LINQ expressions. But the price isn't always clear. For instance, if you do this:

context.MyLongWideTable // A table with many records and columns
       .Where(x => x.Type == "type")
       .Select(x => new { x.Name, x.CreateDate })

...everything is neatly translated into a SQL statement that filters (Where) and projects (Select). That is, both the length and the width, respectively, of the SQL result set are reduced.

Now suppose users only want to see the date part of CreateDate. In Entity Framework you'll quickly discover that...

.Select(x => new { x.Name, x.CreateDate.Date })

...is not supported (at the time of writing). Ah, fortunately there's the AsEnumerable fix:

context.MyLongWideTable.AsEnumerable()
       .Where(x => x.Type == "type")
       .Select(x => new { x.Name, x.CreateDate.Date })

Sure, it runs, probably. But it pulls the entire table into memory and then applies the filter and the projections. Well, most people are smart enough to do the Where first:

context.MyLongWideTable
       .Where(x => x.Type == "type").AsEnumerable()
       .Select(x => new { x.Name, x.CreateDate.Date })

But still all columns are fetched first and the projection is done in memory.

The real fix is:

context.MyLongWideTable
       .Where(x => x.Type == "type")
       .Select(x => new { x.Name, DbFunctions.TruncateTime(x.CreateDate) })

(But that requires just a little bit more knowledge...)

What do these methods NOT do?

Restore IQueryable capabilities

Now an important caveat. When you do

context.Observations.AsEnumerable()
                    .AsQueryable()

you will end up with the source object represented as IQueryable. (Because both methods only cast and don't convert).

But when you do

context.Observations.AsEnumerable().Select(x => x)
                    .AsQueryable()

what will the result be?

The Select produces a WhereSelectEnumerableIterator. This is an internal .Net class that implements IEnumerable, not IQueryable. So a conversion to another type has taken place and the subsequent AsQueryable can never return the original source anymore.

The implication of this is that using AsQueryable is not a way to magically inject a query provider with its specific features into an enumerable. Suppose you do

var query = context.Observations.Select(o => o.Id)
                   .AsEnumerable().Select(x => x.ToString())
                   .AsQueryable()
                   .Where(...)

The where condition will never be translated into SQL. AsEnumerable() followed by LINQ statements definitively cuts the connection with entity framework query provider.

I deliberately show this example because I've seen questions here where people for instance try to 'inject' Include capabilities into a collection by calling AsQueryable. It compiles and runs, but it does nothing because the underlying object does not have an Include implementation anymore.

Execute

Both AsQueryable and AsEnumerable don't execute (or enumerate) the source object. They only change their type or representation. Both involved interfaces, IQueryable and IEnumerable, are nothing but "an enumeration waiting to happen". They are not executed before they're forced to do so, for example, as mentioned above, by calling ToList().

That means that executing an IEnumerable obtained by calling AsEnumerable on an IQueryable object, will execute the underlying IQueryable. A subsequent execution of the IEnumerable will again execute the IQueryable. Which may be very expensive.

Specific Implementations

So far, this was only about the Queryable.AsQueryable and Enumerable.AsEnumerable extension methods. But of course anybody can write instance methods or extension methods with the same names (and functions).

In fact, a common example of a specific AsEnumerable extension method is DataTableExtensions.AsEnumerable. DataTable does not implement IQueryable or IEnumerable, so the regular extension methods don't apply.

LINQ 'AsEnumerable' and the Entity Framework

I'd do

var ids = from o in dbcontext.Orders
          where o.ID==1
          select new { ID = o.ID }; 

var names = from i in ids.AsEnumerable()
            select new Order { Name = GetName(i.ID) };

i.e. do as much querying as possible in the database, and then only perform the ID-to-name transformation in C#.

Am I misunderstanding LINQ to SQL .AsEnumerable()?

Calling AsEnumerable() does not execute the query, enumerating it does.

IQueryable is the interface that allows LINQ to SQL to perform its magic. IQueryable implements IEnumerable so when you call AsEnumerable(), you are changing the extension-methods being called from there on, ie from the IQueryable-methods to the IEnumerable-methods (ie changing from LINQ to SQL to LINQ to Objects in this particular case). But you are not executing the actual query, just changing how it is going to be executed in its entirety.

To force query execution, you must call ToList().

Significane of using AsEnumerable() in query to take anonomous value in to view model

Your first select is querying the database and returns IQueryable<T>. .AsEnumerable() make it IEnumerable<T>. IQueryable<T> extends IEnumerable<T> so whatever you can do with IEnumerable<T>, you can also do with IQueryable<T>.

Another big difference is that IEnumerable<T> fetches the entire table and applies the query on that afterwards, whereas IQueryable<T> only fetches the filtered data.

Note that AsEnumerable() does not materialise the query yet. You often use .ToList() to materialize your query so other non sql statements can be performed on the result set as per this example

You can simplify your code to

var data = (from temp in context.UserDetails select temp)
  .Select(d => new UserDetailsModel
  {
    Fullname = d.fullName,
    Email = d.Email
  }).ToList());

Why Entity Framewok method .AsEnumerable() returns nothing?

Looks to me there is an error serializing your object to either xml or json.
This is usually caused by a circular reference in your data object. For example your client references account and account references client. if this is the case the serializer will keep serializing the objects until it runs out of memory

to solve this there are a few options.

Only return the data you really need by transforming it into a new object (viewmodel).
Disable lazy loading for your query, this will prevent loading of your account / doc objects for that query see (Entity Framework: How to disable lazy loading for specific query?).
Make the serializer ignore the properties that cause the self referencing loop. (for serializing to json use the attribute [JsonIgnore])

Internal Implementation of AsEnumerable() in LINQ

The input argument is already known to have the type IEnumerable<TSource>. Why would it need to cast anything? Casting the objects to the type TSource would have no effect, since they're already guaranteed to be of that type (or a more derived type).
You can't assign a value of type IEnumerable<char> to a variable of type List<char>. I think you're thinking in reverse here; List<char> derives from IEnumerable<char>, not the other way around. This has nothing to do with List<T> being invariant. IEnumerable<T> is covariant (to be more precise, the type parameter T is covariant), which gives us this situation:
```
IEnumerable enumerable = Enumerable.Empty<string>(); // Allowed
IEnumerable<string> genericEnumerable = enumerable; // Not allowed
```
Again, IList<char> inherits from IEnumerable<char>, not the other way around. You can do this:
```
IList<char> content1 = "testString".ToList();
IEnumerable<char> content2 = content1;
```
What you're asking for doesn't make sense, I'm afraid, and it's nothing to do with covariance. The fact that IEnumerable<T> is covariant means that you're allowed to do this:
```
IEnumerable<object> asObject = new List<string>() { "test" };
```
But List<T> is invariant, so you can't do this:
```
List<object> asObject = new List<string>() { "test" };
```

What Is the Effect of Asenumerable() on a Linq Entity