Techie BabajiLearn to Code. Build Real Skills.
ASP.NET Core Web API · Data

Entity Framework Core

Most ShopSmart API endpoints use Entity Framework Core. Beyond the basics, APIs need three things: efficient read queries that go straight to DTOs, bulk updates without loading rows, and protection against two admins overwriting each other’s changes.

Intermediate 8 min Lesson 13 of 37Examples target .NET 9.
Before you start:DTOsASP.NET Core MVC: Entity Framework Core
0/37 in ASP.NET Core Web API
On this page · 8 min · Intermediate

On this page

  1. In simple words
  2. Learning objectives
  3. Explanation
  4. Syntax
  5. Code example
  6. Step by step
  7. Real-world use
  8. Common mistakes
  9. Best practices
  10. Remember this
  11. Practice exercise
  12. Mini quiz
  13. Related topics
  14. My notes
Reading time
8 min
Difficulty
Intermediate
Updated
Oct 29, 2026

Related tutorials

Interview questions

In simple words#

EF Core in an API is a ready-made kitchen assistant: you describe what you want in C#, it prepares the SQL.

DbContext
Database session.
AsNoTracking
Fast read-only queries.
Projection
Select into DTOs directly.

Learning objectives#

  • Register a DbContext and use it in API controllers
  • Project queries directly to DTOs
  • Run set-based updates with ExecuteUpdateAsync
  • Detect lost updates with a rowversion concurrency token

Explanation#

Register the context with AddDbContext (scoped) and inject it into controllers or services. Use async methods and pass the request’s CancellationToken.

For GET endpoints, project with Select(p => new ProductDto(...)) — EF Core selects only the needed columns and no change tracking is needed.

ExecuteUpdateAsync and ExecuteDeleteAsync (EF Core 7+) run one UPDATE or DELETE statement in the database without loading entities — ideal for “raise all prices by 10%”.

Optimistic concurrency: add a [Timestamp] byte[] Version property (SQL Server rowversion). Clients receive the version and send it back with updates. If someone changed the row meanwhile, SaveChanges throws DbUpdateConcurrencyException — return 409 Conflict.

Keep the DbContext out of response objects; never return tracked entities with navigation properties directly.

Syntax#

C# Illustrative
[Timestamp] public byte[] Version { get; set; } = [];


db.Entry(product).Property(p => p.Version).OriginalValue = Convert.FromBase64String(dto.Version);
try { await db.SaveChangesAsync(); } catch (DbUpdateConcurrencyException) { return Conflict(); }


await db.Products.Where(p => p.Stock > 0)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.1m));

Code example: Projections, bulk updates and concurrency conflicts#

C#Controllers/ProductsController.cs Illustrative
// Program.cs
using System.Net.Http.Json;
using Microsoft.EntityFrameworkCore;


var builder = WebApplication.CreateBuilder(args);
builder.Logging.ClearProviders();                                    // keep the demo output clean
builder.Services.AddControllers();
builder.Services.AddDbContext<ApiDbContext>(o => o.UseSqlServer(
    @"Server=(localdb)\mssqllocaldb;Database=ShopSmartApi_Ef;Trusted_Connection=True;TrustServerCertificate=True"));
var app = builder.Build();
using (var scope = app.Services.CreateScope())
{
    var db = scope.ServiceProvider.GetRequiredService<ApiDbContext>();
    await db.Database.EnsureDeletedAsync();                           // demo only
    await db.Database.EnsureCreatedAsync();
    db.Products.AddRange(new Product { Name = "Wireless Mouse", Price = 799m, Stock = 10 },
                         new Product { Name = "USB-C Hub", Price = 1499m, Stock = 5 },
                         new Product { Name = "Webcam", Price = 2199m, Stock = 0 });
    await db.SaveChangesAsync();
}
app.MapControllers();
app.Urls.Add("http://127.0.0.1:5213");
await app.StartAsync();


using var http = new HttpClient { BaseAddress = new Uri("http://127.0.0.1:5213") };
var asha = (await http.GetFromJsonAsync<ProductDto>("/api/products/1"))!;   // both admins open the same product
var ravi = (await http.GetFromJsonAsync<ProductDto>("/api/products/1"))!;


