Boost Your Application's Performance: 7 Effective Strategies for Writing Efficient DB Queries in EF Core

Entity Framework Core (EF Core) is a powerful ORM (Object-Relational Mapper) for .NET applications, simplifying database interactions. However, many developers find it sluggish, particularly when retrieving vast amounts of data. By leveraging EF Core's capabilities correctly, you can optimize your queries for better performance. Here are seven effective strategies to improve your database query efficiency, complete with code examples.

1. Projection: Retrieve Only What You Need

• What It Is:

  Projection refers to selecting only the specific columns you need from the database rather than fetching entire entities with all their properties. This is particularly useful when you are interested in only a subset of data.

• Why It Matters:

  By reducing the amount of data transferred over the network and minimizing memory usage, you can dramatically enhance performance.

    var userDetails = await _context.Users
        .Select(u => new { u.Id, u.Name, u.Email }) 
        .ToListAsync();
  

  In this example, we only retrieve the user ID, name, and email instead of the entire user entity, reducing data load significantly.

2. Split Query: Handle Related Entities Efficiently

• How .AsSplitQuery() Works:

  When you use .AsSplitQuery(), EF Core modifies the way it retrieves data by breaking the query into multiple SQL queries instead of one large query. Here’s a step-by-step breakdown of how it works:

  1. Single Query Approach (Default Behavior):

Without .AsSplitQuery(), if you query an entity that has multiple related entities, EF Core creates a single SQL query with JOIN statements. This results in a larger result set where the related entities are flattened into a single row. For example:

    var orders = await _context.Orders
        .Include(o => o.Customer)
        .Include(o => o.OrderItems)
        .ToListAsync();

This might generate a complex SQL query with multiple joins that could look like this:

    SELECT * 
    FROM Orders o
    JOIN Customers c ON o.CustomerId = c.Id
    JOIN OrderItems oi ON o.Id = oi.OrderId;

2. Split Query Approach:
   When you apply .AsSplitQuery(), EF Core issues separate SQL queries for the main entity and each related entity. For the previous example, it would issue queries like this:

    // First query to get Orders
    SELECT * FROM Orders;
   
    // Second query to get related Customers
    SELECT * FROM Customers WHERE Id IN (/* list of customer IDs from Orders */);
   
    // Third query to get related OrderItems
    SELECT * FROM OrderItems WHERE OrderId IN (/* list of order IDs */);
   

3. Performance Benefits:

   • Reduced Result Set Size: Since related entities are fetched separately, the result set is smaller and easier to manage.

   • Less Memory Usage: It can lead to lower memory consumption because you’re not loading unnecessary data at once.

   • Improved Query Planning: SQL Server (or the database engine you are using) can optimize the execution plan for smaller queries more efficiently.

4. Here’s an example of how you would use .AsSplitQuery() in your code:

    var orders = await _context.Orders
        .Include(o => o.Customer)
        .Include(o => o.OrderItems)
        .AsSplitQuery()
        .ToListAsync();
   

5. When to Use .AsSplitQuery():

   1. When you have complex queries with multiple related entities that can generate large result sets.

   2. When you notice performance issues with the default single-query approach due to the size of the result set or complexity of the joins.

3. Use Async Methods: Enhance Responsiveness

• What It Is:

  EF Core provides asynchronous methods that allow your application to perform database operations without blocking the main thread.

• Why It Matters:

  By freeing up threads to handle other requests while waiting for I/O operations, your application remains responsive, improving user experience.

    var productList = await _context.Products.ToListAsync();
  

  Using ToListAsync() ensures that the application can continue processing other requests while waiting for the product data to load.

4. Use AsNoTracking(): Improve Read Performance

• How .AsSplitQuery() Works:

  1. Default Tracking Behavior:
     By default, Entity Framework Core tracks changes to entities retrieved from the database. This tracking allows EF Core to automatically update the database when SaveChanges() is called. Each entity is monitored by the context, and any changes made to it are stored.

      var user = await _context.Users.FirstOrDefaultAsync(u => u.Id == 1);
      user.Name = "New Name"; 
      // Changes are tracked, and you can call SaveChanges() to persist this change.
     

  2. No Tracking with .AsNoTracking():

     When you call .AsNoTracking(), EF Core does not track the retrieved entities. This means that any changes made to these entities will not be detected by the context, and SaveChanges() will not update the database with any modifications made to them.

      var users = await _context.Users.AsNoTracking().ToListAsync();
      users[0].Name = "New Name"; // Changes are not tracked, and calling SaveChanges() will not affect the database.
     

5. Utilize Count() and Any(): Optimize Queries

• What It Is:

  Using methods like Count() and Any() enables EF Core to generate more efficient SQL queries by directly counting records at the database level.

• Why It Matters:

  These methods avoid retrieving entire entity sets into memory, resulting in faster and more efficient query execution.

  This query directly counts the number of users in the database without loading user data into memory, making it faster and more efficient.

    var userCount = await _context.Users.CountAsync(u=> u.IsActive); // Efficiently counts users in the database
    bool hasUsers = await _context.Users.AnyAsync(u=> u.IsActive); // Efficiently checks for existence of any user
  

6. Call SaveChanges Once: Optimize Bulk Operations

• What It Is:

  When making multiple updates to the database, calling SaveChangesAsync() once at the end of your operations can greatly enhance performance.

• Why It Matters:

  This reduces the number of trips to the database, minimizing latency and improving overall throughput during bulk operations.

    _context.Users.UpdateRange(userList);
    await _context.SaveChangesAsync();
  

  By consolidating multiple updates into a single save operation, you reduce the database load and improve performance, especially in bulk scenarios.

7. Reuse Query with IQueryable: Avoid Multiple Query Variables

• What It Is:

  Creating an initial IQueryable query allows you to build upon it without generating multiple query variables, enhancing code readability and efficiency.

• Why It Matters:

  This avoids duplicate queries and allows for more straightforward adjustments to the query logic, resulting in cleaner code and potentially better performance.

    var query = _context.Users.AsQueryable(); // Initial query
    var activeUsers = await query.Where(u => u.IsActive).ToListAsync(); // Reusing the query
    var inactiveUsers = await query.Where(u => !u.IsActive).ToListAsync(); // Reusing the same query
  

  In this example, the initial query can be reused for different filters, improving maintainability and performance.

By applying these strategies, you can significantly enhance the performance of your applications using EF Core. Efficient database queries not only reduce load times but also improve user experience, making your applications more responsive and scalable. Happy coding!