Differences between IQueryable, List, IEnumerator?

Question

I am wondering what the difference between IQueryable, List, IEnumerator is and when I should use each one?

For instance when using Linq to SQL I would do something like this:

public List<User> GetUsers()
{
   return db.User.where(/* some query here */).ToList();
}

Now I am wondering if I should be using IQueryable instead. I am unsure of the advantages of using it over the list.

Solution 1

IQueryable<T> is intended to allow a query provider (for example, an ORM like LINQ to SQL or the Entity Framework) to use the expressions contained in a query to translate the request into another format. In other words, LINQ-to-SQL looks at the properties of the entities that you're using along with the comparisons you're making and actually creates a SQL statement to express (hopefully) an equivalent request.

IEnumerable<T> is more generic than IQueryable<T> (though all instances of IQueryable<T> implement IEnumerable<T>) and only defines a sequence. However, there are extension methods available within the Enumerable class that define some query-type operators on that interface and use ordinary code to evaluate these conditions.

List<T> is just an output format, and while it implements IEnumerable<T>, is not directly related to querying.

In other words, when you're using IQueryable<T>, you're defining an expression that gets translated into something else. Even though you're writing code, that code never gets executed, it only gets inspected and turned into something else, like an actual SQL query. Because of this, only certain things are valid within these expressions. For instance, you cannot call an ordinary function that you define from within these expressions since LINQ-to-SQL doesn't know how to turn your call into a SQL statement. Most of these restrictions are only evaluated at runtime, unfortunately.

When you use IEnumerable<T> for querying, you're using LINQ-to-Objects, which means you are writing the actual code that is used for evaluating your query or transforming the results, so there are, in general, no restrictions on what you can do. You can call other functions from within these expressions freely.

With LINQ to SQL

Going hand-in-hand with the distinction above, it's also important to bear in mind how this works out in practice. When you write a query against a data context class in LINQ to SQL, it produces an IQueryable<T>. Whatever you do against the IQueryable<T> itself is going to get turned into SQL, so your filtering and transformation will be done on the server. Whatever you do against this as an IEnumerable<T>, will be done at the application level. Sometimes this is desirable (in the case where you need to make use of a client-side code, for example), but in many cases this is unintentional.

For example, if I had a context with a Customers property representing a Customer table, and each customer has a CustomerId column, let's look at two ways to do this query:

var query = (from c in db.Customers where c.CustomerId == 5 select c).First();

This will produce SQL that queries the database for the Customer record with a CustomerId equaling 5. Something like:

select CustomerId, FirstName, LastName from Customer where CustomerId = 5

Now, what happens if we turn Customers into an IEnumerable<Customer> by using the AsEnumerable() extension method?

var query = (from c in db.Customers.AsEnumerable() where c.CustomerId == 5 select c).First();

This simple change has a serious consequence. Since we're turning Customers into an IEnumerable<Customer>, this will bring the entire table back and filter it on the client side (well, strictly speaking this will bring back every row in the table until it encounters one that fits the criteria, but the point is the same).

ToList()

Up until now, we've only talked about IQueryable and IEnumerable. This is because they are similar, complimentary interfaces. In both cases, you're defining a query; that is, you're defining where to find the data, what filters to apply, and what data to return. Both of these are queries

query = from c in db.Customers where c.CustomerId == 5 select c;
query = from c in db.Customers.AsEnumerable() where c.CustomerId == 5 select c;

Like we've talked about, the first query is using IQueryable and the second uses IEnumerable. In both cases, however, this is just a query. Defining the query doesn't actually do anything against the data source. The query is actually executed when code begins to iterate over the list. This can happen multiple ways; a foreach loop, calling ToList(), etc.

The query is executed the first and every time it's iterated. If you were to call ToList() on query two times, you would end up with two lists with completely distinct objects. They might contain the same data, but they would be different references.

Edit after comments

I just want to be clear about the distinction between when things are done client-side and when they're done server-side. If you're referencing an IQueryable<T> as an IEnumerable<T>, only the querying done after it's an IEnumerable<T> will be done client-side. For example, say I have this table and a LINQ-to-SQL context:

Customer
-----------
CustomerId
FirstName
LastName

I first construct a query based on FirstName. This creates an IQueryable<Customer>:

var query = from c in db.Customers where c.FirstName.StartsWith("Ad") select c;

Now I pass that query to a function that takes an IEnumerable<Customer> and does some filtering based on LastName:

public void DoStuff(IEnumerable<Customer> customers)
{
    foreach(var cust in from c in customers where c.LastName.StartsWith("Ro"))
    {
        Console.WriteLine(cust.CustomerId);
    }
}

We've done a second query here, but it's being done on an IEnumerable<Customer>. What's going to happen here is that the first query will be evaluated, running this SQL:

select CustomerId, FirstName, LastName from Customer where FirstName like 'Ad%'

So we're going to bring back everyone whose FirstName starts with "Ad". Note that there's nothing in here about LastName. That's because it's being filtered out client-side.

