Why Use Asqueryable() Instead of List()

Why use AsQueryable() instead of List()?

AsQueryable just creates a query, the instructions needed to get a list. You can make futher changes to the query later such as adding new Where clauses that get sent all the way down to the database level.

AsList returns an actual list with all the items in memory. If you add a new Where cluse to it, you don't get the fast filtering the database provides. Instead you get all the information in the list and then filter out what you don't need in the application.

So basically it comes down to waiting until the last possible momment before committing yourself.

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.

What is the purpose of AsQueryable()?

There are a few main uses.

1. As mentioned in other answers, you can use it to mock a queryable data source using an in-memory data source so that you can more easily test methods that will eventually be used on a non-enumerable based IQueryable.

2. You can write helper methods for manipulating collections that can apply to either in-memory sequences or external data sources. If you write your help methods to use IQueryable entirely you can just use AsQueryable on all enumerables to use them. This allows you to avoid writing two separate versions of very generalized helper methods.

3. It allows you to change the compile time type of a queryable to be an IQueryable, rather than some more derived type. In effect; you'd use it on an IQueryable at the same times that you'd use AsEnumerable on an IEnumerable. You might have an object that implements IQueryable but that also has an instance Select method. If that were the case, and you wanted to use the LINQ Select method, you'd need to change the compile time type of the object to IQueryable. You could just cast it, but by having an AsQueryable method you can take advantage of type inference. This is simply more convenient if the generic argument list is complex, and it is actually necessary if any of the generic arguments are anonymous types.

Do i really need use AsQueryable() on collection?

IQueryable is required/recommended for objects coming from remote source (like from database).

For in memory collections it is of no use.

AsQueryable is used when expression tree is to be constructed.

I can think of scenario where it is best fit. In your example let say you require some information from database based on student ID.

Now student is in memory collection. You need to fire database query based on student ID.

  var studentList = Students.Select(s => s.Id).AsQueryAble().Select(i => remoteDBProvider.GetInfo(i));

Any further operation on the studentList will be invoked from IQueryAble interface ( query expression), and will fetch only those records from data source, which should be returned as final query result (as long as data source, return value of remoteDBProvider.GetInfo in the example, supports QueryProvider).

Do I need to use AsQueryable() for a method that returning IQueryableType in .NET EF

If db.categories is derived from (or simply is) IQueryable<Category> then yeah, sure, you can remove it.

Should I always return IQueryable instead of IList?

Basically, you should try to reference the widest type you need. For example, if some variable is declared as List<...>, you put a constraint for the type of the values that can be assigned to it. It may happen that you need only sequential access, so it would be enough to declare the variable as IEnumerable<...> instead. That will allow you to assign the values of other types to the variable, as well as the results of LINQ operations.

If you see that your variable needs access by index, you can again declare it as IList<...> and not just List<...>, allowing other types implementing IList<...> be assigned to it.

For the function return types, it depends upon you. If you think it's important that the function returns exactly List<...>, you declare it to return exactly List<...>. If the only important thing is access to the result by index, perhaps you don't need to constrain yourself to return exactly List<...>, you may declare return type as IList<...> (but return actually an instance of List<...> in this implementation, and possibly of some another type supporting IList<...> later). Again, if you see that the only important thing about the return value of your function is that it can be enumerated (and the access by index is not needed), you should change the function return type to IEnumerable<...>, giving yourself more freedom.

Now, about AsQueriable, again it depends on your logic. If you think that possible delayed evaluation is a good thing in your case, as it may aid to avoid the unneeded calculations, or you intend to use it as a part of some another query, you use it. If you think that the results have to be "materialized", i.e., calculated at this very moment, you would better return a List<...>. You would especially need to materialize your result if the calculation later may result in a different list!

With the database a good rule of thumb is to use AsQueriable for the short-term intermediate results, but List for the "final" results which will be used within some longer time. Of course having a non-materialized query hanging around makes closing the database impossible (since at the moment of actual evaluation of the the database should be still open).

Does .AsQueryable() stop IEnumerable from pulling all into memory?

If the object that you have (as IEnumerable<T>) also implements IQueryable<T> then you may be in luck, but in the general-case: no. Except perhaps for that corner-case it is then too late, and you can no-longer "compose" the query (i.e. add a "where" condition that will be handled by the DB). Instead, what you have is LINQ-to-Objects doing what it can to look queryable. But th data will be consumed linearly from the underlying enumerable.

use of .ToList().AsQueryable()

You should never need to combine the two.

AsQueryable is there to give you lazy evaluation features, which the ToList completely negates.

So, the LINQ query is forced to be evaluated by ToList, then, the in memory list is converted to an IQueryable in order to allow... querying over it. Instead of constructing the query and only getting the results as needed.

What is the difference between my codes when I use 'dynamic' to use AsQueryable method?

AsQueryable() is an extension method and those don't work on dynamic.

Depending on what you want to do, there are several possible solutions:

1. Don't use dynamic. Instead, make Student and Teacher implement a common interface (say, IPerson) and use that:

   private IReadOnlyList<IPerson> GetPeopleData(int sex, int age)
   {
       if (sex > 30)
           return StudentRepository.GetStudent(sex, age).ToList();
       else
           return TeacherRepository.GetTeacher(sex, age).ToList();
   }
   
   …
   
   var result = GetPeopleData(sex, age);
   IQueryable<IPerson> rows = result2.AsQueryable();
   

2. Call AsQueryable() as a normal static method:

   dynamic result = GetPeopleData(sex, age);
   IQueryable rows = Queryable.AsQueryable(result);
   

BTW, checking whether sex is over 30 doesn't make any kind of sense to me. You should probably rethink that part of your design.

---

Related Topics