1. Introduction

In modern applications, performance is crucial, especially when interacting with databases. Blocking operations can degrade user experience, and that’s where asynchronous queries come in handy. In this article, we will explore how to use asynchronous queries in C# with Entity Framework Core to efficiently handle database operations. You'll learn about the advantages of using async methods like ToListAsync(), FirstOrDefaultAsync(), and Task.WhenAll() to run multiple tasks concurrently.

2. Why Asynchronous Queries?

Asynchronous queries are essential in data-driven applications for several reasons:

Non-blocking operations: Asynchronous queries prevent the application from being blocked while waiting for the database to respond.
Scalability: Asynchronous programming allows an application to handle more requests by freeing up threads when waiting for database operations.
Responsiveness: Asynchronous queries keep the application, particularly the UI, responsive while background tasks complete.

3. Synchronous vs. Asynchronous Queries

Let’s compare synchronous and asynchronous queries for a better understanding.

Synchronous Query Example

public List<Product> GetAllProducts()
{
    return _context.Products.ToList(); // Synchronous, blocking
}

In this example, the ToList() method blocks the thread until the query completes and all products are retrieved from the database.

Asynchronous Query Example

public async Task<List<Product>> GetAllProductsAsync()
{
    return await _context.Products.ToListAsync(); // Asynchronous, non-blocking
}

Here, the ToListAsync() method allows the application to continue other work while the query is being processed in the background.

4. Implementing Asynchronous Queries in Services

Let’s apply asynchronous queries in the ProductService, OrderService, and InventoryService for retrieving products, managing orders, and handling inventory.

ProductService (Asynchronous)

public class ProductService
{
    private readonly AppDbContext _context;

    public ProductService(AppDbContext context)
    {
        _context = context;
    }

    // Asynchronously retrieve all products
    public async Task<List<Product>> GetAllProductsAsync()
    {
        return await _context.Products.ToListAsync(); // Non-blocking query
    }

    // Asynchronously retrieve a product by ID
    public async Task<Product> GetProductByIdAsync(int productId)
    {
        return await _context.Products.FirstOrDefaultAsync(p => p.ProductId == productId);
    }
}

OrderService (Asynchronous)

public class OrderService
{
    private readonly AppDbContext _context;

    public OrderService(AppDbContext context)
    {
        _context = context;
    }

    // Asynchronously create a new order with details
    public async Task CreateOrderAsync(List<OrderDetail> orderDetails)
    {
        var order = new Order
        {
            OrderDate = DateTime.Now,
            OrderDetails = orderDetails
        };

        _context.Orders.Add(order);
        await _context.SaveChangesAsync();  // Asynchronous save
    }

    // Asynchronously retrieve all orders with details
    public async Task<List<Order>> GetAllOrdersAsync()
    {
        return await _context.Orders
                             .Include(o => o.OrderDetails)
                             .ThenInclude(od => od.Product)
                             .ToListAsync();
    }

    // Asynchronously retrieve recent orders within a specific number of days
    public async Task<List<Order>> GetRecentOrdersAsync(int days)
    {
        return await _context.Orders
                             .Where(o => o.OrderDate >= DateTime.Now.AddDays(-days))
                             .Include(o => o.OrderDetails)
                             .ThenInclude(od => od.Product)
                             .ToListAsync();
    }
}

InventoryService (Asynchronous)

public class InventoryService
{
    private readonly AppDbContext _context;

    public InventoryService(AppDbContext context)
    {
        _context = context;
    }

    // Asynchronously get total stock for a product
    public async Task<int> GetTotalStockByProductAsync(int productId)
    {
        return await _context.Inventories
                             .Where(i => i.ProductId == productId)
                             .SumAsync(i => i.Quantity);
    }
}

5. Running Multiple Asynchronous Queries Using `Task.WhenAll`

When you need to fetch data from multiple services concurrently (e.g., ProductService, OrderService, InventoryService), you can use Task.WhenAll to execute asynchronous queries in parallel. This helps improve efficiency by running multiple queries concurrently rather than sequentially.

