[.net] What is the difference between IQueryable<T> and IEnumerable<T>?

What is the difference between IQueryable<T> and IEnumerable<T>?

See also What's the difference between IQueryable and IEnumerable that overlaps with this question.

The primary difference is that the LINQ operators for IQueryable<T> take Expression objects instead of delegates, meaning the custom query logic it receives, e.g., a predicate or value selector, is in the form of an expression tree instead of a delegate to a method.

• IEnumerable<T> is great for working with sequences that are iterated in-memory, but
• IQueryable<T> allows for out-of memory things like a remote data source, such as a database or web service.

Query execution:

• Where the execution of a query is going to be performed "in process", typically all that's required is the code (as code) to execute each part of the query.

• Where the execution will be performed out-of-process, the logic of the query has to be represented in data such that the LINQ provider can convert it into the appropriate form for the out-of-memory execution - whether that's an LDAP query, SQL or whatever.

More in:

• LINQ : IEnumerable<T> and IQueryable<T>
• C# 3.0 and LINQ.
• "Returning IEnumerable<T> vs IQueryable<T>"
• Reactive Programming for .NET and C# Developers - An Introduction To IEnumerable, IQueryable, IObservable, and IQbservable
• 2018: "THE MOST FUNNY INTERFACE OF THE YEAR … IQUERYABLE<T>" from Bart De Smet.

First of all, IQueryable<T> extends the IEnumerable<T> interface, so anything you can do with a "plain" IEnumerable<T>, you can also do with an IQueryable<T>.

IEnumerable<T> just has a GetEnumerator() method that returns an Enumerator<T> for which you can call its MoveNext() method to iterate through a sequence of T.

What IQueryable<T> has that IEnumerable<T> doesn't are two properties in particular—one that points to a query provider (e.g., a LINQ to SQL provider) and another one pointing to a query expression representing the IQueryable<T> object as a runtime-traversable abstract syntax tree that can be understood by the given query provider (for the most part, you can't give a LINQ to SQL expression to a LINQ to Entities provider without an exception being thrown).

The expression can simply be a constant expression of the object itself or a more complex tree of a composed set of query operators and operands. The query provider's IQueryProvider.Execute() or IQueryProvider.CreateQuery() methods are called with an Expression passed to it, and then either a query result or another IQueryable is returned, respectively.

ienumerable:when we want to deal with inprocess memory, i.e without dataconnection iqueryable:when to deal with sql server, i.e with data connection ilist : operations like add object, delete object etc

