Working with Entity Framework in MVC

Analogy categoryAnalogies

DbContext and the Unit of Work

Entity Framework's DbContext class is the primary gateway between your MVC controllers and the database: it exposes DbSet<T> properties for each entity type, tracks changes made to loaded entities in memory, and translates LINQ queries into SQL when the query is enumerated. In a typical scaffolded MVC controller, a new DbContext instance is created per request (often via dependency injection or a using block), queries are issued with syntax like db.Products.Where(p => p.CategoryId == id).ToList(), and calling db.SaveChanges() commits every tracked insert, update, and delete as a single transaction — acting as a unit of work for that request.

🏏

Cricket analogy: It is like a single scorer's ledger opened fresh for each innings, tracking every run and wicket change in memory, and only when the innings ends does the official scorebook get updated in one committed entry, similar to how a Test match scorecard is finalized per session.

Querying with LINQ and Deferred Execution

LINQ queries against a DbSet, such as db.Orders.Where(o => o.CustomerId == id).OrderByDescending(o => o.OrderDate), build an expression tree rather than executing immediately; the query only runs against the database when it is enumerated — via ToList(), a foreach loop, or a Razor view iterating over it. This deferred execution means chaining .Include(o => o.OrderItems) before enumeration produces a single SQL query with a JOIN, while accessing a navigation property after the fact, outside the original query, can trigger lazy-loading and issue an extra round-trip per row, a pattern known as the N+1 query problem that silently kills performance on list pages.

🏏

Cricket analogy: It is like a captain planning a bowling change mentally during an over — the plan (expression tree) isn't executed until the over actually starts; but if he changes the field only after each ball is bowled instead of setting it once, it costs extra time per delivery, mirroring the N+1 problem.

csharp

public class OrdersController : Controller
{
    private readonly AppDbContext db = new AppDbContext();

    public ActionResult Details(int id)
    {
        // .Include eagerly loads OrderItems in ONE SQL query (JOIN)
        var order = db.Orders
            .Include(o => o.OrderItems)
            .Include(o => o.Customer)
            .FirstOrDefault(o => o.Id == id);

        if (order == null)
            return HttpNotFound();

        return View(order);
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult Edit(Order order)
    {
        if (!ModelState.IsValid)
            return View(order);

        db.Entry(order).State = EntityState.Modified;
        db.SaveChanges(); // commits the whole change set as one transaction
        return RedirectToAction("Index");
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing) db.Dispose();
        base.Dispose(disposing);
    }
}

Change Tracking, Entity States, and Concurrency

Every entity loaded through a DbContext is tracked with a state — Added, Modified, Deleted, Unchanged, or Detached — and SaveChanges() inspects these states to generate the correct INSERT, UPDATE, or DELETE statements. In an MVC edit flow, the entity from the POST is often detached (since it was rebound by model binding, not loaded from this context instance), so you explicitly set db.Entry(order).State = EntityState.Modified before saving; skipping this in a disconnected scenario means EF has no idea what changed and does nothing. For concurrent edits, decorating a property with [Timestamp] (a rowversion column) lets EF detect if another user modified the row since you loaded it, throwing a DbUpdateConcurrencyException you can catch and resolve instead of silently overwriting someone else's changes.

🏏

Cricket analogy: It is like a DRS review system tracking exactly which decisions changed on review — upheld, overturned, or unchanged — and if two on-field umpires' notes conflict about the same ball, the system flags a discrepancy for the third umpire to resolve rather than silently picking one.

Creating a new DbContext per request and disposing it properly (or using dependency injection with a scoped lifetime) is essential — a long-lived, shared DbContext across requests is not thread-safe and will accumulate tracked entities, leading to memory growth and unpredictable stale-data bugs.

DbContext exposes DbSet<T> properties, tracks entity state, and translates LINQ into SQL when a query is enumerated.
SaveChanges() commits every tracked insert, update, and delete in one transaction, acting as a unit of work per request.
LINQ queries use deferred execution — they don't hit the database until enumerated with ToList(), a foreach, or view iteration.
Use .Include() to eagerly load related data in one query and avoid the N+1 lazy-loading problem.
Detached entities from model binding need an explicit db.Entry(entity).State = EntityState.Modified before SaveChanges().
[Timestamp] rowversion columns enable optimistic concurrency detection via DbUpdateConcurrencyException.
DbContext should be created per request and disposed properly, never shared long-lived across requests.