To orchestrate the concurrent execution of queries, we’ll create a ReportService that handles data fetching from all three services.

ReportService Example

public class ReportService
{
    private readonly InventoryService _inventoryService;
    private readonly OrderService _orderService;
    private readonly ProductService _productService;

    public ReportService(InventoryService inventoryService, OrderService orderService, ProductService productService)
    {
        _inventoryService = inventoryService;
        _orderService = orderService;
        _productService = productService;
    }

    // Method to run multiple asynchronous queries concurrently
    public async Task RunMultipleQueriesAsync()
    {
        var productsTask = _productService.GetAllProductsAsync();    // Fetch all products
        var ordersTask = _orderService.GetAllOrdersAsync();          // Fetch all orders
        var inventoryTask = _inventoryService.GetAllInventoriesAsync(); // Fetch all inventory records

        // Run all tasks concurrently and wait for them to complete
        await Task.WhenAll(productsTask, ordersTask, inventoryTask);

        // Access the results from all three services
        var products = await productsTask;
        var orders = await ordersTask;
        var inventories = await inventoryTask;

        Console.WriteLine($"Fetched {products.Count} products, {orders.Count} orders, and {inventories.Count} inventory records.");
    }
}

When to Use This Method

You should use this method when:

You need to fetch data from multiple services concurrently.
You want to improve performance by running tasks in parallel.

How to Invoke This Method

Once you have this ReportService, you can invoke the method in Program.cs or from a controller to fetch data concurrently:

using (var context = new AppDbContext())
{
    var inventoryService = new InventoryService(context);
    var orderService = new OrderService(context);
    var productService = new ProductService(context);

    var reportService = new ReportService(inventoryService, orderService, productService);

    // Run the method to fetch products, orders, and inventory concurrently
    await reportService.RunMultipleQueriesAsync();
}

6. Avoiding Common Pitfalls

While asynchronous queries can boost performance, there are some common pitfalls to avoid:

Mixing Synchronous and Asynchronous Code: Once you start an asynchronous workflow, avoid using synchronous methods. Mixing them can cause blocking and reduce performance.

Bad Example:

   public async Task<List<Product>> GetProductsAndOrdersAsync()
   {
       var products = _context.Products.ToList();  // Synchronous call
       var orders = await _context.Orders.ToListAsync();
       return products;
   }

Good Example:

   public async Task<List<Product>> GetProductsAndOrdersAsync()
   {
       var products = await _context.Products.ToListAsync();  // Asynchronous call
       var orders = await _context.Orders.ToListAsync();
       return products;
   }

Deadlocks: Avoid using Task.Result or .Wait() in asynchronous methods, as these can lead to deadlocks. Always use await.

7. Performance Considerations

Asynchronous queries are not always faster in terms of raw execution time, but they improve overall application performance by allowing other tasks to run concurrently. It’s important to benchmark asynchronous and synchronous methods for your specific use case.

Performance Testing Example

public async Task TestQueryPerformanceAsync()
{
    var stopwatch = new Stopwatch();

    // Measure synchronous query time
    stopwatch.Start();
    var productsSync = _context.Products.ToList();  // Synchronous
    stopwatch.Stop();
    Console.WriteLine($"Synchronous query time: {stopwatch.ElapsedMilliseconds} ms");

    // Measure asynchronous query time
    stopwatch.Restart();
    var productsAsync = await _context.Products.ToListAsync();  // Asynchronous
    stopwatch.Stop();
    Console.WriteLine($"Asynchronous query time: {stopwatch.ElapsedMilliseconds} ms");
}

8. Conclusion

In this article, we explored how to implement asynchronous queries in C# using Entity Framework Core. We discussed the importance of asynchronous programming for responsiveness and scalability, learned how to run multiple queries concurrently using Task.WhenAll, and saw how asynchronous queries can improve performance in data-driven applications.

By using async methods such as ToListAsync(), FirstOrDefaultAsync(), and SaveChangesAsync(), and orchestrating queries with Task.WhenAll, you can enhance your application’s efficiency and responsiveness.

Source Code EFCoreDemo

EFCore Tutorial P7:Asynchronous queries