EF Core is a powerful tool for database access in .NET applications, but optimizing queries is crucial to ensure performance and efficiency. Many developers, even senior ones, overlook key optimizations that can make a significant difference.
Here are some EF Core tips to improve your code:
1. Optimize Query Projections
Instead of this: When working with large datasets, fetching unnecessary data can lead to performance issues.
var userDetails = await context.Users
.Where(u => u.IsActive)
.ToListAsync();
Do this: Use projections with Select to retrieve only the required data.
var userDetails = await context.Users
.Where(u => u.IsActive)
.Select(u => new UserDTO
{
u.Name,
u.Email,
u.PhoneNumber,
u.Address,
u.CreatedAt,
u.LastLogin
})
.ToListAsync();
Projection enables you to select specific fields from your entities, tailoring the result to your requirements instead of retrieving the whole entity. Additionally, when you use the Select method to create a projection, the query automatically bypasses EF Core’s change tracker.
2. Use AsNoTracking for Read-Only Queries
When you query your database using Entity Framework Core, it tracks the entities it retrieves by default. This is useful if you plan to update them, but it can be a performance hit if you’re just reading data.
var products = await context.Products
.Where(p => p.IsActive)
.ToListAsync();
Do this: Use AsNoTracking for read-only queries to improve performance.
var products = await context.Products
.AsNoTracking()
.Where(p => p.IsActive)
.ToListAsync();
By disabling tracking, EF Core skips the overhead of tracking changes, which means your queries execute faster, uses less memory, especially important when dealing with large datasets.
3. Use SQL Queries for Complex Operations
For complex queries, SQL can be more efficient than LINQ, leveraging indexes and database optimizations. Use SQL Query for safety and performance, you can also use procedures or dapper.
var products = await context.Products
.FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {category}")
.AsNoTracking()
.ToListAsync();
4. Use Asynchronous Methods
Instead of this: This example blocks the thread and can cause performance issues, especially on high-traffic systems.
return context.Products
.Where(p => p.IsActive)
.Select(p => new ProductDto
{
Id = p.Id,
Name = p.Name,
Price = p.Price
})
.ToList();
Do this: This method uses ToListAsync() to avoid thread blocking and projects data directly to a DTO, optimizing data traffic.
return await context.Products
.Where(p => p.IsActive)
.Select(p => new ProductDto
{
Id = p.Id,
Name = p.Name,
Price = p.Price
})
.ToListAsync();
5. Add Database Indexes
Here, we add an index on the Name column to speed up searches by reducing the number of rows scanned:
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
public void Configure(EntityTypeBuilder<Product> builder)
{
builder.ToTable("Products");
builder.HasKey(p => p.Id);
builder.HasIndex(p => p.Name); // Index on Name for faster searches
}
}
Without an index, queries on the Name column could perform full table scans, leading to poor performance.
6. Filter Early
Filtering with Where as early as possible reduces the amount of data processed and improves performance.
return await context.Products
.Where(p => p.IsActive) // Filter early to reduce unnecessary data
.Select(p => new ProductDto
{
Id = p.Id,
Name = p.Name,
Price = p.Price
})
.ToListAsync();
The order of the filters also matters because applying the most restrictive ones first reduces the number of data processed in subsequent steps. This optimizes the query, reducing resource usage and improving performance. Filtering items such as IsActive and Stock before applying other criteria significantly reduces the load on the query.
return await context.Products
.Where(p => p.IsActive && p.Price >= price && p.Stock > stock) // Efficient application of filters
.Select(p => new ProductDto
{
Id = p.Id,
Name = p.Name,
Price = p.Price,
Category = p.Category,
Stock = p.Stock
})
.ToListAsync();
7. Use Eager Loading
Loads related data upfront in a single query using Include and ThenInclude, reducing the number of queries and improving performance.
var products = await context.Products
.AsNoTracking()
.Include(p => p.Category)
.ThenInclude(c => c.Subcategories.Where(s => s.IsActive)) // Filtered ThenInclude
.ToListAsync();
Conclusion
I hope you found the tips shared in this article helpful and that they’ve given you valuable insights to enhance your development practices. Keep in mind, optimizing performance and maintaining simplicity are crucial for creating efficient and scalable applications.
Would you like me to dive into part 2? Let me know your thoughts!
Let’s keep improving our code, one step at a time.
Thank you so much for reading until the end!
See you next time!
