What's the difference between IQueryable and IEnumerable

I'm confused as to the difference. Being fairly new to .Net, I know I can query IEnumerables using the Linq extensions. So what is this IQueryable and how does it differ?

---

See also What is the difference between IQueryable[T] and IEnumerable[T]? that overlaps with this question.

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

In real life, if you are using a ORM like LINQ-to-SQL

• If you create an IQueryable, then the query may be converted to sql and run on the database server
• If you create an IEnumerable, then all rows will be pulled into memory as objects before running the query.

In both cases if you don't call a ToList() or ToArray() then query will be executed each time it is used, so, say, you have an IQueryable and you fill 4 list boxes from it, then the query will be run against the database 4 times.

Also if you extend your query:

q.Select(x.name = "a").ToList()

Then with an IQueryable the generated SQL will contain where name = "a", but with an IEnumerable many more roles will be pulled back from the database, then the x.name = "a" check will be done by .NET.

"The primary difference is that the extension methods defined for IQueryable take Expression objects instead of Func objects, meaning the delegate it receives is an expression tree instead of a method to invoke. IEnumerable is great for working with in-memory collections, but IQueryable allows for a remote data source, like a database or web service"

Source: here

IEnumerable IEnumerable is best suitable for working with in-memory collection. IEnumerable doesn’t move between items, it is forward only collection.

IQueryable IQueryable best suits for remote data source, like a database or web service. IQueryable is a very powerful feature that enables a variety of interesting deferred execution scenarios (like paging and composition based queries).

So when you have to simply iterate through the in-memory collection, use IEnumerable, if you need to do any manipulation with the collection like Dataset and other data sources, use IQueryable

The principle difference is that IEnumerable will enumerate all of its elements all the time, while IQueryable will enumerate elements, or even do other things, based on a query. The query is an Expression (a data representation of .Net code), which an IQueryProvider must explore/interpret/compile/whatever in order to generate results.

Having a query expression gives two advantages.

The first advantage is optimization. Because modifiers like 'Where' are included in the query expression, the IQueryProvider can apply otherwise impossible optimizations. Instead of returning all elements then throwing away most of them due to a 'Where' clause, the provider could use a hash table to locate items with a given key.

The second advantage is flexibility. Because Expressions are explorable data structures, you can do things like serialize the query and send it to a remote machine (eg. linq-to-sql).

IQueriable is the same as IEnumerable but it also provides additional functionality to implement custom querying with Linq. Here is description on MSDN: http://msdn.microsoft.com/en-us/library/system.linq.iqueryable.aspx