One of the confusion I had when I initially started using IQueryable
, was why do we have both IEnumerable
and IQueryable
. What is the difference between IEnumerable VS IQueryable? That is when I started digging into this topic.
Also, a few of my YouTube subscribers were interested in knowing more about this topic. Hence, I decided to write this blog post.
But before we get into the differences, let me first walk through the definition of both. And explain both of these interfaces. Once I do that, it will be clearer what is the differences between the two.
What is IEnumerable interface?
The main purpose of the IEnumerable
interface is to expose an enumerator. And the enumerator is for iterating through a collection or an array.
To illustrate this further, let me first create a new .Net Core Console application.
I will open Visual Studio 2019, and in Create a new project popup I will select Console App (.NET Core). I will name the new project as IEnumerableIQueryable.Demo
.
To demonstrate how the implementation of IEnumerable
works, let us first consider a use case. The use case is to create an enumerable Employee
object.
Hence, to achieve this, first I will create a new class Employee
, with a few attributes. These are Id
, FirstName
, LastName
, and Address
.
namespace IEnumerableIQueryable.Demo
{
public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
}
}
Creating an IEnumerable implementation
Once this class is ready, it is time to create an IEnumerable
implementation for Employee
. Hence, I will create a new class named EmployeeEnumerable
. This class will implement IEnumerable
and provide an implementation of the GetEnumerator
method of the IEnumerable
interface.
using System;
using System.Collections;
using System.Collections.Generic;
namespace IEnumerableIQueryable.Demo
{
public class EmployeeEnumerable : IEnumerable<Employee>
{
List<Employee> employees = new List<Employee>();
public Employee this[int index]
{
get { return employees[index]; }
set { employees.Insert(index, value); }
}
public int Count => employees.Count;
public IEnumerator<Employee> GetEnumerator()
{
throw new NotImplementedException();
}
IEnumerator IEnumerable.GetEnumerator()
{
return GetEnumerator();
}
}
}
Apart from the methods needed for IEnumerable
, I created an indexer method (this[]
) for this class. I also created a property Count
, returning the count of the Employee
List. We will need both of these properties when we will define the IEnumerator
implementation.
Creating an IEnumerator implementation
Now that the basic implementation for the EmployeeEnumerable
class is ready. It is time to create a class to return the enumerator. And for that I will create a new class EmployeeEnumerator
.
The class EmployeeEnumerator
will implement the interface IEnumerator
. And I will give implementation for all the required methods for the IEnumerator
interface.
using System;
using System.Collections;
using System.Collections.Generic;
namespace IEnumerableIQueryable.Demo
{
public class EmployeeEnumerator : IEnumerator<Employee>
{
private bool disposed = false;
public Employee Current { get; private set; }
object IEnumerator.Current => Current;
public void Dispose()
{
Dispose(true);
}
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
// Dispose of managed resources.
}
}
disposed = true;
}
public bool MoveNext()
{
throw new NotImplementedException();
}
public void Reset()
{
throw new NotImplementedException();
}
}
}
IDisposable
pattern implementation in the above code. You can see the details of this pattern in my YouTube video here.Providing Complete implementation of IEnumerator
Now, I am going to provide the implementation of the EmployeeEnumerator
class.
using System.Collections;
using System.Collections.Generic;
namespace IEnumerableIQueryable.Demo
{
public class EmployeeEnumerator : IEnumerator<Employee>
{
private bool disposed = false;
private int currentIndex;
private EmployeeEnumerable employees;
public EmployeeEnumerator(EmployeeEnumerable employees)
{
this.employees = employees;
currentIndex = -1;
Current = default;
}
public Employee Current { get; private set; }
object IEnumerator.Current => Current;
public void Dispose()
{
Dispose(true);
}
protected virtual void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
// Dispose of managed resources.
}
}
disposed = true;
}
public bool MoveNext()
{
if (++currentIndex >= employees.Count)
{
return false;
}
else
{
Current = employees[currentIndex];
}
return true;
}
public void Reset()
{
currentIndex = -1;
}
}
}
Let me walk through the code above.
Firstly, I declared two variables, currentIndex
, which will keep the index of the item in the collection. And employees
, which is an instance of EmployeeEnumerable
.
Secondly, in the constructor, I am setting employees
to the incoming EmployeeEnumerable
parameter. And then, I am setting the currentIndex
to -1
. And finally, setting the Current
property, which returns an instance of Employee
to default
(which will be null in this case).
Thirdly, in the MoveNext
method, I am setting the current employee based on the currentIndex
. And returning false
if the current index is greater than the total employees.
Finally, in the Reset
method, I am just setting the value of currentIndex
back to -1
.
Updating the EmployeeEnumerable class
Now that the EmployeeEnumerator
class implementation is complete. It is time to update the implementation of EmployeeEnumerable
.
I will update the method GetEnumerator
, and I will return a new instance of EmployeeEnumerator
.
using System.Collections;
using System.Collections.Generic;
namespace IEnumerableIQueryable.Demo
{
public class EmployeeEnumerable : IEnumerable<Employee>
{
List<Employee> employees = new List<Employee>();
public Employee this[int index]
{
get { return employees[index]; }
set { employees.Insert(index, value); }
}
public int Count => employees.Count;
public IEnumerator<Employee> GetEnumerator()
{
return new EmployeeEnumerator(this);
}
IEnumerator IEnumerable.GetEnumerator()
{
return GetEnumerator();
}
}
}
Testing this out in Main method
Now, that we have completed the implementation, we can run the application to check out the implementation.
In the Main
method, I will create a new instance of EmployeeEnumerable
. Add a couple of employees to the EmployeeEnumerable
instance. And after that I will loop through the EmployeeEnumerable
instance and print out the employee id.
using System;
using System.Linq;
namespace IEnumerableIQueryable.Demo
{
class Program
{
static void Main(string[] args)
{
var employees = new EmployeeEnumerable();
employees[0] = new Employee { Id = 1 };
employees[1] = new Employee { Id = 2 };
foreach (var item in employees)
{
Console.WriteLine(item.Id);
}
}
}
}
Now that we went through the implementation of IEnumerable
, it is clear on how the internal enumeration work. The next logical step now, is to understand how IQueryable
works.
What is IQueryable interface?
The IQueryable
interface derives from the IEnumerable
interface. And the purpose of IQueryable
is more specific compare to the IEnumerable
interface.
The main purpose of the IQueryable
interface is to provide the functionality to query data against a specific data source. The intent of IQueryable
is mainly to provide data providers with standard contracts to write query implementation against their data source.
From the above definition, it is clear that data providers like Entity Framework Core implements IQueryable
. To show how it works, I am going to use Entity Framework Core to connect to an existing database.
Prepping up for Entity Framework Core
For Entity Framework Core, I will install the following NuGet packages.
- Microsoft.EntityFrameworkCore – The core framework library
- Microsoft.EntityFrameworkCore.SqlServer – Needed for SQL Server integration
- Microsoft.Extensions.Logging.Console – For logging the auto-generated SQL queries by Entity Framework Core to the console output.
And for this test, I am going to use my existing EFDemo
database in SQL Server Express.
In the EFDemo
database, I already have a table Employees
, and I am going to use it form the demo. The column names of the Employees
table matches with the properties of the Employee
class.
The only difference between the Employees
table and the Employee
class is the name. Hence I will update the Employee
class and add the Table
annotation to it with string parameter value “Employees”.
using System.ComponentModel.DataAnnotations.Schema;
namespace IEnumerableIQueryable.Demo
{
[Table("Employees")]
public class Employee
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string Address { get; set; }
}
}
Creating EmployeeContext
Firstly, to access the employee data from the Employees
table, we will need to create a class deriving from DbContext
. Hence I will create a new class and name it EmployeeContext
.
Secondly, we will update the EmployeeContext
class to derive from the DbContext
Entity Framework Core base class.
Thirdly, I will create a constructor for the EmployeeContext
class and accept the connection string to the database as a parameter.
Fourthly, I will also create a property Employees
which returns a DbSet
of Employee
type.
Fifthly, I will create a local variable for ILoggerFactory
instance. I will use the static method Create
of LoggerFactory
class to do that. And then I will call the AddConsole
method on the ILoggingBuilder
instance from the Action
parameter for setting up console logging.
Finally, I will override the OnConfiguring
method from the DbContext
class. And inside of this overridden method, I will take two steps:
- Firstly, I will call the
UseSqlServer
method on theDbContextOptionsBuilder
instance. And I will pass the connection string to the database to theUseSqlServer
method. This will set the Entity Framework Core up to use SQL Server as a data source. - Secondly, I will call the
UseLoggerFactory
method on theDbContextOptionsBuilder
instance. And I will pass theILoggerFactory
instance I created earlier to theUseLoggerFactory
method. This will enable the internal logging of Entity Framework Core.
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
namespace IEnumerableIQueryable.Demo
{
public class EmployeeContext : DbContext
{
private readonly ILoggerFactory loggerFactory
= LoggerFactory.Create(config => config.AddConsole());
private readonly string connectionString;
public EmployeeContext(string connectionString)
{
this.connectionString = connectionString;
}
public DbSet<Employee> Employees { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseLoggerFactory(loggerFactory);
optionsBuilder.UseSqlServer(connectionString);
}
}
}
Updating Main method
Now, I will update the Main
method, to create an instance of EmployeeContext
. And once the EmployeeContext
instance creation is complete, I will call the Employees
property on the EmployeeContext
instance. And I will filter out all the employees whose id is greater than 1 using Where
LINQ query.
After that, I will loop through employees and print their name.
Another important note, I will declare the employees
variable as IEnumerable
instead of default IQueryable
. This is to initially demonstrate how IEnumerable
will behave.
using System;
using System.Collections.Generic;
using System.Linq;
namespace IEnumerableIQueryable.Demo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext("Persist Security Info = False; Integrated Security = true; Initial Catalog = EFDemo; server = .\\SQLEXPRESS");
IEnumerable<Employee> employees = context.Employees
.Where(e => e.Id > 1);
foreach (var employee in employees)
{
Console.WriteLine($"Name - {employee.FirstName} {employee.LastName}");
}
}
}
}
Now, if I run the application, I will see name of 4 employees, out of 5 employees in my table.
As you can see above, the query is simple select based on a where condition.
SELECT [e].[Id], [e].[Address], [e].[FirstName], [e].[LastName]
FROM [Employees] AS [e]
WHERE [e].[Id] > 1
Using Take with IEnumerable
Now, I will update the code to use Take(2)
LINQ query on the result and see what is the output.
using System;
using System.Collections.Generic;
using System.Linq;
namespace IEnumerableIQueryable.Demo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext("Persist Security Info = False; Integrated Security = true; Initial Catalog = EFDemo; server = .\\SQLEXPRESS");
IEnumerable<Employee> employees = context.Employees
.Where(e => e.Id > 1);
var topTwoEmployees = employees.Take(2);
foreach (var employee in topTwoEmployees)
{
Console.WriteLine($"Name - {employee.FirstName} {employee.LastName}");
}
}
}
}
As you can see from the above response, there is no change in the actual SQL query. It is still getting all employees where employee id is greater than 1 in memory. And post that in-memory the reduction is happening.
Using IQueryable
Now, instead of IEnumerable
, I am going to use IQueryable
for casting the Employee
result. And also, I will remove the Take(2)
LINQ statement for the time being. Just to see the similarity between the SQL queries for IEnumerable vs IQueryable.
using System;
using System.Linq;
namespace IEnumerableIQueryable.Demo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext("Persist Security Info = False; Integrated Security = true; Initial Catalog = EFDemo; server = .\\SQLEXPRESS");
IQueryable<Employee> employees = context.Employees
.Where(e => e.Id > 1);
foreach (var employee in employees)
{
Console.WriteLine($"Name - {employee.FirstName} {employee.LastName}");
}
}
}
}
Now, if I run the application, in the console output I will see that the result, as well as query, is the same as it was for IEnumerable
.
Using Take with IQueryable
Just like before, now, I will update the code to use Take(2)
LINQ query on the result and see what is the output.
using System;
using System.Linq;
namespace IEnumerableIQueryable.Demo
{
class Program
{
static void Main(string[] args)
{
var context = new EmployeeContext("Persist Security Info = False; Integrated Security = true; Initial Catalog = EFDemo; server = .\\SQLEXPRESS");
IQueryable<Employee> employees = context.Employees
.Where(e => e.Id > 1);
var topTwoEmployees = employees.Take(2);
foreach (var employee in topTwoEmployees)
{
Console.WriteLine($"Name - {employee.FirstName} {employee.LastName}");
}
}
}
}
Finally, when I run the application, this time I will see a difference.
This time, as you can see above, the SQL query is different. Now, from the database itself it is getting only two records back to the code.
SELECT TOP(@__p_0) [e].[Id], [e].[Address], [e].[FirstName], [e].[LastName]
FROM [Employees] AS [e]
WHERE [e].[Id] > 1
Hence, when we use IQueryable
with Entity Framework Core, the actual data processing happens in the database. Just as the intent for IQueryable
suggests. It is the data provider, providing a way to query the data source.
Conclusion (IEnumerable VS IQueryable)
The main difference between IEnumerable VS IQueryable as are follows:
- When we use
IEnumerable
, all the queries are performed in memory. Whereas when we useIQueryable
, the actual data processing happens in the data store. IQueryable
derives fromIEnumerable
IEnumerable
is mainly used form LINQ to Object and LINQ to XML. WhereasIQueryable
is mainly used for LINQ to SQL.
I recorded the entire comparison of IEnumerable VS IQueryable in a YouTube video here.
The source code is available in GitHub here.