Entity Framework Core Provider

WitDatabase provides a full Entity Framework Core provider that enables you to use modern ORM capabilities with an embedded database. This guide covers everything from basic setup to advanced patterns.

Overview

What is Entity Framework Core?

Entity Framework Core (EF Core) is Microsoft's modern object-relational mapper (ORM) for .NET. It serves as a bridge between your application's object-oriented code and the relational database, allowing you to work with data using .NET objects instead of writing raw SQL queries.

At its heart, EF Core solves a fundamental problem: the mismatch between how we think about data in code (as objects with properties and relationships) and how databases store data (as tables with rows and columns). Instead of manually converting between these two representations, EF Core handles this translation automatically.

When you write a LINQ query like context.Users.Where(u => u.IsActive), EF Core translates it into the appropriate SQL (SELECT * FROM Users WHERE IsActive = 1), executes it against the database, and converts the results back into .NET objects. This abstraction lets you focus on your application logic rather than database mechanics.

Why Use EF Core with WitDatabase?

Combining EF Core with WitDatabase gives you the best of both worlds: the productivity and type safety of a modern ORM with the simplicity and portability of an embedded database. This combination is particularly powerful for several reasons.

Rapid Development. EF Core significantly accelerates development by eliminating boilerplate code. You define your data model as C# classes, and EF Core generates the database schema, handles CRUD operations, and manages relationships. What would take dozens of lines of ADO.NET code becomes a single LINQ expression.

Type Safety and IntelliSense. Unlike raw SQL strings, LINQ queries are checked at compile time. If you rename a property, the compiler catches every place that needs updating. IntelliSense provides autocomplete for entity properties, making it harder to introduce typos or reference non-existent columns.

Change Tracking. EF Core automatically tracks modifications to your entities. When you change a property value and call SaveChanges(), EF Core generates only the necessary UPDATE statement for the changed columns. This eliminates manual tracking of what needs to be persisted.

Migrations. Database schema changes are inevitable as applications evolve. EF Core migrations provide a systematic way to version your schema, apply changes incrementally, and roll back if needed. This is particularly valuable for desktop applications where databases exist on user machines and must be upgraded gracefully.

Testability. EF Core's architecture makes it straightforward to swap the real database for an in-memory one during testing. Combined with WitDatabase's :memory: mode, you can run fast, isolated integration tests without file system dependencies.

Portability. Since WitDatabase has no native dependencies, your EF Core application runs unchanged on Windows, Linux, macOS, and even Blazor WebAssembly. The same DbContext code works everywhere without platform-specific configuration.

How EF Core Integrates with WitDatabase

Understanding the layered architecture helps you make better decisions about when to use EF Core versus lower-level APIs. The integration follows EF Core's provider model, which is designed to abstract away database-specific details.

[[Svg Src="./witdatabase-efcore-stack.svg" Alt="witdatabase-efcore-stack"]]

When you execute a LINQ query, it flows through several layers. First, EF Core converts your LINQ expression into an abstract representation called an expression tree. The WitDatabase provider then translates this tree into SQL that WitDatabase understands, adapting any syntax or function differences. This SQL is executed through the ADO.NET layer, which manages the actual database communication. Finally, the results flow back up, with EF Core materializing the raw data into your entity objects.

This layered design means you can drop down to lower levels when needed. If EF Core's query translation doesn't produce optimal SQL for a specific case, you can use raw SQL queries while still benefiting from EF Core's materialization and change tracking.

When to Choose EF Core

EF Core excels in scenarios where development speed and maintainability matter more than squeezing out every microsecond of performance. It's the right choice for most line-of-business applications.

Business Applications. When your application has complex domain models with many entities and relationships, EF Core's navigation properties and automatic relationship management reduce errors and simplify code. An order with line items, products, categories, and customer references is natural to work with as an object graph.

CRUD-Heavy Workloads. Applications that primarily create, read, update, and delete individual records benefit enormously from change tracking. You load an entity, modify properties, and save — EF Core handles the rest.

Rapid Prototyping. When you need to iterate quickly, EF Core's code-first approach lets you define models in C# and generate the database automatically. No need to maintain separate SQL scripts during early development.

ASP.NET Core Integration. EF Core integrates seamlessly with ASP.NET Core's dependency injection, configuration, and logging systems. A single line registers your DbContext, and it's automatically available throughout your application.

Team Development. In teams where not everyone is a SQL expert, EF Core levels the playing field. Developers can be productive with database operations without deep SQL knowledge, while experts can still optimize critical paths with raw queries.

When to Consider Alternatives

While EF Core is excellent for most scenarios, there are situations where ADO.NET or the Core API might be better choices.

Bulk Data Operations. Inserting or updating millions of rows is not EF Core's strength. Change tracking has memory overhead, and EF Core generates individual INSERT statements rather than bulk operations. For large data migrations or imports, ADO.NET with explicit transactions is more efficient.

Complex Reporting Queries. Queries with multiple levels of grouping, window functions, CTEs, and complex aggregations can be difficult to express in LINQ. When you find yourself fighting the query translator, writing raw SQL is often clearer and more maintainable.

Maximum Performance. EF Core adds overhead for change tracking, identity resolution, and object materialization. For hot paths where every microsecond counts, ADO.NET provides direct access with minimal abstraction.

Simple Key-Value Storage. If your data model is essentially a key-value store without relationships or complex queries, the Core API provides maximum performance with minimum overhead.

The good news is that these approaches aren't mutually exclusive. You can use EF Core for most operations while dropping to ADO.NET for specific performance-critical sections or complex queries. All three APIs work with the same database file.

Provider Capabilities and Limitations

The WitDatabase EF Core provider implements the vast majority of EF Core features. Understanding what's supported helps you architect your application appropriately.

Most applications will find the provider fully capable. The limitations primarily affect specialized use cases like geographic applications requiring spatial queries.

What's Next

Now that you understand when and why to use EF Core with WitDatabase, the following sections will guide you through practical implementation:

• Installation & Setup — Adding packages and configuring your DbContext
• Defining Your Model — Entity classes, relationships, and configuration
• Migrations — Managing schema changes over time
• Querying Data — LINQ queries, loading strategies, and raw SQL
• Saving Data — Insert, update, delete, and transactions
• Change Tracking — Understanding how EF Core tracks modifications
• Performance — Optimization techniques for production
• Advanced Topics — Value converters, interceptors, and more

Installation & Setup

Getting started with EF Core and WitDatabase requires installing the right NuGet package and configuring your DbContext. This section walks you through the entire setup process, from package installation to production-ready configuration.

Package Installation

The WitDatabase EF Core provider is distributed as a single NuGet package that includes all necessary dependencies. You don't need to install the ADO.NET or Core packages separately — they're pulled in automatically.

Install the package using your preferred method:

# .NET CLI (recommended)
dotnet add package OutWit.Database.EntityFramework

# Package Manager Console (Visual Studio)
Install-Package OutWit.Database.EntityFramework

Or add directly to your .csproj file:

<ItemGroup>
  <PackageReference Include="OutWit.Database.EntityFramework" Version="1.0.0" />
</ItemGroup>

The package has the following dependency chain, all resolved automatically:

[[Svg Src="./witdatabase-efcore-dependencies.svg" Alt="witdatabase-efcore-dependencies"]]

If you need additional features like ChaCha20 encryption for Blazor WebAssembly or browser storage, install the corresponding packages alongside:

# For Blazor WebAssembly browser storage
dotnet add package OutWit.Database.Core.IndexedDb

# For ChaCha20-Poly1305 encryption (ARM, WASM without AES-NI)
dotnet add package OutWit.Database.Core.BouncyCastle

Creating Your First DbContext

The DbContext is the central class in EF Core — it represents a session with the database and provides APIs for querying and saving data. Every EF Core application needs at least one DbContext class.

A minimal DbContext requires two things: one or more DbSet<T> properties representing your tables, and configuration telling EF Core to use WitDatabase. Here's the simplest possible setup:

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseWitDb("Data Source=app.witdb");
    }
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
}

The UseWitDb() extension method registers the WitDatabase provider and accepts a connection string. The DbSet<User> property tells EF Core that you have a Users table mapped to the User class.

With this setup, you can immediately start working with your database:

using var context = new AppDbContext();

// Create the database and tables if they don't exist
context.Database.EnsureCreated();

// Add a user
context.Users.Add(new User { Name = "Alice", Email = "alice@example.com" });
context.SaveChanges();

// Query users
var users = context.Users.Where(u => u.Name.StartsWith("A")).ToList();

While this inline configuration works for simple applications and learning, production applications typically use dependency injection and external configuration.

Dependency Injection Setup

ASP.NET Core and other modern .NET applications use dependency injection (DI) to manage service lifetimes and dependencies. EF Core integrates seamlessly with this pattern through the AddDbContext method.

When using DI, your DbContext accepts options through its constructor rather than configuring them in OnConfiguring. This separation allows the application host to control database configuration:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options)
    {
    }
    
    public DbSet<User> Users => Set<User>();
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();
}

Register the DbContext in your application's service configuration. In ASP.NET Core, this typically happens in Program.cs:

var builder = WebApplication.CreateBuilder(args);

// Register DbContext with WitDatabase
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=app.witdb"));

// ... other service registrations

var app = builder.Build();

The DI container manages the DbContext lifecycle automatically. By default, AddDbContext registers the context with a "scoped" lifetime, meaning each HTTP request gets its own DbContext instance. This is the correct behavior for web applications — DbContext is not thread-safe and should not be shared across requests.

To use the DbContext in your controllers or services, simply request it through constructor injection:

public class UsersController : ControllerBase
{
    private readonly AppDbContext _context;
    
    public UsersController(AppDbContext context)
    {
        _context = context;
    }
    
    [HttpGet]
    public async Task<ActionResult<List<User>>> GetUsers()
    {
        return await _context.Users.ToListAsync();
    }
}

The DI container automatically creates and disposes the DbContext at the appropriate times. You don't need using statements or manual disposal in this pattern.

Connection String Configuration

Hardcoding connection strings in source code is acceptable for examples but problematic for real applications. Connection strings often contain sensitive information like passwords, and they vary between development, staging, and production environments.

ASP.NET Core's configuration system provides a clean solution. Store your connection string in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=app.witdb"
  }
}

Then reference it during service registration:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(builder.Configuration.GetConnectionString("DefaultConnection")!));

For environment-specific settings, create appsettings.Development.json and appsettings.Production.json files. ASP.NET Core automatically loads the appropriate file based on the ASPNETCORE_ENVIRONMENT variable:

// appsettings.Development.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=dev.witdb"
  }
}

// appsettings.Production.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=/var/app/data/prod.witdb;Encryption=aes-gcm;Password=REPLACE_IN_SECRETS"
  }
}

For sensitive values like encryption passwords, use the Secret Manager in development and environment variables or a secrets vault in production:

# Development - User Secrets
dotnet user-secrets set "ConnectionStrings:DefaultConnection" "Data Source=secure.witdb;Encryption=aes-gcm;Password=MyDevSecret"

# Production - Environment Variable
export ConnectionStrings__DefaultConnection="Data Source=/data/app.witdb;Encryption=aes-gcm;Password=ProdSecret"

Provider-Specific Options

The UseWitDb() method accepts an optional configuration action for WitDatabase-specific settings. These options control behaviors that don't exist in generic EF Core configuration:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(connectionString, witOptions =>
    {
        // Parallel write mode for high-throughput scenarios
        witOptions.UseParallelWrites(WitDbParallelMode.Auto);
        
        // Maximum concurrent writers (default: CPU count)
        witOptions.MaxWriters(8);
        
        // Maximum statements per batch (default: 1000)
        witOptions.MaxBatchSize(100);
    }));

Most applications don't need to adjust these settings. The defaults work well for typical workloads. Consider tuning only if you've identified a specific bottleneck through profiling.

DbContext Factory Pattern

The standard DbContext registration creates one instance per scope (per HTTP request in web apps). Sometimes you need more control — for example, when working with background services, parallel processing, or situations where you need multiple simultaneous contexts.

The IDbContextFactory<T> interface solves this by letting you create DbContext instances on demand:

// Registration
builder.Services.AddDbContextFactory<AppDbContext>(options =>
    options.UseWitDb(connectionString));

Inject the factory instead of the context directly:

public class OrderProcessor
{
    private readonly IDbContextFactory<AppDbContext> _contextFactory;
    
    public OrderProcessor(IDbContextFactory<AppDbContext> contextFactory)
    {
        _contextFactory = contextFactory;
    }
    
    public async Task ProcessOrdersAsync(IEnumerable<int> orderIds)
    {
        // Process orders in parallel, each with its own context
        await Parallel.ForEachAsync(orderIds, async (orderId, ct) =>
        {
            await using var context = await _contextFactory.CreateDbContextAsync(ct);
            
            var order = await context.Orders.FindAsync(orderId);
            if (order != null)
            {
                order.Status = OrderStatus.Processed;
                await context.SaveChangesAsync(ct);
            }
        });
    }
}

When you create a context through the factory, you're responsible for disposing it. Always use using or await using to ensure proper cleanup.

The factory is also useful in Blazor Server applications, where the default scoped lifetime (tied to the circuit) can cause issues with long-lived connections. Using a factory lets you create short-lived contexts for each operation.

DbContext Pooling

Creating a DbContext has some overhead — initializing the change tracker, compiling the model, setting up internal structures. For high-throughput applications handling many requests per second, this overhead adds up.

DbContext pooling reuses context instances instead of creating new ones. When you dispose a pooled context, it's reset and returned to the pool rather than being garbage collected:

builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseWitDb(connectionString), 
    poolSize: 128);  // Maximum contexts in pool

Pooling provides significant performance benefits in high-load scenarios — benchmarks typically show 20-40% improvement in throughput. However, it comes with constraints:

No constructor injection in DbContext. Pooled contexts are reused, so their constructor runs only once. You cannot inject scoped services into a pooled DbContext. If you need per-request services, inject them into your application services instead.

State must be cleared. EF Core automatically resets tracked entities and change tracker state when a context returns to the pool. However, any custom state you add to your DbContext class will persist across uses. Avoid storing request-specific data in DbContext properties.

Cannot use OnConfiguring. Configuration happens at pool creation time, not for each checkout. All configuration must happen in AddDbContextPool.

For most applications, the factory pattern (AddDbContextFactory) with pooling provides the best combination of flexibility and performance:

builder.Services.AddPooledDbContextFactory<AppDbContext>(options =>
    options.UseWitDb(connectionString),
    poolSize: 128);

Database Initialization Strategies

When your application starts, the database might not exist yet, or it might need schema updates. EF Core provides several approaches to handle this.

EnsureCreated creates the database and all tables based on your current model. It's simple but doesn't support incremental updates — if the schema changes, you must delete and recreate the database:

using var context = new AppDbContext();
context.Database.EnsureCreated();

This approach works well for prototyping, testing, and applications where data can be regenerated (like caches).

Migrations track schema changes over time and can update existing databases incrementally. This is the recommended approach for production applications:

using var context = new AppDbContext();
context.Database.Migrate();

We'll cover migrations in detail in section 4.

EnsureDeleted removes the database entirely. Useful in testing scenarios where you want a fresh start:

context.Database.EnsureDeleted();  // Delete if exists
context.Database.EnsureCreated();  // Create fresh

For ASP.NET Core applications, run initialization during startup:

var app = builder.Build();

// Initialize database
using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    
    if (app.Environment.IsDevelopment())
    {
        // Development: recreate database
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();
    }
    else
    {
        // Production: apply migrations
        context.Database.Migrate();
    }
}

app.Run();

Console and Desktop Applications

Not all applications use dependency injection. For console apps, desktop applications (WPF, WinForms, MAUI), and other scenarios, you can configure the DbContext directly.

The simplest approach overrides OnConfiguring:

public class AppDbContext : DbContext
{
    public DbSet<User> Users => Set<User>();
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Only configure if not already configured (allows DI override)
        if (!optionsBuilder.IsConfigured)
        {
            var dbPath = Path.Combine(
                Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData),
                "MyApp",
                "data.witdb");
            
            Directory.CreateDirectory(Path.GetDirectoryName(dbPath)!);
            
            optionsBuilder.UseWitDb(

        
        

            
            Loading...
        
quot;Data Source={dbPath}");
        }
    }
}

Create and use the context with standard using patterns:

// Console app
using var context = new AppDbContext();
context.Database.EnsureCreated();

// Work with data
var users = context.Users.ToList();

For desktop applications with MVVM architecture, you might create a simple service locator or use a lightweight DI container:

public static class Services
{
    private static readonly Lazy<AppDbContext> _context = 
        new(() => CreateContext());
    
    public static AppDbContext DbContext => _context.Value;
    
    private static AppDbContext CreateContext()
    {
        var context = new AppDbContext();
        context.Database.EnsureCreated();
        return context;
    }
}

// Usage in ViewModel
public class MainViewModel
{
    public ObservableCollection<User> Users { get; } = new();
    
    public async Task LoadUsersAsync()
    {
        var users = await Services.DbContext.Users.ToListAsync();
        foreach (var user in users)
            Users.Add(user);
    }
}

For long-running desktop applications, be mindful of DbContext lifetime. A single long-lived context accumulates tracked entities in memory. Consider creating fresh contexts for distinct operations or periodically clearing the change tracker with context.ChangeTracker.Clear().

Verifying Your Setup

After installation and configuration, verify everything works with a simple test:

using var context = new AppDbContext();

// Test 1: Database creation
context.Database.EnsureCreated();
Console.WriteLine("✓ Database created successfully");

// Test 2: Insert
context.Users.Add(new User { Name = "Test User", Email = "test@example.com" });
context.SaveChanges();
Console.WriteLine("✓ Insert successful");

// Test 3: Query
var user = context.Users.FirstOrDefault(u => u.Name == "Test User");
Console.WriteLine(

        
        

            
            Loading...
        
quot;✓ Query successful: Found {user?.Name}");

// Test 4: Update
user!.Email = "updated@example.com";
context.SaveChanges();
Console.WriteLine("✓ Update successful");

// Test 5: Delete
context.Users.Remove(user);
context.SaveChanges();
Console.WriteLine("✓ Delete successful");

Console.WriteLine("\nAll tests passed! EF Core with WitDatabase is working correctly.");

If you encounter errors, check these common issues:

Summary

Setting up EF Core with WitDatabase involves:

1. Install the package — OutWit.Database.EntityFramework includes all dependencies
2. Create a DbContext — Define your entity classes and DbSet<T> properties
3. Configure the provider — Use UseWitDb() with your connection string
4. Register with DI (optional) — Use AddDbContext for web apps
5. Initialize the database — Use EnsureCreated() or migrations

With setup complete, you're ready to define your data model. The next section covers entity configuration, relationships, and mapping between .NET types and database columns.

Defining Your Model

The data model is the foundation of any EF Core application. It defines what data your application stores, how entities relate to each other, and how .NET types map to database columns. A well-designed model makes your application easier to understand, maintain, and evolve over time.

This section covers everything you need to know about defining entities, configuring properties, establishing relationships, and controlling how EF Core maps your classes to database tables.

Entity Classes Fundamentals

An entity class is simply a .NET class that EF Core maps to a database table. Each instance of the class represents a row in that table, and each property represents a column. EF Core discovers entities through DbSet<T> properties on your DbContext.

The simplest entity needs only a primary key property:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
    public DateTime CreatedAt { get; set; }
    public bool IsActive { get; set; }
}

EF Core uses conventions to infer configuration from your class structure. Understanding these conventions helps you write less configuration code while maintaining control over the database schema.

Primary Key Convention. EF Core automatically recognizes a property named Id or {ClassName}Id as the primary key. In the example above, Id becomes the primary key for the Products table. Both Id and ProductId would work equally well.

Table Name Convention. By default, EF Core uses the DbSet<T> property name as the table name. If you have DbSet<Product> Products, the table is named Products. If there's no DbSet property, EF Core uses the class name instead.

Column Name Convention. Property names become column names directly. The Name property maps to a Name column. EF Core preserves the exact casing you use in your class.

