CRUD Operations — Create, Read, Update and Delete with EF Core

CRUD operations with EF Core follow consistent patterns. The change tracker is central: entities must be tracked (or attached) before changes are detected and persisted. EF Core 7+ added bulk ExecuteUpdateAsync() and ExecuteDeleteAsync() for set-based operations that skip loading entities — critical for performance when updating or deleting many rows at once. This lesson builds the complete PostRepository that is used by the PostsController for the remainder of Part 4.

Complete PostRepository #

public class PostRepository(AppDbContext db) : IPostRepository
{
    // ── CREATE ─────────────────────────────────────────────────────────────
    public async Task<Post> AddAsync(Post post, CancellationToken ct = default)
    {
        db.Posts.Add(post);              // EntityState.Added
        await db.SaveChangesAsync(ct);   // → INSERT INTO Posts ...
        return post;                     // post.Id now populated with DB-assigned value
    }

    // ── READ — single ─────────────────────────────────────────────────────
    public async Task<Post?> GetByIdAsync(int id, CancellationToken ct = default)
        => await db.Posts
            .AsNoTracking()
            .Include(p => p.Author)
            .Include(p => p.Tags)
            .FirstOrDefaultAsync(p => p.Id == id, ct);

    // ── READ — paged list ─────────────────────────────────────────────────
    public async Task<(IReadOnlyList<Post> Posts, int Total)> GetPageAsync(
        int page, int size, CancellationToken ct = default)
    {
        var query = db.Posts.AsNoTracking()
            .Where(p => p.IsPublished)
            .OrderByDescending(p => p.PublishedAt);

        var total = await query.CountAsync(ct);
        var posts = await query
            .Skip((page - 1) * size).Take(size)
            .Include(p => p.Author).Include(p => p.Tags)
            .AsSplitQuery()
            .ToListAsync(ct);

        return (posts, total);
    }

    // ── UPDATE — tracked entity ────────────────────────────────────────────
    public async Task<Post?> UpdateAsync(
        int id, Action<Post> update, CancellationToken ct = default)
    {
        // Load tracked (not AsNoTracking) so change tracking detects modifications
        var post = await db.Posts.FindAsync(new object[] { id }, ct);
        if (post is null) return null;

        update(post);                    // apply changes to tracked entity
        await db.SaveChangesAsync(ct);   // → UPDATE Posts SET ... WHERE Id = @id
        return post;
    }

    // ── DELETE — single entity ─────────────────────────────────────────────
    public async Task<bool> DeleteAsync(int id, CancellationToken ct = default)
    {
        var post = await db.Posts.FindAsync(new object[] { id }, ct);
        if (post is null) return false;

        db.Posts.Remove(post);           // EntityState.Deleted
        await db.SaveChangesAsync(ct);   // → DELETE FROM Posts WHERE Id = @id
        return true;
    }

    // ── BULK UPDATE — set-based, no entity loading (EF Core 7+) ────────────
    public async Task PublishAllPendingAsync(CancellationToken ct = default)
        => await db.Posts
            .Where(p => !p.IsPublished && p.ScheduledFor <= DateTime.UtcNow)
            .ExecuteUpdateAsync(
                s => s.SetProperty(p => p.IsPublished, true)
                      .SetProperty(p => p.PublishedAt, DateTime.UtcNow), ct);
    // → UPDATE Posts SET IsPublished=1, PublishedAt=@now
    //   WHERE IsPublished=0 AND ScheduledFor <= @now
    // No entities loaded — runs as pure SQL

    // ── BULK DELETE — set-based (EF Core 7+) ─────────────────────────────
    public async Task DeleteOldDraftsAsync(int daysOld, CancellationToken ct = default)
        => await db.Posts
            .Where(p => !p.IsPublished && p.CreatedAt < DateTime.UtcNow.AddDays(-daysOld))
            .ExecuteDeleteAsync(ct);
    // → DELETE FROM Posts WHERE IsPublished=0 AND CreatedAt < @cutoff
}

Optimistic Concurrency #

// ── Configure RowVersion for optimistic concurrency ────────────────────────
entity.Property(p => p.RowVersion)
    .IsRowVersion();   // SQL Server ROWVERSION / TIMESTAMP type
// EF Core adds WHERE RowVersion = @original to UPDATE and DELETE statements

// ── Entity ─────────────────────────────────────────────────────────────────
public class Post
{
    public byte[] RowVersion { get; set; } = [];   // auto-updated by SQL Server
}

// ── Handle DbUpdateConcurrencyException ────────────────────────────────────
try
{
    await db.SaveChangesAsync(ct);
}
catch (DbUpdateConcurrencyException ex)
{
    // Another user modified the same row between our read and write
    // Options: retry with fresh data, return 409 Conflict, merge changes
    var entry = ex.Entries.First();
    throw new ConflictException($"The {entry.Metadata.Name} was modified by another user.");
}

Common Mistakes #

Mistake 1 — Using db.Posts.Update() on an untracked entity (updates all columns) #

❌ Wrong — db.Posts.Update(post) marks all properties as modified; generates UPDATE of every column.

✅ Correct — load the tracked entity with FindAsync(), modify specific properties, call SaveChangesAsync().

Mistake 2 — Not handling DbUpdateConcurrencyException (silent data loss) #

❌ Wrong — two users edit the same post; second save silently overwrites first user’s changes.

✅ Correct — configure RowVersion, catch DbUpdateConcurrencyException, return 409 Conflict to the client.