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.Resultor.Wait()in asynchronous methods, as these can lead to deadlocks. Always useawait.
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
static async Task Main(string[] args)
{
//CallProdcut();
//CallInventory();
//callOrder();
await TestQueryPerformanceAsync();
}
public static async Task TestQueryPerformanceAsync()
{
using (var _context = new AppDbContext())
{
var productService = new ProductService(_context);
var stopwatch = new Stopwatch();
// Measure synchronous query time
stopwatch.Start();
var productsSync = productService.GetAllProducts(); // Synchronous
stopwatch.Stop();
Console.WriteLine($"Synchronous query time: {stopwatch.ElapsedMilliseconds} ms");
// Measure asynchronous query time
stopwatch.Start();
var productsAsync = await productService.GetAllProductsAsync(); // 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
Top comments (1)
EF Core + SQL server. Performance Bug to using methods async. Please check is. github.com/dotnet/SqlClient/issues...