Nullability Convention. EF Core respects C# nullability. A property of type string (non-nullable reference type) becomes a NOT NULL column, while string? allows nulls. Similarly, int cannot be null, but int? can.

Type Mapping Convention. EF Core maps .NET types to appropriate database types automatically. We'll cover the specific mappings for WitDatabase later in this section.

These conventions mean that a simple class with properties often requires zero additional configuration. EF Core figures out the table name, column names, primary key, and nullability from the class definition alone.

Configuring with Data Annotations

While conventions handle common cases, you often need more control. Data annotations are attributes you apply directly to entity classes and properties. They're visible right where they apply, making them easy to discover and understand.

Data annotations come from two namespaces:

• System.ComponentModel.DataAnnotations — standard .NET attributes like [Required], [MaxLength]
• System.ComponentModel.DataAnnotations.Schema — database-specific attributes like [Table], [Column]

Here's an entity with comprehensive data annotation configuration:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

[Table("Products", Schema = "inventory")]
public class Product
{
    [Key]
    [Column("ProductId")]
    public int Id { get; set; }
    
    [Required]
    [MaxLength(200)]
    public string Name { get; set; } = string.Empty;
    
    [MaxLength(2000)]
    public string? Description { get; set; }
    
    [Column(TypeName = "DECIMAL(10,2)")]
    [Range(0.01, 999999.99)]
    public decimal Price { get; set; }
    
    [Column("Qty")]
    public int StockQuantity { get; set; }
    
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public DateTime CreatedAt { get; set; }
    
    [ConcurrencyCheck]
    public DateTime UpdatedAt { get; set; }
    
    [NotMapped]
    public decimal PriceWithTax => Price * 1.2m;
}

Let's examine what each annotation does and when you'd use it.

[Table] specifies the table name and optionally a schema. Use this when you want a table name different from your DbSet property name, or when organizing tables into schemas.

[Key] explicitly marks a property as the primary key. You need this when your key property isn't named Id or {ClassName}Id, or for composite keys (multiple properties forming the key together).

[Column] controls the column name and type. The Name parameter changes the column name in the database while keeping your preferred property name in code. The TypeName parameter specifies the exact database type, useful for controlling precision of decimal numbers or using specific string types.

[Required] marks a property as non-nullable in the database. For reference types with nullable reference types enabled, EF Core infers this from the type itself (string vs string?). The attribute is still useful for explicit documentation and validation.

[MaxLength] sets the maximum length for string and byte array columns. Without this, string columns default to an unlimited or maximum-size type. Setting appropriate lengths improves storage efficiency and catches data problems early.

[DatabaseGenerated] controls whether the database generates values for a column. Identity means the database auto-generates values on insert (like auto-increment). Computed means the database calculates the value. None means your application always provides the value.

[ConcurrencyCheck] marks a property for optimistic concurrency checking. When you update an entity, EF Core includes this column in the WHERE clause to detect concurrent modifications by other users.

[NotMapped] excludes a property from the database entirely. Use this for calculated properties, temporary values, or properties that exist only in your application logic.

The following table summarizes the most commonly used annotations:

Data annotations have limitations. They can't configure everything EF Core supports, and they mix database concerns into your domain classes. For more complex scenarios or cleaner separation, use the Fluent API.

Configuring with Fluent API

The Fluent API provides the most comprehensive and flexible way to configure your model. You write configuration code in the OnModelCreating method of your DbContext, using a fluent (method-chaining) syntax.

The Fluent API offers several advantages over data annotations. It can configure anything EF Core supports, including features with no annotation equivalent. It keeps your entity classes clean of database-specific attributes. And it centralizes all configuration in one place, making it easier to see the complete picture.

Here's the same Product entity configured entirely with Fluent API:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string? Description { get; set; }
    public decimal Price { get; set; }
    public int StockQuantity { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime UpdatedAt { get; set; }
    public decimal PriceWithTax => Price * 1.2m;
}

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>(entity =>
        {
            // Table configuration
            entity.ToTable("Products", "inventory");
            
            // Primary key
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Id).HasColumnName("ProductId");
            
            // Property configuration
            entity.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(200);
            
            entity.Property(e => e.Description)
                .HasMaxLength(2000);
            
            entity.Property(e => e.Price)
                .HasColumnType("DECIMAL(10,2)");
            
            entity.Property(e => e.StockQuantity)
                .HasColumnName("Qty");
            
            entity.Property(e => e.CreatedAt)
                .ValueGeneratedOnAdd();
            
            entity.Property(e => e.UpdatedAt)
                .IsConcurrencyToken();
            
            // Exclude computed property
            entity.Ignore(e => e.PriceWithTax);
            
            // Index
            entity.HasIndex(e => e.Name);
        });
    }
}

The Fluent API uses a consistent pattern: you call modelBuilder.Entity<T>() to get a builder for a specific entity, then chain method calls to configure it. Most methods return the builder, allowing continued chaining.

For large models, putting all configuration in OnModelCreating becomes unwieldy. EF Core supports separating configuration into dedicated classes implementing IEntityTypeConfiguration<T>:

public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.ToTable("Products", "inventory");
        
        builder.HasKey(e => e.Id);
        builder.Property(e => e.Id).HasColumnName("ProductId");
        
        builder.Property(e => e.Name)
            .IsRequired()
            .HasMaxLength(200);
        
        builder.Property(e => e.Price)
            .HasColumnType("DECIMAL(10,2)");
        
        // ... rest of configuration
    }
}

Apply configuration classes in OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Apply individual configuration
    modelBuilder.ApplyConfiguration(new ProductConfiguration());
    
    // Or apply all configurations from an assembly
    modelBuilder.ApplyConfigurationsFromAssembly(typeof(AppDbContext).Assembly);
}

This pattern keeps each entity's configuration in its own file, making large models manageable. Many teams create a Configurations folder containing one file per entity.

Choosing Between Annotations and Fluent API

Both approaches configure the same underlying model. Your choice depends on team preferences, project complexity, and specific requirements.

Use Data Annotations when:

• Configuration is simple and standard
• You want configuration visible alongside the entity definition
• You're also using the attributes for validation (ASP.NET Core model validation)
• Team members are more familiar with attribute-based configuration

Use Fluent API when:

• You need features not available as annotations (many advanced features)
• You want to keep entity classes free of database concerns
• You have complex configurations that are easier to read as code
• You need conditional configuration (e.g., different settings per environment)

Combine both when:

• You want common settings as annotations for visibility (like [MaxLength])
• You need Fluent API for complex or unsupported configurations

When both are used, Fluent API configuration takes precedence over annotations. This allows you to use annotations for most properties and override specific ones in code.

Type Mapping

EF Core automatically maps .NET types to database types. Understanding these mappings helps you choose appropriate types and avoid surprises.

The WitDatabase provider uses the following default mappings:

A few mappings deserve special attention.

Decimals require care for financial applications. By default, EF Core stores decimals as text to preserve full precision. For better query performance and explicit precision control, configure the column type:

entity.Property(e => e.Price)
    .HasColumnType("DECIMAL(10,2)");  // 10 digits total, 2 after decimal

Enums store as integers by default, which is efficient but makes database inspection difficult. For readability, you can store them as strings:

entity.Property(e => e.Status)
    .HasConversion<string>();  // Stores "Active", "Inactive", etc.

DateTime stores in ISO 8601 format, which preserves full precision and sorts correctly. Always use UTC times in your database to avoid timezone confusion:

entity.Property(e => e.CreatedAt)
    .HasConversion(
        v => v.ToUniversalTime(),
        v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

Guids store as 36-character strings. If storage efficiency matters, you can store them as 16-byte blobs, though this makes database inspection harder:

entity.Property(e => e.ExternalId)
    .HasConversion(
        v => v.ToByteArray(),
        v => new Guid(v));

Relationships Overview

Real applications have entities that relate to each other. A customer places orders. An order contains line items. A product belongs to categories. EF Core supports all standard relationship types and handles the foreign key management automatically.

Relationships in EF Core have two sides: the principal entity (the "one" side, like Customer) and the dependent entity (the "many" side, like Order). The dependent contains the foreign key that references the principal's primary key.

EF Core discovers relationships through navigation properties — properties that reference other entities. A navigation can be a single entity reference (public Customer Customer { get; set; }) or a collection (public ICollection<Order> Orders { get; set; }).

Understanding relationship configuration is crucial for any non-trivial application, so let's examine each relationship type in detail.

One-to-Many Relationships

One-to-many is the most common relationship type. One customer has many orders. One category contains many products. One author writes many books.

By convention, EF Core creates a one-to-many relationship when it finds:

• A collection navigation property on one entity
• A reference navigation property on another entity
• Optionally, a foreign key property

Here's a complete example with Customer and Order:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    
    // Collection navigation - one customer has many orders
    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
    
    // Foreign key property
    public int CustomerId { get; set; }
    
    // Reference navigation - each order belongs to one customer
    public Customer Customer { get; set; } = null!;
}

EF Core automatically:

• Recognizes the relationship from the navigation properties
• Uses CustomerId as the foreign key (matches Customer + Id convention)
• Creates the appropriate constraint in the database

The = null! on the Customer navigation tells the compiler "this will be null sometimes (when not loaded) but I'll handle it." Alternatively, make it nullable: public Customer? Customer { get; set; }.

For explicit configuration or non-conventional setups, use Fluent API:

modelBuilder.Entity<Order>(entity =>
{
    entity.HasOne(o => o.Customer)           // Order has one Customer
        .WithMany(c => c.Orders)             // Customer has many Orders
        .HasForeignKey(o => o.CustomerId)    // FK is CustomerId
        .OnDelete(DeleteBehavior.Cascade);   // Delete orders when customer deleted
});

Delete Behavior controls what happens to dependent entities when the principal is deleted:

Many-to-Many Relationships

Many-to-many relationships connect entities where each side can relate to multiple instances of the other. A student enrolls in many courses. A course has many students. A product belongs to many categories. A category contains many products.

In databases, many-to-many requires a join table (also called junction or linking table) that contains foreign keys to both sides. EF Core can manage this automatically or let you define the join entity explicitly.

Automatic join table (EF Core 5+):

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    // Many products belong to many categories
    public ICollection<Category> Categories { get; set; } = new List<Category>();
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    // Many categories contain many products
    public ICollection<Product> Products { get; set; } = new List<Product>();
}

EF Core automatically creates a CategoryProduct join table with CategoriesId and ProductsId columns. You work with the relationship naturally:

// Add product to category
var product = context.Products.Find(1);
var category = context.Categories.Find(1);
product.Categories.Add(category);
context.SaveChanges();

// Query products in a category
var electronics = context.Categories
    .Include(c => c.Products)
    .First(c => c.Name == "Electronics");

Explicit join entity — when you need additional data on the relationship:

public class Enrollment
{
    public int StudentId { get; set; }
    public Student Student { get; set; } = null!;
    
    public int CourseId { get; set; }
    public Course Course { get; set; } = null!;
    
    // Additional data about the relationship
    public DateTime EnrolledAt { get; set; }
    public Grade? Grade { get; set; }
    public bool IsAuditing { get; set; }
}

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public ICollection<Enrollment> Enrollments { get; set; } = new List<Enrollment>();
}

public class Course
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public ICollection<Enrollment> Enrollments { get; set; } = new List<Enrollment>();
}

Configure with Fluent API:

modelBuilder.Entity<Enrollment>(entity =>
{
    entity.HasKey(e => new { e.StudentId, e.CourseId });  // Composite PK
    
    entity.HasOne(e => e.Student)
        .WithMany(s => s.Enrollments)
        .HasForeignKey(e => e.StudentId);
    
    entity.HasOne(e => e.Course)
        .WithMany(c => c.Enrollments)
        .HasForeignKey(e => e.CourseId);
});

Use explicit join entities when you need to store data about the relationship itself, query the join table directly, or have more control over the table structure.

One-to-One Relationships

One-to-one relationships are less common but important for certain patterns. Each user has one profile. Each order has one shipping address. Each employee has one workstation.

One-to-one requires careful consideration of which side is the principal and which is the dependent. The dependent side contains the foreign key.

public class User
{
    public int Id { get; set; }
    public string Username { get; set; } = string.Empty;
    
    // Navigation to dependent
    public UserProfile? Profile { get; set; }
}

public class UserProfile
{
    public int Id { get; set; }
    public string Bio { get; set; } = string.Empty;
    public string? AvatarUrl { get; set; }
    public DateTime? DateOfBirth { get; set; }
    
    // Foreign key (same as principal's PK in this case)
    public int UserId { get; set; }
    
    // Navigation to principal
    public User User { get; set; } = null!;
}

Configure to clarify the relationship:

modelBuilder.Entity<User>(entity =>
{
    entity.HasOne(u => u.Profile)
        .WithOne(p => p.User)
        .HasForeignKey<UserProfile>(p => p.UserId);
});

A common pattern uses the same primary key value for both entities (shared primary key):

public class UserProfile
{
    // PK is also FK to User - they share the same value
    public int UserId { get; set; }
    public string Bio { get; set; } = string.Empty;
    public User User { get; set; } = null!;
}

// Configuration
modelBuilder.Entity<UserProfile>(entity =>
{
    entity.HasKey(p => p.UserId);  // UserId is both PK and FK
    
    entity.HasOne(p => p.User)
        .WithOne(u => u.Profile)
        .HasForeignKey<UserProfile>(p => p.UserId);
});

This approach enforces that a profile cannot exist without a user and simplifies queries.

Self-Referencing Relationships

Sometimes an entity relates to itself. Employees have managers (who are also employees). Categories have parent categories. Comments have replies (which are also comments).

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    // Self-referencing FK (nullable for top-level employees)
    public int? ManagerId { get; set; }
    
    // Navigation to parent
    public Employee? Manager { get; set; }
    
    // Navigation to children
    public ICollection<Employee> DirectReports { get; set; } = new List<Employee>();
}

Configuration:

modelBuilder.Entity<Employee>(entity =>
{
    entity.HasOne(e => e.Manager)
        .WithMany(e => e.DirectReports)
        .HasForeignKey(e => e.ManagerId)
        .OnDelete(DeleteBehavior.Restrict);  // Prevent cascading delete loops
});

Self-referencing relationships require careful delete behavior configuration to avoid issues. Restrict or SetNull is usually safer than Cascade.

Indexes

Indexes improve query performance by allowing the database to find rows without scanning entire tables. EF Core creates indexes automatically for foreign keys but you should add indexes for other frequently queried columns.

Using Data Annotations:

[Index(nameof(Email), IsUnique = true)]
[Index(nameof(LastName), nameof(FirstName))]  // Composite index
public class User
{
    public int Id { get; set; }
    public string Email { get; set; } = string.Empty;
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
}

Using Fluent API:

modelBuilder.Entity<User>(entity =>
{
    entity.HasIndex(e => e.Email)
        .IsUnique();
    
    entity.HasIndex(e => new { e.LastName, e.FirstName })
        .HasDatabaseName("IX_User_FullName");
    
    // Filtered index (only active users)
    entity.HasIndex(e => e.Email)
        .HasFilter("[IsActive] = 1")
        .HasDatabaseName("IX_User_Email_Active");
});

When to add indexes:

• Columns frequently used in WHERE clauses
• Columns used in ORDER BY (for large tables)
• Columns used in JOIN conditions (EF Core adds FK indexes automatically)
• Unique constraints (which are implemented as unique indexes)

When to avoid indexes:

• Small tables (full scan might be faster than index lookup)
• Columns rarely used in queries
• Tables with heavy insert/update workloads (indexes slow writes)
• Low-cardinality columns (like boolean flags — index provides little benefit)

Model Building Best Practices

Building a robust data model requires balancing many concerns. Here are practices that lead to maintainable, efficient models.

Use nullable reference types. Enable nullable reference types in your project and let them guide your model. A non-nullable string becomes NOT NULL, a nullable string? allows nulls. This catches null reference errors at compile time.

Keep entities focused. Each entity should represent one clear concept. If an entity has dozens of properties, consider splitting it into related entities or using owned types for logical groupings.

Prefer explicit configuration for important settings. While conventions are convenient, explicit configuration serves as documentation. A future developer immediately sees that Email must be unique without tracing through conventions.

Use configuration classes for large models. Once you have more than a handful of entities, separate IEntityTypeConfiguration<T> classes keep configuration organized and each file focused.

Be deliberate about delete behavior. The default cascade delete can cause surprising data loss. Consider each relationship and choose the appropriate behavior explicitly.

Add indexes proactively. It's easier to add indexes during initial development than to diagnose slow queries in production. Consider your likely query patterns and index accordingly.

Document complex relationships. When relationships are non-obvious or involve business rules, add comments explaining the design intent.

Summary

Defining your model involves:

1. Creating entity classes with properties for each column
2. Using conventions for common patterns (Id as PK, property names as columns)
3. Adding configuration via Data Annotations or Fluent API for custom requirements
4. Establishing relationships with navigation properties and foreign keys
5. Adding indexes for query performance

With your model defined, the next section covers migrations — how to create, evolve, and manage your database schema over time.

Migrations

Database schemas evolve over time. You add new tables, modify columns, create indexes, and sometimes remove features entirely. Managing these changes across development, staging, and production environments is challenging — especially when databases already contain user data that must be preserved.

EF Core migrations solve this problem by tracking schema changes as versioned code files. Each migration captures a specific set of changes: what the schema looked like before, what it should look like after, and how to transform between the two states. This approach brings the same version control benefits you enjoy for application code to your database schema.

Why Migrations Matter

Without migrations, you face a difficult choice when your model changes. You can delete the database and recreate it from scratch, losing all data. Or you can manually write SQL scripts to alter the existing schema, which is error-prone and hard to track across environments.

Consider a realistic scenario: your application launches with a simple User table. Later, you need to add an Email column. Then you realize Email should be unique. Then you add an Orders table with a foreign key to User. Each change must be applied consistently across every developer's machine, your test servers, and production.

Migrations turn each change into a discrete, version-controlled step. You can apply migrations sequentially to bring any database to the current schema, roll back to previous versions when needed, and generate SQL scripts for environments where you can't run EF Core directly.

For desktop applications, migrations are even more critical. Your application runs on users' machines, each with their own database. When you release an update with schema changes, the application must upgrade existing databases without losing user data. Migrations make this upgrade path explicit and testable.

Migrations vs EnsureCreated

EF Core provides two ways to create your database schema, and understanding when to use each prevents common mistakes.

EnsureCreated examines your current model and creates all tables, columns, and constraints in one operation. It's simple and immediate — call it once and your database matches your model. However, EnsureCreated has a critical limitation: it only works on an empty database. If tables already exist, it does nothing. It cannot add a column to an existing table or modify any existing structure.

// EnsureCreated: simple but limited
context.Database.EnsureCreated();

Use EnsureCreated for:

• Prototyping and experimentation
• Unit tests with fresh databases
• Applications where data can be regenerated (caches, temporary storage)
• Initial development before you've committed to a schema

Migrations track each change incrementally. When you modify your model, you create a new migration that captures only the differences. Applying migrations transforms an existing database step by step, preserving data along the way.

// Migrations: powerful and production-ready
context.Database.Migrate();

Use Migrations for:

• Production applications with persistent user data
• Team development where multiple developers modify the schema
• Applications requiring rollback capability
• Desktop applications that upgrade in the field

The decision is straightforward: if your database contains data you can't afford to lose, use migrations. If you're experimenting or testing with disposable data, EnsureCreated is simpler.

One important rule: don't mix the two approaches. Once you start using migrations, always use migrations. Calling EnsureCreated on a database that was created or modified by migrations can cause EF Core to lose track of schema state.

Setting Up the Migrations Environment

Before creating migrations, ensure your development environment is properly configured. You need the EF Core tools and a project structure that supports design-time operations.

Install the EF Core CLI tools globally:

dotnet tool install --global dotnet-ef

Or update if already installed:

dotnet tool update --global dotnet-ef

Verify the installation:

dotnet ef --version

Add the design package to your project. This package contains components needed to create migrations at design time:

dotnet add package Microsoft.EntityFrameworkCore.Design

The design package is only needed at development time for creating migrations. It doesn't ship with your application.

