Differences Between Iqueryable, List, Ienumerator

Differences between IQueryable, List, IEnumerator?

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.

What's the difference between IQueryable and IEnumerable

IEnumerable<T> represents a forward-only cursor of T. .NET 3.5 added extension methods that included the LINQ standard query operators like Where and First, with any operators that require predicates or anonymous functions taking Func<T>.

IQueryable<T> implements the same LINQ standard query operators, but accepts Expression<Func<T>> for predicates and anonymous functions. Expression<T> is a compiled expression tree, a broken-up version of the method ("half-compiled" if you will) that can be parsed by the queryable's provider and used accordingly.

For example:

IEnumerable<Person> people = GetEnumerablePeople();
Person person = people.Where(x => x.Age > 18).FirstOrDefault();

IQueryable<Person> people = GetQueryablePeople();
Person person = people.Where(x => x.Age > 18).FirstOrDefault();

In the first block, x => x.Age > 18 is an anonymous method (Func<Person, bool>), which can be executed like any other method. Enumerable.Where will execute the method once for each person, yielding values for which the method returned true.

In the second block, x => x.Age > 18 is an expression tree (Expression<Func<Person, bool>>), which can be thought of as "is the 'Age' property > 18".

This allows things like LINQ-to-SQL to exist because they can parse the expression tree and convert it into equivalent SQL. And because the provider doesn't need to execute until the IQueryable is enumerated (it implements IEnumerable<T>, after all), it can combine multiple query operators (in the above example Where and FirstOrDefault) to make smarter choices on how to execute the entire query against the underlying data source (like using SELECT TOP 1 in SQL).

See:

The .NET Standard Query Operators

Difference in execution between ListT and IQueryableT

Following is the implementation of the IQueryable<T>.Except, check here :

public static IQueryable<TSource> Except<TSource>(this IQueryable<TSource> source1, IEnumerable<TSource> source2) {
            if (source1 == null)
                throw Error.ArgumentNull("source1");
            if (source2 == null)
                throw Error.ArgumentNull("source2");
            return source1.Provider.CreateQuery<TSource>( 
                Expression.Call(
                    null, 
                    GetMethodInfo(Queryable.Except, source1, source2),
                    new Expression[] { source1.Expression, GetSourceExpression(source2) }
                    ));
        }

Prime difference between the working of the IQueryable<T> and List<T>, Queryable type internally works with Expression<Func<T>>, since its getting executed remotely, in your case using the provider, when List<T> works with Func<T>, since its an in memory processing. When it comes to remote processing something like EF translates into relevant Sql query for processing, when in your case the following translates to null during remote processing: bs.Select(b => b.ToType()).

Following is the implementation of IEnumerable<T>.Except, check here:

public static IEnumerable<TSource> Except<TSource>(this IEnumerable<TSource> first, 
                                                   IEnumerable<TSource> second) 
{
    if (first == null) throw Error.ArgumentNull("first");
    if (second == null) throw Error.ArgumentNull("second");
    return ExceptIterator<TSource>(first, second, null);
}

Except itself is internally a set operation, even for List<T> call to Except(null) will lead to same exception.

As you have seen the definition of the IQueryable<T>.Except, its important to understand the difference in processing of the Expression and Func, Expression is more about what to do and Func is about how to do check this.

For a simple var intList = new List<int>{1,2,3}, this is what Queryable expression looks like (as shown in the attached image).

Essence remains check what your provider is internally translating the Queryable Expression into, which is leading to null and thus exception while processing

Sample Image

Real example of the differences between IQueryable and IEnumerable?

There is no IEnumerable example in your code. Both

var data = context.Books.Where(x => x.Id >930); // Queryable.Where(Expression)

and

var eData = context.Books.Where(x => x.Id >930).ToList(); // Queryable.Where(Expression)

use IQueryable extensions to convert expression into SQL query and run it on server-side. The only difference between data and eData is that you process query results immediately in second case (i.e. enumerate them and put into list). If you want to use IEnumerable:

var eData = context.Books.AsEnumerable().Where(x => x.Id >930); // Enumerable.Where(Lambda)

And difference here is that you can use any code in filter predicate to filter collection. Because this predicate will not be converted into SQL to run on server side. E.g. you can run your custom method:

var eData = context.Books.AsEnumerable().Where(x => _someObject.CheckBook(x));

but all books should be downloaed from server to client in order to run IEnumerable query.

When should I use IEnumerable and when IQueryable?

IEnumerable<T> represents something which produces a sequence of results. However, it doesn't expose any information about how the sequence is produced.

IQueryable<T> exposes the information about how the sequence is to be produced, at the Expression property, in the form of an expression tree. This information can then be easily mapped to a different set of instructions.

If you call Enumerable.Where on an IEnumerable<T>, you're passing in a compiled method compatible with Func<T, bool>. In theory, we could parse the IL of the compiled method to figure out what the method does, and use that to map to another set of instructions; but that's very complex. Inevitably, the only way to work with this is to load all the objects into memory from the server/provider/datasource, and apply the compiled method on each object.

If you call Queryable.Where on an IQueryable<T>, you're passing in an object which by definition represents different code operations -- Expression<Func<T, bool>>. For example:

IQueryable<Person> qry = /* ... */;
qry = qry.Where(x => x.LastName.StartsWith("A"));

the compiler converts x => x.LastName.StartsWith("A") to an object representing its various parts:

Lambda expression, returning a `bool`
    with an `x` parameter of type `Person`
    Call the `StartsWith` method, passing in a constant string `"A"`, on
        Result of the `LastName` property, of
            the `x` element

More, calling Queryable.Where itself also modifies the underlying expression tree:

Call to Queryable.Where, passing in
    The object at `qry`, and
    The previous lambda expression (see above)

