How to Optimize Database Interactions in Entity Framework Core?

Question

I am using Entity Framework Core for database interactions in my .NET application. I am seeking advice on optimizing database queries and transactions for better performance and efficiency. Any tips or best practices would be appreciated.

Answer 1

Optimize LINQ queries, use AsNoTracking() for read-only operations, and leverage Eager, Lazy, and Explicit Loading appropriately. Utilize indexing and consider using Raw SQL queries or Stored Procedures for complex queries. Profile and analyze queries using tools like EF Profiler.

Answer 2

Hi @Kevin Beasley, Welcome to Microsoft Q&A,

Usually we can use the following six methods to optimize the performance of EF Core when using EF Core:

1. Avoid query operations in loops:

To avoid performing query operations in a loop, you can cache the query results in memory and then operate on the data in memory, which can improve performance. This method is suitable for collecting data with a small amount of data, otherwise the advantages outweigh the disadvantages.

// Not recommended: performing query operations in a loop
foreach (var item in itemList)
{
     var result = context.Items.FirstOrDefault(i => i.Id == item.Id);
//execution logic
}
// Recommended method: cache the query results in memory, and then operate on the data in memory
var itemIds = itemList.Select(i => i.Id).ToList();
var results = context.Items.Where(i => itemIds.Contains(i.Id)).ToList();
foreach (var item in itemList)
{
     var result = results.FirstOrDefault(r => r.Id == item.Id);
     //execution logic
}

2. Avoid using lazy loading:

Avoid using lazy loading, as each access to a navigation property triggers an additional database query. Using explicit loading or eager loading techniques can improve performance.

// Not recommended: use lazy loading
var order = context.Orders.FirstOrDefault();
foreach (var item in order.Items)
{
   //execution logic
}
// Recommended way: use eager loading
var order = context.Orders.Include(o => o.Items).FirstOrDefault();
foreach (var item in order.Items)
{
    //execution logic
}

3. Use the Include method rationally:

The Include method can obtain all related entity objects in one query. However, when large amounts of data are involved, the Include method can cause performance degradation. Manual chaining queries can be used instead of the Include method.

// Not recommended: use the Include method to get all related entities
var orders = context.Orders.Include(o => o.Items).ToList();
 
// Recommended way: use manual link query instead of Include method
var orders = context.Orders
     .Join(context.OrderItems,
           o => o.Id,
           oi => oi.OrderId,
           (o, oi) => new { Order = o, OrderItem = oi })
     .ToList();

4. Use the NoTracking method:

Use the NoTracking method to avoid EF Core's tracking functionality. Tracking is useful when updating and deleting entity objects, but it incurs additional overhead when you only need to read the data. Use the NoTracking method to disable tracking, thereby improving performance.

// Not recommended: use the default tracking function
var order = context.Orders.FirstOrDefault();
// Recommended way: use NoTracking method
var order = context.Orders.AsNoTracking().FirstOrDefault();

5. Execute the original SQL query:

In some cases, using raw SQL statements can be more efficient than using EF Core. Raw SQL queries can be executed using the FromSqlRaw or ExecuteSqlRaw methods.

//Execute raw SQL query
var orders = context.Orders.FromSqlRaw("SELECT * FROM Orders WHERE Status = 'Complete'").ToList();

6. Use EF.CompileAsyncQuery:

EF.CompileAsyncQuery is an extension method of EF Core that compiles LINQ expressions into an asynchronous query. Compared with dynamically generating LINQ queries, using EF.CompileAsyncQuery can improve query performance and reduce unnecessary memory allocation.

Compiled queries can be called multiple times without having to dynamically generate query expressions each time. This can avoid unnecessary memory allocation and query optimization overhead and improve query performance.

In EF Core 5.0 and above, EF.CompileAsyncQuery has become a standard extension method and can be used without any special installation or configuration. It is suitable for situations where the query conditions are fixed. Of course, it can also be recompiled, but frequent compilation will cause memory and performance overhead.

Examples are as follows:

using Microsoft.EntityFrameworkCore.Query;
//Define an asynchronous query
private static readonly Func<MyDbContext, int, Task<Order>> GetOrderById =
     EF.CompileAsyncQuery((MyDbContext context, int id) =>
         context.Orders.FirstOrDefaultAsync(o => o.Id == id));
//Call asynchronous query
var order = await GetOrderById(context, 1);

You need to use the above optimization methods according to your actual situation

Best Regards,

Wenbin

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.