ASP.NET Core Web APIs · Configuration, Security, Data and Operations
EF Core: Queries, Projections and Optimistic Concurrency
Use EF Core with a scoped DbContext, project queries to DTOs without N+1, and handle concurrent updates with concurrency tokens.
What you'll build
An inventory API on EF Core with SQLite: a list endpoint that projects products and their reviews in one SQL query, and a stock-reservation endpoint protected by a concurrency token, so two requests that read the same stock can't both succeed. The losing request gets a 409.
The idea
- Register the context with
AddDbContext<T>()(scoped). Use migrations (dotnet ef migrations add) in real apps; this example usesEnsureCreated()to stay self-contained. - Project with
Selectinto DTOs: EF generates one query with only the needed columns, avoiding lazy-loading N+1 queries. UseAsNoTracking()for read-only queries. - Optimistic concurrency: mark a property as a concurrency token; EF adds it to the
UPDATE ... WHEREclause and throwsDbUpdateConcurrencyExceptionwhen another update changed the row first. SQL Server hasrowversion; elsewhere, use a version column you increment.
The code
// package: [email protected]
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder();
var queries = new List<string>();
var dbPath = Path.Combine(Path.GetTempPath(), $"shop-{Guid.NewGuid():N}.db");
builder.Services.AddDbContext<ShopDb>(o => o.UseSqlite($"Data Source={dbPath}")
.LogTo(sql => queries.Add(sql), [DbLoggerCategory.Database.Command.Name], LogLevel.Information));
var app = builder.Build();
using (var scope = app.Services.CreateScope())
{
var db = scope.ServiceProvider.GetRequiredService<ShopDb>();
db.Database.EnsureCreated();
db.Products.AddRange(
new Product { Name = "Mug", Stock = 1, Reviews = [new() { Stars = 5 }, new() { Stars = 4 }] },
new Product { Name = "Teapot", Stock = 5, Reviews = [new() { Stars = 3 }] });
db.SaveChanges();
}
app.MapGet("/products", (ShopDb db) => db.Products.AsNoTracking()
.OrderBy(p => p.Name)
.Select(p => new { p.Name, p.Stock, Reviews = p.Reviews.Count, Rating = p.Reviews.Average(r => (double?)r.Stars) })
.ToListAsync());
app.MapPost("/products/{id:int}/reserve", async (int id, ShopDb db, int delayMs = 0) =>
{
var product = await db.Products.FindAsync(id);
if (product is null) return Results.NotFound();
await Task.Delay(delayMs); // simulate slow work between read and write
if (product.Stock < 1) return Results.Conflict(new { error = "out of stock" });
product.Stock -= 1;
product.Version += 1;
try { await db.SaveChangesAsync(); }
catch (DbUpdateConcurrencyException) { return Results.Conflict(new { error = "changed by another request, retry" }); }
return Results.Ok(new { product.Name, product.Stock });
});
app.Urls.Add("http://127.0.0.1:0");
await app.StartAsync();
using var http = new HttpClient { BaseAddress = new Uri(app.Urls.First()) };
queries.Clear();
await Show(await http.GetAsync("/products"), "GET /products");
Console.WriteLine($"SQL queries for the list: {queries.Count}");
var slow = http.PostAsync("/products/1/reserve?delayMs=300", null); // both requests read Stock = 1
var fast = http.PostAsync("/products/1/reserve?delayMs=0", null);
await Show(await fast, "reserve (fast)");
await Show(await slow, "reserve (slow)");
await Show(await http.PostAsync("/products/1/reserve", null), "reserve again");
await app.StopAsync();
File.Delete(dbPath);
static async Task Show(HttpResponseMessage r, string label)
{
var body = await r.Content.ReadAsStringAsync();
Console.WriteLine($"{label} -> {(int)r.StatusCode} {body}");
}
class ShopDb(DbContextOptions<ShopDb> options) : DbContext(options)
{
public DbSet<Product> Products => Set<Product>();
}
class Product
{
public int Id { get; set; }
public string Name { get; set; } = "";
public int Stock { get; set; }
[System.ComponentModel.DataAnnotations.ConcurrencyCheck] public int Version { get; set; }
public List<Review> Reviews { get; set; } = [];
}
class Review
{
public int Id { get; set; }
public int Stars { get; set; }
}
Output
GET /products -> 200 [{"name":"Mug","stock":1,"reviews":2,"rating":4.5},{"name":"Teapot","stock":5,"reviews":1,"rating":3}]
SQL queries for the list: 1
reserve (fast) -> 200 {"name":"Mug","stock":0}
reserve (slow) -> 409 {"error":"changed by another request, retry"}
reserve again -> 409 {"error":"out of stock"}Run this on your computer — see “Get Started” for setup.
Common mistakes
- Loading entities with all their columns and navigation properties for a list page, or looping and querying per item (N+1).
- Registering the DbContext as a singleton or sharing one across threads.
- "Check then update" without a concurrency token or an atomic
UPDATE ... SET Stock = Stock - 1 WHERE Stock > 0(ExecuteUpdateAsync): overselling under load. - Running
EnsureCreated()in production instead of migrations.
Key points
- Scoped DbContext, migrations for schema changes.
- Project to DTOs with Select and AsNoTracking for reads.
- Concurrency tokens (or atomic updates) prevent lost updates; return 409 and let clients retry.
Further reading: Handling concurrency conflicts · Efficient querying · ExecuteUpdate and ExecuteDelete
Practice questions
How does optimistic concurrency work in EF Core?
Answer
A concurrency token's original value is added to the UPDATE's WHERE clause; if no row matches because another update changed it, EF throws DbUpdateConcurrencyException.
Why project with Select for list endpoints?
Answer
EF generates one SQL query selecting only the needed columns, including aggregates over related data, instead of loading full entities or issuing per-row queries.
What does AsNoTracking do?
Answer
It skips change tracking for query results, which is faster and uses less memory for read-only queries.
Open in the app to mark lessons complete, take notes and track your progress.