By the end of this session, you will be able to:

• Create and apply EF Core migrations to generate and evolve a real database schema
• Implement full CRUD operations — Create, Read, Update, Delete — against a live database using DbContext
• Write LINQ queries that execute at the database level for filtering, sorting, and pagination
• Understand eager loading and recognize the N+1 query problem before it reaches production

• Schema lives in code, not in a DBA's head — every column, index, and constraint is defined in C# entity classes and committed to version control alongside the application code that uses it
• Changes are incremental and reversible — each migration is a named, ordered script. You can roll forward to apply it or roll back to undo it with a single CLI command
• Teams stay in sync — when a teammate adds a new column, they add a migration. When you pull their branch and run dotnet ef database update, your local database reflects their change automatically
• No manual SQL required — EF Core generates the SQL diff from the model snapshot. You review the generated migration, verify it, and apply it — the SQL is produced for you

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Tasks",
            columns: table => new
            {
                Id          = table.Column<int>(nullable: false)
                                   .Annotation("Npgsql:ValueGenerationStrategy",
                                                NpgsqlValueGenerationStrategy.IdentityByDefaultColumn),
                Title       = table.Column<string>(nullable: false),
                IsCompleted = table.Column<bool>(nullable: false),
                CreatedAt   = table.Column<DateTime>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Tasks", x => x.Id);
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "Tasks");
    }
}

Up() runs when the migration is applied. Down() runs when it is rolled back. EF Core generates both methods — you should always review them before applying.

// CREATE — add a new row to the Tasks table
var task = new TaskItem { Title = "Write tests", CreatedAt = DateTime.UtcNow };
_context.Tasks.Add(task);
await _context.SaveChangesAsync();
// task.Id is now populated with the generated primary key

// READ ALL — load every row as a list
var tasks = await _context.Tasks.ToListAsync();

// READ ONE — look up by primary key; returns null if not found
var task = await _context.Tasks.FindAsync(id);

• LINQ on a DbSet returns IQueryable<T> — not a list. The query has not run yet; EF Core is building an expression tree in memory
• Composition is free — you can call .Where(), .OrderBy(), .Skip(), and .Take() in any order and as many times as needed; each call adds a clause to the tree, not to a result set
• A terminal operator fires the SQL — calling ToListAsync(), CountAsync(), FirstOrDefaultAsync(), or AnyAsync() translates the entire tree into one SQL statement and sends it to the database
• This is database-side filtering — only the rows that match your Where clause are returned over the network. The alternative — loading all rows into memory and filtering in C# — is never acceptable at scale

var query = _context.Tasks.AsQueryable();

if (!string.IsNullOrEmpty(filter.Search))
    query = query.Where(t =>
        EF.Functions.ILike(t.Title, $"%{filter.Search}%"));

if (filter.IsCompleted.HasValue)
    query = query.Where(t => t.IsCompleted == filter.IsCompleted.Value);

var total = await query.CountAsync();   // first SQL call — COUNT only

var items = await query
    .OrderByDescending(t => t.CreatedAt)
    .Skip((filter.Page - 1) * filter.PageSize)
    .Take(filter.PageSize)
    .ToListAsync();                     // second SQL call — the page of rows

EF.Functions.ILike maps to PostgreSQL's case-insensitive ILIKE operator. Use EF.Functions.Like for SQL Server. Both translate to a SQL predicate — no data is loaded for the search.

• The pattern — you fetch N items (one query), then for each item you access a navigation property that triggers another query — a total of N+1 database round trips
• Why it hides in development — with 5 rows in your local database, 6 queries finish in milliseconds. In production with 10,000 rows, 10,001 queries saturate the connection pool and the page times out
• The tell-tale sign — a loop body that accesses task.User, task.Category, or any navigation property without .Include() on the original query

var tasks = await _context.Tasks.ToListAsync();
foreach (var task in tasks)
{
    var user = await _context.Users.FindAsync(task.UserId); // N extra queries!
}

Replace the in-memory list in TaskService with real EF Core calls. All existing endpoints must behave identically from the outside — only the data storage layer changes.

• Migrations are version-controlled schema changes — EF Core generates them from your entity model; dotnet ef database update applies them in order
• CRUD follows a consistent pattern — add or load an entity, modify it in memory, call SaveChangesAsync() — EF Core's change tracker handles the SQL
• LINQ queries are deferred — each .Where() and .OrderBy() adds to an expression tree; the SQL is not sent until you call a terminal operator like ToListAsync()
• Always filter at the database level — compose your Where clauses before calling ToListAsync(); never load all rows and filter in memory
• The N+1 problem is silent until production — use .Include() for every related entity you know you will access to collapse N+1 round trips into a single SQL JOIN

Session 11 — Repository Pattern & DTOs

• The TaskService now talks directly to DbContext. Session 11 introduces the Repository Pattern to sit between the service layer and EF Core — making the data layer swappable and independently testable
• You will define a ITaskRepository interface, implement it with an EF Core-backed class, and inject it into the service — the service never imports EF Core again
• DTOs (Data Transfer Objects) will replace direct entity exposure in controller responses — decoupling your API's public contract from the database schema
• After Session 11, the architecture will have three clear layers: Controller → Service → Repository — each with a single responsibility and a testable seam between them

Migrate the Task Manager API from in-memory storage to a real database using EF Core.

What to build:

• Create and apply an InitialCreate migration that produces the Tasks table with all required columns
• Rewrite TaskService to use AppDbContext for all five CRUD operations, with full async/await
• Add LINQ filtering and pagination to the GetAll method — search by title (case-insensitive), filter by completion status, and return a paginated response with a total count

Acceptance criteria:

• The Tasks table exists in the database and matches the entity definition after running dotnet ef database update
• All five CRUD endpoints return correct results against the database — verified through Swagger UI
• Data created through POST /api/tasks survives an application restart and is visible on the next GET /api/tasks
• GET /api/tasks?search=meeting&page=1&pageSize=5 returns only matching tasks and the correct totalCount, without loading all rows into memory