Project structure considerations. The EF Core tools need to instantiate your DbContext to analyze the model. This works automatically when your DbContext has a parameterless constructor or uses OnConfiguring. For DbContexts that receive options through dependency injection, you need a design-time factory:

using Microsoft.EntityFrameworkCore.Design;

public class AppDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    public AppDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        optionsBuilder.UseWitDb("Data Source=design-time.witdb");
        
        return new AppDbContext(optionsBuilder.Options);
    }
}

Place this class anywhere in your project. The EF Core tools discover it automatically.

For ASP.NET Core projects, the tools can often use your Program.cs configuration directly. If you encounter "Unable to create an object of type 'AppDbContext'" errors, adding a design-time factory resolves them.

Creating Your First Migration

With the environment ready, you can create migrations as your model evolves. Let's walk through the complete workflow.

Start with a simple model:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    
    public DbSet<User> Users => Set<User>();
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
}

Create the initial migration from the command line in your project directory:

dotnet ef migrations add InitialCreate

This command analyzes your model, compares it to the previous migration (none exists yet), and generates code representing the differences. EF Core creates a Migrations folder containing three files:

Migrations/
├── 20240115120000_InitialCreate.cs           # The migration itself
├── 20240115120000_InitialCreate.Designer.cs  # Model snapshot at this point
└── AppDbContextModelSnapshot.cs              # Current model snapshot

The timestamp prefix ensures migrations sort chronologically. The main migration file contains Up and Down methods:

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Users",
            columns: table => new
            {
                Id = table.Column<int>(type: "INTEGER", nullable: false)
                    .Annotation("WitDb:Autoincrement", true),
                Name = table.Column<string>(type: "TEXT", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Users", x => x.Id);
            });
    }

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

The Up method transforms the database from the previous state to the new state. The Down method reverses those changes. This symmetry enables both upgrading and rolling back.

Applying Migrations

Creating a migration generates code but doesn't modify the database. You must explicitly apply migrations to update the schema.

From the command line:

# Apply all pending migrations
dotnet ef database update

# Apply up to a specific migration
dotnet ef database update InitialCreate

From application code:

using var context = new AppDbContext(options);
context.Database.Migrate();

The Migrate() method checks which migrations have been applied (tracked in a __EFMigrationsHistory table), and applies any that are pending. It's safe to call on every application startup — if the database is current, it does nothing.

For ASP.NET Core applications, apply migrations during startup:

var app = builder.Build();

using (var scope = app.Services.CreateScope())
{
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    context.Database.Migrate();
}

app.Run();

For desktop applications, apply migrations when the application launches:

public partial class App : Application
{
    protected override void OnStartup(StartupEventArgs e)
    {
        using var context = new AppDbContext();
        context.Database.Migrate();
        
        base.OnStartup(e);
    }
}

Evolving Your Model

As your application grows, you'll make many model changes. Each change becomes a new migration that builds on previous ones.

Adding a column. Suppose you need to track user email addresses:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;  // New property
}

Create a migration for this change:

dotnet ef migrations add AddUserEmail

The generated migration adds just the new column:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "Email",
        table: "Users",
        type: "TEXT",
        nullable: false,
        defaultValue: "");
}

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

Adding a related entity. Now add an Order entity related to User:

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Total { get; set; }
    
    public int UserId { get; set; }
    public User User { get; set; } = null!;
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    
    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

// Add to DbContext
public DbSet<Order> Orders => Set<Order>();

Create the migration:

dotnet ef migrations add AddOrders

This migration creates the Orders table with the foreign key relationship.

Renaming and modifying. When you rename properties or change types, EF Core generates drop/add operations by default. These operations lose data. For renames, you should modify the generated migration to use RenameColumn instead:

// Generated (loses data):
migrationBuilder.DropColumn(name: "Name", table: "Users");
migrationBuilder.AddColumn<string>(name: "FullName", table: "Users", ...);

// Modified (preserves data):
migrationBuilder.RenameColumn(
    name: "Name",
    table: "Users",
    newName: "FullName");

Always review generated migrations before applying them. EF Core does its best to infer intent, but it can't always distinguish between a rename and a drop-then-add.

Migration Operations Reference

The MigrationBuilder provides methods for all schema modifications. Understanding these helps you write and modify migrations effectively.

Table operations:

// Create table
migrationBuilder.CreateTable(
    name: "Products",
    columns: table => new
    {
        Id = table.Column<int>(nullable: false)
            .Annotation("WitDb:Autoincrement", true),
        Name = table.Column<string>(maxLength: 200, nullable: false),
        Price = table.Column<decimal>(type: "DECIMAL(10,2)", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Products", x => x.Id);
    });

// Rename table
migrationBuilder.RenameTable(name: "Products", newName: "Items");

// Drop table
migrationBuilder.DropTable(name: "Products");

Column operations:

// Add column
migrationBuilder.AddColumn<string>(
    name: "Description",
    table: "Products",
    type: "TEXT",
    nullable: true);

// Add column with default value
migrationBuilder.AddColumn<bool>(
    name: "IsActive",
    table: "Products",
    nullable: false,
    defaultValue: true);

// Alter column (change type, nullability, etc.)
migrationBuilder.AlterColumn<string>(
    name: "Name",
    table: "Products",
    type: "VARCHAR(500)",  // Changed from 200
    nullable: false,
    oldClrType: typeof(string),
    oldMaxLength: 200);

// Rename column
migrationBuilder.RenameColumn(
    name: "Name",
    table: "Products",
    newName: "ProductName");

// Drop column
migrationBuilder.DropColumn(name: "Description", table: "Products");

Index operations:

// Create index
migrationBuilder.CreateIndex(
    name: "IX_Products_Name",
    table: "Products",
    column: "Name");

// Create unique index
migrationBuilder.CreateIndex(
    name: "IX_Users_Email",
    table: "Users",
    column: "Email",
    unique: true);

// Create composite index
migrationBuilder.CreateIndex(
    name: "IX_Orders_UserId_OrderDate",
    table: "Orders",
    columns: new[] { "UserId", "OrderDate" });

// Drop index
migrationBuilder.DropIndex(name: "IX_Products_Name", table: "Products");

Foreign key operations:

// Add foreign key
migrationBuilder.AddForeignKey(
    name: "FK_Orders_Users_UserId",
    table: "Orders",
    column: "UserId",
    principalTable: "Users",
    principalColumn: "Id",
    onDelete: ReferentialAction.Cascade);

// Drop foreign key
migrationBuilder.DropForeignKey(
    name: "FK_Orders_Users_UserId",
    table: "Orders");

Raw SQL for complex operations:

// Execute arbitrary SQL
migrationBuilder.Sql("UPDATE Users SET Email = LOWER(Email)");

// Complex data transformation
migrationBuilder.Sql(@"
    UPDATE Products 
    SET Category = 'Uncategorized' 
    WHERE Category IS NULL OR Category = ''
");

Rolling Back Migrations

Sometimes you need to undo migrations — perhaps a migration introduced a bug, or you need to return to a known good state during development.

Roll back to a specific migration:

# Roll back to AddUserEmail (undoes all migrations after it)
dotnet ef database update AddUserEmail

# Roll back all migrations (empty database)
dotnet ef database update 0

Remove the last migration (only works if not yet applied):

dotnet ef migrations remove

This deletes the migration files and updates the model snapshot. You cannot remove a migration that has been applied to any database — you must roll back first.

Understanding rollback limitations. The Down method reverses schema changes, but data transformations may not be reversible. If an Up method drops a column, the Down method can recreate the column but cannot restore the data. Plan your migrations with this in mind.

For critical production systems, consider:

• Taking database backups before applying migrations
• Testing migrations against a copy of production data
• Writing custom Down logic that preserves data when possible

Data Seeding

Migrations can include initial data (seed data) that ships with your application. This is useful for lookup tables, default configuration, and required reference data.

Configure seeding in OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Role>().HasData(
        new Role { Id = 1, Name = "Administrator" },
        new Role { Id = 2, Name = "User" },
        new Role { Id = 3, Name = "Guest" }
    );
    
    modelBuilder.Entity<Setting>().HasData(
        new Setting { Key = "AppName", Value = "My Application" },
        new Setting { Key = "MaxUploadSize", Value = "10485760" }
    );
}

When you create a migration after adding HasData, the migration includes INSERT statements. EF Core uses primary key values to track seeded data — if you change a seeded row's values, the migration generates an UPDATE.

Important constraints for seed data:

• You must specify primary key values explicitly
• Navigation properties can't be used; use foreign key values instead
• Seed data becomes part of migrations and can't be changed without new migrations

Seeding related entities:

modelBuilder.Entity<Category>().HasData(
    new Category { Id = 1, Name = "Electronics" },
    new Category { Id = 2, Name = "Clothing" }
);

modelBuilder.Entity<Product>().HasData(
    new Product { Id = 1, Name = "Laptop", CategoryId = 1, Price = 999.99m },
    new Product { Id = 2, Name = "T-Shirt", CategoryId = 2, Price = 19.99m }
);

For more complex seeding scenarios — like data that depends on environment or external sources — perform seeding in application code after migration:

using var context = new AppDbContext(options);
context.Database.Migrate();

if (!context.Roles.Any())
{
    context.Roles.AddRange(
        new Role { Name = "Administrator" },
        new Role { Name = "User" }
    );
    context.SaveChanges();
}

Generating SQL Scripts

In some environments, you can't run EF Core directly — security policies may require DBAs to review and execute SQL, or deployment pipelines may use SQL-based tools. EF Core can generate SQL scripts from migrations.

Generate a script for all migrations:

dotnet ef migrations script --output schema.sql

Generate a script from a specific migration to another:

# From InitialCreate to latest
dotnet ef migrations script InitialCreate --output upgrade.sql

# From AddUserEmail to AddOrders
dotnet ef migrations script AddUserEmail AddOrders --output changes.sql

Generate an idempotent script. An idempotent script can be run multiple times safely — it checks which migrations have been applied and only runs pending ones:

dotnet ef migrations script --idempotent --output deploy.sql

Idempotent scripts are ideal for deployment pipelines where you're not sure which migrations have already been applied.

The generated SQL includes both schema changes and inserts into the __EFMigrationsHistory table that tracks applied migrations:

CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
    "MigrationId" TEXT NOT NULL,
    "ProductVersion" TEXT NOT NULL,
    CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
);

-- Migration: 20240115120000_InitialCreate
CREATE TABLE "Users" (
    "Id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "Name" TEXT NOT NULL
);

INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20240115120000_InitialCreate', '8.0.0');

Migration Bundles

Migration bundles (introduced in EF Core 6) package migrations into a self-contained executable. This is useful for deployment scenarios where you can't use the .NET SDK or EF Core tools.

Create a bundle:

dotnet ef migrations bundle --output migrate.exe

Run the bundle:

# Apply all pending migrations
./migrate.exe --connection "Data Source=production.witdb"

# Apply to a specific migration
./migrate.exe --connection "Data Source=production.witdb" --target AddOrders

Bundles are self-contained executables that include the migrations and the necessary EF Core components. They're ideal for:

• Docker containers without the .NET SDK
• Deployment to machines without development tools
• Including in installer packages for desktop applications

Team Development Workflows

When multiple developers work on the same project, migration conflicts can occur. Two developers might independently create migrations that modify the same table, or create migrations with overlapping timestamps.

Prevent conflicts with communication:

• Coordinate significant schema changes with your team
• Create migrations in small, focused increments
• Push migrations promptly; don't let them pile up locally

Resolve conflicts when they occur:

If two developers create migrations independently:

1. Pull the latest changes, which brings in their migration
2. The model snapshot may have conflicts — resolve them in your source control tool
3. Remove your local migration: dotnet ef migrations remove
4. Recreate your migration on top of theirs: dotnet ef migrations add YourMigrationName

Consider a migrations lock in CI/CD:

Some teams enforce that migrations can only be added on the main branch, preventing conflicts entirely. Feature branches make model changes but don't create migrations; the migration is created when the feature merges.

Production Deployment Considerations

Deploying migrations to production requires care. A mistake can cause data loss or application downtime.

Test migrations against production-like data. A migration that works on an empty database might fail or run slowly on a database with millions of rows. Test against a copy of production data before deploying.

Consider migration timing. Adding a non-nullable column to a large table requires providing values for existing rows. This can lock the table for extended periods. Strategies to minimize impact:

// Step 1: Add column as nullable
migrationBuilder.AddColumn<string>(
    name: "NewColumn",
    table: "LargeTable",
    nullable: true);

// Step 2: In application code, gradually populate the column

// Step 3: Later migration makes it non-nullable
migrationBuilder.AlterColumn<string>(
    name: "NewColumn",
    table: "LargeTable",
    nullable: false,
    defaultValue: "");

Have a rollback plan. Know how to roll back before you deploy. Test the rollback process. For critical systems, have database backups ready.

Monitor after deployment. Watch for slow queries, errors, or unexpected behavior. Sometimes issues only appear under production load.

Use deployment slots or blue-green deployments. These strategies let you test the new version with the new schema before routing production traffic to it.

Migrations Best Practices

Following these practices leads to smoother deployments and fewer surprises.

Keep migrations small and focused. Each migration should represent one logical change. "Add email to users" is good. "Restructure entire database" is not. Small migrations are easier to review, test, and roll back.

Use meaningful migration names. AddUserEmail tells you what changed. Migration3 does not. Good names serve as documentation and make the migration history understandable.

Review generated migrations. Always examine what EF Core generated. It might have inferred a drop-and-recreate where you intended a rename. It might have chosen suboptimal indexes. Treat generated code as a starting point.

Don't modify applied migrations. Once a migration has been applied to any database (even a developer's local database), treat it as immutable. Create new migrations to make further changes. Modifying applied migrations causes the model snapshot to diverge from actual database state.

Test both Up and Down. Run your migrations forward and backward during development. Ensure rollback works before you need it in an emergency.

Include migrations in code review. Schema changes deserve the same scrutiny as application code. Reviewers can catch potential data loss, performance issues, and conflicts.

Document breaking changes. If a migration removes or renames something that other systems depend on, document it prominently. Consider coordinated deployments.

Summary

Migrations provide a robust system for managing database schema evolution:

1. Create migrations with dotnet ef migrations add as your model changes
2. Apply migrations with dotnet ef database update or context.Database.Migrate()
3. Roll back by updating to a previous migration when needed
4. Generate scripts for environments where direct migration isn't possible
5. Use bundles for self-contained deployment artifacts

With migrations handling schema management, the next section covers querying data — how to retrieve information from your database using LINQ and other techniques.

Querying Data

Retrieving data from the database is the most common operation in most applications. EF Core provides a powerful query system built on LINQ (Language Integrated Query) that lets you write queries as C# code with full IntelliSense support and compile-time checking. This section covers everything from basic queries to advanced techniques for optimal performance.

How EF Core Queries Work

Before diving into query syntax, understanding how EF Core processes queries helps you write more effective code and debug problems when they arise.

When you write a LINQ query against a DbSet, you're not immediately executing database operations. Instead, you're building an expression tree — a data structure that represents your query logic. This expression tree exists entirely in memory and describes what you want, not how to get it.

The query doesn't execute until you do something that requires actual results. This is called deferred execution, and it's a fundamental concept in LINQ. Methods like ToList(), First(), Count(), or iterating with foreach trigger execution.

When execution triggers, EF Core's query pipeline takes over:

1. Translation — The expression tree is analyzed and converted to SQL. EF Core examines each LINQ method (Where, Select, OrderBy, etc.) and generates corresponding SQL clauses.

2. Execution — The generated SQL is sent to WitDatabase through the ADO.NET layer. The database processes the query and returns results.

3. Materialization — Raw database results are converted into .NET objects. EF Core creates entity instances, populates their properties, and wires up navigation properties.

4. Tracking (optional) — By default, EF Core remembers the entities it returned. This enables change detection when you later call SaveChanges.

This pipeline means that query execution happens at the database level, not in your application. A query like .Where(u => u.Age > 18) becomes a SQL WHERE clause — WitDatabase filters the rows, and only matching rows travel over the wire. This is far more efficient than loading all data and filtering in C#.

Basic Query Patterns

The foundation of EF Core querying is the DbSet, which represents a table you can query. Starting from a DbSet, you chain LINQ methods to filter, sort, and shape the results.

Retrieving all entities is the simplest query:

// Get all users
var users = context.Users.ToList();

// Async version (preferred for web applications)
var users = await context.Users.ToListAsync();

Without any filtering, this loads every row from the Users table. For tables with many rows, this can consume significant memory and time. Always consider whether you truly need all records.

Filtering with Where reduces the result set to matching rows:

// Users who are active
var activeUsers = await context.Users
    .Where(u => u.IsActive)
    .ToListAsync();

// Users in a specific department with recent activity
var recentEngineers = await context.Users
    .Where(u => u.Department == "Engineering")
    .Where(u => u.LastLoginAt > DateTime.UtcNow.AddDays(-30))
    .ToListAsync();

Multiple Where clauses combine with AND logic. EF Core merges them into a single SQL WHERE clause with multiple conditions.

Sorting with OrderBy controls result order:

// Sort by name ascending
var sorted = await context.Users
    .OrderBy(u => u.Name)
    .ToListAsync();

// Sort by registration date descending (newest first)
var newest = await context.Users
    .OrderByDescending(u => u.CreatedAt)
    .ToListAsync();

// Multiple sort levels
var multiSort = await context.Users
    .OrderBy(u => u.Department)
    .ThenBy(u => u.Name)
    .ThenByDescending(u => u.Salary)
    .ToListAsync();

Use ThenBy and ThenByDescending for secondary sort criteria. Using OrderBy twice would replace the first sort, not add to it.

Limiting results with Take and Skip enables pagination:

// First 10 users
var firstPage = await context.Users
    .OrderBy(u => u.Id)
    .Take(10)
    .ToListAsync();

// Skip first 20, take next 10 (page 3 with page size 10)
var pageThree = await context.Users
    .OrderBy(u => u.Id)
    .Skip(20)
    .Take(10)
    .ToListAsync();

Always use OrderBy before Skip/Take. Without explicit ordering, the database may return rows in any order, making pagination unpredictable.

Finding Single Entities

Often you need exactly one entity, not a collection. EF Core provides several methods with different behaviors for edge cases.

Find retrieves an entity by its primary key. It's special because it checks the local cache first, potentially avoiding a database query:

// Find by primary key - may use cache
var user = await context.Users.FindAsync(userId);

// Returns null if not found
if (user == null)
{
    Console.WriteLine("User not found");
}

Find only works with primary keys. For other lookups, use the methods below.

First and FirstOrDefault return the first matching entity:

// First user with email - throws if none found
var user = await context.Users
    .FirstAsync(u => u.Email == email);

// FirstOrDefault returns null instead of throwing
var user = await context.Users
    .FirstOrDefaultAsync(u => u.Email == email);

// Without predicate, returns first by database order
var anyUser = await context.Users.FirstOrDefaultAsync();

Use FirstOrDefault when absence is a normal possibility. Use First when absence indicates a bug or invalid state — the exception helps identify problems quickly.

Single and SingleOrDefault are stricter — they throw if more than one entity matches:

// Exactly one user with this email, throws if 0 or 2+
var user = await context.Users
    .SingleAsync(u => u.Email == email);

// Returns null if none, throws if 2+
var user = await context.Users
    .SingleOrDefaultAsync(u => u.Email == email);

Single is appropriate when your query should logically return at most one result. It catches data integrity issues where duplicates exist unexpectedly.

Last and LastOrDefault return the last matching entity. These require an OrderBy clause to define what "last" means:

// Most recent order for a customer
var lastOrder = await context.Orders
    .Where(o => o.CustomerId == customerId)
    .OrderBy(o => o.OrderDate)
    .LastOrDefaultAsync();

The following table summarizes the behavior differences:

Projections with Select

By default, queries return complete entity objects with all properties populated. When you only need a subset of data, projections reduce memory usage and improve query performance.

Selecting specific columns creates anonymous types or DTOs:

// Anonymous type with just what we need
var nameAndEmail = await context.Users
    .Select(u => new { u.Name, u.Email })
    .ToListAsync();

