Entity Framework Core is a powerful ORM that enables .NET developers to work with databases using domain-specific objects. While EF Core is a great tool for database access, it can be slow if not used correctly. In this article, we'll provide ten tips for optimizing EF Core performance, with examples.
1. Use AsNoTracking
By default, EF Core tracks all entities that are queried from the database. This means that every change to the entity is tracked, even if it's not necessary. To improve performance, you can use the AsNoTracking method to prevent EF Core from tracking entities. Here's an example:
var customers = context.Customers.AsNoTracking().ToList();
In this example, we're using the AsNoTracking method to prevent EF Core from tracking entities when querying all customers from the database.
2. Use Projections
Projections allow you to select only the columns that you need from the database, reducing the amount of data that is returned and improving performance. Here's an example:
var customers = context.Customers.Select(c => new { c.Name, c.Email }).ToList();
In this example, we're using a projection to select only the Name and Email columns from the customers table.
3. Use Compiled Queries
Compiled queries can significantly improve query performance by generating and caching a compiled expression tree for a given query. Here's an example:
private static Func<MyDbContext, int, Customer> _getCustomerById =
EF.CompileQuery((MyDbContext context, int id) =>
context.Customers.FirstOrDefault(c => c.Id == id));
var customer = _getCustomerById(context, customerId);
In this example, we're using a compiled query to get a customer by their Id from the database.
4. Use Lazy Loading
Lazy loading allows you to load related entities only when they are accessed, reducing the amount of data that is initially loaded from the database. Here's an example:
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public virtual ICollection<Order> Orders { get; set; }
}
var customer = context.Customers.Include(c => c.Orders).FirstOrDefault(c => c.Id == id);
var orders = customer.Orders.ToList();
In this example, we're using lazy loading to load a customer's orders only when they are accessed.
5. Use Eager Loading
Eager loading allows you to load related entities when they are needed, reducing the number of database queries that are required. Here's an example:
var customer = context.Customers.Include(c => c.Orders).FirstOrDefault(c => c.Id == id);
var orders = customer.Orders.ToList();
In this example, we're using eager loading to load a customer's orders when querying the customer from the database.
6. Use Indexes
Indexes can significantly improve query performance by speeding up the search for data in the database. Here's an example:
public class Customer
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
[Index]
public string City { get; set; }
}
var customers = context.Customers.Where(c => c.City == "New York").ToList();
In this example, we're using an index on the City column to speed up the search for customers who live in New York.
7. Limit the Amount of Data Retrieved
One of the easiest ways to improve EF Core performance is to limit the amount of data that is retrieved from the database. This can be accomplished by using the Take and Skip methods to paginate data, or by using projections to select only the necessary data. Here's an example that demonstrates how to use projections to retrieve only the necessary data:
var customers = context.Customers
.Select(c => new CustomerDto
{
Id = c.Id,
Name = c.Name
})
.ToList();
8. Avoid Unnecessary Queries
EF Core has a feature called "lazy loading," which allows related entities to be loaded automatically when accessed. While this feature can be convenient, it can also lead to performance problems if too many queries are executed. To avoid unnecessary queries, consider using eager loading or explicit loading instead. Here's an example that demonstrates how to use eager loading to load related data:
var orders = context.Orders
.Include(o => o.Customer)
.ToList();
In this example, we're using the Include method to eagerly load the related Customer entity when retrieving Order entities. This reduces the number of queries that are executed and can improve query performance.
9. Use raw SQL for complex queries
While EF Core provides a rich set of querying APIs, there may be cases where you need to execute a complex query that is difficult to express using the API. In these cases, you can use raw SQL to execute the query directly against the database.
var customers = context.Customers.FromSql("SELECT * FROM Customers WHERE Age > 18").ToList();
10. Use lazy loading judiciously to avoid performance issues
Lazy loading allows developers to load related entities on demand, but it can cause performance issues if used excessively. Lazy loading can result in multiple roundtrips to the database and can lead to the dreaded "N+1" problem. Here's an example of how to disable lazy loading:
services.AddDbContext<MyDbContext>(options =>
options.UseLazyLoadingProxies().UseSqlServer(connectionString));
In this example, we're disabling lazy loading for the MyDbContext by setting the UseLazyLoadingProxies method to false.