IQueryable<T> intended to provide the query provider (for example, ORM, for example LINQ to SQL or Entity Framework) to use the expressions contained in the query to translate the query into a different format. In other words, LINQ-to-SQL looks for the properties of the entities that you use with the comparison you make, and actually creates an SQL statement to express (hopefully) an equivalent query.
IEnumerable<T> more general than IQueryable<T> (although all instances of IQueryable<T> implement IEnumerable<T> ) and only defines a sequence. However, there are extension methods available in the Enumerable class that define some query type statements on this interface and use regular code to evaluate these conditions.
List<T> is only the output format, and although it implements IEnumerable<T> , it is not directly related to the request.
In other words, when you use IQueryable<T> , you define and express what translates into something else. Despite the fact that you write code, this code never runs, it is only checked and turns into something else, like a real SQL query. Because of this, only certain things act in these expressions. For example, you cannot call a regular function that you define from these expressions, since LINQ-to-SQL does not know how to turn your call into an SQL statement. Unfortunately, most of these restrictions are evaluated only at runtime.
When you use IEnumerable<T> for the query, you use LINQ-to-Objects, which means that you write the actual code that is used to evaluate your query or transform the results, so there is usually no limit to what you can to do. You can freely call other functions from these expressions.
From LINQ to SQL
Walking hand in hand with the distinction above, it is also important to keep in mind how this works out in practice. When you write a query against a data context class in LINQ to SQL, it creates an IQueryable<T> . No matter what you do against IQueryable<T> , it will turn into SQL, so your filtering and transformation will be done on the server. Whatever you do against this as an IEnumerable<T> , this will be done at the application level. This is sometimes desirable (for example, if you need to use client-side code), but in many cases it is unintentional.
For example, if I had a context with a Customers value representing the Customer table, and each customer has a CustomerId column, consider two ways to make this query:
var query = (from c in db.Customers where c.CustomerId == 5 select c).First();
This will create SQL that queries the database for the Customer record with CustomerId equal to 5. Something like:
select CustomerId, FirstName, LastName from Customer where CustomerId = 5
Now, what happens if we turn Customers into an IEnumerable<Customer> using the AsEnumerable() extension method?
var query = (from c in db.Customers.AsEnumerable() where c.CustomerId == 5 select c).First();
This simple change has serious consequences. Since we turn Customers into an IEnumerable<Customer> , this will return the entire table and filter it on the client side (well, strictly speaking, this will return each row in the table until it encounters one that matches the critique, but the dot same).
ToList ()
So far, we have only talked about IQueryable and IEnumerable . This is because they are similar, free interfaces. In both cases, you define a query; that is, you determine where to look for data, which filters to apply, and what data to return. Both of these queries are
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;
As we said, the first query uses IQueryable , and the second uses IEnumerable . However, in both cases this is just a request. Defining a query does virtually nothing against the data source. The request is actually executed when the code begins to iterate over the list. This can happen in several ways; a foreach , a call to ToList() , etc.
The request is executed first and every time it is repeated. If you called ToList() twice on query twice, you would have two lists with completely different objects. They may contain the same data, but they will be different links.
Edit after comments
I just want to be clear about the difference between when they are done on the client side and when they are done on the server side. If you reference IQueryable<T> as IEnumerable<T> , only the request after it IEnumerable<T> will be executed on the client side. For example, let's say I have this table and the LINQ-to-SQL context:
Customer ----------- CustomerId FirstName LastName
First I will build 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 this request 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 fulfilled the second request here, but this is done on IEnumerable<Customer> . What will happen here is that the first query will be evaluated by running this SQL:
select CustomerId, FirstName, LastName from Customer where FirstName like 'Ad%'
So, we are going to bring back everyone who starts FirstName with "Ad" . Note that there is nothing LastName . This is because it is filtered on the client side.
As soon as it returns these results, the program then iterates over the results and displays only the records whose LastName begins with "Ro" . The disadvantage of this is that we returned the data - namely, all lines whose LastName does not start with "Ro" - that could be filtered on the server.