When the query is enumerated (either with a foreach, or a call to ToList, or something similar), the information can easily be mapped from this object into another form, such as SQL:

SELECT *
FROM Persons
WHERE LastName LIKE N'A%'

or a web request:

http://example.com/Person?lastname[0]=a

The final expression tree after calling Queryable.Where will look something like this object graph, if it were possible to construct expression trees using constructors and object and collection initializers:

var x = new ParameterExpression {
    Type = typeof(Person),
    Name = "x"
};

new MethodCallExpression {
    Type = typeof(IQueryable<Person>),
    Arguments = new ReadOnlyCollection<Expression> {
        new ConstantExpression {
            Type = typeof(EnumerableQuery<Person>)
        },
        new UnaryExpression {
            NodeType = ExpressionType.Quote,
            Type = typeof(Expression<Func<Person, bool>>),
            Operand = new Expression<Func<Person, bool>> {
                NodeType = ExpressionType.Lambda,
                Type = typeof(Func<Person, bool>),
                Parameters = new ReadOnlyCollection<ParameterExpression> {
                    x
                },
                Body = new MethodCallExpression {
                    Type = typeof(bool),
                    Object = new MemberExpression {
                        Type = typeof(string),
                        Expression = x,
                        Member = typeof(Person).GetProperty("LastName")
                    },
                    Arguments = new ReadOnlyCollection<Expression> {
                        new ConstantExpression {
                            Type = typeof(string),
                            Value = "A"
                        }
                    },
                    Method = typeof(string).GetMethod("StartsWith", new[] { typeof(string) })
                },
                ReturnType = typeof(bool)
            }
        }
    },
    Method = typeof(Queryable).GetMethod("Where", new[] { typeof(IQueryable<Person>), typeof(Expression<Func<Person, bool>>) })
}

(NB. This was written using the ExpressionTreeToString library. Disclaimer: I am the author.)

IQueryable vs IEnumerable: is IQueryable always better and faster?

If the IQueryable perform the query Expression in the server rather
than fetching all records like IEnumerable, why IQueryable not
replaced by IEnumerable where it can be faster and more efficient?

IQueryable and IEnumerable represent two different things. Think of a IQueryable as a "question", it does not have any results itself. A IEnumerable is an "answer", it only has data connected to it but you can't tell what generated that data.

It is not that a IQueryable is "faster" per say, it just allows you to put your filtering and projections in to the "question" you ask to the SQL server and let it return only the answers it needs to (In the form of a IEnumerable by calling .ToList() or similar).

If you only use a IEnumerable the only question you can ask is "Give me everything you know" then on the answer it gives you you perform your filtering and projections. That is why IQueryable is considered faster, because there is a lot less data that needs to be processed because you where able to ask a more specific question to the server.

The reason IQueryable has not replaced IEnumerable everywhere is because the thing you are asking a question has to be able to understand the question you are asking it. It takes a lot of work to be able to parse every possible thing you could ask it to filter or project on so most implementations limit themselves to only common things they know they need to be able to answer. For example in Entity Framework when you ask a question it does not understand how to handle you will get a error that says something similar to "Specified method is not supported" when you try to get a IEnumerable (an answer) from the IQueryable.

DBSet<T> has two flavors of Where (IQueryable and IEnumerable).
is there a way to call the IEnumerable version because the
IQueryable is called by default, without calling ToList()?

The class DBSet<T> has no Where method on it at all. The two Where functions come from two extension methods, Enumerable.Where and Queryable.Where. You can force it to use the Enumerable overload by casting the object to a IEnumerable<T> before you call the extension method. However do remember, Queryable.Where filters the question, Enumerable.Where only filters the result.

It is wasteful to ask for results from the server to then just throw them away, so I would not recommend doing this.

Returning IEnumerableT vs. IQueryableT

Yes, both will give you deferred execution.

The difference is that IQueryable<T> is the interface that allows LINQ-to-SQL (LINQ.-to-anything really) to work. So if you further refine your query on an IQueryable<T>, that query will be executed in the database, if possible.

For the IEnumerable<T> case, it will be LINQ-to-object, meaning that all objects matching the original query will have to be loaded into memory from the database.

In code:

IQueryable<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

That code will execute SQL to only select gold customers. The following code, on the other hand, will execute the original query in the database, then filtering out the non-gold customers in the memory:

IEnumerable<Customer> custs = ...;
// Later on...
var goldCustomers = custs.Where(c => c.IsGold);

This is quite an important difference, and working on IQueryable<T> can in many cases save you from returning too many rows from the database. Another prime example is doing paging: If you use Take and Skip on IQueryable, you will only get the number of rows requested; doing that on an IEnumerable<T> will cause all of your rows to be loaded in memory.

Differences when using IEnumerable and IQueryable as a type of ObjectSet

Undeleting my answer because I just tested it and it works exactly as I described:

None of mentioned queries will hit the database because there was no enumeration. The difference between IQueryable query and IEnumerable query is that in the case of IQueryable the filtering will be executed on the database server whereas in the case of IEnumerable all objects will be loaded from the database to a memory and the filtering will be done in .NET code (linq-to-objects). As you can imagine that is usually performance killer.

I wrote simple test in my project:

[TestMethod]
public void Test()
{
    // ObjectQuery<Department> converted ot IEnumerable<Department>
    IEnumerable<Department> departmetns = CreateUnitOfWork().GetRepository<Department>().GetQuery();
    // No query execution here - Enumerable has also deffered exection
    var query = departmetns.Where(d => d.Id == 1); 
    // Queries ALL DEPARTMENTS here and executes First on the retrieved result set
    var result = departmetns.First(); 
}

Differences Between Iqueryable, List, Ienumerator