Entity Framework Core (EF Core) - from the Trenches by Jennifer Parker

Welcome to my comprehensive multi-part blog series on EF Core.  The goal of this series will be to outline various essential concepts and best practices, including the nuances of IQueryable vs IEnumerable, optimizing database calls, efficient sorting, paging and grouping, managing navigation properties, tuning queries, unit testing with mocked DbContext, and intercepting database calls for custom logic.

Part 1: Understanding the Differences Between IQueryable and IEnumerable in EF Core

In Part 1 of my series on EF Core, I’ll dive into two common interfaces that we use all the time for querying data: IQueryable and IEnumerable. Because they are fundamental to query optimization and performance, the differences between these two interfaces become the cornerstone for understanding why certain patterns might exist and how to best take advantage of these differences to build flexible, scalable and performant applications.

Let’s check out some key differences between IQueryable and IEnumerable and provide some guidance and considerations for choosing which interface to use in various circumstances.

What is IQueryable?

IQueryable is an interface provided by the System.Linq namespace. It is designed for querying data from a data source, such as a database, and supports deferred execution. This means that the query is not executed until the data is iterated over, allowing for dynamic query composition and optimization at runtime. This is often referred to as materializing the database query and projecting the results to a concrete object (for example, an IEnumerable).  When you work with EF Core, IQueryable is typically used to build queries that are translated into SQL and executed on the database server.

 Key Features of IQueryable

  1. Deferred Execution: The query is not executed until the data is enumerated.

  2. Database Translation: LINQ queries using IQueryable are translated into SQL and executed on the database server.

  3. Efficient Data Retrieval: Only the required data is retrieved, minimizing both the load on the database server and the network bandwidth of bringing back extra data that will be filtered in memory.

  4. Dynamic Query Composition: Queries can be built and modified at runtime, allowing for flexible data retrieval scenarios.

  5. Memory Management: By minimizing the amount of data returned, the memory management and IO cost of deserializing (materializing) the objects in memory can be reduced significantly.

Example

In this example, the query is built using IQueryable and executed when ToList is called, retrieving only the active entities from the database:

EF Core:

using (var context = new ApplicationDbContext())
{
    IQueryable<Entity> query = context.Entities.Where(e => e.IsActive);
    IEnumerable<Entity> activeEntities = query.ToList(); // The query is executed here - only one list in memory
}

SQL translation:

SELECT * FROM ENTITY
WHERE IsActive = true

What is IEnumerable? 

IEnumerable is an interface that represents a collection of objects that can be iterated over. Unlike IQueryable, IEnumerable does not support deferred execution in the same manner and typically involves in-memory operations. When a LINQ query is cast to IEnumerable, it fetches the data from the database and performs subsequent operations in memory. 

Key Features of IEnumerable

  1. Immediate Execution: The query is executed immediately, and the data is loaded into memory.

  2. In-Memory Operations: Subsequent operations are performed in memory.

  3. Simplicity: Suitable for small datasets or scenarios where in-memory processing is sufficient.

  4. Easier to translate: In some cases, IQueryable can be difficult to use to compose queries that are understood by the database, making IEnumerable easier to work with in memory.

Example

In this example, the data is first retrieved from the database and then filtered in memory, which can be less efficient for large datasets:

EF Core:

using (var context = new ApplicationDbContext())
{
    IEnumerable<Entity> entities = context.Entities.ToList(); //first list in memory
    IEnumerable<Entity> activeEntities = entities.Where(e => e.IsActive).ToList(); // In-memory filtering - second list in memory
}

SQL Translation:

SELECT * FROM ENTITY

Considerations for Using IQueryable vs IEnumerable

Use IQueryable when:

  • Querying Large Datasets: IQueryable allows the database server to handle the heavy lifting, reducing memory usage and improving performance.

  • Optimizing Performance: Deferred execution and database translation minimize network and resource overhead.

  • Dynamic Query Composition: Building complex queries at runtime is more efficient with IQueryable.

  • Efficient Data Retrieval: Only the necessary data is retrieved from the database.

 

Use IEnumerable When:

  • Working with Small Datasets: If the data can be efficiently handled in memory, IEnumerable can be simpler to use.

  • In-Memory Processing: When subsequent operations are better performed in memory, such as complex business logic that cannot be translated to SQL.

  • Simplicity and Readability: For straightforward scenarios where in-memory processing is sufficient.

Combining IQueryable and IEnumerable

Most of the time, we’re working with both IQueryable and IEnumerable interchangeable.

In the following example, you might fetch data using IQueryable and then perform in-memory operations with IEnumerable.

Example

In this example, active entities are retrieved from the database using IQueryable, and the sorting is performed in memory using IEnumerable.

EF Core:

using (var context = new ApplicationDbContext())
{
    IQueryable<Entity> query = context.Entities.Where(e => e.IsActive);
    IEnumerable<Entity> activeEntities = query.ToList(); // Query executed here - first list in memory
    Entity firstEntity = activeEntities.OrderBy(e => e.EntityProp).First(); // In-memory sorting and materialization
}

SQL Translation:

SELECT * FROM ENTITY
WHERE IsActive = true

In contrast, we could have done this same thing on the SQL side with some slight modifications to the code:

EF Core:

using (var context = new ApplicationDbContext())
{
    Entity entity = context.Entities.Where(e => e.IsActive).OrderBy(e => e.EntityProp).First();
}

SQL Translation:

SELECT TOP 1 * FROM ENTITY
WHERE IsActive = true
ORDER BY EntityProp

As you can see, the resulting queries that are executed are vastly different. The bottom query only returns one row, already filtered and ordered in SQL. Depending on the size of the table, this can improve performance substantially.

Additional Considerations 

As always, every use case is different.  In some scenarios, loading a small dataset entirely in memory because it’s going to be used repeatedly is a far better approach than using IQueryable to selectively get a subset of that data.

Because these two interfaces are very similar, it's easy to mix them up when working with code in a DbContext. Be cautious and double-check which interface you're using to avoid misinterpretations. Throughout my career, I've spent a lot of time improving performance. I've seen many instances where less experienced developers didn't realize the distinctions and ended up fetching excessive data by mistakenly using IEnumerable. 

Conclusion and Key Takeaways

IQueryable means deferred execution.

If you are looking for ways to optimize memory usage and reduce the amount of data that you are retrieving from the database, it is worth considering the composition of your queries and using IQueryable to optimize query execution. On the other hand, if you need to work with an entire dataset in memory, the dataset is relatively small (less than 1k records), or you need to perform complex operations that aren’t easily or cleanly translated to SQL, working with IEnumerable is a better approach.

Understanding the differences between IQueryable and IEnumerable is key for optimizing data access and application performance. By consideration the tradeoffs and choosing the appropriate interface based on your application's needs, you can save yourself and future developers countless hours doing costly performance tuning and ensure your application is more scalable and performant for the long haul.

Jennifer Parker