// DTO class for type safety and reuse
public class UserSummaryDto
{
    public int Id { get; init; }
    public string Name { get; init; } = string.Empty;
    public string Email { get; init; } = string.Empty;
}

var summaries = await context.Users
    .Select(u => new UserSummaryDto
    {
        Id = u.Id,
        Name = u.Name,
        Email = u.Email
    })
    .ToListAsync();

Projections are translated to SQL SELECT clauses that only retrieve the specified columns. If your table has 20 columns but you only need 3, the query is faster and uses less memory.

Computing values in projections allows transformations in the database:

var stats = await context.Users
    .Select(u => new
    {
        u.Name,
        FullName = u.FirstName + " " + u.LastName,
        IsNew = u.CreatedAt > DateTime.UtcNow.AddDays(-7),
        OrderCount = u.Orders.Count()
    })
    .ToListAsync();

EF Core translates these expressions to SQL when possible. String concatenation becomes SQL concatenation, date comparisons become SQL comparisons, and Count() becomes a subquery or join.

Projection with related data lets you flatten or reshape relationships:

var orderDetails = await context.Orders
    .Select(o => new
    {
        OrderId = o.Id,
        CustomerName = o.Customer.Name,
        CustomerEmail = o.Customer.Email,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
    })
    .ToListAsync();

This is often more efficient than loading complete entities with Include, especially when you only need a few properties from related entities.

Loading Related Data

Entities often have relationships — an order belongs to a customer, contains line items, and so on. EF Core provides three strategies for loading related data, each with different performance characteristics.

Eager loading with Include retrieves related data in the same query:

// Load orders with their customers
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

// Chain ThenInclude for deeper relationships
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
            .ThenInclude(p => p.Category)
    .ToListAsync();

Include causes EF Core to generate a JOIN or additional SELECT to fetch related entities. All data arrives in a single round-trip (or a few, with split queries).

When to use eager loading:

• You know you'll need the related data
• You're processing a batch of entities
• The relationship is one-to-one or one-to-few

When eager loading can hurt:

• Including large collections bloats results (each parent row repeats for every child)
• Including many relationships creates complex queries with many JOINs
• You might not actually use all the loaded data

Explicit loading retrieves related data on demand, in a separate query:

var order = await context.Orders.FindAsync(orderId);

// Load the customer separately
await context.Entry(order)
    .Reference(o => o.Customer)
    .LoadAsync();

// Load the items collection
await context.Entry(order)
    .Collection(o => o.Items)
    .LoadAsync();

// Now you can access them
Console.WriteLine(order.Customer.Name);
Console.WriteLine(

        
        

            
            Loading...
        
quot;Items: {order.Items.Count}");

Explicit loading is useful when you conditionally need related data. Instead of always loading everything, you load what you need based on runtime logic.

Filtered includes let you load only matching related entities:

// Only include active items
var orders = await context.Orders
    .Include(o => o.Items.Where(i => i.IsActive))
    .ToListAsync();

// Order and limit included collections
var posts = await context.Blogs
    .Include(b => b.Posts
        .OrderByDescending(p => p.PublishedAt)
        .Take(5))
    .ToListAsync();

This avoids loading related data you don't need, like historical items or all posts when you only want recent ones.

Lazy loading automatically loads related data when you access a navigation property. It's convenient but can cause performance problems if misused. Lazy loading requires additional setup and is not covered in detail here — explicit loading and eager loading are generally preferred for their predictability.

The N+1 Query Problem

One of the most common performance pitfalls with EF Core is the N+1 query problem. It occurs when you iterate over entities and access navigation properties that weren't loaded, triggering a separate query for each entity.

Consider this problematic code:

// BAD: N+1 queries - one for orders, one for EACH customer
var orders = await context.Orders.ToListAsync();  // Query 1

foreach (var order in orders)
{
    // Each iteration triggers a new query if lazy loading is enabled
    // or throws NullReferenceException if it's not
    Console.WriteLine(order.Customer.Name);  // Query 2, 3, 4, ...
}

If there are 100 orders, this executes 101 queries: one for orders, and 100 for customers (one per order). Even though many orders might share the same customer, EF Core doesn't know that when lazy loading individual navigation properties.

The solution is to load related data upfront:

// GOOD: Two queries at most (or one with JOIN)
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);  // Already loaded, no query
}

Or use projection when you only need specific properties:

// GOOD: Single query returning just what we need
var orderSummaries = await context.Orders
    .Select(o => new { o.Id, CustomerName = o.Customer.Name })
    .ToListAsync();

Split Queries

When you use Include with collections, EF Core generates a single query with JOINs. For complex queries or large collections, this can cause "cartesian explosion" — if an order has 10 items and you also include the customer (1 row), the joined result has 10 rows with customer data repeated.

Split queries address this by executing separate SQL statements for each Include:

var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .AsSplitQuery()  // Execute as multiple queries
    .ToListAsync();

Instead of one complex JOIN, EF Core executes:

1. SELECT * FROM Orders JOIN Customers
2. SELECT * FROM OrderItems WHERE OrderId IN (...)

EF Core correlates the results automatically.

Tradeoffs of split queries:

• Pros: No data duplication, potentially faster for large collections
• Cons: Multiple round-trips to database, data might change between queries

You can set split queries as the default for a DbContext:

