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.

Why You Should Never Trust EF Core's Default Connection Handling

Every junior I've onboarded thinks they can hardcode a connection string and call it done. You can't. Connection strings are the keys to your kingdom. If you check them into source control — even a private repo — they'll leak. EF Core gives you a dozen ways to configure connections, and most of them are wrong for production. The WHY is simple: you need to separate configuration from code, encrypt secrets at rest, and rotate credentials without redeploying. The HOW: use the Options pattern with IConfiguration and a secrets manager like Azure Key Vault or AWS Secrets Manager. EF Core's OnConfiguring is for prototyping only. In production, register your DbContext through dependency injection and pull the connection string from a secure source. Your CTO will audit your code. Make sure the first thing they see is a proper configuration setup.

// io.thecodeforge
var builder = WebApplication.CreateBuilder(args);

// Pull connection from secure config, not hardcoded
var connectionString = builder.Configuration.GetConnectionString("BlogDb");

builder.Services.AddDbContext<BloggingContext>(options =>
    options.UseSqlServer(connectionString));

var app = builder.Build();
// ... other middleware
app.Run();

Query Splitting: The Silent Performance Killer

I was debugging a support ticket last week where a simple GET on /api/blogs took 14 seconds in production. The junior who wrote it used Include() on three navigation properties and never thought about the cartesian explosion. EF Core generates a single query with JOINs for every Include. When you chain two or three one-to-many relationships, the result set multiplies — 10 blogs, 50 posts each, 3 tags each equals 1500 rows where you expected 10. The fix isn't lazy loading. It's query splitting. Tell EF Core to split that monster JOIN into separate queries using AsSplitQuery(). This sends one query per table and assembles the graph client-side. It's safer for memory, faster for larger datasets, and the database thanks you. But remember: splitting trades fewer round trips for more consistent performance. Profile first. If your dataset is small, a single query is fine. If it's production data with real volume, split.

// io.thecodeforge
// Before: 4 JOINs => 14 seconds
var slowQuery = context.Blogs
    .Include(b => b.Posts)
    .ThenInclude(p => p.Tags)
    .ToList();

// After: 3 separate queries => 200ms
var fastQuery = context.Blogs
    .Include(b => b.Posts)
    .ThenInclude(p => p.Tags)
    .AsSplitQuery()
    .ToList();

Raw SQL: When LINQ Hits Its Limit (And You Need to Be Careful)

LINQ is great until it isn't. Complex reporting queries with window functions, full-text search, or recursive CTEs? LINQ either can't express them or generates garbage SQL. That's when you write raw SQL. EF Core supports FromSqlRaw and ExecuteSqlRaw. The WHY is performance and expressiveness. The HOW is where juniors burn themselves. Never use string concatenation. Ever. SQL injection is still a thing in 2024. Use parameterized queries with FromSqlInterpolated or format your SQL with placeholders. Also, raw SQL bypasses change tracking. If you're querying for read-only data, that's fine. If you need updates, you must attach and track manually. Rule of thumb: if your query has SELECT, JOIN, WHERE, GROUP BY, HAVING, ORDER BY, and you can do it in LINQ — do it. If it needs a subquery with ROW_NUMBER() or a recursive traversal — write raw SQL with parameters.

// io.thecodeforge
// Safe parameterized raw SQL for a Top N per Group query
var topPostsPerBlog = await context.Posts
    .FromSqlInterpolated($@"
        SELECT p.*
        FROM (
            SELECT p.*,
                   ROW_NUMBER() OVER (PARTITION BY BlogId ORDER BY PublishedDate DESC) AS rn
            FROM Posts p
        ) p
        WHERE p.rn <= {maxPerBlog}")
    .ToListAsync();