DbContext & DbSet Interview Questions & Answers

DbContext is the primary entry point for EF Core. It combines three patterns: Unit of Work (batches changes), Repository (queries via DbSet<T>), and Identity Map (tracks entity instances by primary key within the same scope).

// Define your context by deriving from DbContext:
public class AppDbContext : DbContext
{
    // Each DbSet<T> maps to a table — the query surface for that entity:
    public DbSet<Order>   Orders   { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Customer> Customers { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent API configuration lives here:
        modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
    }
}

// Register in Program.cs — the container creates a new instance per request:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

// Inject and use in a service:
public class OrderService
{
    private readonly AppDbContext _db;
    public OrderService(AppDbContext db) => _db = db;

    public async Task<Order?> GetOrderAsync(int id)
        => await _db.Orders.FindAsync(id); // uses identity map first
}

Rule of thumb: DbContext is a scoped service — one instance per HTTP request. Never register it as Singleton; the change tracker is not thread-safe.

DbSet<T> is the query and change surface for one entity type. LINQ methods on it build SQL; add/remove/update methods stage changes in the change tracker.

// Querying — translated to SQL by EF Core's query pipeline:
var orders = await _db.Orders
    .Where(o => o.CustomerId == customerId)   // WHERE
    .OrderByDescending(o => o.CreatedAt)      // ORDER BY
    .Take(20)                                  // TOP 20
    .ToListAsync();                            // executes → SELECT

// Find by primary key (checks identity map before hitting DB):
var order = await _db.Orders.FindAsync(42);

// Staging changes — do NOT hit the DB until SaveChanges:
_db.Orders.Add(new Order { CustomerId = 1 });    // INSERT
_db.Orders.Update(existingOrder);                // UPDATE (all columns)
_db.Orders.Remove(existingOrder);                // DELETE

// SaveChanges wraps all staged ops in one transaction:
await _db.SaveChangesAsync();

// Direct SQL for non-LINQ operations:
var orders = await _db.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE Status = {0}", "Pending")
    .ToListAsync();

// ExecuteSqlRawAsync for non-query statements (INSERT/UPDATE/DELETE):
await _db.Database.ExecuteSqlRawAsync(
    "DELETE FROM Orders WHERE CreatedAt < {0}", cutoff);

Rule of thumb: Use LINQ on DbSet<T> for queries — EF translates it to optimised SQL. Fall back to FromSqlRaw or ExecuteSqlRawAsync only for operations LINQ can't express.

DbContextOptions<TContext> carries the provider choice, connection string, and behaviour options. It's set during registration and injected into the context constructor.

// SQL Server:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("Default"),
        sql => sql.CommandTimeout(60)    // per-query timeout in seconds
                  .MigrationsAssembly("MyApp.Data") // separate assembly
    ));

// SQLite (dev / tests):
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlite("Data Source=app.db"));

// PostgreSQL (Npgsql):
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(connectionString));

// In-memory (tests only — not a real DB; no transactions or constraints):
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseInMemoryDatabase("TestDb"));

// Add diagnostics in development:
if (builder.Environment.IsDevelopment())
{
    options.EnableSensitiveDataLogging(); // shows param values in logs
    options.EnableDetailedErrors();        // richer error messages
}

// Manual construction (e.g., in a factory):
var opts = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlServer(connectionString)
    .Options;

using var db = new AppDbContext(opts);

Rule of thumb: Use AddDbContext<T> for web apps — it handles lifetime and disposal. Build DbContextOptions manually only in tests or migration tooling.

The change tracker watches entity instances and records mutations so SaveChanges can generate the correct SQL without you specifying it.

// Entity states:
// Added    → INSERT on SaveChanges
// Modified → UPDATE on SaveChanges
// Deleted  → DELETE on SaveChanges
// Unchanged→ no SQL
// Detached → not tracked at all

var order = await _db.Orders.FindAsync(42); // state: Unchanged
order.Status = "Shipped";                   // state: Modified (detected automatically)
await _db.SaveChangesAsync();               // generates: UPDATE Orders SET Status=... WHERE Id=42

// Manual state management:
_db.Entry(order).State = EntityState.Modified; // mark all columns dirty
_db.Entry(order).Property(o => o.Status).IsModified = true; // only one column

// Inspect current state:
Console.WriteLine(_db.Entry(order).State); // EntityState.Modified