optionsBuilder.UseWitDb(connectionString, options =>
    options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Then use AsSingleQuery() to override for specific queries where single-query behavior is preferred.

Grouping and Aggregation

Aggregation queries compute summaries like counts, sums, and averages. EF Core translates LINQ aggregation methods to SQL GROUP BY and aggregate functions.

Simple aggregations without grouping:

// Count all users
var totalUsers = await context.Users.CountAsync();

// Count with condition
var activeUsers = await context.Users.CountAsync(u => u.IsActive);

// Sum, Average, Min, Max
var totalSalary = await context.Users.SumAsync(u => u.Salary);
var avgSalary = await context.Users.AverageAsync(u => u.Salary);
var highestSalary = await context.Users.MaxAsync(u => u.Salary);
var earliestHire = await context.Users.MinAsync(u => u.HireDate);

// Any and All for existence checks
var hasAdmins = await context.Users.AnyAsync(u => u.Role == "Admin");
var allVerified = await context.Users.AllAsync(u => u.IsEmailVerified);

GroupBy for aggregations per category:

// Count users per department
var deptCounts = await context.Users
    .GroupBy(u => u.Department)
    .Select(g => new
    {
        Department = g.Key,
        UserCount = g.Count()
    })
    .ToListAsync();

// Multiple aggregations
var deptStats = await context.Users
    .GroupBy(u => u.Department)
    .Select(g => new
    {
        Department = g.Key,
        Count = g.Count(),
        TotalSalary = g.Sum(u => u.Salary),
        AverageSalary = g.Average(u => u.Salary),
        MaxSalary = g.Max(u => u.Salary)
    })
    .ToListAsync();

// Group by multiple columns
var locationStats = await context.Users
    .GroupBy(u => new { u.Country, u.City })
    .Select(g => new
    {
        g.Key.Country,
        g.Key.City,
        UserCount = g.Count()
    })
    .OrderByDescending(x => x.UserCount)
    .ToListAsync();

Having-equivalent filtering uses Where after GroupBy:

// Only departments with more than 5 users
var largeDepts = await context.Users
    .GroupBy(u => u.Department)
    .Where(g => g.Count() > 5)
    .Select(g => new
    {
        Department = g.Key,
        Count = g.Count()
    })
    .ToListAsync();

Raw SQL Queries

Sometimes LINQ can't express the query you need, or you want to use database-specific features. EF Core supports raw SQL while still providing materialization and optional change tracking.

FromSqlRaw for queries returning entities:

// Basic raw query
var users = await context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE IsActive = 1")
    .ToListAsync();

// With parameters (use numbered placeholders)
var users = await context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE Department = {0}", department)
    .ToListAsync();

FromSqlInterpolated is safer for parameterized queries using string interpolation:

// Parameters are automatically sanitized
var users = await context.Users
    .FromSqlInterpolated(

        
        

            
            Loading...
        
quot;SELECT * FROM Users WHERE Department = {department}")
    .ToListAsync();

The interpolated values become SQL parameters, protecting against SQL injection. Never use regular string concatenation with raw SQL.

Composing over raw SQL — you can add LINQ methods after FromSql:

var users = await context.Users
    .FromSqlInterpolated(

        
        

            
            Loading...
        
quot;SELECT * FROM Users WHERE Department = {dept}")
    .Where(u => u.IsActive)  // Added to WHERE clause
    .OrderBy(u => u.Name)    // Added ORDER BY
    .Take(10)                // Added LIMIT
    .ToListAsync();

EF Core wraps your SQL in a subquery and adds the LINQ-generated clauses.

SqlQuery for non-entity results (EF Core 7+):

// Return scalar values
var emails = await context.Database
    .SqlQuery<string>(

        
        

            
            Loading...
        
quot;SELECT Email FROM Users WHERE IsActive = 1")
    .ToListAsync();

// Return unmapped types
var stats = await context.Database
    .SqlQuery<DepartmentStats>($@"
        SELECT 
            Department,
            COUNT(*) AS UserCount,
            AVG(Salary) AS AverageSalary
        FROM Users
        GROUP BY Department")
    .ToListAsync();

ExecuteSqlRaw and ExecuteSqlInterpolated for commands that don't return data:

// Update without loading entities
var affected = await context.Database
    .ExecuteSqlInterpolatedAsync(
        

        
        

            
            Loading...
        
quot;UPDATE Users SET LastNotified = {DateTime.UtcNow} WHERE IsActive = 1");

Console.WriteLine(

        
        

            
            Loading...
        
quot;Updated {affected} rows");

No-Tracking Queries

By default, EF Core tracks every entity it returns. It keeps a reference in the change tracker and creates a snapshot of the original values. When you call SaveChanges, EF Core compares current values to snapshots to detect changes.

Tracking has overhead: memory for storing snapshots, CPU time for comparison. For read-only scenarios where you won't modify and save the entities, tracking is pure waste.

AsNoTracking disables tracking for a query:

var users = await context.Users
    .AsNoTracking()
    .Where(u => u.IsActive)
    .ToListAsync();

// These entities are not tracked
// Modifying them and calling SaveChanges does nothing

AsNoTrackingWithIdentityResolution disables change tracking but maintains identity. Without this, if the same entity appears multiple times in results (like the same customer in multiple orders), EF Core creates multiple instances. With identity resolution, they're the same object:

var orders = await context.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer)
    .ToListAsync();

// orders[0].Customer and orders[1].Customer might be the same object
// if they have the same CustomerId

Set no-tracking as default for the entire context:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseWitDb(connectionString)
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

Then use AsTracking() for queries where you need tracking.

When to use no-tracking:

• API endpoints that return data to clients
• Reports and dashboards (read-only display)
• Data exports
• Any query where you won't modify and save the entities

Client vs Server Evaluation

EF Core tries to translate your entire LINQ query to SQL. When it can't translate an expression, it has two choices: throw an exception or evaluate that part in your application (client evaluation).

Modern EF Core (3.0+) does not silently fall back to client evaluation. If part of your query can't be translated, you get an exception. This is good — silent client evaluation often caused severe performance problems when entire tables were loaded into memory.

However, you can explicitly opt into client evaluation when appropriate:

// This fails - custom FormatName can't be translated to SQL
var users = await context.Users
    .Select(u => new { Name = FormatName(u.FirstName, u.LastName) })
    .ToListAsync();  // Exception!

// Solution 1: Move processing after materialization
var users = await context.Users
    .Select(u => new { u.FirstName, u.LastName })
    .ToListAsync();

var formatted = users
    .Select(u => new { Name = FormatName(u.FirstName, u.LastName) })
    .ToList();

// Solution 2: Use AsEnumerable() to switch to client evaluation
var users = await context.Users
    .AsAsyncEnumerable()
    .Select(u => new { Name = FormatName(u.FirstName, u.LastName) })
    .ToListAsync();

The second approach streams results and processes them one by one. It's convenient but still loads all matching rows. Be mindful of data volumes.

Signs you might have client evaluation issues:

• Queries that work but are surprisingly slow
• Custom methods or properties in LINQ expressions
• Complex string manipulation or date calculations

When possible, rewrite queries to use only translatable operations. When client processing is necessary, fetch only the columns you need and be aware of the performance implications.

Querying Best Practices

Following these practices leads to efficient, maintainable query code.

Filter early, select late. Put Where clauses as early as possible in your query chain. This minimizes the data processed by subsequent operations and returned from the database.

Project to what you need. If you only need three columns from a twenty-column table, use Select. Don't load complete entities out of habit.

Use async methods. For web applications, always use ToListAsync, FirstOrDefaultAsync, etc. Async queries free the thread while waiting for the database, improving scalability.

Be explicit about includes. Document which relationships your query needs. Adding unexpected Includes later can break code that assumed certain navigation properties weren't loaded.

Watch for N+1. When you iterate over entities and access navigation properties, ensure they're already loaded via Include, or use projection to fetch what you need in one query.

Paginate large results. Never return unbounded result sets. Always use Take with OrderBy for pagination.

Profile your queries. Use logging to see the generated SQL. Unexpected query patterns often indicate room for optimization.

Summary

EF Core querying provides:

1. LINQ syntax for type-safe, IntelliSense-supported queries
2. Deferred execution — queries run when you request results
3. Projections to fetch only needed columns
4. Include for eager loading related data
5. Aggregations for counts, sums, and grouping
6. Raw SQL when LINQ isn't enough
7. No-tracking for read-only performance

With querying covered, the next section addresses saving data — how to insert, update, and delete entities efficiently.

Saving Data

While querying retrieves information from the database, saving data is how your application creates, modifies, and removes records. EF Core's change tracking system monitors your entities and generates the appropriate INSERT, UPDATE, and DELETE statements when you call SaveChanges. This section covers all aspects of persisting data, from basic operations to advanced scenarios like bulk updates and concurrency handling.

The SaveChanges Workflow

Understanding how SaveChanges works helps you write correct and efficient data modification code. When you call SaveChanges, EF Core performs a carefully orchestrated sequence of operations.

First, EF Core examines the change tracker to find all entities in Added, Modified, or Deleted states. For each Modified entity, it compares current property values against the original snapshot to determine which columns actually changed.

Next, EF Core generates SQL statements for each change. Added entities become INSERT statements. Modified entities become UPDATE statements that set only the changed columns. Deleted entities become DELETE statements.

EF Core then wraps all statements in a transaction. If you haven't started an explicit transaction, SaveChanges creates one automatically. This ensures that either all changes succeed or none do — you won't end up with a partially applied set of changes.

The statements execute against the database. For inserts with auto-generated keys, EF Core retrieves the generated values and updates your entity objects. After successful completion, EF Core updates the change tracker: Added entities become Unchanged, Modified entities become Unchanged with new snapshots, and Deleted entities are removed from tracking entirely.

If any statement fails, the transaction rolls back and SaveChanges throws an exception. Your entities remain in their modified states, allowing you to fix the problem and retry.

[[Svg Src="./witdatabase-efcore-change-tracking.svg" Alt="witdatabase-efcore-change-tracking"]]

Adding Entities

Creating new records starts with instantiating entity objects and telling EF Core to track them as additions.

Adding a single entity:

var user = new User
{
    Name = "Alice Johnson",
    Email = "alice@example.com",
    CreatedAt = DateTime.UtcNow
};

context.Users.Add(user);
await context.SaveChangesAsync();

// After SaveChanges, the generated Id is populated
Console.WriteLine(

        
        

            
            Loading...
        
quot;Created user with Id: {user.Id}");

The Add method marks the entity as Added in the change tracker. The actual database insert happens when you call SaveChanges. After SaveChanges completes successfully, auto-generated values like identity columns are populated on your entity object.

Adding multiple entities:

var users = new List<User>
{
    new User { Name = "Bob Smith", Email = "bob@example.com" },
    new User { Name = "Carol White", Email = "carol@example.com" },
    new User { Name = "David Brown", Email = "david@example.com" }
};

context.Users.AddRange(users);
await context.SaveChangesAsync();

// All users now have their generated Ids
foreach (var user in users)
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Created: {user.Name} (Id: {user.Id})");
}

AddRange is more efficient than calling Add in a loop because EF Core can batch the inserts into fewer database round-trips.

Adding related entities together:

var order = new Order
{
    OrderDate = DateTime.UtcNow,
    Customer = new Customer
    {
        Name = "New Customer",
        Email = "new@example.com"
    },
    Items = new List<OrderItem>
    {
        new OrderItem { ProductId = 1, Quantity = 2, UnitPrice = 29.99m },
        new OrderItem { ProductId = 2, Quantity = 1, UnitPrice = 49.99m }
    }
};

context.Orders.Add(order);
await context.SaveChangesAsync();

When you add an entity with navigation properties pointing to new entities, EF Core automatically adds the related entities too. It figures out the correct insert order based on relationships and populates foreign keys appropriately.

Adding to an existing entity's collection:

var customer = await context.Customers.FindAsync(customerId);

customer.Orders.Add(new Order
{
    OrderDate = DateTime.UtcNow,
    Total = 99.99m
});

await context.SaveChangesAsync();

The new Order is detected through the navigation property and inserted with the correct CustomerId foreign key.

Updating Entities

EF Core's change tracking makes updates straightforward. Load an entity, modify its properties, and save. EF Core detects the changes and generates an UPDATE statement for only the modified columns.

Basic update pattern:

var user = await context.Users.FindAsync(userId);

if (user != null)
{
    user.Name = "Updated Name";
    user.Email = "updated@example.com";
    user.ModifiedAt = DateTime.UtcNow;
    
    await context.SaveChangesAsync();
}

EF Core compares the current values against the original snapshot and generates: UPDATE Users SET Name = 'Updated Name', Email = 'updated@example.com', ModifiedAt = '...' WHERE Id = @userId

Only the changed columns appear in the SET clause. Unchanged columns are not included, reducing data transferred and potential for conflicts.

Updating related entities:

var order = await context.Orders
    .Include(o => o.Items)
    .FirstAsync(o => o.Id == orderId);

// Modify the order
order.Status = OrderStatus.Shipped;
order.ShippedAt = DateTime.UtcNow;

// Modify an existing item
var firstItem = order.Items.First();
firstItem.Quantity = 5;

// Add a new item
order.Items.Add(new OrderItem
{
    ProductId = 3,
    Quantity = 1,
    UnitPrice = 19.99m
});

// Remove an item
var itemToRemove = order.Items.Last();
order.Items.Remove(itemToRemove);

await context.SaveChangesAsync();

A single SaveChanges call generates all necessary statements: UPDATE for the order, UPDATE for the modified item, INSERT for the new item, and DELETE for the removed item.

Updating disconnected entities:

In web applications, you often receive entity data from a client that wasn't loaded from the current DbContext. These "disconnected" entities need explicit handling.

// Entity received from API request (not tracked)
var userDto = new User
{
    Id = 5,  // Existing user
    Name = "Client-Provided Name",
    Email = "client@example.com"
};

// Option 1: Update (marks all properties as modified)
context.Users.Update(userDto);
await context.SaveChangesAsync();
// Generates: UPDATE Users SET Name=..., Email=..., AND ALL OTHER COLUMNS

// Option 2: Attach and mark specific properties (more efficient)
context.Users.Attach(userDto);
context.Entry(userDto).Property(u => u.Name).IsModified = true;
context.Entry(userDto).Property(u => u.Email).IsModified = true;
await context.SaveChangesAsync();
// Generates: UPDATE Users SET Name=..., Email=... WHERE Id=5

The Update method marks every property as modified, which can be wasteful if only a few changed. Attaching and explicitly marking modified properties gives you control over what gets updated.

Reload to discard changes:

Sometimes you need to discard pending changes and restore an entity to its database state:

var user = await context.Users.FindAsync(userId);
user.Name = "Changed but we want to undo";

// Reload from database, discarding local changes
await context.Entry(user).ReloadAsync();

Console.WriteLine(user.Name);  // Original database value

Deleting Entities

Removing records follows a similar pattern: mark entities for deletion, then save.

Deleting a tracked entity:

var user = await context.Users.FindAsync(userId);

if (user != null)
{
    context.Users.Remove(user);
    await context.SaveChangesAsync();
}

Deleting without loading first:

If you know the primary key, you can delete without a round-trip to load the entity:

var user = new User { Id = userId };
context.Users.Remove(user);
await context.SaveChangesAsync();

This creates a stub entity with only the key populated, marks it for deletion, and generates a DELETE statement. It's more efficient but won't trigger cascade deletes for relationships configured in EF Core (only database-level cascades work).

Deleting from a collection:

var order = await context.Orders
    .Include(o => o.Items)
    .FirstAsync(o => o.Id == orderId);

// Remove specific item
var itemToRemove = order.Items.First(i => i.ProductId == productId);
order.Items.Remove(itemToRemove);

// Or clear all items
// order.Items.Clear();

await context.SaveChangesAsync();

When you remove an entity from a collection navigation property, EF Core marks it for deletion if the relationship is configured for cascade delete, or sets the foreign key to null for optional relationships.

Bulk Operations

Traditional EF Core operations load entities into memory before modifying them. For operations affecting many rows, this is inefficient. EF Core 7 introduced ExecuteUpdate and ExecuteDelete for bulk operations that execute directly in the database.

Bulk update without loading:

// Update all inactive users
var affected = await context.Users
    .Where(u => u.LastLoginAt < DateTime.UtcNow.AddYears(-1))
    .ExecuteUpdateAsync(setters => setters
        .SetProperty(u => u.IsActive, false)
        .SetProperty(u => u.DeactivatedAt, DateTime.UtcNow));

Console.WriteLine(

        
        

            
            Loading...
        
quot;Deactivated {affected} users");

This generates and executes: UPDATE Users SET IsActive = 0, DeactivatedAt = '...' WHERE LastLoginAt < '...'

No entities are loaded. No change tracking occurs. The operation executes as a single SQL statement, making it dramatically faster for large datasets.

Bulk delete without loading:

// Delete all unconfirmed orders older than 24 hours
var deleted = await context.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .Where(o => o.CreatedAt < DateTime.UtcNow.AddHours(-24))
    .ExecuteDeleteAsync();

Console.WriteLine(

        
        

            
            Loading...
        
quot;Deleted {deleted} abandoned orders");

Generates: DELETE FROM Orders WHERE Status = 0 AND CreatedAt < '...'

Important considerations for bulk operations:

Bulk operations bypass change tracking entirely. They don't update tracked entities in memory, don't trigger EF Core interceptors or events, and don't respect entity configurations like query filters (unless you explicitly include them in the Where clause).

They also execute immediately — unlike regular EF Core operations that queue until SaveChanges, ExecuteUpdate and ExecuteDelete run the moment you call them. They're not part of any SaveChanges transaction unless you explicitly wrap them in one.

// If you need bulk ops in a transaction with other changes
using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Bulk operation
    await context.OldRecords
        .Where(r => r.CreatedAt < cutoff)
        .ExecuteDeleteAsync();
    
    // Regular tracked changes
    context.AuditLogs.Add(new AuditLog { Action = "Purged old records" });
    await context.SaveChangesAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Transactions

By default, each SaveChanges call runs in its own transaction. For operations that span multiple SaveChanges calls or need specific isolation levels, explicit transaction control is necessary.

Explicit transaction basics:

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // First operation
    var order = new Order { CustomerId = 1, Total = 100 };
    context.Orders.Add(order);
    await context.SaveChangesAsync();
    
    // Second operation (might fail)
    var payment = new Payment { OrderId = order.Id, Amount = 100 };
    context.Payments.Add(payment);
    await context.SaveChangesAsync();
    
    // Both succeeded, commit
    await transaction.CommitAsync();
}
catch
{
    // Either failed, rollback both
    await transaction.RollbackAsync();
    throw;
}

If any operation fails, the entire transaction rolls back, leaving the database unchanged.

Savepoints for partial rollback:

Savepoints let you roll back to an intermediate state without abandoning the entire transaction:

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Critical operations
    context.Accounts.Add(new Account { ... });
    await context.SaveChangesAsync();
    
    await transaction.CreateSavepointAsync("AfterAccountCreated");
    
    try
    {
        // Optional operation that might fail
        await SendWelcomeEmail(account);
        context.EmailLogs.Add(new EmailLog { ... });
        await context.SaveChangesAsync();
    }
    catch
    {
        // Roll back only the email part
        await transaction.RollbackToSavepointAsync("AfterAccountCreated");
        // Account creation is preserved
    }
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Isolation levels:

Different isolation levels control how transactions interact with concurrent operations:

using var transaction = await context.Database.BeginTransactionAsync(
    IsolationLevel.Serializable);

Higher isolation levels provide more consistency but reduce concurrency. For most applications, the default ReadCommitted is appropriate.

Concurrency Handling

When multiple users or processes modify the same data simultaneously, conflicts can occur. One user's changes might overwrite another's unknowingly. EF Core provides optimistic concurrency control to detect and handle such conflicts.

Optimistic concurrency concept:

Instead of locking records during editing (pessimistic locking), optimistic concurrency allows concurrent edits but detects conflicts at save time. When updating, EF Core checks that the record hasn't changed since it was loaded. If it has, SaveChanges fails rather than blindly overwriting.

Setting up a concurrency token:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    
    [ConcurrencyCheck]
    public int Version { get; set; }
}

// Or with Fluent API
modelBuilder.Entity<Product>()
    .Property(p => p.Version)
    .IsConcurrencyToken();

With this setup, EF Core includes the Version in the WHERE clause of updates:

UPDATE Products 
SET Name = 'New Name', Price = 19.99, Version = 2 
WHERE Id = 1 AND Version = 1

If another transaction changed the Version to 2 between your load and save, the WHERE matches zero rows, and EF Core throws DbUpdateConcurrencyException.

Using a row version (timestamp):

A dedicated row version column that the database updates automatically on every modification is often more reliable than manual versioning:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    [Timestamp]
    public byte[] RowVersion { get; set; } = null!;
}

WitDatabase updates the RowVersion automatically on each change, and EF Core uses it for concurrency checking.

Handling concurrency conflicts:

var product = await context.Products.FindAsync(productId);
product.Price = newPrice;

try
{
    await context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Someone else modified this product since we loaded it
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();
    
    if (databaseValues == null)
    {
        // The product was deleted
        throw new Exception("Product was deleted by another user");
    }
    
    var databaseProduct = (Product)databaseValues.ToObject();
    
    // Option 1: Database wins - discard our changes
    entry.OriginalValues.SetValues(databaseValues);
    entry.CurrentValues.SetValues(databaseValues);
    
    // Option 2: Client wins - overwrite database
    entry.OriginalValues.SetValues(databaseValues);
    await context.SaveChangesAsync();  // Retry with updated original
    
    // Option 3: Merge - combine changes intelligently
    // (application-specific logic)
    
    // Option 4: Notify user and let them decide
    throw new ConcurrencyConflictException(
        

        
        

            
            Loading...
        
quot;Product was modified by another user. " +
        

        
        

            
            Loading...
        
quot;Current price in database: {databaseProduct.Price}");
}

The right resolution strategy depends on your application. For some cases, last-write-wins is acceptable. For others, user intervention is required.

Generated Values

Many columns have values generated by the database rather than provided by your application. Identity columns, computed columns, and default values all fall into this category.

Identity columns (auto-increment):

public class Order
{
    public int Id { get; set; }  // Generated by database on insert
    // ...
}

EF Core automatically recognizes integer keys named Id as identity columns. After SaveChanges, the generated value is populated on your entity.

Database defaults:

modelBuilder.Entity<Order>()
    .Property(o => o.CreatedAt)
    .HasDefaultValueSql("DATETIME('now')");

When you insert an Order without setting CreatedAt, the database provides the default. EF Core retrieves this value after insert.

Computed columns:

modelBuilder.Entity<Order>()
    .Property(o => o.TotalWithTax)
    .HasComputedColumnSql("[Total] * 1.2");

Computed columns are calculated by the database. EF Core reads them but never attempts to write them.

Client-side value generation:

For values generated in your application (not the database), use value generators:

modelBuilder.Entity<User>()
    .Property(u => u.Id)
    .HasValueGenerator<GuidValueGenerator>();

// Or inline with a factory
modelBuilder.Entity<AuditLog>()
    .Property(l => l.Timestamp)
    .HasValueGenerator((p, e) => new CurrentUtcDateTimeGenerator());

Working with Disconnected Scenarios

Web APIs and other distributed systems often work with entities that weren't loaded from the current DbContext. The client receives data, possibly modifies it, and sends it back. Handling these scenarios correctly requires understanding entity states.

Determine required operations from DTOs:

Rather than sending EF Core entities over the wire (which causes serialization issues and exposes internal structure), use DTOs and map them:

public class UpdateOrderRequest
{
    public int OrderId { get; set; }
    public List<OrderItemDto> Items { get; set; } = new();
}

public class OrderItemDto
{
    public int? Id { get; set; }  // Null for new items
    public int ProductId { get; set; }
    public int Quantity { get; set; }
    public bool IsDeleted { get; set; }  // Flag for deletion
}

public async Task UpdateOrderAsync(UpdateOrderRequest request)
{
    var order = await context.Orders
        .Include(o => o.Items)
        .FirstAsync(o => o.Id == request.OrderId);
    
    foreach (var itemDto in request.Items)
    {
        if (itemDto.IsDeleted && itemDto.Id.HasValue)
        {
            // Remove existing item
            var toRemove = order.Items.First(i => i.Id == itemDto.Id);
            order.Items.Remove(toRemove);
        }
        else if (itemDto.Id.HasValue)
        {
            // Update existing item
            var existing = order.Items.First(i => i.Id == itemDto.Id);
            existing.Quantity = itemDto.Quantity;
        }
        else
        {
            // Add new item
            order.Items.Add(new OrderItem
            {
                ProductId = itemDto.ProductId,
                Quantity = itemDto.Quantity
            });
        }
    }
    
    await context.SaveChangesAsync();
}

This pattern loads the current state, reconciles it with the requested changes, and lets change tracking generate the appropriate SQL.

TrackGraph for complex object graphs:

When you have a complete object graph from a client, TrackGraph visits each entity and lets you set its state:

context.ChangeTracker.TrackGraph(orderGraph, node =>
{
    var entity = node.Entry.Entity;
    
    if (entity is Order order)
    {
        node.Entry.State = order.Id == 0 
            ? EntityState.Added 
            : EntityState.Modified;
    }
    else if (entity is OrderItem item)
    {
        node.Entry.State = item.Id == 0 
            ? EntityState.Added 
            : EntityState.Modified;
    }
});

await context.SaveChangesAsync();

Saving Best Practices

Following these practices leads to reliable, efficient data modifications.

Save frequently in appropriate units. Each SaveChanges is a transaction boundary. Group related changes together, but don't accumulate huge numbers of changes before saving — you risk losing everything if something fails.

Use async methods. SaveChangesAsync releases the thread while waiting for the database, improving scalability in web applications.

Handle concurrency explicitly. Don't assume you're the only one modifying data. Add concurrency tokens to entities where conflicts are possible, and implement appropriate conflict resolution.

Prefer bulk operations for large datasets. If you're updating thousands of rows, ExecuteUpdate is orders of magnitude faster than loading and modifying entities individually.

Validate before saving. Catch invalid data before it reaches the database. EF Core performs some validation, but business rules often require additional checks.

Use transactions intentionally. Understand when implicit transactions (one per SaveChanges) are sufficient and when explicit transactions spanning multiple operations are needed.

Be careful with disconnected entities. Don't blindly Update entities received from clients. Validate them, load the current state, and reconcile changes explicitly.

Summary

Saving data with EF Core involves:

1. Adding entities with Add/AddRange and letting EF Core track them
2. Updating by modifying tracked entities — change tracking generates efficient UPDATE statements
3. Deleting with Remove or by removing from collections
4. Bulk operations with ExecuteUpdate/ExecuteDelete for efficiency
5. Transactions for grouping related operations atomically
6. Concurrency handling to detect and resolve conflicting modifications

With querying and saving covered, the next section explores change tracking in depth — the system that makes EF Core's "modify and save" workflow possible.

Change Tracking

Change tracking is the mechanism that makes EF Core's "load, modify, save" workflow possible. It monitors your entities, detects when properties change, and generates precise SQL statements that update only what actually changed. Understanding change tracking helps you write more efficient code and debug unexpected behaviors.

How Change Tracking Works

When EF Core executes a query that returns entities, it doesn't simply hand you objects and forget about them. Instead, it stores each entity in the change tracker along with a snapshot of its original values. This snapshot serves as the baseline for detecting modifications.

The change tracker maintains three pieces of information for each tracked entity: a reference to the entity itself, its current state (Added, Unchanged, Modified, Deleted, or Detached), and the original property values captured when the entity was first tracked.

When you modify a property on a tracked entity, the entity object changes immediately — it's just a regular .NET object. The change tracker doesn't intercept property setters in real-time. Instead, when you call SaveChanges, EF Core compares each tracked entity's current values against the stored originals. Properties that differ are marked as modified and included in the generated UPDATE statement.

This approach is called snapshot change tracking. It's simple and works with any class — no special base class or interface required. The tradeoff is that change detection happens all at once during SaveChanges rather than as changes occur.

[[Svg Src="./witdatabase-efcore-change-tracker-state.svg" Alt="witdatabase-efcore-change-tracker-state"]]

Entity States

Every tracked entity exists in one of five states. Understanding these states clarifies how EF Core decides what SQL to generate.

Detached means the entity is not tracked at all. EF Core knows nothing about it. This is the state of any object you create with new before adding it to a context, or an entity you explicitly detach.

Added means the entity is tracked and will be inserted into the database on SaveChanges. Entities enter this state when you call Add() or AddRange(), or when EF Core discovers them through navigation properties of other added entities.

Unchanged means the entity is tracked, exists in the database, and has not been modified since it was loaded or since the last SaveChanges. No SQL is generated for unchanged entities.

Modified means the entity is tracked, exists in the database, and has changes that need to be persisted. SaveChanges generates an UPDATE statement including only the modified properties.

Deleted means the entity is tracked, exists in the database, and should be removed. SaveChanges generates a DELETE statement.

After SaveChanges completes successfully:

• Added entities become Unchanged (they now exist in the database)
• Modified entities become Unchanged (database matches current values)
• Deleted entities become Detached (they no longer exist)
• Unchanged entities remain Unchanged

Inspecting Entity State

You can examine and manipulate entity state through the Entry method, which returns an EntityEntry providing access to tracking information.

var user = await context.Users.FindAsync(userId);

// Get the entry for this entity
var entry = context.Entry(user);

// Check the state
Console.WriteLine(

        
        

            
            Loading...
        
quot;State: {entry.State}");  // Unchanged

// Modify the entity
user.Name = "Modified Name";

// State hasn't changed yet - EF Core uses snapshot comparison
Console.WriteLine(

        
        

            
            Loading...
        
quot;State after modification: {entry.State}");  // Still Unchanged

// Detect changes manually
context.ChangeTracker.DetectChanges();
Console.WriteLine(

        
        

            
            Loading...
        
quot;State after DetectChanges: {entry.State}");  // Modified

Note that modifying a property doesn't immediately change the state. EF Core detects modifications by comparing current values to snapshots, which happens automatically during SaveChanges or when you explicitly call DetectChanges.

Examining property-level details:

var entry = context.Entry(user);

foreach (var property in entry.Properties)
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;{property.Metadata.Name}:");
    Console.WriteLine(

        
        

            
            Loading...
        
quot;  Current: {property.CurrentValue}");
    Console.WriteLine(

        
        

            
            Loading...
        
quot;  Original: {property.OriginalValue}");
    Console.WriteLine(

        
        

            
            Loading...
        
quot;  Modified: {property.IsModified}");
}

Checking specific properties:

var nameProperty = context.Entry(user).Property(u => u.Name);

Console.WriteLine(

        
        

            
            Loading...
        
quot;Current: {nameProperty.CurrentValue}");
Console.WriteLine(

        
        

            
            Loading...
        
quot;Original: {nameProperty.OriginalValue}");
Console.WriteLine(

        
        

            
            Loading...
        
quot;Is Modified: {nameProperty.IsModified}");

Manually Setting State

Sometimes you need to override EF Core's automatic state management. This is common when working with disconnected entities or implementing custom logic.

Setting entity state directly:

// Mark an entity as modified (useful for disconnected scenarios)
context.Entry(user).State = EntityState.Modified;

// Mark as added
context.Entry(newEntity).State = EntityState.Added;

// Mark as deleted
context.Entry(entityToDelete).State = EntityState.Deleted;

// Detach (stop tracking)
context.Entry(entity).State = EntityState.Detached;

Setting state to Modified marks all properties as modified, which generates an UPDATE that sets every column. This might not be what you want if only some properties changed.

Marking specific properties as modified:

// Attach without marking anything as modified
context.Users.Attach(user);

// Mark only specific properties
context.Entry(user).Property(u => u.Name).IsModified = true;
context.Entry(user).Property(u => u.Email).IsModified = true;

// SaveChanges generates: UPDATE Users SET Name=..., Email=... WHERE Id=...
// Other columns are not touched

This approach is more precise and efficient when you know exactly what changed.

Resetting to unchanged:

// Discard modifications, revert to original values
context.Entry(user).State = EntityState.Unchanged;

// Or reload from database
await context.Entry(user).ReloadAsync();

The ChangeTracker Object

The DbContext exposes a ChangeTracker property that provides access to all tracked entities and global tracking operations.

Listing all tracked entities:

foreach (var entry in context.ChangeTracker.Entries())
{
    Console.WriteLine(

        
        

            
            Loading...
        
quot;{entry.Entity.GetType().Name}: {entry.State}");
}

// Filter by state
var modified = context.ChangeTracker.Entries()
    .Where(e => e.State == EntityState.Modified)
    .ToList();

// Filter by type
var modifiedUsers = context.ChangeTracker.Entries<User>()
    .Where(e => e.State == EntityState.Modified)
    .ToList();

Detecting changes manually:

// Force change detection (normally automatic before SaveChanges)
context.ChangeTracker.DetectChanges();

DetectChanges compares all tracked entities against their snapshots. It's called automatically by SaveChanges and certain query operations, but you might call it explicitly when inspecting state or before making decisions based on what changed.

Checking if anything changed:

// Returns true if any entity is Added, Modified, or Deleted
bool hasChanges = context.ChangeTracker.HasChanges();

if (hasChanges)
{
    Console.WriteLine("There are unsaved changes");
}

Clearing the change tracker:

// Stop tracking all entities
context.ChangeTracker.Clear();

This detaches every entity, releasing memory and allowing the context to start fresh. Useful for long-lived contexts that process batches of data.

Original Values and Current Values

Each tracked entity has two sets of values: original (from when it was loaded or added) and current (the actual property values now). You can read and write both.

Reading values:

var entry = context.Entry(user);

// Current values (same as reading properties directly)
var currentName = entry.CurrentValues["Name"];
var currentNameTyped = entry.CurrentValues.GetValue<string>("Name");

// Original values (what was in the database when loaded)
var originalName = entry.OriginalValues["Name"];
var originalNameTyped = entry.OriginalValues.GetValue<string>("Name");

Modifying values:

// Change current value (same as setting the property)
entry.CurrentValues["Name"] = "New Name";

// Change original value (affects change detection)
entry.OriginalValues["Name"] = "Different Original";

Changing original values affects what EF Core considers "changed." This is useful for advanced scenarios like implementing custom concurrency resolution.

Copying values between entities:

// Copy current values to a new object
var clone = entry.CurrentValues.ToObject();

// Copy current values from another source
entry.CurrentValues.SetValues(sourceObject);

// Copy original values from database (refresh)
var databaseValues = await entry.GetDatabaseValuesAsync();
entry.OriginalValues.SetValues(databaseValues);

Tracking Behavior Options

EF Core provides options to control default tracking behavior, which can improve performance for read-heavy scenarios.

No-tracking by default:

// Set in OnConfiguring
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

// Or per context instance
context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

With no-tracking as default, queries return entities that aren't tracked. This saves memory and CPU for read-only operations. Use AsTracking() on specific queries where you need change tracking.

Auto-detect changes control:

// Disable automatic change detection
context.ChangeTracker.AutoDetectChangesEnabled = false;

// Now you must call DetectChanges manually before SaveChanges
context.ChangeTracker.DetectChanges();
await context.SaveChangesAsync();

Disabling auto-detect improves performance when you're making many changes and don't need EF Core to detect them incrementally. Remember to call DetectChanges before SaveChanges, or set states manually.

Lazy loading configuration:

// Disable lazy loading (often a good idea for performance predictability)
context.ChangeTracker.LazyLoadingEnabled = false;

Change Tracking Events

EF Core fires events when tracking state changes, allowing you to hook into the tracking lifecycle.

Tracked event — when an entity starts being tracked:

context.ChangeTracker.Tracked += (sender, args) =>
{
    var entity = args.Entry.Entity;
    var state = args.Entry.State;
    var fromQuery = args.FromQuery;  // true if entity came from a query
    
    Console.WriteLine(

        
        

            
            Loading...
        
quot;Now tracking {entity.GetType().Name} in state {state}");
};

StateChanged event — when an entity's state changes:

context.ChangeTracker.StateChanged += (sender, args) =>
{
    var entity = args.Entry.Entity;
    var oldState = args.OldState;
    var newState = args.NewState;
    
    Console.WriteLine(

        
        

            
            Loading...
        
quot;{entity.GetType().Name}: {oldState} → {newState}");
};

These events are useful for auditing, logging, or implementing cross-cutting concerns like automatic timestamp updates.

Practical example — automatic audit fields:

context.ChangeTracker.StateChanged += (sender, args) =>
{
    if (args.Entry.Entity is IAuditable auditable)
    {
        var now = DateTime.UtcNow;
        
        if (args.NewState == EntityState.Added)
        {
            auditable.CreatedAt = now;
            auditable.CreatedBy = currentUser;
        }
        
        if (args.NewState == EntityState.Modified)
        {
            auditable.ModifiedAt = now;
            auditable.ModifiedBy = currentUser;
        }
    }
};

Identity Resolution

When EF Core loads entities, it checks whether an entity with the same key is already tracked. If so, it returns the existing instance rather than creating a duplicate. This is called identity resolution.

var user1 = await context.Users.FindAsync(1);
var user2 = await context.Users.FindAsync(1);

Console.WriteLine(ReferenceEquals(user1, user2));  // True - same instance

// Even from different queries
var user3 = await context.Users.FirstAsync(u => u.Id == 1);
Console.WriteLine(ReferenceEquals(user1, user3));  // True

Identity resolution ensures consistency: if you load the same entity twice and modify it, you're always modifying the same instance. Without this, you could have two objects representing the same database row with different values, leading to confusion about which to save.

No-tracking queries don't use identity resolution by default:

var user1 = await context.Users.AsNoTracking().FirstAsync(u => u.Id == 1);
var user2 = await context.Users.AsNoTracking().FirstAsync(u => u.Id == 1);

Console.WriteLine(ReferenceEquals(user1, user2));  // False - different instances

If you want identity resolution without change tracking, use AsNoTrackingWithIdentityResolution():

var users = await context.Users
    .AsNoTrackingWithIdentityResolution()
    .Include(u => u.Orders)
    .ToListAsync();

// Same user appearing in multiple places will be the same instance
// But the user won't be tracked for changes

Performance Considerations

Change tracking consumes memory and CPU. Each tracked entity requires storage for the entity reference, original values snapshot, and tracking metadata. Change detection compares every property of every tracked entity.

Symptoms of change tracking overhead:

• High memory usage in DbContext instances
• Slow SaveChanges calls even with few actual changes
• GC pressure from many tracked entities

Strategies to reduce overhead:

Use no-tracking queries for read-only operations. If you're displaying data, generating reports, or sending responses to API clients, you probably don't need change tracking.

// Read-only: skip tracking
var products = await context.Products
    .AsNoTracking()
    .Where(p => p.Category == "Electronics")
    .ToListAsync();

Clear the change tracker for batch processing. If you're processing records in batches, clear between batches to release tracked entities:

foreach (var batch in records.Chunk(1000))
{
    foreach (var record in batch)
    {
        // Process and save
    }
    await context.SaveChangesAsync();
    context.ChangeTracker.Clear();
}

Use short-lived contexts. Rather than keeping one context for the entire application lifetime, create contexts per operation or request. ASP.NET Core's scoped DbContext registration does this automatically for web requests.

Disable auto-detect for bulk modifications. When making many changes with explicit state management, disable auto-detection:

context.ChangeTracker.AutoDetectChangesEnabled = false;

foreach (var item in manyItems)
{
    context.Entry(item).State = EntityState.Modified;
}

await context.SaveChangesAsync();
context.ChangeTracker.AutoDetectChangesEnabled = true;

Common Change Tracking Pitfalls

Modifying detached entities without reattaching:

var user = await context.Users.AsNoTracking().FirstAsync(u => u.Id == 1);
user.Name = "Changed";
await context.SaveChangesAsync();  // Does nothing! Entity not tracked.

// Solution: attach or use tracking query
context.Users.Update(user);  // Now tracked as Modified
await context.SaveChangesAsync();

Expecting immediate state changes:

var user = await context.Users.FindAsync(1);
Console.WriteLine(context.Entry(user).State);  // Unchanged

user.Name = "New Name";
Console.WriteLine(context.Entry(user).State);  // Still Unchanged!

// State changes after DetectChanges (called automatically by SaveChanges)
context.ChangeTracker.DetectChanges();
Console.WriteLine(context.Entry(user).State);  // Modified

Tracking entities from multiple contexts:

User user;
using (var context1 = new AppDbContext())
{
    user = await context1.Users.FindAsync(1);
}

using (var context2 = new AppDbContext())
{
    // user is not tracked by context2
    user.Name = "Changed";
    await context2.SaveChangesAsync();  // Does nothing!
    
    // Solution: attach to new context
    context2.Users.Update(user);
    await context2.SaveChangesAsync();
}

Forgetting that Clear() detaches everything:

var user = await context.Users.FindAsync(1);
user.Name = "Changed";

context.ChangeTracker.Clear();  // Oops, user is now detached

await context.SaveChangesAsync();  // Nothing saved!

Summary

Change tracking is EF Core's mechanism for detecting modifications:

1. Snapshot comparison detects what changed by comparing current values to originals
2. Entity states (Added, Unchanged, Modified, Deleted, Detached) determine what SQL is generated
3. EntityEntry provides access to inspect and manipulate tracking information
4. ChangeTracker manages all tracked entities and provides global operations
5. Identity resolution ensures one instance per primary key within a context
6. Performance can be tuned with no-tracking queries, Clear(), and disabling auto-detect

With change tracking understood, the next section covers performance optimization techniques for production applications.

Performance Optimization

EF Core adds abstraction layers between your code and the database. While these layers provide productivity benefits, they also introduce overhead. For most applications, this overhead is negligible compared to network latency and actual database processing time. However, high-throughput applications, batch processing jobs, and performance-critical paths benefit from deliberate optimization.

This section covers techniques to make your EF Core applications faster, from simple configuration changes to architectural decisions that fundamentally improve efficiency.

Understanding Where Time Goes

Before optimizing, understand where your application spends time. EF Core operations involve several phases, each with different performance characteristics.

Query translation converts your LINQ expression to SQL. This happens every time you execute a query, though EF Core caches the translation for identical query shapes. Complex queries with many operations take longer to translate.

Command execution sends SQL to the database and waits for results. This typically dominates total time — network round-trips and database processing are orders of magnitude slower than in-memory operations.

Materialization converts raw database results into .NET objects. For queries returning many entities with many properties, materialization time becomes noticeable.

Change tracking stores entities and their original values. Memory pressure from tracking many entities can cause garbage collection pauses.

Effective optimization targets the phases that matter most for your specific workload. Optimizing translation is pointless if your application is bottlenecked on disk I/O.

Connection and Context Management

How you manage DbContext instances significantly impacts performance. Creating a DbContext has overhead: building the internal model, setting up change tracking, and potentially opening database connections.

Use DbContext pooling in web applications. Instead of creating new contexts for each request, pooling reuses context instances:

// Registration with pooling
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseWitDb(connectionString),
    poolSize: 128);

When a request needs a context, it gets one from the pool. After the request completes, the context is reset (change tracker cleared, state cleaned up) and returned to the pool. This eliminates per-request construction overhead.

Pooling constraints to remember: pooled contexts can't have constructor-injected scoped services, and any state you add to your DbContext class persists across pool checkouts. Keep DbContext classes stateless.

Configure connection pooling. The underlying database connection also benefits from pooling:

var connectionString = "Data Source=app.witdb;Pooling=true;Max Pool Size=100";

Connection pooling reuses database connections rather than opening and closing them for each operation. This is especially beneficial for encrypted databases where connection setup includes key derivation.

Scope contexts appropriately. For web applications, one context per request (the default with AddDbContext) works well. For background services processing items independently, create short-lived contexts per item or batch:

public async Task ProcessItemsAsync(IEnumerable<int> itemIds)
{
    foreach (var itemId in itemIds)
    {
        await using var context = await _contextFactory.CreateDbContextAsync();
        
        var item = await context.Items.FindAsync(itemId);
        // Process item
        await context.SaveChangesAsync();
        
        // Context disposed, memory released
    }
}

Long-lived contexts accumulate tracked entities, increasing memory usage and slowing change detection.

Query Optimization

Most EF Core performance issues stem from inefficient queries. Small improvements in query patterns often yield dramatic results.

Retrieve only what you need. If you need three columns, don't load entire entities with dozens of properties:

// Inefficient: loads all columns, tracks entities
var users = await context.Users
    .Where(u => u.IsActive)
    .ToListAsync();

// Better: loads only needed columns, no tracking overhead
var userSummaries = await context.Users
    .Where(u => u.IsActive)
    .Select(u => new { u.Id, u.Name, u.Email })
    .ToListAsync();

The projected query transfers less data from the database and creates smaller objects in memory.

Use no-tracking for read-only operations. When you won't modify entities, skip change tracking:

var products = await context.Products
    .AsNoTracking()
    .Where(p => p.Category == "Electronics")
    .OrderBy(p => p.Name)
    .ToListAsync();

No-tracking queries skip snapshot creation and identity resolution, reducing memory allocation and CPU usage.

Avoid N+1 queries with proper loading. The N+1 problem occurs when you iterate over entities and access unloaded navigation properties:

// BAD: 1 query for orders + N queries for customers
var orders = await context.Orders.ToListAsync();
foreach (var order in orders)
{
    Console.WriteLine(order.Customer.Name);  // Each access may trigger a query
}

// GOOD: 1 or 2 queries total
var orders = await context.Orders
    .Include(o => o.Customer)
    .ToListAsync();

Use logging to identify N+1 patterns in your application. The symptom is many similar queries executed in rapid succession.

Filter in the database, not in memory. Apply Where clauses before materialization:

// BAD: loads all orders, filters in memory
var recentOrders = (await context.Orders.ToListAsync())
    .Where(o => o.OrderDate > cutoff)
    .ToList();

// GOOD: database does the filtering
var recentOrders = await context.Orders
    .Where(o => o.OrderDate > cutoff)
    .ToListAsync();

The database can use indexes and process data without transferring it over the wire.

Paginate large result sets. Never return unbounded query results:

var page = await context.Products
    .OrderBy(p => p.Id)  // Stable ordering required for pagination
    .Skip(pageNumber * pageSize)
    .Take(pageSize)
    .ToListAsync();

For very large datasets where offset-based pagination becomes slow, consider keyset pagination:

// More efficient for large offsets
var nextPage = await context.Products
    .Where(p => p.Id > lastSeenId)
    .OrderBy(p => p.Id)
    .Take(pageSize)
    .ToListAsync();

Compiled Queries

Every time you execute a LINQ query, EF Core translates the expression tree to SQL. For frequently executed queries, this translation overhead adds up. Compiled queries perform translation once and cache the result.

// Define the compiled query (typically as a static field)
private static readonly Func<AppDbContext, int, Task<User?>> GetUserById =
    EF.CompileAsyncQuery((AppDbContext ctx, int id) =>
        ctx.Users.FirstOrDefault(u => u.Id == id));

// Use it
var user = await GetUserById(context, userId);

The first call compiles and caches the query. Subsequent calls reuse the cached SQL, skipping translation entirely.

When to use compiled queries:

• Queries executed thousands of times per second
• Simple queries where translation overhead is significant relative to execution
• Hot paths identified through profiling

When not to bother:

• Queries executed occasionally
• Complex queries where translation is a small fraction of total time
• Queries whose shape varies based on runtime conditions

// More compiled query examples
private static readonly Func<AppDbContext, string, IAsyncEnumerable<Product>> 
    GetProductsByCategory = EF.CompileAsyncQuery(
        (AppDbContext ctx, string category) =>
            ctx.Products.Where(p => p.Category == category));

private static readonly Func<AppDbContext, int, int, IAsyncEnumerable<Order>> 
    GetOrdersPage = EF.CompileAsyncQuery(
        (AppDbContext ctx, int skip, int take) =>
            ctx.Orders.OrderBy(o => o.Id).Skip(skip).Take(take));

// Usage
await foreach (var product in GetProductsByCategory(context, "Electronics"))
{
    // Process product
}

Bulk Operations

Traditional EF Core patterns load entities, modify them, and save. For operations affecting many rows, this is terribly inefficient. Bulk operations execute directly in the database without loading entities.

ExecuteUpdate for mass updates:

// Update thousands of rows in one statement
var affected = await context.Products
    .Where(p => p.Category == "Discontinued")
    .ExecuteUpdateAsync(s => s
        .SetProperty(p => p.IsActive, false)
        .SetProperty(p => p.DiscontinuedAt, DateTime.UtcNow));

This generates: UPDATE Products SET IsActive = 0, DiscontinuedAt = '...' WHERE Category = 'Discontinued'

No entities are loaded. No change tracking occurs. One round-trip regardless of how many rows match.

ExecuteDelete for mass deletion:

// Delete thousands of rows in one statement
var deleted = await context.OldLogs
    .Where(l => l.CreatedAt < DateTime.UtcNow.AddYears(-1))
    .ExecuteDeleteAsync();

Batching for inserts:

For inserting many entities, EF Core batches INSERT statements automatically. You can tune the batch size:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(connectionString, witOptions =>
    {
        witOptions.MaxBatchSize(100);  // Statements per batch
    }));

Larger batches mean fewer round-trips but larger individual commands. The optimal size depends on your network latency and database configuration.

Consider raw SQL for complex bulk operations:

When EF Core's bulk operations don't support your scenario, raw SQL might:

await context.Database.ExecuteSqlInterpolatedAsync($@"
    UPDATE Products 
    SET Price = Price * {multiplier}
    WHERE CategoryId IN (
        SELECT Id FROM Categories WHERE Region = {region}
    )");

Efficient Loading Strategies

How you load related data dramatically affects performance. Choose the right strategy for each scenario.

Eager loading with Include is best when you know you'll need related data:

var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .Where(o => o.Status == OrderStatus.Pending)
    .ToListAsync();

The downside: if you include collections, the result set grows multiplicatively. An order with 10 items means 10 rows in the JOIN result, with order data repeated.

Split queries separate the includes into multiple statements:

var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

EF Core executes separate queries for orders-with-customers and for items, then correlates them in memory. This avoids data duplication but requires multiple round-trips.

Projection is often better than either:

var orderDtos = await context.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .Select(o => new OrderDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Quantity * i.UnitPrice)
    })
    .ToListAsync();

Projection lets the database compute aggregates and returns only the data you need. No entity tracking, no extra data transferred.

Explicit loading works well when you conditionally need related data:

var order = await context.Orders.FindAsync(orderId);

if (order.RequiresShipping)
{
    await context.Entry(order)
        .Reference(o => o.ShippingAddress)
        .LoadAsync();
}

if (order.Status == OrderStatus.Processing)
{
    await context.Entry(order)
        .Collection(o => o.Items)
        .LoadAsync();
}

Indexing Strategies

Database indexes are crucial for query performance. Without appropriate indexes, the database must scan entire tables to find matching rows.

Index columns used in WHERE clauses:

modelBuilder.Entity<User>()
    .HasIndex(u => u.Email);

modelBuilder.Entity<Order>()
    .HasIndex(o => o.Status);

Create composite indexes for multi-column queries:

// For queries like: WHERE CustomerId = x AND Status = y
modelBuilder.Entity<Order>()
    .HasIndex(o => new { o.CustomerId, o.Status });

Column order matters in composite indexes. Put the most selective column first, or the column that appears in equality conditions.

Index columns used in ORDER BY for large tables:

modelBuilder.Entity<Event>()
    .HasIndex(e => e.CreatedAt);

Without an index, sorting requires loading and sorting all matching rows. With an index, the database can read rows in sorted order directly.

Add unique indexes for uniqueness constraints:

modelBuilder.Entity<User>()
    .HasIndex(u => u.Email)
    .IsUnique();

Unique indexes serve double duty: they enforce the constraint and optimize lookups.

Don't over-index. Each index consumes storage and slows down inserts and updates. Index strategically based on actual query patterns.

Monitoring and Profiling

You can't optimize what you can't measure. Enable logging to see what queries EF Core generates.

Enable EF Core logging:

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging()  // Include parameter values (dev only!)
        .EnableDetailedErrors());

Log only database commands:

options.LogTo(
    Console.WriteLine,
    new[] { DbLoggerCategory.Database.Command.Name },
    LogLevel.Information);

Capture query metrics:

// Simple timing
var sw = Stopwatch.StartNew();
var results = await context.Users.Where(u => u.IsActive).ToListAsync();
sw.Stop();
Console.WriteLine(

        
        

            
            Loading...
        
quot;Query took {sw.ElapsedMilliseconds}ms, returned {results.Count} rows");

What to look for:

• Queries returning many more rows than expected
• Multiple similar queries in rapid succession (N+1 problem)
• Queries not using expected indexes (check EXPLAIN plans)
• Large numbers of tracked entities
• Frequent garbage collections

Common Performance Pitfalls

Avoid these patterns that cause performance problems:

Loading entire tables:

// BAD: loads everything
var allUsers = await context.Users.ToListAsync();
var activeCount = allUsers.Count(u => u.IsActive);

// GOOD: database counts
var activeCount = await context.Users.CountAsync(u => u.IsActive);

Calling ToList too early:

// BAD: loads all products, then filters in memory
var products = await context.Products.ToListAsync();
var expensive = products.Where(p => p.Price > 100);

// GOOD: database filters
var expensive = await context.Products
    .Where(p => p.Price > 100)
    .ToListAsync();

Ignoring async in web applications:

// BAD: blocks thread while waiting for database
var users = context.Users.ToList();

// GOOD: releases thread during database I/O
var users = await context.Users.ToListAsync();

Tracking entities you won't modify:

// BAD: tracking overhead for read-only data
var report = await context.Orders
    .Include(o => o.Items)
    .ToListAsync();

// GOOD: skip tracking
var report = await context.Orders
    .AsNoTracking()
    .Include(o => o.Items)
    .ToListAsync();

Using string-based queries when LINQ suffices:

// Usually unnecessary and error-prone
var users = await context.Users
    .FromSqlRaw("SELECT * FROM Users WHERE IsActive = 1")
    .ToListAsync();

// Cleaner and equally efficient
var users = await context.Users
    .Where(u => u.IsActive)
    .ToListAsync();

Performance Checklist

Use this checklist when optimizing EF Core applications:

Query optimization:

• Use projection (Select) to retrieve only needed columns
• Apply AsNoTracking for read-only queries
• Eliminate N+1 queries with Include or projection
• Filter in database with Where before ToList
• Paginate large result sets with Skip/Take

Loading strategies:

• Choose appropriate loading strategy per query (eager, explicit, projection)
• Use AsSplitQuery for queries with large collections
• Avoid loading related data you won't use

Bulk operations:

• Use ExecuteUpdate/ExecuteDelete for mass modifications
• Consider raw SQL for complex bulk scenarios

Context management:

• Use DbContext pooling in web applications
• Keep contexts short-lived for batch processing
• Clear change tracker between batches

Infrastructure:

• Add indexes for frequently queried columns
• Configure appropriate connection pool size
• Enable and monitor query logging

Summary

EF Core performance optimization involves:

1. Understanding the cost model — translation, execution, materialization, tracking
2. Managing contexts efficiently — pooling, appropriate lifetimes
3. Writing efficient queries — projection, filtering, no-tracking
4. Using compiled queries for high-frequency operations
5. Bulk operations for mass updates and deletes
6. Strategic indexing based on actual query patterns
7. Monitoring and profiling to identify bottlenecks

The next section covers advanced EF Core features including value converters, interceptors, and global query filters.

Advanced Topics

Beyond the fundamentals of querying and saving data, EF Core offers powerful features for sophisticated scenarios. This section covers techniques that help you handle complex requirements: custom type mappings, automatic behaviors through interceptors, cross-cutting concerns like soft delete, and patterns for domain-driven design.

Value Converters

Value converters transform data between your .NET types and database storage. They're useful when you want to store data in a format different from how you use it in code, or when working with types that don't have native database equivalents.

Why use value converters:

• Store enums as strings for readability in the database
• Encrypt sensitive fields transparently
• Store complex objects as JSON
• Handle legacy database formats
• Map custom types to database primitives

Basic enum-to-string conversion:

By default, EF Core stores enums as integers. For database readability and forward compatibility, strings are often better:

public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

// Configure the conversion
modelBuilder.Entity<Order>()
    .Property(o => o.Status)
    .HasConversion<string>();

// Or with explicit converter
modelBuilder.Entity<Order>()
    .Property(o => o.Status)
    .HasConversion(
        v => v.ToString(),
        v => Enum.Parse<OrderStatus>(v));

Now the Status column stores "Pending", "Processing", etc., instead of 0, 1, 2.

Custom type conversion:

Convert any type by specifying how to translate to and from the storage type:

public class Money
{
    public decimal Amount { get; }
    public string Currency { get; }
    
    public Money(decimal amount, string currency)
    {
        Amount = amount;
        Currency = currency;
    }
}

modelBuilder.Entity<Product>()
    .Property(p => p.Price)
    .HasConversion(
        v => 

        
        

            
            Loading...
        
quot;{v.Amount}|{v.Currency}",  // To database
        v => ParseMoney(v));               // From database

private static Money ParseMoney(string stored)
{
    var parts = stored.Split('|');
    return new Money(decimal.Parse(parts[0]), parts[1]);
}

JSON storage for complex objects:

Store objects that don't warrant their own table as JSON:

public class UserPreferences
{
    public string Theme { get; set; } = "light";
    public string Language { get; set; } = "en";
    public bool EmailNotifications { get; set; } = true;
    public List<string> FavoriteCategories { get; set; } = new();
}

modelBuilder.Entity<User>()
    .Property(u => u.Preferences)
    .HasConversion(
        v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
        v => JsonSerializer.Deserialize<UserPreferences>(v, (JsonSerializerOptions?)null) 
             ?? new UserPreferences());

The Preferences column stores JSON text. When you load a user, EF Core deserializes it back to a UserPreferences object.

Built-in converters:

EF Core includes many predefined converters for common scenarios:

// Use a built-in converter
modelBuilder.Entity<User>()
    .Property(u => u.IsActive)
    .HasConversion(new BoolToStringConverter("No", "Yes"));

Converter limitations:

• Converters apply to simple properties, not navigation properties
• You can't use converted properties in some query operations (EF Core can't translate the conversion to SQL)
• Consider performance — conversion happens for every read and write

Owned Types

Owned types model value objects — objects that have no identity of their own and exist only as part of their parent entity. They're common in domain-driven design for concepts like addresses, money values, or date ranges.

Defining an owned type:

public class Address
{
    public string Street { get; set; } = string.Empty;
    public string City { get; set; } = string.Empty;
    public string State { get; set; } = string.Empty;
    public string PostalCode { get; set; } = string.Empty;
    public string Country { get; set; } = string.Empty;
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    public Address ShippingAddress { get; set; } = new();
    public Address? BillingAddress { get; set; }
}

Configure Address as an owned type:

modelBuilder.Entity<Customer>(entity =>
{
    entity.OwnsOne(c => c.ShippingAddress, address =>
    {
        address.Property(a => a.Street).HasColumnName("ShippingStreet");
        address.Property(a => a.City).HasColumnName("ShippingCity");
        // ... other properties
    });
    
    entity.OwnsOne(c => c.BillingAddress, address =>
    {
        address.Property(a => a.Street).HasColumnName("BillingStreet");
        address.Property(a => a.City).HasColumnName("BillingCity");
    });
});

The Address properties are stored in the Customer table as separate columns (ShippingStreet, ShippingCity, BillingStreet, etc.), but in code you work with them as coherent Address objects.

Owned type collections:

For scenarios like a customer with multiple addresses:

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Address> Addresses { get; set; } = new();
}

modelBuilder.Entity<Customer>()
    .OwnsMany(c => c.Addresses, address =>
    {
        address.ToTable("CustomerAddresses");
        address.WithOwner().HasForeignKey("CustomerId");
        address.Property<int>("Id");
        address.HasKey("Id");
    });

Owned collections create a separate table but maintain the ownership semantics — addresses are deleted when the customer is deleted, and you can't query addresses independently of customers.

Global Query Filters

Query filters apply automatically to all queries for an entity type. They're powerful for implementing soft delete, multi-tenancy, and other cross-cutting data filtering concerns.

Soft delete implementation:

Instead of physically deleting records, mark them as deleted:

public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
    DateTime? DeletedAt { get; set; }
}

public class Product : ISoftDeletable
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

modelBuilder.Entity<Product>()
    .HasQueryFilter(p => !p.IsDeleted);

Now all queries automatically exclude deleted products:

// This query only returns non-deleted products
var products = await context.Products.ToListAsync();

// SQL: SELECT * FROM Products WHERE IsDeleted = 0

Override filters when needed:

// Include deleted products
var allProducts = await context.Products
    .IgnoreQueryFilters()
    .ToListAsync();

Multi-tenancy with query filters:

Isolate data between tenants:

public class AppDbContext : DbContext
{
    private readonly int _tenantId;
    
    public AppDbContext(DbContextOptions options, ITenantProvider tenantProvider) 
        : base(options)
    {
        _tenantId = tenantProvider.GetCurrentTenantId();
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Product>()
            .HasQueryFilter(p => p.TenantId == _tenantId);
        
        modelBuilder.Entity<Customer>()
            .HasQueryFilter(c => c.TenantId == _tenantId);
    }
}

Every query automatically filters to the current tenant. This makes tenant data isolation automatic and hard to accidentally bypass.

Combining multiple filters:

modelBuilder.Entity<Product>()
    .HasQueryFilter(p => !p.IsDeleted && p.TenantId == _tenantId);

Be thoughtful about filter complexity — they apply to every query, so expensive conditions impact performance across the board.

Interceptors

Interceptors let you hook into EF Core's operations at various points. They're useful for logging, auditing, modifying commands before execution, or implementing automatic behaviors.

SaveChanges interceptor for auditing:

public interface IAuditable
{
    DateTime CreatedAt { get; set; }
    string? CreatedBy { get; set; }
    DateTime? ModifiedAt { get; set; }
    string? ModifiedBy { get; set; }
}

public class AuditInterceptor : SaveChangesInterceptor
{
    private readonly IUserContext _userContext;
    
    public AuditInterceptor(IUserContext userContext)
    {
        _userContext = userContext;
    }
    
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        var context = eventData.Context;
        if (context == null) return ValueTask.FromResult(result);
        
        var now = DateTime.UtcNow;
        var user = _userContext.CurrentUser;
        
        foreach (var entry in context.ChangeTracker.Entries<IAuditable>())
        {
            if (entry.State == EntityState.Added)
            {
                entry.Entity.CreatedAt = now;
                entry.Entity.CreatedBy = user;
            }
            
            if (entry.State == EntityState.Modified)
            {
                entry.Entity.ModifiedAt = now;
                entry.Entity.ModifiedBy = user;
            }
        }
        
        return ValueTask.FromResult(result);
    }
}

Register the interceptor:

builder.Services.AddDbContext<AppDbContext>((provider, options) =>
    options.UseWitDb(connectionString)
        .AddInterceptors(provider.GetRequiredService<AuditInterceptor>()));

Now every entity implementing IAuditable automatically gets audit timestamps.

Command interceptor for logging:

public class QueryLoggingInterceptor : DbCommandInterceptor
{
    private readonly ILogger<QueryLoggingInterceptor> _logger;
    
    public QueryLoggingInterceptor(ILogger<QueryLoggingInterceptor> logger)
    {
        _logger = logger;
    }
    
    public override ValueTask<DbDataReader> ReaderExecutedAsync(
        DbCommand command,
        CommandExecutedEventData eventData,
        DbDataReader result,
        CancellationToken cancellationToken = default)
    {
        _logger.LogInformation(
            "Query executed in {Duration}ms: {Sql}",
            eventData.Duration.TotalMilliseconds,
            command.CommandText);
        
        return ValueTask.FromResult(result);
    }
}

Connection interceptor for automatic retries:

public class ConnectionRetryInterceptor : DbConnectionInterceptor
{
    public override async ValueTask<InterceptionResult> ConnectionOpeningAsync(
        DbConnection connection,
        ConnectionEventData eventData,
        InterceptionResult result,
        CancellationToken cancellationToken = default)
    {
        var attempts = 0;
        const int maxAttempts = 3;
        
        while (true)
        {
            try
            {
                await connection.OpenAsync(cancellationToken);
                return InterceptionResult.Suppress();  // We opened it, skip default
            }
            catch (Exception) when (++attempts < maxAttempts)
            {
                await Task.Delay(TimeSpan.FromSeconds(attempts), cancellationToken);
            }
        }
    }
}

Shadow Properties

Shadow properties exist in the EF Core model but have no corresponding property in your entity class. They're useful for tracking metadata that you don't want cluttering your domain model.

Defining shadow properties:

modelBuilder.Entity<Product>(entity =>
{
    entity.Property<DateTime>("LastUpdated");
    entity.Property<string>("UpdatedBy");
});

The Product class has no LastUpdated or UpdatedBy properties, but the database table does.

Setting shadow property values:

var product = await context.Products.FindAsync(productId);
product.Price = newPrice;

// Set shadow properties via Entry
context.Entry(product).Property("LastUpdated").CurrentValue = DateTime.UtcNow;
context.Entry(product).Property("UpdatedBy").CurrentValue = currentUser;

await context.SaveChangesAsync();

Querying with shadow properties:

var recentlyUpdated = await context.Products
    .Where(p => EF.Property<DateTime>(p, "LastUpdated") > DateTime.UtcNow.AddDays(-7))
    .OrderByDescending(p => EF.Property<DateTime>(p, "LastUpdated"))
    .ToListAsync();

Shadow properties are also used automatically by EF Core for foreign keys when you don't define them explicitly.

Table Splitting

Table splitting maps multiple entity types to a single database table. This is useful when you have a table with many columns but want to load subsets of them in different scenarios.

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public decimal Price { get; set; }
    public bool IsActive { get; set; }
    
    public ProductDetails Details { get; set; } = null!;
}

public class ProductDetails
{
    public int Id { get; set; }  // Same PK as Product
    public string FullDescription { get; set; } = string.Empty;
    public string Specifications { get; set; } = string.Empty;
    public byte[]? Image { get; set; }
}

modelBuilder.Entity<Product>(entity =>
{
    entity.ToTable("Products");
    entity.HasOne(p => p.Details)
        .WithOne()
        .HasForeignKey<ProductDetails>(d => d.Id);
});

modelBuilder.Entity<ProductDetails>(entity =>
{
    entity.ToTable("Products");  // Same table!
});

Now you can load just the core Product properties when you don't need the heavy details:

// Quick listing - doesn't load description, specs, or image
var products = await context.Products
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToListAsync();

// Full details when needed
var product = await context.Products
    .Include(p => p.Details)
    .FirstAsync(p => p.Id == productId);

Entity Splitting

The opposite of table splitting — one entity maps to multiple tables. Useful for denormalized databases or when certain columns are stored separately for performance.

modelBuilder.Entity<Customer>(entity =>
{
    entity.ToTable("Customers");
    
    entity.SplitToTable("CustomerDetails", table =>
    {
        table.Property(c => c.Biography);
        table.Property(c => c.ProfilePicture);
        table.Property(c => c.Preferences);
    });
});

The Customer entity has some properties in the Customers table and others in CustomerDetails. EF Core JOINs them automatically when you query.

Raw SQL and Database Functions

Sometimes you need database capabilities that EF Core doesn't expose directly. You can define custom functions that translate to SQL.

Mapping a database function:

public static class DatabaseFunctions
{
    [DbFunction("LENGTH", IsBuiltIn = true)]
    public static int StringLength(string value) 
        => throw new NotSupportedException();
    
    [DbFunction("SUBSTR", IsBuiltIn = true)]
    public static string Substring(string value, int start, int length)
        => throw new NotSupportedException();
}

// Register in OnModelCreating
modelBuilder.HasDbFunction(typeof(DatabaseFunctions)
    .GetMethod(nameof(DatabaseFunctions.StringLength))!);

Use in queries:

var longNames = await context.Products
    .Where(p => DatabaseFunctions.StringLength(p.Name) > 50)
    .ToListAsync();

// Translates to: SELECT * FROM Products WHERE LENGTH(Name) > 50

Defining scalar SQL expressions:

modelBuilder.Entity<Order>()
    .Property(o => o.TaxAmount)
    .HasComputedColumnSql("[Total] * 0.1");

Database-Generated Values

Configure how EF Core handles values generated by the database.

Identity columns:

modelBuilder.Entity<Order>()
    .Property(o => o.Id)
    .UseIdentityColumn();  // Auto-increment

Default values:

modelBuilder.Entity<Order>()
    .Property(o => o.CreatedAt)
    .HasDefaultValueSql("DATETIME('now')");

modelBuilder.Entity<Product>()
    .Property(p => p.IsActive)
    .HasDefaultValue(true);

Computed columns:

modelBuilder.Entity<Order>()
    .Property(o => o.TotalWithTax)
    .HasComputedColumnSql("[Total] * 1.2", stored: true);

The stored: true parameter persists the computed value, creating an indexed column rather than computing on every read.

Working with Views

Map entities to database views for complex queries:

modelBuilder.Entity<OrderSummary>(entity =>
{
    entity.ToView("vw_OrderSummaries");
    entity.HasNoKey();  // Views often don't have a single PK
});

// Query the view like any entity
var summaries = await context.OrderSummaries
    .Where(s => s.TotalAmount > 1000)
    .ToListAsync();

Create the view in a migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        CREATE VIEW vw_OrderSummaries AS
        SELECT 
            c.Id AS CustomerId,
            c.Name AS CustomerName,
            COUNT(o.Id) AS OrderCount,
            SUM(o.Total) AS TotalAmount
        FROM Customers c
        LEFT JOIN Orders o ON c.Id = o.CustomerId
        GROUP BY c.Id, c.Name
    ");
}

Summary

Advanced EF Core features enable sophisticated scenarios:

1. Value converters transform data between .NET and database representations
2. Owned types model value objects that belong to parent entities
3. Query filters apply automatic filtering for soft delete, multi-tenancy
4. Interceptors hook into EF Core operations for auditing, logging, modification
5. Shadow properties track metadata without cluttering entity classes
6. Table/entity splitting maps entities to tables in flexible ways
7. Custom functions expose database capabilities to LINQ queries

The final section covers testing strategies for applications using EF Core with WitDatabase.

Testing

Testing applications that use databases presents unique challenges. Tests need to be fast, isolated, and repeatable. You don't want tests failing because of leftover data from previous runs, and you don't want tests taking minutes because they're hitting a real database over the network.

WitDatabase's in-memory mode and file-based embedded nature make it excellent for testing. This section covers strategies for unit testing, integration testing, and testing best practices with EF Core.

Testing Strategies Overview

There are several approaches to testing code that uses EF Core, each with different tradeoffs.

In-memory database testing uses WitDatabase's :memory: mode. Each test gets a fresh, empty database that exists only in RAM. Tests are fast and completely isolated. The database behaves exactly like a real WitDatabase instance because it is one — just stored in memory.

File-based test database creates a temporary database file for each test or test class. This tests the full I/O path and is useful for scenarios where file-based behavior matters, like testing backup/restore or file locking.

Mocking the DbContext replaces EF Core entirely with mock objects. This tests your code's logic without any database involvement. It's fast but doesn't verify that your LINQ queries translate correctly to SQL.

Shared test database uses a single database populated with known test data. Multiple tests read from it, and tests that modify data reset it afterward. This is slower but tests against realistic data volumes.

For most applications, in-memory testing provides the best balance of speed, isolation, and realism. It tests actual EF Core behavior — your LINQ queries are translated and executed against a real database engine.

Setting Up Test Infrastructure

A well-designed test infrastructure makes writing tests easy and keeps them maintainable.

Base test class with database setup:

using Microsoft.EntityFrameworkCore;
using OutWit.Database.EntityFramework;
using Xunit;

public abstract class DatabaseTestBase : IDisposable
{
    protected AppDbContext Context { get; }
    
    protected DatabaseTestBase()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseWitDb("Data Source=:memory:")
            .Options;
        
        Context = new AppDbContext(options);
        Context.Database.EnsureCreated();
        
        // Optional: seed common test data
        SeedTestData();
    }
    
    protected virtual void SeedTestData()
    {
        // Override in derived classes if needed
    }
    
    public void Dispose()
    {
        Context.Dispose();
    }
}

Tests inherit from this base class and get a fresh database automatically:

public class UserServiceTests : DatabaseTestBase
{
    private readonly UserService _userService;
    
    public UserServiceTests()
    {
        _userService = new UserService(Context);
    }
    
    [Fact]
    public async Task CreateUser_AssignsId()
    {
        // Arrange
        var request = new CreateUserRequest("Alice", "alice@test.com");
        
        // Act
        var user = await _userService.CreateUserAsync(request);
        
        // Assert
        Assert.True(user.Id > 0);
        Assert.Equal("Alice", user.Name);
    }
}

Factory method for test contexts:

For tests that need multiple contexts or finer control:

public static class TestDbContextFactory
{
    public static AppDbContext Create()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseWitDb("Data Source=:memory:")
            .Options;
        
        var context = new AppDbContext(options);
        context.Database.EnsureCreated();
        return context;
    }
    
    public static AppDbContext CreateWithData(Action<AppDbContext> seedAction)
    {
        var context = Create();
        seedAction(context);
        context.SaveChanges();
        return context;
    }
}

// Usage
[Fact]
public async Task GetUserById_ReturnsCorrectUser()
{
    using var context = TestDbContextFactory.CreateWithData(ctx =>
    {
        ctx.Users.Add(new User { Id = 1, Name = "Test User" });
    });
    
    var user = await context.Users.FindAsync(1);
    
    Assert.NotNull(user);
    Assert.Equal("Test User", user.Name);
}

Unit Testing with In-Memory Database

Unit tests verify individual components in isolation. With EF Core, "isolation" typically means a fresh database for each test.

Testing repository methods:

public class UserRepositoryTests : DatabaseTestBase
{
    private readonly UserRepository _repository;
    
    public UserRepositoryTests()
    {
        _repository = new UserRepository(Context);
    }
    
    [Fact]
    public async Task GetByEmail_ExistingUser_ReturnsUser()
    {
        // Arrange
        var user = new User 
        { 
            Name = "Alice", 
            Email = "alice@test.com" 
        };
        Context.Users.Add(user);
        await Context.SaveChangesAsync();
        
        // Act
        var result = await _repository.GetByEmailAsync("alice@test.com");
        
        // Assert
        Assert.NotNull(result);
        Assert.Equal("Alice", result.Name);
    }
    
    [Fact]
    public async Task GetByEmail_NonExistentUser_ReturnsNull()
    {
        // Act
        var result = await _repository.GetByEmailAsync("nobody@test.com");
        
        // Assert
        Assert.Null(result);
    }
    
    [Fact]
    public async Task GetActiveUsers_ReturnsOnlyActiveUsers()
    {
        // Arrange
        Context.Users.AddRange(
            new User { Name = "Active1", IsActive = true },
            new User { Name = "Active2", IsActive = true },
            new User { Name = "Inactive", IsActive = false }
        );
        await Context.SaveChangesAsync();
        
        // Act
        var activeUsers = await _repository.GetActiveUsersAsync();
        
        // Assert
        Assert.Equal(2, activeUsers.Count);
        Assert.All(activeUsers, u => Assert.True(u.IsActive));
    }
}

Testing service layer:

public class OrderServiceTests : DatabaseTestBase
{
    private readonly OrderService _orderService;
    
    public OrderServiceTests()
    {
        _orderService = new OrderService(Context);
    }
    
    protected override void SeedTestData()
    {
        Context.Products.AddRange(
            new Product { Id = 1, Name = "Widget", Price = 9.99m, Stock = 100 },
            new Product { Id = 2, Name = "Gadget", Price = 19.99m, Stock = 50 }
        );
        
        Context.Customers.Add(
            new Customer { Id = 1, Name = "Test Customer", Email = "test@example.com" }
        );
        
        Context.SaveChanges();
    }
    
    [Fact]
    public async Task PlaceOrder_ValidOrder_CreatesOrderAndUpdatesStock()
    {
        // Arrange
        var request = new PlaceOrderRequest
        {
            CustomerId = 1,
            Items = new[]
            {
                new OrderItemRequest { ProductId = 1, Quantity = 2 },
                new OrderItemRequest { ProductId = 2, Quantity = 1 }
            }
        };
        
        // Act
        var order = await _orderService.PlaceOrderAsync(request);
        
        // Assert
        Assert.NotNull(order);
        Assert.Equal(2, order.Items.Count);
        Assert.Equal(39.97m, order.Total);  // 2*9.99 + 1*19.99
        
        // Verify stock was reduced
        var widget = await Context.Products.FindAsync(1);
        Assert.Equal(98, widget!.Stock);
    }
    
    [Fact]
    public async Task PlaceOrder_InsufficientStock_ThrowsException()
    {
        // Arrange
        var request = new PlaceOrderRequest
        {
            CustomerId = 1,
            Items = new[]
            {
                new OrderItemRequest { ProductId = 1, Quantity = 999 }
            }
        };
        
        // Act & Assert
        await Assert.ThrowsAsync<InsufficientStockException>(
            () => _orderService.PlaceOrderAsync(request));
    }
}

Testing with Query Filters

Global query filters require special attention in tests. By default, filters apply even in tests, which might not be what you want.

Testing with filters active:

[Fact]
public async Task GetProducts_ExcludesDeletedProducts()
{
    // Arrange
    Context.Products.AddRange(
        new Product { Name = "Active", IsDeleted = false },
        new Product { Name = "Deleted", IsDeleted = true }
    );
    await Context.SaveChangesAsync();
    
    // Act
    var products = await Context.Products.ToListAsync();
    
    // Assert
    Assert.Single(products);
    Assert.Equal("Active", products[0].Name);
}

Testing filter behavior explicitly:

[Fact]
public async Task IgnoreQueryFilters_ReturnsDeletedProducts()
{
    // Arrange
    Context.Products.AddRange(
        new Product { Name = "Active", IsDeleted = false },
        new Product { Name = "Deleted", IsDeleted = true }
    );
    await Context.SaveChangesAsync();
    
    // Act
    var allProducts = await Context.Products
        .IgnoreQueryFilters()
        .ToListAsync();
    
    // Assert
    Assert.Equal(2, allProducts.Count);
}

Testing Transactions

Test that your transaction logic behaves correctly under various scenarios.

Testing successful transactions:

[Fact]
public async Task TransferFunds_Success_UpdatesBothAccounts()
{
    // Arrange
    Context.Accounts.AddRange(
        new Account { Id = 1, Balance = 1000 },
        new Account { Id = 2, Balance = 500 }
    );
    await Context.SaveChangesAsync();
    
    var service = new AccountService(Context);
    
    // Act
    await service.TransferAsync(fromId: 1, toId: 2, amount: 200);
    
    // Assert
    var account1 = await Context.Accounts.FindAsync(1);
    var account2 = await Context.Accounts.FindAsync(2);
    
    Assert.Equal(800, account1!.Balance);
    Assert.Equal(700, account2!.Balance);
}

[Fact]
public async Task TransferFunds_Failure_RollsBackBothAccounts()
{
    // Arrange
    Context.Accounts.AddRange(
        new Account { Id = 1, Balance = 100 },
        new Account { Id = 2, Balance = 500 }
    );
    await Context.SaveChangesAsync();
    
    var service = new AccountService(Context);
    
    // Act & Assert
    await Assert.ThrowsAsync<InsufficientFundsException>(
        () => service.TransferAsync(fromId: 1, toId: 2, amount: 200));
    
    // Verify rollback - balances unchanged
    var account1 = await Context.Accounts.FindAsync(1);
    var account2 = await Context.Accounts.FindAsync(2);
    
    Assert.Equal(100, account1!.Balance);
    Assert.Equal(500, account2!.Balance);
}

Testing Concurrency

Concurrency tests verify that optimistic locking works correctly.

[Fact]
public async Task UpdateProduct_ConcurrencyConflict_ThrowsException()
{
    // Arrange
    Context.Products.Add(new Product 
    { 
        Id = 1, 
        Name = "Original", 
        Version = 1 
    });
    await Context.SaveChangesAsync();
    
    // Simulate two users loading the same product
    var options = new DbContextOptionsBuilder<AppDbContext>()
        .UseWitDb("Data Source=:memory:")
        .Options;
    
    // Note: For true concurrency testing, we need a shared database
    // In-memory databases are isolated per connection
    // Use a file-based database for this test:
    
    var dbPath = Path.GetTempFileName();
    try
    {
        var options1 = new DbContextOptionsBuilder<AppDbContext>()
            .UseWitDb(

        
        

            
            Loading...
        
quot;Data Source={dbPath}")
            .Options;
        
        using var context1 = new AppDbContext(options1);
        context1.Database.EnsureCreated();
        context1.Products.Add(new Product { Id = 1, Name = "Original", Version = 1 });
        await context1.SaveChangesAsync();
        
        // User 1 loads product
        var product1 = await context1.Products.FindAsync(1);
        
        // User 2 loads and modifies product (different context, same database)
        using var context2 = new AppDbContext(options1);
        var product2 = await context2.Products.FindAsync(1);
        product2!.Name = "User2Change";
        await context2.SaveChangesAsync();
        
        // User 1 tries to save their change
        product1!.Name = "User1Change";
        
        // Act & Assert
        await Assert.ThrowsAsync<DbUpdateConcurrencyException>(
            () => context1.SaveChangesAsync());
    }
    finally
    {
        File.Delete(dbPath);
    }
}

Integration Testing

Integration tests verify that multiple components work together correctly. They typically use a real database (file-based) and test complete workflows.

Test fixture with shared database:

public class IntegrationTestFixture : IDisposable
{
    public string DatabasePath { get; }
    public IServiceProvider ServiceProvider { get; }
    
    public IntegrationTestFixture()
    {
        DatabasePath = Path.Combine(Path.GetTempPath(), 

        
        

            
            Loading...
        
quot;test_{Guid.NewGuid()}.witdb");
        
        var services = new ServiceCollection();
        
        services.AddDbContext<AppDbContext>(options =>
            options.UseWitDb(

        
        

            
            Loading...
        
quot;Data Source={DatabasePath}"));
        
        services.AddScoped<UserService>();
        services.AddScoped<OrderService>();
        // ... other services
        
        ServiceProvider = services.BuildServiceProvider();
        
        // Initialize database
        using var scope = ServiceProvider.CreateScope();
        var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        context.Database.Migrate();
    }
    
    public void Dispose()
    {
        if (File.Exists(DatabasePath))
            File.Delete(DatabasePath);
    }
}

public class OrderWorkflowTests : IClassFixture<IntegrationTestFixture>
{
    private readonly IntegrationTestFixture _fixture;
    
    public OrderWorkflowTests(IntegrationTestFixture fixture)
    {
        _fixture = fixture;
    }
    
    [Fact]
    public async Task CompleteOrderWorkflow_Success()
    {
        using var scope = _fixture.ServiceProvider.CreateScope();
        var userService = scope.ServiceProvider.GetRequiredService<UserService>();
        var orderService = scope.ServiceProvider.GetRequiredService<OrderService>();
        
        // Create customer
        var customer = await userService.CreateCustomerAsync(
            new CreateCustomerRequest("Test Customer", "test@example.com"));
        
        // Place order
        var order = await orderService.PlaceOrderAsync(
            new PlaceOrderRequest
            {
                CustomerId = customer.Id,
                Items = new[] { new OrderItemRequest { ProductId = 1, Quantity = 2 } }
            });
        
        // Process order
        await orderService.ProcessOrderAsync(order.Id);
        
        // Ship order
        await orderService.ShipOrderAsync(order.Id, "TRACK123");
        
        // Verify final state
        var finalOrder = await orderService.GetOrderAsync(order.Id);
        Assert.Equal(OrderStatus.Shipped, finalOrder.Status);
        Assert.Equal("TRACK123", finalOrder.TrackingNumber);
    }
}

Testing Best Practices

Follow these practices for maintainable, reliable tests.

Keep tests independent. Each test should be able to run in isolation, regardless of what other tests do or the order they run in. Use fresh databases or clean up after each test.

Use meaningful test data. Instead of "Test1", "Test2", use names that indicate purpose: "ActiveCustomerWithOrders", "CustomerWithNoOrders". This makes test failures easier to diagnose.

// Harder to understand
Context.Users.Add(new User { Id = 1, Name = "User1" });
Context.Users.Add(new User { Id = 2, Name = "User2" });

// Clearer intent
Context.Users.Add(new User { Id = 1, Name = "ActiveUser", IsActive = true });
Context.Users.Add(new User { Id = 2, Name = "InactiveUser", IsActive = false });

Test edge cases explicitly. Don't just test the happy path. Test empty collections, null values, boundary conditions, and error scenarios.

[Theory]
[InlineData(0)]
[InlineData(-1)]
[InlineData(int.MaxValue)]
public async Task GetUser_InvalidId_ReturnsNull(int invalidId)
{
    var user = await _repository.GetByIdAsync(invalidId);
    Assert.Null(user);
}

Use test data builders for complex entities:

public class UserBuilder
{
    private int _id;
    private string _name = "Test User";
    private string _email = "test@example.com";
    private bool _isActive = true;
    
    public UserBuilder WithId(int id) { _id = id; return this; }
    public UserBuilder WithName(string name) { _name = name; return this; }
    public UserBuilder WithEmail(string email) { _email = email; return this; }
    public UserBuilder Inactive() { _isActive = false; return this; }
    
    public User Build() => new User
    {
        Id = _id,
        Name = _name,
        Email = _email,
        IsActive = _isActive
    };
}

// Usage
var user = new UserBuilder()
    .WithName("Alice")
    .WithEmail("alice@test.com")
    .Inactive()
    .Build();

Verify database state, not just return values:

[Fact]
public async Task DeleteUser_RemovesFromDatabase()
{
    // Arrange
    var user = new User { Name = "ToDelete" };
    Context.Users.Add(user);
    await Context.SaveChangesAsync();
    var userId = user.Id;
    
    // Act
    await _service.DeleteUserAsync(userId);
    
    // Assert - check the database directly
    var deletedUser = await Context.Users
        .IgnoreQueryFilters()  // In case of soft delete
        .FirstOrDefaultAsync(u => u.Id == userId);
    
    // For hard delete:
    Assert.Null(deletedUser);
    
    // For soft delete:
    // Assert.True(deletedUser.IsDeleted);
}

Use async test methods for async code:

// Correct
[Fact]
public async Task MyTest()
{
    var result = await _service.DoSomethingAsync();
    Assert.NotNull(result);
}

// Avoid - can hide exceptions and timing issues
[Fact]
public void MyTest()
{
    var result = _service.DoSomethingAsync().Result;
    Assert.NotNull(result);
}

Summary

Testing EF Core applications with WitDatabase provides:

1. In-memory databases for fast, isolated unit tests
2. File-based databases for integration tests requiring persistence
3. Fresh database per test ensuring test independence
4. Real EF Core behavior — your LINQ queries are actually translated and executed
5. Support for testing transactions, concurrency, query filters, and complex workflows

With testing strategies in place, you have all the knowledge needed to build robust applications with EF Core and WitDatabase. The final section provides a quick reference for commonly needed information.

Quick Reference

This section provides a condensed reference for common EF Core operations with WitDatabase. Use it as a cheat sheet when you know what you want to do but need a quick syntax reminder.

Setup

Package installation:

dotnet add package OutWit.Database.EntityFramework

Basic DbContext:

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }
    
    public DbSet<User> Users => Set<User>();
    public DbSet<Order> Orders => Set<Order>();
}

Registration (ASP.NET Core):

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseWitDb("Data Source=app.witdb"));

Registration with pooling:

builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseWitDb("Data Source=app.witdb"), poolSize: 128);

Connection Strings

Entity Configuration

Data Annotations:

[Table("Products")]
public class Product
{
    [Key]
    public int Id { get; set; }
    
    [Required, MaxLength(200)]
    public string Name { get; set; }
    
    [Column(TypeName = "DECIMAL(10,2)")]
    public decimal Price { get; set; }
    
    [NotMapped]
    public decimal PriceWithTax => Price * 1.2m;
}

Fluent API:

modelBuilder.Entity<Product>(entity =>
{
    entity.ToTable("Products");
    entity.HasKey(p => p.Id);
    entity.Property(p => p.Name).IsRequired().HasMaxLength(200);
    entity.Property(p => p.Price).HasColumnType("DECIMAL(10,2)");
    entity.Ignore(p => p.PriceWithTax);
    entity.HasIndex(p => p.Name);
});

Relationships

One-to-Many:

// Entities
public class Customer
{
    public int Id { get; set; }
    public ICollection<Order> Orders { get; set; } = new List<Order>();
}

public class Order
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; } = null!;
}

// Configuration
modelBuilder.Entity<Order>()
    .HasOne(o => o.Customer)
    .WithMany(c => c.Orders)
    .HasForeignKey(o => o.CustomerId);

Many-to-Many:

public class Product
{
    public int Id { get; set; }
    public ICollection<Category> Categories { get; set; } = new List<Category>();
}

public class Category
{
    public int Id { get; set; }
    public ICollection<Product> Products { get; set; } = new List<Product>();
}
// EF Core auto-creates join table

One-to-One:

modelBuilder.Entity<User>()
    .HasOne(u => u.Profile)
    .WithOne(p => p.User)
    .HasForeignKey<UserProfile>(p => p.UserId);

Migrations

In code:

context.Database.Migrate();        // Apply migrations
context.Database.EnsureCreated();  // Create without migrations
context.Database.EnsureDeleted();  // Delete database

Querying

Basic queries:

// All
var all = await context.Users.ToListAsync();

// Filtered
var active = await context.Users.Where(u => u.IsActive).ToListAsync();

// Sorted
var sorted = await context.Users.OrderBy(u => u.Name).ToListAsync();

// Paginated
var page = await context.Users.Skip(20).Take(10).ToListAsync();

// Single
var user = await context.Users.FindAsync(id);
var user = await context.Users.FirstOrDefaultAsync(u => u.Email == email);
var user = await context.Users.SingleAsync(u => u.Id == id);

Projection:

var dtos = await context.Users
    .Select(u => new { u.Id, u.Name, u.Email })
    .ToListAsync();

Related data:

// Eager loading
var orders = await context.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .ToListAsync();

// Explicit loading
await context.Entry(order).Collection(o => o.Items).LoadAsync();

// Split query
var orders = await context.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

Aggregations:

var count = await context.Users.CountAsync();
var activeCount = await context.Users.CountAsync(u => u.IsActive);
var total = await context.Orders.SumAsync(o => o.Total);
var avg = await context.Products.AverageAsync(p => p.Price);

var grouped = await context.Orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new { CustomerId = g.Key, Total = g.Sum(o => o.Total) })
    .ToListAsync();

No-tracking:

var users = await context.Users.AsNoTracking().ToListAsync();

Raw SQL:

var users = await context.Users
    .FromSqlInterpolated(

        
        

            
            Loading...
        
quot;SELECT * FROM Users WHERE Status = {status}")
    .ToListAsync();

await context.Database.ExecuteSqlInterpolatedAsync(
    

        
        

            
            Loading...
        
quot;UPDATE Users SET IsActive = 0 WHERE LastLogin < {cutoff}");

Saving Data

Insert:

context.Users.Add(new User { Name = "Alice" });
context.Users.AddRange(users);
await context.SaveChangesAsync();

Update:

var user = await context.Users.FindAsync(id);
user.Name = "Updated";
await context.SaveChangesAsync();

Delete:

context.Users.Remove(user);
await context.SaveChangesAsync();

// Without loading
context.Users.Remove(new User { Id = id });
await context.SaveChangesAsync();

Bulk operations (EF Core 7+):

await context.Users
    .Where(u => u.IsActive == false)
    .ExecuteDeleteAsync();

await context.Products
    .Where(p => p.Category == "Sale")
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 0.9m));

Transactions

using var transaction = await context.Database.BeginTransactionAsync();
try
{
    // Operations...
    await context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Change Tracking

Entity states:

Inspecting:

var state = context.Entry(entity).State;
var isModified = context.Entry(entity).Property(e => e.Name).IsModified;
bool hasChanges = context.ChangeTracker.HasChanges();

Manipulating:

context.Entry(entity).State = EntityState.Modified;
context.ChangeTracker.Clear();
await context.Entry(entity).ReloadAsync();

Common Patterns

Repository pattern:

public class Repository<T> where T : class
{
    private readonly DbContext _context;
    public Repository(DbContext context) => _context = context;
    
    public Task<T?> GetByIdAsync(int id) => _context.Set<T>().FindAsync(id).AsTask();
    public Task<List<T>> GetAllAsync() => _context.Set<T>().ToListAsync();
    public void Add(T entity) => _context.Set<T>().Add(entity);
    public void Remove(T entity) => _context.Set<T>().Remove(entity);
    public Task SaveAsync() => _context.SaveChangesAsync();
}

Soft delete filter:

modelBuilder.Entity<Product>()
    .HasQueryFilter(p => !p.IsDeleted);

// Bypass when needed
context.Products.IgnoreQueryFilters().ToList();

Audit interceptor:

public class AuditInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData, InterceptionResult<int> result, 
        CancellationToken ct = default)
    {
        foreach (var entry in eventData.Context!.ChangeTracker.Entries<IAuditable>())
        {
            if (entry.State == EntityState.Added)
                entry.Entity.CreatedAt = DateTime.UtcNow;
            if (entry.State == EntityState.Modified)
                entry.Entity.ModifiedAt = DateTime.UtcNow;
        }
        return ValueTask.FromResult(result);
    }
}

Value converter:

modelBuilder.Entity<Order>()
    .Property(o => o.Status)
    .HasConversion<string>();  // Enum to string

modelBuilder.Entity<User>()
    .Property(u => u.Settings)
    .HasConversion(
        v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
        v => JsonSerializer.Deserialize<UserSettings>(v, (JsonSerializerOptions?)null)!);

Testing Setup

public class TestBase : IDisposable
{
    protected AppDbContext Context { get; }
    
    public TestBase()
    {
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseWitDb("Data Source=:memory:")
            .Options;
        Context = new AppDbContext(options);
        Context.Database.EnsureCreated();
    }
    
    public void Dispose() => Context.Dispose();
}

Type Mapping Reference

Troubleshooting