That way to build High-Performance APIs in .NET - Part 1: Data Access

Hello, I’m Duc Nguyen (Duke)

Part 1: Advanced Data Access Optimization

One of the most important components of creating high-performance APIs is efficient data access.

1.Minimizing Tracking for Performance Gains

To minimize memory overhead and expedite query execution, you can disable change tracking using AsNoTracking if you're not making any changes to the entities that were retrieved from the database.

var customers = await _context.Customers
    .AsNoTracking()
    .ToListAsync();

2.Reducing Database Calls with In-Memory Collections

One way to optimize your queries when using EF Core is to minimize the number of round-trips to the database.

This means:

Step 1: Load data from the database once.
Step 2: Store it in memory.
Step 3: Perform further operations (filters, searches) on this in-memory collection rather than querying the database again.

For example:

• Without Using In-Memory Collections

// First query to get customers who placed orders in the last 6 months
var recentCustomers = await _context.Customers
    .Where(c => c.Orders.Any(o => o.OrderDate > DateTime.UtcNow.AddMonths(-6)))
    .ToListAsync();

// Another query to filter customers who ordered a specific product
var customersOrderedProduct = await _context.Customers
    .Where(c => c.Orders.Any(o => o.OrderItems.Any(oi => oi.ProductId == productId)))
    .ToListAsync();

• Optimized Using In-Memory Collections:

// Load all customers and their orders once
var allCustomers = await _context.Customers
    .Include(c => c.Orders)
    .ThenInclude(o => o.OrderItems)
    .ToListAsync();

// Filter customers who placed orders in the last 6 months (in-memory)
var recentCustomers = allCustomers
    .Where(c => c.Orders.Any(o => o.OrderDate > DateTime.UtcNow.AddMonths(-6)))
    .ToList();

// Filter customers who ordered a specific product (in-memory)
var customersOrderedProduct = allCustomers
    .Where(c => c.Orders.Any(o => o.OrderItems.Any(oi => oi.ProductId == productId)))
    .ToList();

3.Minimize Data Retrieval in Large Datasets with Take() and OrderBy()

You can increase query performance when working with large datasets by using Take() to retrieve only the top few results and properly sorting the data.

For example:

var recentOrders = await _context.Orders
    .OrderByDescending(o => o.OrderDate)
    .Take(10)
    .ToListAsync();

4.Avoid Querying for Count
For example:

var customerCount = await _context.Customers
    .Where(c => c.Country == "VN")
    .CountAsync();

All of the records that meet the condition will be sorted and counted by this query but this is inefficient if all you want to know is if there is at least one record

var hasCustomers = await _context.Customers
    .Where(c => c.Country == "VN")
    .AnyAsync();

5.Using CompileAsyncQuery() for Repeated Queries

Use compiled queries to minimize the overhead of parsing and compiling the query each time it is executed if you have a query that is run frequently.

private static readonly Func<MyDbContext, int, Task<Product>> GetProductByIdQuery =
    EF.CompileAsyncQuery((MyDbContext context, int id) => context.Products.FirstOrDefault(p => p.Id == id));

var product = await GetProductByIdQuery(_context, productId);

6.Using AsSplitQuery for complex queries

For example

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

Without AsSplitQuery()

SELECT *
FROM Orders
LEFT JOIN OrderItems ON Orders.Id = OrderItems.OrderId
LEFT JOIN Products ON OrderItems.ProductId = Products.Id

With AsSplitQuery()

-- Query 1
SELECT * FROM Orders;

-- Query 2
SELECT * FROM OrderItems WHERE OrderId IN (List of Order Ids);

-- Query 3
SELECT * FROM Products WHERE ProductId IN (List of Product Ids);