IndianTechnoEra: Chapter 4: Async Data Access in .NET APIs (EF Core, ADO.NET, Dapper)

Async Data Access in .NET APIs

In this chapter, we will cover:

• Why async data access is mandatory for scalable APIs
• How EF Core async actually works internally
• All major async methods in EF Core
• Async transactions
• ADO.NET async (low-level access)
• Dapper async (high-performance micro-ORM)
• Real-life production examples
• Common performance traps and best practices

4.1 Why Async Data Access Is Non-Negotiable in APIs

Database operations are I/O-bound:

• Network latency
• Disk I/O
• Lock waits
• Query execution time

If you call the database synchronously:

• Your request thread is blocked
• That thread cannot serve other users
• Under load → thread starvation + timeouts

❌ Synchronous DB Access (Dangerous Under Load)

public List<Order> GetOrders()
{
    return _db.Orders.ToList(); // Blocks thread
}

✅ Asynchronous DB Access (Production-Grade)

public async Task<List<Order>> GetOrdersAsync()
{
    return await _db.Orders.ToListAsync(); // Non-blocking
}

With async:

• Thread is released while DB is executing
• Server handles thousands of concurrent requests efficiently

4.2 How EF Core Async Works Internally (Important Concept)

When you call:

await _db.Orders.ToListAsync();

EF Core:

1. Sends SQL to the database asynchronously
2. Registers a continuation callback
3. Releases the request thread
4. Resumes execution when DB returns data
5. Materializes entities into memory

⚠️ Important:

• Async does NOT make your query faster
• It makes your server more scalable

4.3 Core EF Core Async Methods (Must-Know List)

These are used in almost every production API:

Purpose	Sync	Async
Get list	ToList()	ToListAsync()
Single row	FirstOrDefault()	FirstOrDefaultAsync()
Single row	SingleOrDefault()	SingleOrDefaultAsync()
Count	Count()	CountAsync()
Exists	Any()	AnyAsync()
Find by PK	Find()	FindAsync()
Save	SaveChanges()	SaveChangesAsync()
Add	Add()	AddAsync()

✅ Rule:
If an async version exists → always use it in APIs.

4.4 Repository Pattern with Async (Production Standard)

Repository Interface

public interface ICustomerRepository
{
    Task<Customer?> GetByIdAsync(int id);
    Task<List<Customer>> GetAllAsync();
    Task<bool> ExistsAsync(string email);
    Task<int> CreateAsync(Customer customer);
}

Repository Implementation (EF Core)

public class CustomerRepository : ICustomerRepository
{
    private readonly AppDbContext _db;

    public CustomerRepository(AppDbContext db)
    {
        _db = db;
    }

    public async Task<Customer?> GetByIdAsync(int id)
    {
        return await _db.Customers
                        .AsNoTracking()
                        .FirstOrDefaultAsync(x => x.Id == id);
    }

    public async Task<List<Customer>> GetAllAsync()
    {
        return await _db.Customers
                        .AsNoTracking()
                        .ToListAsync();
    }

    public async Task<bool> ExistsAsync(string email)
    {
        return await _db.Customers
                        .AnyAsync(x => x.Email == email);
    }

    public async Task<int> CreateAsync(Customer customer)
    {
        await _db.Customers.AddAsync(customer);
        await _db.SaveChangesAsync();
        return customer.Id;
    }
}

✅ Notes:

• AsNoTracking() improves read performance
• AnyAsync() is much faster than CountAsync() > 0
• AddAsync() + SaveChangesAsync() must always be used together

4.5 Async Transactions with EF Core (Real-Life Use Case)

Example: Create Order + Create Payment + Update Wallet
All must succeed or all must rollback.

