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
UseSqlServermethod on theDbContextOptionsBuilderinstance. And I will pass the connection string to the database to theUseSqlServermethod. This will set the Entity Framework Core up to use SQL Server as a data source. - Secondly, I will call the
UseLoggerFactorymethod on theDbContextOptionsBuilderinstance. And I will pass theILoggerFactoryinstance I created earlier to theUseLoggerFactorymethod. 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. IQueryablederives fromIEnumerableIEnumerableis mainly used form LINQ to Object and LINQ to XML. WhereasIQueryableis 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.