Here is what I wrote on a similar post (on this topic). (And no, I don't usually quote myself, but these are very good articles.)

"This article is helpful: IQueryable vs IEnumerable in LINQ-to-SQL.

Quoting that article, 'As per the MSDN documentation, calls made on IQueryable operate by building up the internal expression tree instead. "These methods that extend IQueryable(Of T) do not perform any querying directly. Instead, their functionality is to build an Expression object, which is an expression tree that represents the cumulative query. "'

Expression trees are a very important construct in C# and on the .NET platform. (They are important in general, but C# makes them very useful.) To better understand the difference, I recommend reading about the differences between expressions and statements in the official C# 5.0 specification here. For advanced theoretical concepts that branch into lambda calculus, expressions enable support for methods as first-class objects. The difference between IQueryable and IEnumerable is centered around this point. IQueryable builds expression trees whereas IEnumerable does not, at least not in general terms for those of us who don't work in the secret labs of Microsoft.

Here is another very useful article that details the differences from a push vs. pull perspective. (By "push" vs. "pull," I am referring to direction of data flow. Reactive Programming Techniques for .NET and C#

Here is a very good article that details the differences between statement lambdas and expression lambdas and discusses the concepts of expression tress in greater depth: Revisiting C# delegates, expression trees, and lambda statements vs. lambda expressions.."

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<T> and you fill 4 list boxes from it, then the query will be run against the database 4 times.

Also if you extent your query:

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

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

Below mentioned small test might help you understand one aspect of difference between IQueryable<T> and IEnumerable<T>. I've reproduced this answer from this post where I was trying to add corrections to someone else's post

I created following structure in DB (DDL script):

CREATE TABLE [dbo].[Employee]([PersonId] [int] NOT NULL PRIMARY KEY,[Salary] [int] NOT NULL)

Here is the record insertion script (DML script):

INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(1, 20)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(2, 30)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(3, 40)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(4, 50)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(5, 60)
GO

Now, my goal was to simply get top 2 records from Employee table in database. I added an ADO.NET Entity Data Model item into my console application pointing to Employee table in my database and started writing LINQ queries.

Code for IQueryable route:

using (var efContext = new EfTestEntities())
{
    IQueryable<int> employees = from e in efContext.Employees  select e.Salary;
    employees = employees.Take(2);

    foreach (var item in employees)
    {
        Console.WriteLine(item);
    }
}

When I started to run this program, I had also started a session of SQL Query profiler on my SQL Server instance and here is the summary of execution:

1. Total number of queries fired: 1
2. Query text: SELECT TOP (2) [c].[Salary] AS [Salary] FROM [dbo].[Employee] AS [c]

It is just that IQueryable is smart enough to apply the Top (2) clause on database server side itself so it brings only 2 out of 5 records over the wire. Any further in-memory filtering is not required at all on client computer side.

Code for IEnumerable route:

using (var efContext = new EfTestEntities())
{
    IEnumerable<int> employees = from e in efContext.Employees  select e.Salary;
    employees = employees.Take(2);

    foreach (var item in employees)
    {
        Console.WriteLine(item);
    }
}

Summary of execution in this case:

1. Total number of queries fired: 1
2. Query text captured in SQL profiler: SELECT [Extent1].[Salary] AS [Salary] FROM [dbo].[Employee] AS [Extent1]

Now the thing is IEnumerable brought all the 5 records present in Salary table and then performed an in-memory filtering on the client computer to get top 2 records. So more data (3 additional records in this case) got transferred over the wire unnecessarily.

In simple words other major difference is that IEnumerable execute select query on server side, load data in-memory on client side and then filter data while IQueryable execute select query on server side with all filters.

The primary difference is that the LINQ operators for IQueryable<T> take Expression objects instead of delegates, meaning the custom query logic it receives, e.g., a predicate or value selector, is in the form of an expression tree instead of a delegate to a method.

• IEnumerable<T> is great for working with sequences that are iterated in-memory, but
• IQueryable<T> allows for out-of memory things like a remote data source, such as a database or web service.

Query execution:

• Where the execution of a query is going to be performed "in process", typically all that's required is the code (as code) to execute each part of the query.

• Where the execution will be performed out-of-process, the logic of the query has to be represented in data such that the LINQ provider can convert it into the appropriate form for the out-of-memory execution - whether that's an LDAP query, SQL or whatever.

More in:

• LINQ : IEnumerable<T> and IQueryable<T>
• C# 3.0 and LINQ.
• "Returning IEnumerable<T> vs IQueryable<T>"
• Reactive Programming for .NET and C# Developers - An Introduction To IEnumerable, IQueryable, IObservable, and IQbservable
• 2018: "THE MOST FUNNY INTERFACE OF THE YEAR … IQUERYABLE<T>" from Bart De Smet.

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<T> and you fill 4 list boxes from it, then the query will be run against the database 4 times.

Also if you extent your query:

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

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

IEnumerable: IEnumerable is best suitable for working with in-memory collection (or local queries). 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 (or remote queries). 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

IQueryable is faster than IEnumerable if we are dealing with huge amounts of data from database because,IQueryable gets only required data from database where as IEnumerable gets all the data regardless of the necessity from the database

First of all, IQueryable<T> extends the IEnumerable<T> interface, so anything you can do with a "plain" IEnumerable<T>, you can also do with an IQueryable<T>.

IEnumerable<T> just has a GetEnumerator() method that returns an Enumerator<T> for which you can call its MoveNext() method to iterate through a sequence of T.

What IQueryable<T> has that IEnumerable<T> doesn't are two properties in particular—one that points to a query provider (e.g., a LINQ to SQL provider) and another one pointing to a query expression representing the IQueryable<T> object as a runtime-traversable abstract syntax tree that can be understood by the given query provider (for the most part, you can't give a LINQ to SQL expression to a LINQ to Entities provider without an exception being thrown).

The expression can simply be a constant expression of the object itself or a more complex tree of a composed set of query operators and operands. The query provider's IQueryProvider.Execute() or IQueryProvider.CreateQuery() methods are called with an Expression passed to it, and then either a query result or another IQueryable is returned, respectively.

This is a nice video on youtube which demonstrates how these interfaces differ , worth a watch.

Below goes a long descriptive answer for it.

The first important point to remember is IQueryable interface inherits from IEnumerable, so whatever IEnumerable can do, IQueryable can also do.

There are many differences but let us discuss about the one big difference which makes the biggest difference. IEnumerable interface is useful when your collection is loaded using LINQ or Entity framework and you want to apply filter on the collection.

Consider the below simple code which uses IEnumerable with entity framework. It’s using a Where filter to get records whose EmpId is 2.

EmpEntities ent = new EmpEntities();
IEnumerable<Employee> emp = ent.Employees; 
IEnumerable<Employee> temp = emp.Where(x => x.Empid == 2).ToList<Employee>();

This where filter is executed on the client side where the IEnumerable code is. In other words all the data is fetched from the database and then at the client its scans and gets the record with EmpId is 2.

But now see the below code we have changed IEnumerable to IQueryable. It creates a SQL Query at the server side and only necessary data is sent to the client side.

EmpEntities ent = new EmpEntities();
IQueryable<Employee> emp = ent.Employees;
IQueryable<Employee> temp =  emp.Where(x => x.Empid == 2).ToList<Employee>();

So the difference between IQueryable and IEnumerable is about where the filter logic is executed. One executes on the client side and the other executes on the database.

So if you working with only in-memory data collection IEnumerable is a good choice but if you want to query data collection which is connected with database `IQueryable is a better choice as it reduces network traffic and uses the power of SQL language.

The primary difference is that the LINQ operators for IQueryable<T> take Expression objects instead of delegates, meaning the custom query logic it receives, e.g., a predicate or value selector, is in the form of an expression tree instead of a delegate to a method.

• IEnumerable<T> is great for working with sequences that are iterated in-memory, but
• IQueryable<T> allows for out-of memory things like a remote data source, such as a database or web service.

Query execution:

• Where the execution of a query is going to be performed "in process", typically all that's required is the code (as code) to execute each part of the query.

• Where the execution will be performed out-of-process, the logic of the query has to be represented in data such that the LINQ provider can convert it into the appropriate form for the out-of-memory execution - whether that's an LDAP query, SQL or whatever.

More in:

• LINQ : IEnumerable<T> and IQueryable<T>
• C# 3.0 and LINQ.
• "Returning IEnumerable<T> vs IQueryable<T>"
• Reactive Programming for .NET and C# Developers - An Introduction To IEnumerable, IQueryable, IObservable, and IQbservable
• 2018: "THE MOST FUNNY INTERFACE OF THE YEAR … IQUERYABLE<T>" from Bart De Smet.

Below mentioned small test might help you understand one aspect of difference between IQueryable<T> and IEnumerable<T>. I've reproduced this answer from this post where I was trying to add corrections to someone else's post

I created following structure in DB (DDL script):

CREATE TABLE [dbo].[Employee]([PersonId] [int] NOT NULL PRIMARY KEY,[Salary] [int] NOT NULL)

Here is the record insertion script (DML script):

INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(1, 20)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(2, 30)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(3, 40)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(4, 50)
INSERT INTO [EfTest].[dbo].[Employee] ([PersonId],[Salary])VALUES(5, 60)
GO

Now, my goal was to simply get top 2 records from Employee table in database. I added an ADO.NET Entity Data Model item into my console application pointing to Employee table in my database and started writing LINQ queries.

Code for IQueryable route:

using (var efContext = new EfTestEntities())
{
    IQueryable<int> employees = from e in efContext.Employees  select e.Salary;
    employees = employees.Take(2);

    foreach (var item in employees)
    {
        Console.WriteLine(item);
    }
}

When I started to run this program, I had also started a session of SQL Query profiler on my SQL Server instance and here is the summary of execution:

1. Total number of queries fired: 1
2. Query text: SELECT TOP (2) [c].[Salary] AS [Salary] FROM [dbo].[Employee] AS [c]

It is just that IQueryable is smart enough to apply the Top (2) clause on database server side itself so it brings only 2 out of 5 records over the wire. Any further in-memory filtering is not required at all on client computer side.

Code for IEnumerable route:

using (var efContext = new EfTestEntities())
{
    IEnumerable<int> employees = from e in efContext.Employees  select e.Salary;
    employees = employees.Take(2);

    foreach (var item in employees)
    {
        Console.WriteLine(item);
    }
}

Summary of execution in this case:

1. Total number of queries fired: 1
2. Query text captured in SQL profiler: SELECT [Extent1].[Salary] AS [Salary] FROM [dbo].[Employee] AS [Extent1]

Now the thing is IEnumerable brought all the 5 records present in Salary table and then performed an in-memory filtering on the client computer to get top 2 records. So more data (3 additional records in this case) got transferred over the wire unnecessarily.

We use IEnumerable and IQueryable to manipulate the data that is retrieved from database. IQueryable inherits from IEnumerable, so IQueryable does contain all the IEnumerable features. The major difference between IQueryable and IEnumerable is that IQueryable executes query with filters whereas IEnumerable executes the query first and then it filters the data based on conditions.

Find more detailed differentiation below :

IEnumerable

1. IEnumerable exists in the System.Collections namespace
2. IEnumerable execute a select query on the server side, load data in-memory on a client-side and then filter data
3. IEnumerable is suitable for querying data from in-memory collections like List, Array
4. IEnumerable is beneficial for LINQ to Object and LINQ to XML queries

IQueryable

1. IQueryable exists in the System.Linq namespace
2. IQueryable executes a 'select query' on server-side with all filters
3. IQueryable is suitable for querying data from out-memory (like remote database, service) collections
4. IQueryable is beneficial for LINQ to SQL queries

So IEnumerable is generally used for dealing with in-memory collection, whereas, IQueryable is generally used to manipulate collections.

The primary difference is that the LINQ operators for IQueryable<T> take Expression objects instead of delegates, meaning the custom query logic it receives, e.g., a predicate or value selector, is in the form of an expression tree instead of a delegate to a method.

• IEnumerable<T> is great for working with sequences that are iterated in-memory, but
• IQueryable<T> allows for out-of memory things like a remote data source, such as a database or web service.

Query execution:

• Where the execution of a query is going to be performed "in process", typically all that's required is the code (as code) to execute each part of the query.

• Where the execution will be performed out-of-process, the logic of the query has to be represented in data such that the LINQ provider can convert it into the appropriate form for the out-of-memory execution - whether that's an LDAP query, SQL or whatever.

More in:

• LINQ : IEnumerable<T> and IQueryable<T>
• C# 3.0 and LINQ.
• "Returning IEnumerable<T> vs IQueryable<T>"
• Reactive Programming for .NET and C# Developers - An Introduction To IEnumerable, IQueryable, IObservable, and IQbservable
• 2018: "THE MOST FUNNY INTERFACE OF THE YEAR … IQUERYABLE<T>" from Bart De Smet.

In simple words other major difference is that IEnumerable execute select query on server side, load data in-memory on client side and then filter data while IQueryable execute select query on server side with all filters.

IEnumerable is refering to a collection but IQueryable is just a query and it will be generated inside a Expression Tree.we will run this query to get data from database.

First of all, IQueryable<T> extends the IEnumerable<T> interface, so anything you can do with a "plain" IEnumerable<T>, you can also do with an IQueryable<T>.

IEnumerable<T> just has a GetEnumerator() method that returns an Enumerator<T> for which you can call its MoveNext() method to iterate through a sequence of T.

What IQueryable<T> has that IEnumerable<T> doesn't are two properties in particular—one that points to a query provider (e.g., a LINQ to SQL provider) and another one pointing to a query expression representing the IQueryable<T> object as a runtime-traversable abstract syntax tree that can be understood by the given query provider (for the most part, you can't give a LINQ to SQL expression to a LINQ to Entities provider without an exception being thrown).

The expression can simply be a constant expression of the object itself or a more complex tree of a composed set of query operators and operands. The query provider's IQueryProvider.Execute() or IQueryProvider.CreateQuery() methods are called with an Expression passed to it, and then either a query result or another IQueryable is returned, respectively.

We use IEnumerable and IQueryable to manipulate the data that is retrieved from database. IQueryable inherits from IEnumerable, so IQueryable does contain all the IEnumerable features. The major difference between IQueryable and IEnumerable is that IQueryable executes query with filters whereas IEnumerable executes the query first and then it filters the data based on conditions.

Find more detailed differentiation below :

IEnumerable

1. IEnumerable exists in the System.Collections namespace
2. IEnumerable execute a select query on the server side, load data in-memory on a client-side and then filter data
3. IEnumerable is suitable for querying data from in-memory collections like List, Array
4. IEnumerable is beneficial for LINQ to Object and LINQ to XML queries

IQueryable

1. IQueryable exists in the System.Linq namespace
2. IQueryable executes a 'select query' on server-side with all filters
3. IQueryable is suitable for querying data from out-memory (like remote database, service) collections
4. IQueryable is beneficial for LINQ to SQL queries

So IEnumerable is generally used for dealing with in-memory collection, whereas, IQueryable is generally used to manipulate collections.

ienumerable:when we want to deal with inprocess memory, i.e without dataconnection iqueryable:when to deal with sql server, i.e with data connection ilist : operations like add object, delete object etc

IQueryable is faster than IEnumerable if we are dealing with huge amounts of data from database because,IQueryable gets only required data from database where as IEnumerable gets all the data regardless of the necessity from the database

Here is what I wrote on a similar post (on this topic). (And no, I don't usually quote myself, but these are very good articles.)

"This article is helpful: IQueryable vs IEnumerable in LINQ-to-SQL.

Quoting that article, 'As per the MSDN documentation, calls made on IQueryable operate by building up the internal expression tree instead. "These methods that extend IQueryable(Of T) do not perform any querying directly. Instead, their functionality is to build an Expression object, which is an expression tree that represents the cumulative query. "'

Expression trees are a very important construct in C# and on the .NET platform. (They are important in general, but C# makes them very useful.) To better understand the difference, I recommend reading about the differences between expressions and statements in the official C# 5.0 specification here. For advanced theoretical concepts that branch into lambda calculus, expressions enable support for methods as first-class objects. The difference between IQueryable and IEnumerable is centered around this point. IQueryable builds expression trees whereas IEnumerable does not, at least not in general terms for those of us who don't work in the secret labs of Microsoft.

Here is another very useful article that details the differences from a push vs. pull perspective. (By "push" vs. "pull," I am referring to direction of data flow. Reactive Programming Techniques for .NET and C#

Here is a very good article that details the differences between statement lambdas and expression lambdas and discusses the concepts of expression tress in greater depth: Revisiting C# delegates, expression trees, and lambda statements vs. lambda expressions.."

This is a nice video on youtube which demonstrates how these interfaces differ , worth a watch.

Below goes a long descriptive answer for it.

The first important point to remember is IQueryable interface inherits from IEnumerable, so whatever IEnumerable can do, IQueryable can also do.

There are many differences but let us discuss about the one big difference which makes the biggest difference. IEnumerable interface is useful when your collection is loaded using LINQ or Entity framework and you want to apply filter on the collection.

Consider the below simple code which uses IEnumerable with entity framework. It’s using a Where filter to get records whose EmpId is 2.

EmpEntities ent = new EmpEntities();
IEnumerable<Employee> emp = ent.Employees; 
IEnumerable<Employee> temp = emp.Where(x => x.Empid == 2).ToList<Employee>();

This where filter is executed on the client side where the IEnumerable code is. In other words all the data is fetched from the database and then at the client its scans and gets the record with EmpId is 2.

But now see the below code we have changed IEnumerable to IQueryable. It creates a SQL Query at the server side and only necessary data is sent to the client side.

EmpEntities ent = new EmpEntities();
IQueryable<Employee> emp = ent.Employees;
IQueryable<Employee> temp =  emp.Where(x => x.Empid == 2).ToList<Employee>();

So the difference between IQueryable and IEnumerable is about where the filter logic is executed. One executes on the client side and the other executes on the database.

So if you working with only in-memory data collection IEnumerable is a good choice but if you want to query data collection which is connected with database `IQueryable is a better choice as it reduces network traffic and uses the power of SQL language.

IEnumerable is refering to a collection but IQueryable is just a query and it will be generated inside a Expression Tree.we will run this query to get data from database.