public async Task<int> CreateOrderWithTransactionAsync(Order order, Payment payment)
{
    using var transaction = await _db.Database.BeginTransactionAsync();

    try
    {
        await _db.Orders.AddAsync(order);
        await _db.SaveChangesAsync();

        payment.OrderId = order.Id;
        await _db.Payments.AddAsync(payment);
        await _db.SaveChangesAsync();

        await transaction.CommitAsync();

        return order.Id;
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

✅ Fully async:

• Transaction begin
• Commit
• Rollback

No thread blocking at any point.

4.6 Async with Raw ADO.NET (Low-Level, High Control)

When performance is critical or stored procedures are heavily used.

Async Stored Procedure Call

public async Task<List<Product>> GetProductsAsync()
{
    var result = new List<Product>();

    using var conn = new SqlConnection(_connectionString);
    await conn.OpenAsync();

    using var cmd = new SqlCommand("sp_GetProducts", conn);
    cmd.CommandType = CommandType.StoredProcedure;

    using var reader = await cmd.ExecuteReaderAsync();

    while (await reader.ReadAsync())
    {
        result.Add(new Product
        {
            Id = reader.GetInt32(0),
            Name = reader.GetString(1),
            Price = reader.GetDecimal(2)
        });
    }

    return result;
}

✅ Async used at:

• OpenAsync()
• ExecuteReaderAsync()
• ReadAsync()

4.7 Async with Dapper (High-Performance Micro ORM)

Dapper is widely used in high-throughput APIs.

Async Query

public async Task<List<Product>> GetProductsAsync()
{
    using var conn = new SqlConnection(_connectionString);

    var data = await conn.QueryAsync<Product>(
        "SELECT * FROM Products WHERE IsActive = 1"
    );

    return data.ToList();
}

Async Insert

public async Task<int> CreateAsync(Product product)
{
    using var conn = new SqlConnection(_connectionString);

    var sql = @"INSERT INTO Products (Name, Price) 
                VALUES (@Name, @Price);
                SELECT CAST(SCOPE_IDENTITY() as int);";

    return await conn.ExecuteScalarAsync<int>(sql, product);
}

✅ Dapper async is:

• Faster than EF Core in many scenarios
• Ideal for reporting APIs & heavy read workloads

4.8 Real-Life Example: Async Search API with Pagination

public async Task<PagedResult<Product>> SearchAsync(
    string keyword, int page, int size)
{
    var query = _db.Products.AsNoTracking()
                 .Where(x => x.Name.Contains(keyword));

    var total = await query.CountAsync();

    var data = await query
        .OrderBy(x => x.Name)
        .Skip((page - 1) * size)
        .Take(size)
        .ToListAsync();

    return new PagedResult<Product>
    {
        TotalRecords = total,
        Data = data
    };
}

✅ Efficient because:

• CountAsync() and ToListAsync() are non-blocking
• Only the required page is fetched

4.9 Async Bulk Operations (Important for Large Data)

Bulk Insert Example

public async Task BulkInsertAsync(List<LogEntry> logs)
{
    await _db.LogEntries.AddRangeAsync(logs);
    await _db.SaveChangesAsync();
}

⚠️ For very large datasets (50k+ records):

• Use SqlBulkCopyAsync
• EF will be slow

4.10 Common Async Data Access Mistakes (Critical)

❌ 1. Mixing Sync DB Calls in Async APIs

public async Task<IActionResult> Get()
{
    var data = _db.Products.ToList(); // Sync ❌
    return Ok(data);
}

✅ Correct:

var data = await _db.Products.ToListAsync();

❌ 2. Forgetting await on SaveChangesAsync

_db.SaveChangesAsync(); // Fire-and-forget ❌

✅ Correct:

await _db.SaveChangesAsync();

❌ 3. Long-Lived DbContext in Async

DbContext is not thread-safe.

✅ Always:

• Use scoped lifetime
• One request → one DbContext

❌ 4. Using .Result with EF Core

var user = _db.Users.FirstAsync().Result; // Deadlock risk ❌

✅ Correct:

var user = await _db.Users.FirstAsync();

4.11 Performance Tuning for Async Data Access

• ✅ Use AsNoTracking() for read-only queries
• ✅ Use AnyAsync() instead of CountAsync() > 0
• ✅ Avoid returning large graphs (use DTO projections)
• ✅ Use proper indexes in SQL
• ✅ Avoid N+1 queries
• ✅ Batch DB calls when possible
• ✅ Prefer Dapper for heavy read/report workloads

4.12 When Async Data Access Does NOT Help

Async gives no benefit if:

• DB server is already CPU-saturated
• Queries are poorly written
• Proper indexing is missing

Async improves thread scalability, not query performance.

Chapter 4 Summary

• All DB access in APIs must be async
• EF Core provides async for all major operations
• Transactions fully support async
• ADO.NET and Dapper offer low-level async control
• Async prevents:
  • Thread blocking
  • Timeouts under load
  • Request queue buildup
• Performance still depends on:
  • Query quality
  • Indexes
  • Database design