Once it brings back these results, the program will then iterate over the results and deliver only the records whose LastName starts with "Ro". The downside to this is that we brought back data--namely, all rows whose LastName doesn't start with "Ro"--that could have been filtered out on the server.

Solution 2

IQueryable<T>: abstracts database access, supports lazy evaluation of queries
List<T>: a collection of entries. No support of lazy evaluation
IEnumerator<T>: supplies the ability of iterating over and IEnumerable<T> (which both IQueryable<T> and List<T> are)

The problem with that code is quite simple - it always executes the query when it is called. If you were to return db.User.Where(...) instead (which is an IQueryable<T>), you'd hold the evaluation of the query until it is actually needed (iterated over). Also, if the user of that method would need to specify further predicates, those also be executed in the database, which makes it way faster.

Solution 3

Use iList or List<item> when you want a strongly typed collection of a some entity.

And Use Iqueryable and Ienumurator when you want to get dumb data as an collection of objects, it will come back as a loose type collection and no restrictions applied.

I would rather use List<type> because using a list wrap and cast in a strongly type collection my result set.

Also, using a list will give you the ability to add, sort and convert layer into Array, Ienumurator or as Queryable.

Comments

[chobo2]

@Adam Robinson - So is that better allowing them to get a returned query that still has not hit the database and when they need a list they can iterate it through(and then it hits the db source). Or is better to just give them a list straight of the bat?

[chobo2]

What kinda of further predicates are you saying that say they do your where clause it does not hit the database? Then this way if they need to say filter it even more down they can without hitting the db until you do a iteration over it?

[Adam Robinson]

@chobo2: It depends on what you're trying to do, so there's no one correct answer there. In some cases, it may be advantageous to allow the user to further refine the query, especially in cases where you may have complex query logic that is common to several different queries. While this isn't saying that you should always do this, returning IQueryable with the base complex logic in place would allow you to reuse that logic in different places without duplicating it in code

[Femaref]

In general, an IQueryable<T> only gets evaluated if iterated over. Now, if one was to chain another Where(...) to it, that would be executed in the database as well. With your code, it would be evaluated in memory. A database is designed to do such tasks and will do it faster than in memory filtering.

[chobo2]

@Adam Robinson -What would happen in this scenario. I return a IQueryable list but my view needs uses IEnumerator. I would cast it to this would there be some that problem you talked about with Enumerable?

[Adam Robinson]

@chobo2: IEnumerator or IEnumerable? If you pass an instance of IQueryable<T> to a function that takes an IEnumerable<T>, you're fine, but all querying done in that function will be done client-side (unless it casts it back into an IQueryable<T>, of course, but it should just take an IQueryable<T> if it's going to do that). If it expects IEnumerator<T>, that's a different interface entirely.

[chobo2]

Adam Robinson - hmm I did not realize that there where 2 of them I think IEnumerable. Is that bad if it does all the querying on the client side. What I am trying to do is have a viewmodel that is sent to the view that contains a IEnumerable to go through.

[Adam Robinson]

@chobo2: It's very likely IEnumerable. There are very few things that would take an IEnumerator itself, since it's only a temporary object used for enumerating. Just making sure. As for doing the filtering client side, in general you want to do as much filtering on the server as you can. You want to avoid bringing back data that you're just going to throw away if at all possible. Sometimes it's impossible (if the logic can't be expressed in SQL), sometimes it's prohibitive (the SQL logic is very difficult to read but client code is easy), but otherwise that's the preferred thing to do.

[Adam Robinson]

@chobo2: Check the edits I just made to the answer to see if that makes it a little clearer.

[dreza]

@AdamRobinson Won't var query = (from c in db.Customers where c.CustomerId == 5 select c).First(); create SQL like Select top 1 CustomerId, ... etc ??

[RBT]

Is there anyway to validate this fact that you have mentioned in your post - well, strictly speaking this will bring back every row in the table until it encounters one that fits the critiera. When we capture query in query profiler and if where clause is present then it will fetch the exact record. Isn't it?

[Adam Robinson]

@RBT: That comment is referring to the usage of .AsEnumerable().Where(), which would bypass the IQueryable interface.

[RBT]

Correct. So there will be no filtering on DB server side. So let's say if my table has records with ids 1,2,3,4,5,6,7 and query has a condition for CustomerId=5 then will the LINQ query using Enumerable (converted to SQL for DB server) will bring records having ids from 1 to 5 to the client side or from 1 to 7?

[Adam Robinson]

@RBT: As a matter of specification, you can't say--without an order by clause your database server is under no obligation to return those rows in any particular order. As a matter of practice (for SQL Server) they will get returned in whatever order the clustered index stores them in. That said, this is only true of this simple case where you have a table as the data source. If you did anything more advanced, namely join tables or (heaven forbid) use an Include to eager load navigation properties, you'll end up pulling back a lot more.

[Bijay Yadav]

I saw so many answers and I found this one more promising. Thanks.