// AsNoTracking — read-only queries; no change tracker overhead:
var orders = await _db.Orders
    .AsNoTracking()
    .Where(o => o.Status == "Pending")
    .ToListAsync();
// Useful for read-heavy endpoints — faster, lower memory usage

// Attach a detached entity and mark it modified:
_db.Orders.Attach(detachedOrder);
_db.Entry(detachedOrder).State = EntityState.Modified;
await _db.SaveChangesAsync();

Rule of thumb: Use AsNoTracking() on read-only queries — it skips change tracker overhead and is measurably faster. Only track entities you intend to mutate.

SaveChanges / SaveChangesAsync inspects the change tracker, generates SQL for every Added, Modified, and Deleted entity, and executes all of them in a single implicit transaction.

// Everything between two SaveChanges calls is one unit of work:
var customer = new Customer { Name = "Alice" };
_db.Customers.Add(customer);                  // queued: INSERT Customer

var product = await _db.Products.FindAsync(5);
product.Stock -= 1;                            // queued: UPDATE Product

var order = new Order { CustomerId = customer.Id, ProductId = 5 };
_db.Orders.Add(order);                         // queued: INSERT Order

// One round trip — all three ops in one transaction:
int rows = await _db.SaveChangesAsync();       // returns count of affected rows

// Manual transaction for multiple SaveChanges calls:
await using var tx = await _db.Database.BeginTransactionAsync();
try
{
    _db.Orders.Add(order);
    await _db.SaveChangesAsync();          // first save — inside tx

    _db.Invoices.Add(invoice);
    await _db.SaveChangesAsync();          // second save — same tx

    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

// Intercept every save with SaveChangesInterceptor:
public class AuditInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(...)
    {
        foreach (var entry in context.ChangeTracker.Entries<IAuditable>())
            entry.Entity.UpdatedAt = DateTime.UtcNow; // stamp before save
        return base.SavingChangesAsync(...);
    }
}

Rule of thumb: Call SaveChangesAsync once per logical operation. Multiple SaveChanges calls in one request need an explicit transaction to stay atomic.

DbContext is stateful — the change tracker holds entity references tied to a specific database connection. Registering it wrong causes data corruption or leaks.

// Scoped (correct) — one DbContext per HTTP request:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));
// Default lifetime from AddDbContext is Scoped.
// Each request gets its own change tracker and connection.

// Singleton — DbContext shared across ALL requests concurrently:
// - Change tracker corrupted by overlapping requests.
// - Connection held open forever → exhausts the pool.
// - First writer's uncommitted data visible to other requests.

// Transient — new DbContext for every injection within a request:
// - An entity modified in ServiceA's context is NOT visible in ServiceB's context.
// - SaveChanges in ServiceA doesn't commit ServiceB's work.
// - Double the connection pool usage per request.

// Background services must create their own scope:
public class ReportWorker : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;
    public ReportWorker(IServiceScopeFactory scopeFactory)
        => _scopeFactory = scopeFactory;

    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        using var scope = _scopeFactory.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        // db is a fresh, properly scoped context for this job
    }
}

Rule of thumb: DbContext must be Scoped. Background services and singletons must never inject it directly — use IServiceScopeFactory instead.

AddDbContextPool reuses DbContext instances from a pool rather than creating a new one per request — removing the allocation and garbage collection cost per context (though EF Core resets the instance before reuse).

// Standard — creates a new AppDbContext per request:
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

// Pooled — reuses instances from a pool (default size: 1024):
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(connectionString),
    poolSize: 128);        // tune to expected concurrency

// Constraints with pooling:
// 1. AppDbContext constructor MUST accept only DbContextOptions — no extra injected deps.
// If you need IConfiguration, use the IDbContextFactory pattern instead.
// 2. OnConfiguring is called once, not per-request — configuration must be in options.
// 3. EF resets state (change tracker, transactions) but custom fields are NOT reset.
// Store no request-specific state in the context.

// IDbContextFactory — create short-lived contexts on demand (e.g. in Blazor Server):
builder.Services.AddDbContextFactory<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

public class ProductImporter
{
    private readonly IDbContextFactory<AppDbContext> _factory;
    public ProductImporter(IDbContextFactory<AppDbContext> factory)
        => _factory = factory;