var first = await http.PutAsJsonAsync("/api/products/1", new { name = "Wireless Mouse", price = 749m, version = asha.Version });
Console.WriteLine(
quot;Asha saves price 749: {(int)first.StatusCode}");
var second = await http.PutAsJsonAsync("/api/products/1", new { name = "Wireless Mouse", price = 849m, version = ravi.Version });
Console.WriteLine(
quot;Ravi saves price 849: {(int)second.StatusCode} {(await second.Content.ReadFromJsonAsync<System.Text.Json.JsonElement>()).GetProperty("title")}");


var raised = await http.PostAsync("/api/products/raise-prices?percent=10", null);
Console.WriteLine(
quot;Raise in-stock prices by 10%: {await raised.Content.ReadAsStringAsync()}");
foreach (var p in (await http.GetFromJsonAsync<List<ProductDto>>("/api/products"))!)
    Console.WriteLine(
quot;  {p.Name}: {p.Price}");


await app.StopAsync();
using (var scope = app.Services.CreateScope())
    await scope.ServiceProvider.GetRequiredService<ApiDbContext>().Database.EnsureDeletedAsync();


// Data/ApiDbContext.cs
using System.ComponentModel.DataAnnotations;
using Microsoft.EntityFrameworkCore;


public class Product
{
    public int Id { get; set; }
    [MaxLength(100)] public string Name { get; set; } = "";
    [Precision(10, 2)] public decimal Price { get; set; }
    public int Stock { get; set; }
    [Timestamp] public byte[] Version { get; set; } = [];               // SQL Server rowversion
}


public class ApiDbContext(DbContextOptions<ApiDbContext> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
}


// Controllers/ProductsController.cs
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;


public record ProductDto(int Id, string Name, decimal Price, string Version);
public record UpdateProductDto(string Name, decimal Price, string Version);


[ApiController]
[Route("api/products")]
public class ProductsController(ApiDbContext db) : ControllerBase
{
    [HttpGet]
    public async Task<List<ProductDto>> GetAll(CancellationToken ct) =>
        await db.Products.OrderBy(p => p.Id)
            .Select(p => new ProductDto(p.Id, p.Name, p.Price, Convert.ToBase64String(p.Version)))
            .ToListAsync(ct);


    [HttpGet("{id:int}")]
    public async Task<ActionResult<ProductDto>> Get(int id, CancellationToken ct) =>
        await db.Products.Where(p => p.Id == id)
            .Select(p => new ProductDto(p.Id, p.Name, p.Price, Convert.ToBase64String(p.Version)))
            .FirstOrDefaultAsync(ct) is { } dto ? dto : NotFound();


    [HttpPut("{id:int}")]
    public async Task<IActionResult> Update(int id, UpdateProductDto dto, CancellationToken ct)
    {
        var product = await db.Products.FindAsync([id], ct);
        if (product is null) return NotFound();
        db.Entry(product).Property(p => p.Version).OriginalValue = Convert.FromBase64String(dto.Version);   // the version the client saw
        product.Name = dto.Name;
        product.Price = dto.Price;
        try
        {
            await db.SaveChangesAsync(ct);
            return NoContent();
        }
        catch (DbUpdateConcurrencyException)
        {
            return Problem(statusCode: StatusCodes.Status409Conflict, title: "Changed by someone else - reload and try again");
        }
    }


    [HttpPost("raise-prices")]
    public async Task<IActionResult> RaisePrices(decimal percent, CancellationToken ct)
    {
        var updated = await db.Products.Where(p => p.Stock > 0)            // one UPDATE statement, no entities loaded
            .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => Math.Round(p.Price * (1 + percent / 100), 2)), ct);
        return Ok(new { updated });
    }
}
Expected output
Asha saves price 749: 204
Ravi saves price 849: 409 Changed by someone else - reload and try again
Raise in-stock prices by 10%: {"updated":2}
  Wireless Mouse: 823.90
  USB-C Hub: 1648.90
  Webcam: 2199.00

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

  1. GET actions project straight to ProductDto, including the rowversion as a Base64 string.
  2. Asha and Ravi load product 1 with the same version. Asha saves first and the rowversion changes.
  3. Ravi’s update sends the old version; EF Core adds WHERE Version = @old to the UPDATE, no row matches, and DbUpdateConcurrencyException becomes a 409.
  4. ExecuteUpdateAsync raises prices of in-stock products in one SQL statement — the out-of-stock webcam is untouched.
  5. Asha’s 749 becomes 823.90; Ravi’s 849 was never saved.

Real-world use case#

ShopSmart’s admin app shows “This product was changed by someone else — reload?” instead of silently overwriting a colleague’s price change, and the seasonal sale job updates thousands of prices with one ExecuteUpdate.

Common mistakes#

Last write wins

Without a concurrency token, the second save silently overwrites the first. Add [Timestamp] for data several people edit.

Loading thousands of rows to update them

foreach + SaveChanges sends one UPDATE per row. Use ExecuteUpdateAsync.

Forgetting that ExecuteUpdate bypasses tracking

Entities already loaded in the context do not see the new values. Reload them if you need them afterwards.

Best practices#

  • Projections for reads
  • ExecuteUpdate/ExecuteDelete for set-based changes
  • Rowversion + 409 for concurrent edits
  • CancellationToken on all async EF calls

Remember this#

Select to DTOs for GET endpoints.

ExecuteUpdateAsync = one SQL UPDATE, no tracking.

[Timestamp] + DbUpdateConcurrencyException → 409.

Practice exercise#

Your task: Add DELETE /api/products/discontinued that deletes every product with Stock == 0 using ExecuteDeleteAsync and returns { deleted }.

Need a hint?
await db.Products.Where(p => p.Stock == 0).ExecuteDeleteAsync(ct);
Show solution
C# Illustrative
// Controllers/CleanupController.cs
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;


[ApiController]
[Route("api/products")]
public class CleanupController(AppDbContext db) : ControllerBase
{
    [HttpDelete("discontinued")]
    public async Task<IActionResult> DeleteDiscontinued(CancellationToken ct)
    {
        var deleted = await db.Products.Where(p => p.Stock == 0).ExecuteDeleteAsync(ct);
        return Ok(new { deleted });
    }
}

Mini quiz#

Entity Framework Core quizMultiple choice Question 1 of 3

What does a [Timestamp] byte[] property give you on SQL Server?

Score so far: 0/0

Want more? Practise all ASP.NET Core Web API MCQs · ASP.NET Core Web API interview questions

My notes#

Saved in this browser.

Progress is saved in this browser. Sign in to sync.