Entity Framework Core (EF Core) is an incredibly powerful ORM that simplifies data access by abstracting much of the complexity of database interactions. However, this convenience can sometimes lead to performance bottlenecks if not used wisely. In this article, we’ll explore some of the most effective ways to optimize EF Core performance to ensure your applications run efficiently, even when dealing with large datasets.
1. Efficient query optimization
EF Core generates SQL queries on your behalf, but that doesn’t mean they are always the most optimal. To avoid performance pitfalls, it’s essential to understand how to write efficient LINQ queries that translate to optimized SQL. Here are a few tips:
Filter early
Always apply filters (Where clauses) as early as possible. This ensures that unnecessary data is not loaded into memory.
var orders = context.Orders
.Where(o => o.CustomerId == customerId)
.ToList();
In this example, we filter by CustomerId before loading the orders into memory. This avoids fetching irrelevant data.
Avoid Select N+1 problem
Be cautious when querying related data inside loops, as this can lead to multiple database calls. Use eager loading to fetch related data in a single query.
var orders = context.Orders
.Include(o => o.Customer)
.ToList(); // Single query with JOIN to fetch orders and customers.
Use projections
Fetch only the data you need by using projections (Select) instead of loading entire entities.
var orderSummaries = context.Orders
.Where(o => o.CustomerId == customerId)
.Select(o => new { o.OrderId, o.TotalAmount })
.ToList();
By projecting into an anonymous type, you’re reducing the amount of data being retrieved from the database.
2. Tracking vs. No-Tracking queries
By default, EF Core tracks changes to entities when they are loaded from the database. This is useful if you need to modify the data and persist the changes, but it adds overhead. If you’re fetching data only for read purposes, consider using No-Tracking queries.
When to use tracking queries
When you need to update or delete entities.
var customer = context.Customers
.FirstOrDefault(c => c.Id == id);
customer.Name = "Updated Name";
context.SaveChanges(); // Tracking enabled by default.
When to use No-Tracking queries
When data is read-only and doesn’t require modifications.
var customers = context.Customers
.AsNoTracking() // No tracking for better performance.
.ToList();
Disabling tracking reduces the overhead of change tracking, especially when dealing with large datasets.
3. Optimizing loading strategies
EF Core offers several strategies for loading related entities, and selecting the right one can significantly impact performance. Let’s look at the three main types: eager loading, lazy loading, and explicit loading.
Eager loading
Eager loading fetches related data as part of the initial query using the Include method. It’s helpful when you know you'll need related entities upfront.
var customers = context.Customers
.Include(c => c.Orders) // Eagerly load related orders.
.ToList();
Use eager loading sparingly for frequently accessed data, as unnecessary includes can lead to heavy JOINs and bloated queries.
Lazy loading
Lazy loading retrieves related data only when it’s accessed for the first time. While this can reduce initial query load, it may lead to the N+1 problem if not managed carefully.
var customer = context.Customers.First();
var orders = customer.Orders; // Orders are loaded when accessed.
Lazy loading is useful in scenarios where related data might not always be needed. However, if you’re accessing related entities frequently, consider eager or explicit loading to avoid multiple database calls.
Explicit loading
Explicit loading is useful when you want more control over when related entities are fetched, without loading them upfront. You can load related data on demand using the Load method.
var customer = context.Customers.First();
context.Entry(customer).Collection(c => c.Orders).Load(); // Explicitly load related orders.
This strategy allows you to load related data when needed, avoiding unnecessary data retrieval while maintaining control over database calls.
4. Efficient data updates
Bulk updates and deletions are often better performed directly in the database rather than loading entities into memory, modifying them, and then calling SaveChanges. For example, use direct SQL commands or ExecuteUpdateAsync in EF Core 7.0+ to perform batch operations efficiently.
await context.Customers
.Where(c => c.IsInactive)
.ExecuteUpdateAsync(c => c.SetProperty(c => c.Status, "Archived"));
This approach minimizes the amount of data loaded into memory and reduces the round-trips to the database.
5. Leveraging compiled queries
EF Core allows you to cache the execution plan of frequently executed queries using compiled queries, which can significantly improve performance for repetitive queries.
static readonly Func<MyDbContext, string, Customer> getCustomerByName =
EF.CompileQuery((MyDbContext context, string name) =>
context.Customers.FirstOrDefault(c => c.Name == name));
var customer = getCustomerByName(context, "John Doe");
Compiled queries avoid the overhead of translating LINQ into SQL for every execution and are ideal for high-traffic queries.
6. Using asynchronous methods
EF Core supports asynchronous database operations, which can significantly improve application scalability, especially in environments where many concurrent operations occur, such as web applications.
Using asynchronous methods like ToListAsync, FirstOrDefaultAsync, and SaveChangesAsync ensures that threads are not blocked while waiting for the database to respond. This frees up resources for other tasks, improving overall throughput.
var orders = await context.Orders
.Where(o => o.CustomerId == customerId)
.ToListAsync(); // Asynchronous query execution
When to use async methods:
- In ASP.NET Core or other environments where many requests are processed concurrently.
- When performing I/O-bound operations, such as database queries.
7. Limiting the data with pagination
Fetching large datasets in one go can degrade performance, increase memory usage, and result in slow response times. Pagination helps by fetching only the required data in smaller chunks.
EF Core provides methods like Skip and Take to implement pagination easily.
var customers = await context.Customers
.OrderBy(c => c.Id)
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.ToListAsync();
This method ensures that only a small portion of the data is retrieved, reducing the load on both the database and the application.
8. Avoiding lazy loading in performance-critical applications
While lazy loading can be convenient, it often leads to the N+1 query problem, where a new query is generated for each related entity access. In performance-critical applications, lazy loading should be avoided unless it’s explicitly needed.
Instead, prefer eager loading (Include) or explicit loading (Load) for related entities to keep the number of database calls minimal and predictable. If you must use lazy loading, be cautious and profile your queries to ensure you're not inadvertently causing performance issues.
You can disable lazy loading entirely:
services.AddDbContext<MyDbContext>(options =>
options.UseLazyLoadingProxies(false)); // Disables lazy loading for the context.
9. Cache frequently used data
For data that doesn’t change often, consider caching it instead of querying the database repeatedly. EF Core itself doesn’t provide caching, but you can use caching solutions like MemoryCache or DistributedCache to store query results.
Example with MemoryCache
if (!_cache.TryGetValue("ProductList", out List<Product> products))
{
products = dbContext.Products.AsNoTracking().ToList();
_cache.Set("ProductList", products, TimeSpan.FromMinutes(30));
}
Here, products are cached for 30 minutes, and subsequent requests will use the cache instead of querying the database.
10. Use batching for multiple Inserts/Updates
When inserting or updating multiple entities, EF Core by default sends one SQL query per entity. You can optimize this by using batching.
Example using AddRange()
var newProducts = new List<Product>
{
new Product { Name = "Product1" },
new Product { Name = "Product2" }
};
dbContext.Products.AddRange(newProducts);
dbContext.SaveChanges();
This results in a single batch insert, which is much faster than calling Add() for each entity.
11. Indexing database columns
Ensure that your database tables have appropriate indexes on columns frequently used in queries. EF Core allows you to configure indexes using the Fluent API or Data Annotations.
Example using Fluent API
modelBuilder.Entity<Product>()
.HasIndex(p => p.Name);
This creates an index on the Name column, which speeds up queries that search or filter based on this field.
12. Avoid large transactions
Performing large transactions with too many database operations in a single SaveChanges() call can degrade performance. If possible, break down large transactions into smaller, more manageable operations.
// Break down operations into smaller batches
foreach (var batch in largeDataSet.Batch(100))
{
dbContext.Entities.AddRange(batch);
dbContext.SaveChanges();
}
This improves performance and reduces the chance of locking issues in the database.
13. Optimize migrations
Migrations can introduce inefficiencies if not handled properly, especially in large-scale applications. Be sure to review and refine migrations to avoid unnecessary schema changes. Use the EnsureCreated() method in scenarios where a simple database initialization is required without full migrations.
dbContext.Database.EnsureCreated();
This ensures the database schema is created if it doesn’t already exist, avoiding the overhead of migrations in certain environments.
14. Optimize connection pooling
Connection pooling is managed by ADO.NET and automatically enabled by EF Core. However, you can optimize it further by adjusting settings such as Max Pool Size and Min Pool Size in the connection string to match your application’s workload.
Example connection string:
"Server=myServer;Database=myDb;User Id=myUser;Password=myPass;Max Pool Size=100;Min Pool Size=5;"
Adjusting pool sizes ensures that EF Core can efficiently reuse existing database connections, reducing the overhead of creating new ones.
Tips and tricks for EF Core optimization
1. Use indexes wisely
When querying large datasets, ensure that the columns being filtered or joined on are properly indexed in the database. EF Core doesn’t automatically create indexes for all columns, so you might need to add them manually for high-traffic queries. You can create indexes directly through migrations:
modelBuilder.Entity<Customer>()
.HasIndex(c => c.Email) // Creating an index on the Email column.
.IsUnique();
2. Leverage the UseQueryTrackingBehavior option
Instead of manually adding .AsNoTracking() everywhere, you can set the default tracking behavior for all queries in your DbContext:
services.AddDbContext<MyDbContext>(options =>
options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));
This way, all queries will be no-tracking by default unless you explicitly specify otherwise.
3. Disable change tracking for read-only scenarios
If you’re working with a read-only scenario, you can globally disable change tracking, which can help in reducing overhead when querying large amounts of data:
context.ChangeTracker.AutoDetectChangesEnabled = false;
Make sure to enable it again if you need to track changes for inserts or updates later.
4. Avoid using .Count() in loops
Avoid calling .Count() repeatedly in loops, as it executes a query to count the rows every time. Instead, fetch the count once and store it in a variable:
var customerCount = await context.Customers.CountAsync(); // Executes only once.
5. Minimize database round-trips
Try to batch updates, inserts, and deletes instead of performing individual database calls. This can be achieved by calling SaveChanges() after multiple operations:
context.Customers.Add(newCustomer1);
context.Customers.Add(newCustomer2);
await context.SaveChangesAsync(); // Saves changes for both operations at once.
6. Use FromSqlRaw for complex queries
If you need to execute a complex query that isn’t easily expressible using LINQ, or if you need full control over the SQL being executed, you can use the FromSqlRaw method to run raw SQL queries:
var customers = await context.Customers
.FromSqlRaw("SELECT * FROM Customers WHERE IsActive = 1")
.ToListAsync();
This method allows you to write optimized SQL for scenarios where LINQ might generate inefficient queries.
7. Profile and benchmark your queries
Always profile your database queries to ensure they’re performing optimally. Tools like SQL Server Profiler, MiniProfiler, or EF Core’s built-in logging can help you identify bottlenecks.
Enable detailed logging in EF Core for query insights:
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
This will show you the SQL being generated, which can help you refine queries and avoid inefficient patterns.
By applying these tips and tricks, you can fine-tune EF Core for optimal performance, making your application more robust and scalable.
Conclusion
Optimizing EF Core performance requires a combination of thoughtful query design, judicious use of tracking, and efficient loading strategies. By implementing the practices outlined in this article — such as using no-tracking queries, eager or explicit loading, compiled queries, and batching operations — you can ensure your applications handle data efficiently, even at scale.
Stay mindful of the trade-offs between simplicity and performance, and always profile your queries to find potential bottlenecks in your specific scenarios.