Database Connectivity (Entity Framework Core Basics)

Introduction

Entity Framework Core is the ORM mapping C# classes to relational tables — LINQ queries translate to SQL. DbContext manages change tracking, migrations version schema, and providers connect PostgreSQL, SQL Server, SQLite.

Interviewers ask tracking vs AsNoTracking, N+1, migration strategy, and raw SQL when LINQ insufficient.

The story

A CRM support agent opens a customer record and needs all related orders loaded in one database round trip — not one query per order (the classic N+1 problem). EF Core maps C# entities to SQL tables, and Include eager-loads the order collection while AsNoTracking keeps read-only screens fast.

Understanding the topic

Key concepts

• DbContext represents session with DbSet tables.
• Code-first migrations dotnet ef migrations add.
• Fluent API or attributes configure keys, indexes.
• LINQ IQueryable translates to SQL.
• AsNoTracking for read-only queries.
• SaveChanges persists unit of work transaction.

flowchart TB
  Controller --> DbContext[AppDbContext]
  DbContext --> ChangeTracker[Change Tracker]
  ChangeTracker --> SQL[SQL Provider]
  SQL --> Database[(PostgreSQL / SQL Server)]

Step-by-step explanation

1. Register AddDbContext Scoped.
2. Inject context in repository/service.
3. Query with Where/Include/Select.
4. Add/Update/Remove mark entities; SaveChanges commits.
5. Migration generates Up/Down SQL scripts.
6. Connection string in appsettings + secrets.

Practical code example

EF Core entity, DbContext, and async query with Include:

namespace TechLearningPro.EfCore;

public sealed class Customer
{
    public Guid Id { get; set; }
    public string Email { get; set; } = "";
    public List<Order> Orders { get; set; } = [];
}

public sealed class Order
{
    public Guid Id { get; set; }
    public Guid CustomerId { get; set; }
    public decimal Total { get; set; }
    public Customer Customer { get; set; } = null!;
}

public sealed class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Customer> Customers => Set<Customer>();
    public DbSet<Order> Orders => Set<Order>();
}

public sealed class CustomerQueries(AppDbContext db)
{
    public Task<Customer?> GetWithOrdersAsync(Guid id, CancellationToken ct) =>
        db.Customers.AsNoTracking()
            .Include(c => c.Orders)
            .FirstOrDefaultAsync(c => c.Id == id, ct);
}

Line-by-line code explanation

• class Customer with List<Order> Orders models a one-to-many relationship.
• class Order with CustomerId foreign key links back to the parent customer.
• AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options) is the EF session per request.
• DbSet<Customer> Customers => Set<Customer>() exposes the customers table as a queryable set.
• CustomerQueries(AppDbContext db) injects the context into a query service class.
• db.Customers.AsNoTracking() disables change tracking for read-only API responses.
• .Include(c => c.Orders) eager-loads related orders in the same SQL query.
• .FirstOrDefaultAsync(c => c.Id == id, ct) finds one customer asynchronously or returns null.

Key takeaway: Include eager-loads Orders avoiding N+1. AsNoTracking for read API responses. Primary constructor on DbContext (C# 12).

Real-world use

Where you'll use this in production

• CRUD APIs backed by PostgreSQL.
• Banking ledger with transactional SaveChanges.
• Healthcare patient records with audit columns.
• Reporting read replicas with AsNoTracking.

Best practices

• Short-lived DbContext per request Scoped.
• Project Select DTOs instead of returning entities.
• Index columns used in Where/Join.
• Split queries for large Include graphs.
• Retry on transient failures EnableRetryOnFailure.

Common mistakes

• N+1 lazy loading in loops.
• Tracking large graphs unnecessarily.
• Missing migration in CI deploy.
• Storing secrets in appsettings committed to Git.

Advanced interview questions

Summary

EF Core maps objects to relational data with LINQ. DbContext Scoped; migrations manage schema. AsNoTracking and projection optimize reads. Avoid N+1 with Include or careful projection. Next: structured logging.