Entity Framework Core Basics: DbContext, Migrations & Queries Explained

Every real-world ASP.NET application eventually needs to store data — user profiles, orders, blog posts, inventory. The question isn't whether you'll talk to a database, it's how painful that conversation will be. Writing raw ADO.NET SQL by hand works, but it's brittle: typos in query strings only blow up at runtime, schema changes mean hunting down magic strings across fifty files, and mapping result rows to C# objects is pure boilerplate. That's the world before Object-Relational Mappers (ORMs).

Entity Framework Core solves this by letting you define your data model as regular C# classes, then automatically generating the SQL, handling connections, and mapping query results back to those same objects. It also tracks changes — so when you modify a property on an object and call SaveChanges(), EF Core figures out the exact UPDATE statement needed. It bridges the fundamental mismatch between how databases think (tables and rows) and how C# thinks (objects and properties).

By the end of this article you'll have a fully wired ASP.NET Core app using EF Core with SQLite: a working DbContext, a real database migration, typed LINQ queries, and a proper understanding of why each piece exists. You'll also know the three most common mistakes that burn developers in production — and exactly how to avoid them.

What Is DbContext and How Do You Set It Up?

DbContext is the bridge between your C# code and the database. Think of it as a session — it holds the connection, tracks changes to entities, and manages identity mapping (two references to the same row return the same object).

Setting up a DbContext involves three things: a class that inherits from DbContext, a set of DbSet properties for each table, and a connection string. Here's a minimal example for SQLite:

```csharp using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext { public DbSet<Order> Orders { get; set; } public DbSet<OrderItem> OrderItems { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlite("Data Source=app.db"); } } ```

In ASP.NET Core, you never call OnConfiguring manually. Instead, register the context in the DI container:

``csharp var builder = WebApplication.CreateBuilder(args); builder.Services.AddDbContext<AppDbContext>(options => options.UseSqlite(builder.Configuration.GetConnectionString("DefaultConnection"))); ``

Now controllers can inject AppDbContext directly. Each request gets a fresh context—this is critical because the change tracker accumulates state over time. A stale context leads to memory leaks and stale data.

Migrations: Evolving the Schema Without Losing Data

Migrations let you version-control your database schema. When you change a C# model (add a property, rename a column), EF Core generates a migration file with Up() and Down() methods. You apply them to the database with:

``bash dotnet ef migrations add AddOrderDate dotnet ef database update ``

Under the hood, EF Core reads your current model snapshot and compares it to the database. It generates the exact ALTER TABLE statements needed. The __EFMigrationsHistory table tracks which migrations have been applied.

Production gotcha: never run EnsureCreated() in production. It skips migrations entirely and creates the schema from scratch — losing all data if the table already exists. Use Migrate() or run migrations as part of deployment scripts.

using Microsoft.EntityFrameworkCore.Migrations;

public partial class AddOrderDate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<DateTime>(
            name: "OrderDate",
            table: "Orders",
            type: "TEXT",
            nullable: false,
            defaultValueSql: "datetime('now')");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "OrderDate",
            table: "Orders");
    }
}

Writing Queries with LINQ — The Right Way

EF Core translates LINQ queries into SQL. That's the whole point — you write C# and it becomes WHERE, JOIN, GROUP BY. But not all LINQ methods translate. Where(), Select(), Join(), OrderBy() work. FirstOrDefault(), ToList(), Count() execute immediately (they force query execution).

Here's a query that fetches all orders from last week with their item counts:

``csharp var lastWeek = DateTime.UtcNow.AddDays(-7); var orders = await context.Orders .Where(o => o.OrderDate >= lastWeek) .Select(o => new { o.Id, o.CustomerName, ItemCount = o.OrderItems.Count }) .ToListAsync(); ``

This generates a single SQL query with a subquery for the count. Notice we project to an anonymous type — that's key. If we had said .Include(o => o.OrderItems).ToList(), we'd load all items into memory just to count them.

Deferred execution trap: LINQ queries are not executed until you iterate or call a terminal method. That's why storing an IQueryable and then modifying it later can surprise you. If you change a variable used in the query before it's executed, the query picks up the new value.

Change Tracking: How EF Core Knows What to UPDATE

When you load an entity with a SELECT, EF Core takes a snapshot of its property values. Then you modify one property (e.g., order.Status = "Shipped"). When you call SaveChangesAsync(), EF Core compares current values to the snapshot and generates a SQL UPDATE for only the changed columns.

But here's the trap: if you load an entity, detach it from the context, then re-attach it later, EF Core assumes all properties are modified unless you tell it otherwise. That means it generates an UPDATE setting every column — even the ones that didn't change.

``csharp public async Task UpdateOrderAsync(Order modifiedOrder) { var existing = await context.Orders.FindAsync(modifiedOrder.Id); // Map modified fields explicitly existing.Status = modifiedOrder.Status; existing.ShippedDate = modifiedOrder.ShippedDate; await context.SaveChangesAsync(); } ``

Always load the existing entity and copy properties. Never attach a disconnected entity unless you're okay with full-column updates. For APIs that receive the full object, consider using AutoMapper with a clear map or manual assignment.

Disconnected scenarios (e.g., REST APIs) are the number one cause of unintended updates. The context has no idea which properties the client changed — it only sees the final object.

Lazy Loading vs Eager Loading vs Explicit Loading

EF Core gives you three ways to load related data. Each has cost:

• Eager loading: Use .Include() and .ThenInclude() to load related entities in one SQL query. Best for read-only UIs where you know you need the data. But too many Includes cause cartesian explosion (rows multiplied).
• Lazy loading: Navigation properties are loaded on first access. Requires services.AddDbContext<...>(options => options.UseLazyLoadingProxies()). Convenient, but each access generates a separate SQL query — classic N+1 problem.
• Explicit loading: You manually call .Load() on a collection reference. Gives you control: "Load all OrderItems for these orders in one batch".

Rule of thumb: Start with eager loading. If you see duplicate data or huge result sets, switch to explicit loading. Never enable lazy loading by default in production — it's a performance landmine.

Here's explicit loading in action:

```csharp var orders = await context.Orders.Take(100).ToListAsync(); var orderIds = orders.Select(o => o.Id).ToList(); var orderItems = await context.OrderItems .Where(i => orderIds.Contains(i.OrderId)) .ToListAsync();

foreach (var order in orders) order.OrderItems = orderItems.Where(i => i.OrderId == order.Id).ToList(); ```

Three queries, total data transferred exactly what's needed — no joins, no duplicates.