    public async Task ImportAsync(IEnumerable<Product> products)
    {
        await using var db = await _factory.CreateDbContextAsync();
        db.Products.AddRange(products);
        await db.SaveChangesAsync();
    } // context disposed here — safe in Blazor Server long-lived components
}

Rule of thumb: Use AddDbContextPool for high-throughput APIs where context creation is a measurable overhead. Use IDbContextFactory in Blazor Server or background jobs that need fine-grained context lifetime control.

OnConfiguring is the context's own method for self-configuring; AddDbContext configures externally via DI. The two approaches differ in testability and coupling.

// OnConfiguring — hardcodes provider; can't swap in tests:
public class AppDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder builder)
    {
        // Connection string hardcoded; no way to inject a test database:
        builder.UseSqlServer("Server=prod;Database=App;...");
    }
}

// Constructor injection + AddDbContext (preferred):
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options) { }   // options come from the DI container

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
    }
}

// Wire-up in Program.cs — swap to SQLite in tests without changing the context:
builder.Services.AddDbContext<AppDbContext>(o =>
    o.UseSqlServer(builder.Configuration.GetConnectionString("Default")));

// Test setup:
services.AddDbContext<AppDbContext>(o =>
    o.UseSqlite("Data Source=:memory:"));

OnConfiguring is only appropriate when the context is constructed manually (migration tooling, design-time factories).

Rule of thumb: Never hardcode a connection string in OnConfiguring in production code. Pass DbContextOptions via the constructor so tests can inject a different provider.

EF Core offers two mapping approaches: data annotations (attributes on the class) and Fluent API (code in OnModelCreating). Fluent API is more powerful and keeps domain models free of infrastructure concerns.

// Data annotation approach — quick but pollutes the domain model:
[Table("Orders")]
public class Order
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [Required]
    [MaxLength(50)]
    public string Status { get; set; } = "";

    [Column(TypeName = "decimal(18,2)")]
    public decimal Total { get; set; }
}

// Fluent API approach — preferred; domain model stays clean:
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Orders");
        builder.HasKey(o => o.Id);
        builder.Property(o => o.Status)
               .IsRequired()
               .HasMaxLength(50);
        builder.Property(o => o.Total)
               .HasColumnType("decimal(18,2)");
        builder.HasIndex(o => o.CustomerId); // index on FK
    }
}

// Register all IEntityTypeConfiguration<T> in one call:
protected override void OnModelCreating(ModelBuilder mb)
    => mb.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);

Rule of thumb: Use Fluent API via IEntityTypeConfiguration<T> and ApplyConfigurationsFromAssembly. It scales to large models and keeps entity classes free of EF-specific attributes.

Value conversions let EF Core convert a property value between its in-memory CLR type and the database column type — useful for enums, strongly typed IDs, JSON columns, and encrypted fields.

// Store an enum as a string instead of an int:
public enum OrderStatus { Pending, Shipped, Delivered, Cancelled }

builder.Property(o => o.Status)
       .HasConversion<string>()   // stores "Pending", not 0
       .HasMaxLength(20);

// Strongly typed ID value object:
public record OrderId(int Value);

builder.Property(o => o.Id)
       .HasConversion(
           id => id.Value,        // CLR → DB
           val => new OrderId(val)); // DB → CLR

// JSON column (EF Core 7+) — stores a complex type as JSON:
builder.OwnsOne(o => o.ShippingAddress, addr =>
{
    addr.ToJson(); // stored as a JSON column — one column, full object
});

// Custom converter class:
public class MoneyConverter : ValueConverter<Money, decimal>
{
    public MoneyConverter() : base(
        m => m.Amount,              // CLR → DB
        d => new Money(d, "USD"))   // DB → CLR
    { }
}

builder.Property(o => o.Price)
       .HasConversion(new MoneyConverter());

Rule of thumb: Use value conversions to keep your domain model clean and strongly typed. Store enums as strings for readability in production databases; store value objects with HasConversion or ToJson rather than flattening manually.

Global query filters apply a WHERE clause automatically to every query on an entity — the standard EF Core mechanism for soft deletes and multi-tenancy.

// Entity with soft-delete flag:
public class Order
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }
    public int TenantId { get; set; }
    // ...
}

// Register global filter in OnModelCreating:
modelBuilder.Entity<Order>()
    .HasQueryFilter(o => !o.IsDeleted);
