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.
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 circ*mstances.
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
Deferred Execution: The query is not executed until the data is enumerated.
Database Translation: LINQ queries using
IQueryableare translated into SQL and executed on the database server.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.
Dynamic Query Composition: Queries can be built and modified at runtime, allowing for flexible data retrieval scenarios.
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 ENTITYWHERE IsActive = trueWhat 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
Immediate Execution: The query is executed immediately, and the data is loaded into memory.
In-Memory Operations: Subsequent operations are performed in memory.
Simplicity: Suitable for small datasets or scenarios where in-memory processing is sufficient.
Easier to translate: In some cases,
IQueryablecan be difficult to use to compose queries that are understood by the database, makingIEnumerableeasier 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 ENTITYConsiderations for Using IQueryable vs IEnumerable
Use IQueryable when:
Querying Large Datasets:
IQueryableallows 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,
IEnumerablecan 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 ENTITYWHERE IsActive = trueIn 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 ENTITYWHERE IsActive = trueORDER BY EntityPropAs 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