// Every query: SELECT * FROM Orders WHERE IsDeleted = 0 — automatic

// Multi-tenancy filter (inject the current tenant via constructor):
public class AppDbContext : DbContext
{
    private readonly int _tenantId;

    public AppDbContext(DbContextOptions<AppDbContext> opts, ITenantContext tenant)
        : base(opts)
    {
        _tenantId = tenant.CurrentTenantId;
    }

    protected override void OnModelCreating(ModelBuilder mb)
    {
        mb.Entity<Order>().HasQueryFilter(o =>
            !o.IsDeleted && o.TenantId == _tenantId);
    }
}

// Bypass the filter when needed (admin operations, data migration):
var allOrders = await _db.Orders
    .IgnoreQueryFilters()      // disables global filters for this query
    .ToListAsync();

// Soft delete — just set the flag, don't call Remove:
order.IsDeleted = true;
await _db.SaveChangesAsync(); // UPDATE Orders SET IsDeleted=1 WHERE Id=...

Rule of thumb: Use global query filters for cross-cutting concerns (soft delete, multi-tenancy) so they're applied consistently. Add IgnoreQueryFilters() only in explicit admin or migration paths.

Keyless entity types (HasNoKey) map to database objects that have no primary key — database views, raw SQL result sets, or tables used only for reporting. They are always read-only; EF Core never tracks or saves them.

// DTO mapped to a view (no primary key):
public class OrderSummaryView
{
    public int CustomerId { get; set; }
    public string CustomerName { get; set; } = "";
    public int OrderCount { get; set; }
    public decimal TotalRevenue { get; set; }
}

// Configuration — mark as keyless and point to the view:
modelBuilder.Entity<OrderSummaryView>(b =>
{
    b.HasNoKey();                          // no PK — never tracked
    b.ToView("vw_OrderSummaries");         // maps to a DB view
});

// Expose via a DbSet property on the context:
public DbSet<OrderSummaryView> OrderSummaries { get; set; }

// Query exactly like any DbSet — fully composable:
var top10 = await _db.OrderSummaries
    .AsNoTracking()
    .OrderByDescending(v => v.TotalRevenue)
    .Take(10)
    .ToListAsync();
// SQL: SELECT TOP 10 ... FROM vw_OrderSummaries ORDER BY TotalRevenue DESC

// Use FromSqlRaw with a keyless type for arbitrary result shapes:
var results = await _db.OrderSummaries
    .FromSqlRaw("EXEC sp_GetOrderSummaries @Year = {0}", 2026)
    .ToListAsync();

Rule of thumb: Use keyless entity types for read-only views and stored procedure results. Never call SaveChanges with them — EF has no way to generate INSERT/UPDATE/ DELETE without a primary key.

SqlQuery<T> (EF Core 8+) executes raw SQL and maps results to any CLR type — including primitives and non-entity types — without requiring a DbSet<T> or keyless entity registration. FromSqlRaw only works with types known to the EF model.

// FromSqlRaw — requires the type to be in the EF model (entity or keyless):
var orders = await _db.Orders
    .FromSqlRaw("SELECT * FROM Orders WHERE Status = {0}", "Pending")
    .ToListAsync(); // Order must be a tracked or keyless type

// SqlQuery<T> (EF Core 8+) — works with any scalar or simple CLR type:

// Scalar: fetch a single value:
var count = await _db.Database
    .SqlQuery<int>($"SELECT COUNT(*) FROM Orders WHERE Status = 'Pending'")
    .SingleAsync();

// Primitive list:
var ids = await _db.Database
    .SqlQuery<int>($"SELECT Id FROM Orders WHERE Total > {threshold}")
    .ToListAsync();

// Anonymous-like DTO (must be a concrete class with matching property names):
public record StatusCount(string Status, int Count);

var breakdown = await _db.Database
    .SqlQuery<StatusCount>(
        $"SELECT Status, COUNT(*) AS Count FROM Orders GROUP BY Status")
    .ToListAsync();

// Compose LINQ on top (EF wraps it in a subquery):
var top = await _db.Database
    .SqlQuery<StatusCount>(
        $"SELECT Status, COUNT(*) AS Count FROM Orders GROUP BY Status")
    .OrderByDescending(r => r.Count)
    .Take(3)
    .ToListAsync();

Rule of thumb: Use SqlQuery<T> when you need raw SQL that returns scalars, primitives, or ad-hoc DTOs not registered in the EF model. Use FromSqlRaw when you want to compose LINQ on top of a raw SQL fragment that returns a tracked entity type.

Interceptors hook into EF Core's internal pipeline at specific points — command execution, connection events, and SaveChanges — without modifying entity classes.

// Auditing interceptor — stamp UpdatedAt before every save:
public class AuditInterceptor : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(
        DbContextEventData eventData, InterceptionResult<int> result)
    {
        StampAuditFields(eventData.Context!);
        return base.SavingChanges(eventData, result);
    }

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, InterceptionResult<int> result,
        CancellationToken ct = default)
    {
        StampAuditFields(eventData.Context!);
        return base.SavingChangesAsync(eventData, result, ct);
    }

    private static void StampAuditFields(DbContext db)
    {
        var now = DateTime.UtcNow;
        foreach (var entry in db.ChangeTracker.Entries<IAuditable>())
        {
            if (entry.State == EntityState.Added)
                entry.Entity.CreatedAt = now;
            if (entry.State is EntityState.Added or EntityState.Modified)
                entry.Entity.UpdatedAt = now;
        }
    }
}

// Command interceptor — log slow queries:
public class SlowQueryInterceptor : DbCommandInterceptor
{
    public override DbDataReader ReaderExecuted(
        DbCommand command, CommandExecutedEventData data, DbDataReader result)
    {
        if (data.Duration > TimeSpan.FromSeconds(1))
            Console.WriteLine($"SLOW QUERY ({data.Duration.TotalMs}ms): {command.CommandText}");
        return result;
    }
}

// Register interceptors on the context:
builder.Services.AddDbContext<AppDbContext>(options =>
    options
        .UseSqlServer(connectionString)
        .AddInterceptors(new AuditInterceptor(), new SlowQueryInterceptor()));

Rule of thumb: Use interceptors for cross-cutting concerns (auditing, logging, soft-delete) that shouldn't pollute entity classes or service code. Prefer SaveChangesInterceptor over overriding SaveChanges directly — it composes cleanly.

DbContext already implements the Unit of Work pattern (change tracker + SaveChanges) and DbSet<T> already acts as a Repository (query + add/remove surface). Wrapping them in another layer duplicates those abstractions. However, a thin repository interface can be justified for testability or for abstracting query logic.

// Anti-pattern — generic repository that mirrors DbSet exactly:
public interface IRepository<T>
{
    Task<T?> GetByIdAsync(int id);
    Task AddAsync(T entity);
    void Remove(T entity);
    Task SaveAsync(); // re-exposes SaveChanges — leaks UoW into the repo
}
// Problem: SaveAsync in each repository breaks the Unit of Work contract.
// Two repos saving independently means two transactions.

// Better — inject DbContext directly in services (simple apps):
public class OrderService
{
    private readonly AppDbContext _db;
    public OrderService(AppDbContext db) => _db = db;

    public async Task<List<Order>> GetPendingAsync()
        => await _db.Orders
            .AsNoTracking()
            .Where(o => o.Status == "Pending")
            .ToListAsync();
}

// Acceptable thin interface — encapsulates query logic, not UoW:
public interface IOrderRepository
{
    Task<List<Order>> GetPendingAsync(CancellationToken ct = default);
    Task<Order?> GetWithItemsAsync(int orderId, CancellationToken ct = default);
}

public class EfOrderRepository : IOrderRepository
{
    private readonly AppDbContext _db;
    public EfOrderRepository(AppDbContext db) => _db = db;

    public Task<List<Order>> GetPendingAsync(CancellationToken ct)
        => _db.Orders.AsNoTracking().Where(o => o.Status == "Pending").ToListAsync(ct);

    public Task<Order?> GetWithItemsAsync(int orderId, CancellationToken ct)
        => _db.Orders.Include(o => o.Items).FirstOrDefaultAsync(o => o.Id == orderId, ct);
}
// SaveChanges remains on the context — owned by the service layer, not the repo.

Rule of thumb: Don't create a generic IRepository<T> that wraps DbSet<T> — it adds indirection without benefit. A domain-specific repository interface is fine when it encapsulates complex queries. Never expose SaveChanges inside a repository method.