Advanced EF Core: Migrations, Performance, and Raw SQL

13 min read•March 9, 2026
EF Core advancedEF Core migrationsEF Core performanceEF Core raw SQLEF Core interceptorsEF Core compiled queriesEF Core bulk operationsEF Core concurrency

# Advanced Entity Framework Core: Migrations, Performance, and Raw SQL

Entity Framework Core covers the basics well, but production systems demand more. Once your application grows past a handful of tables and a few hundred requests per second, you need advanced migration strategies, deliberate performance tuning, and the confidence to drop into raw SQL when LINQ falls short. In high-volume data systems I have worked on, the gap between a naive EF Core setup and a properly tuned one was often the difference between 50ms and 500ms response times under load.

Advanced Migration Strategies

Migrations are deployment artifacts. Treat them with the same rigor you treat application code.

Data Seeding in Migrations

EF Core's `HasData` is convenient for reference data, but it has limitations -- it only works with constant values and regenerates the full seed on every migration. For dynamic seeding, use raw SQL inside migrations:

csharp
public partial class SeedOrderStatuses : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: class="code-string">"OrderStatuses",
            columns: new[] { class="code-string">"Id", class="code-string">"Name", class="code-string">"Description" },
            values: new object[,]
            {
                { class="code-number">1, class="code-string">"Pending", class="code-string">"Order has been placed" },
                { class="code-number">2, class="code-string">"Processing", class="code-string">"Order is being prepared" },
                { class="code-number">3, class="code-string">"Shipped", class="code-string">"Order has left the warehouse" },
                { class="code-number">4, class="code-string">"Delivered", class="code-string">"Order has been received" },
                { class="code-number">5, class="code-string">"Cancelled", class="code-string">"Order was cancelled" }
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: class="code-string">"OrderStatuses",
            keyColumn: class="code-string">"Id",
            keyValues: new object[] { class="code-number">1, class="code-number">2, class="code-number">3, class="code-number">4, class="code-number">5 });
    }
}

For large seed datasets or complex transforms, keep data seeding in a separate migration from schema changes. This makes rollbacks predictable.

Idempotent Migrations

Production deployments should always use idempotent scripts. If a deployment fails midway, re-running the script must be safe:

bash
dotnet ef migrations script --idempotent -o migrate.sql

The generated script wraps each migration in an existence check against the `__EFMigrationsHistory` table. I always generate and review these scripts before applying them to staging or production -- never run `dotnet ef database update` directly against a production database.

Production Rollback Patterns

Every migration needs a working `Down` method. But beyond that, I follow a specific pattern for high-risk schema changes:

csharp
public partial class RenameCustomerEmailColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        class=class="code-string">"code-comment">// Step class="code-number">1: Add new column
        migrationBuilder.AddColumn<string>(
            name: class="code-string">"EmailAddress",
            table: class="code-string">"Customers",
            type: class="code-string">"nvarchar(class="code-number">256)",
            nullable: true);

        class=class="code-string">"code-comment">// Step class="code-number">2: Copy data
        migrationBuilder.Sql(
            class="code-string">"UPDATE Customers SET EmailAddress = Email");

        class=class="code-string">"code-comment">// Step class="code-number">3: Make non-nullable after data copy
        migrationBuilder.AlterColumn<string>(
            name: class="code-string">"EmailAddress",
            table: class="code-string">"Customers",
            type: class="code-string">"nvarchar(class="code-number">256)",
            nullable: false,
            defaultValue: class="code-string">"");

        class=class="code-string">"code-comment">// Step class="code-number">4: Drop old column only after verification
        class=class="code-string">"code-comment">// This goes in a SEPARATE migration applied after deployment validation
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: class="code-string">"EmailAddress",
            table: class="code-string">"Customers");
    }
}

Never rename a column in a single migration. The expand-contract pattern -- add new, copy, verify, drop old -- prevents data loss and allows zero-downtime deployments.

Performance Deep Dive

Compiled Queries

For queries that execute thousands of times per minute, the expression tree compilation overhead adds up. Compiled queries pay that cost once:

csharp
public class ProductRepository
{
    private static readonly Func<AppDbContext, int, CancellationToken, Task<Product?>>
        GetByIdQuery = EF.CompileAsyncQuery(
            (AppDbContext ctx, int id, CancellationToken ct) =>
                ctx.Products.FirstOrDefault(p => p.Id == id));

    private static readonly Func<AppDbContext, string, CancellationToken, Task<List<Product>>>
        GetByCategoryQuery = EF.CompileAsyncQuery(
            (AppDbContext ctx, string category, CancellationToken ct) =>
                ctx.Products
                    .Where(p => p.Category == category && p.IsActive)
                    .OrderBy(p => p.Name)
                    .ToList());

    private readonly AppDbContext _context;

    public ProductRepository(AppDbContext context) => _context = context;

    public Task<Product?> GetByIdAsync(int id, CancellationToken ct)
        => GetByIdQuery(_context, id, ct);

    public Task<List<Product>> GetByCategoryAsync(string category, CancellationToken ct)
        => GetByCategoryQuery(_context, category, ct);
}

In high-volume data systems I have profiled, compiled queries on the top 10 most-hit endpoints reduced average query overhead by 3-4ms each. That is modest per call, but at 10,000 RPM it translates to meaningful CPU savings.

Split Queries

When a query includes multiple collection navigations, EF Core generates a single SQL query with JOINs that can produce a cartesian explosion. A parent with 10 items and 5 tags produces 50 rows instead of 15:

csharp
class=class="code-string">"code-comment">// Single query: cartesian explosion risk
var orders = await _context.Orders
    .Include(o => o.OrderItems)
    .Include(o => o.Tags)
    .Include(o => o.AuditEntries)
    .ToListAsync(ct);

class=class="code-string">"code-comment">// Split query: multiple roundtrips but no data duplication
var orders = await _context.Orders
    .Include(o => o.OrderItems)
    .Include(o => o.Tags)
    .Include(o => o.AuditEntries)
    .AsSplitQuery()
    .ToListAsync(ct);

Split queries trade a single large result set for multiple smaller ones. The total bytes transferred is often dramatically lower. I default to split queries when including more than one collection navigation.

Bulk Operations with ExecuteUpdate and ExecuteDelete

EF Core 7+ introduced set-based operations that translate directly to SQL without loading entities:

csharp
class=class="code-string">"code-comment">// Archive all orders older than class="code-number">2 years -- single UPDATE statement
await _context.Orders
    .Where(o => o.CreatedAt < DateTime.UtcNow.AddYears(-class="code-number">2))
    .ExecuteUpdateAsync(s => s
        .SetProperty(o => o.IsArchived, true)
        .SetProperty(o => o.ArchivedAt, DateTime.UtcNow), ct);

class=class="code-string">"code-comment">// Purge archived audit logs -- single DELETE statement
await _context.AuditLogs
    .Where(a => a.IsArchived && a.CreatedAt < DateTime.UtcNow.AddYears(-class="code-number">5))
    .ExecuteDeleteAsync(ct);

class=class="code-string">"code-comment">// Batch price increase for a category
await _context.Products
    .Where(p => p.Category == class="code-string">"Electronics" && p.IsActive)
    .ExecuteUpdateAsync(s => s
        .SetProperty(p => p.Price, p => p.Price * class="code-number">1.1m)
        .SetProperty(p => p.LastModified, DateTime.UtcNow), ct);

These bypass the change tracker entirely. No entities are materialized, no memory is consumed. For bulk operations on tens of thousands of rows, this is orders of magnitude faster than loading and calling `SaveChanges`.

Raw SQL and SqlQuery

LINQ is excellent for 90% of queries. For the remaining 10%, raw SQL is not a compromise -- it is the right tool.

FromSqlRaw for Entity Queries

csharp
var topProducts = await _context.Products
    .FromSqlRaw(@class="code-string">"
        SELECT p.*
        FROM Products p
        INNER JOIN OrderItems oi ON p.Id = oi.ProductId
        GROUP BY p.Id, p.Name, p.Price, p.Category, p.IsActive,
                 p.CreatedAt, p.LastModified, p.IsDeleted
        HAVING COUNT(oi.Id) > {class="code-number">0}
        ORDER BY COUNT(oi.Id) DESC", minimumOrders)
    .AsNoTracking()
    .ToListAsync(ct);

`FromSqlRaw` returns tracked entities by default. You can chain LINQ operators on top of it -- EF Core wraps your SQL as a subquery.

SqlQuery for Arbitrary Result Sets

EF Core 8 introduced `SqlQuery` for projecting into arbitrary types:

csharp
var salesReport = await _context.Database
    .SqlQuery<MonthlySalesDto>($@class="code-string">"
        SELECT
            DATEPART(YEAR, o.CreatedAt) AS Year,
            DATEPART(MONTH, o.CreatedAt) AS Month,
            COUNT(*) AS OrderCount,
            SUM(o.TotalAmount) AS TotalRevenue,
            AVG(o.TotalAmount) AS AverageOrderValue
        FROM Orders o
        WHERE o.CreatedAt >= {startDate}
          AND o.IsDeleted = class="code-number">0
        GROUP BY DATEPART(YEAR, o.CreatedAt), DATEPART(MONTH, o.CreatedAt)
        ORDER BY Year DESC, Month DESC")
    .ToListAsync(ct);

This is invaluable for reporting queries, complex aggregations, and any scenario where mapping to your entity model would be forced or wasteful.

ExecuteSqlRaw for DDL and Non-Query Operations

csharp
class=class="code-string">"code-comment">// Create a filtered index that EF Core migrations cannot express
await _context.Database.ExecuteSqlRawAsync(@class="code-string">"
    CREATE INDEX IX_Orders_ActivePending
    ON Orders (CustomerId, CreatedAt)
    WHERE IsDeleted = class="code-number">0 AND Status = class="code-string">'Pending'", ct);

Interceptors and Query Filters

Global Query Filters

Query filters apply automatically to every query against a given entity. They are the foundation of multi-tenancy and soft delete:

csharp
public class AppDbContext : DbContext
{
    private readonly ITenantProvider _tenantProvider;

    public AppDbContext(
        DbContextOptions<AppDbContext> options,
        ITenantProvider tenantProvider)
        : base(options)
    {
        _tenantProvider = tenantProvider;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        class=class="code-string">"code-comment">// Soft delete filter
        modelBuilder.Entity<Order>()
            .HasQueryFilter(o => !o.IsDeleted);

        class=class="code-string">"code-comment">// Multi-tenant filter
        modelBuilder.Entity<Order>()
            .HasQueryFilter(o => !o.IsDeleted
                && o.TenantId == _tenantProvider.CurrentTenantId);
    }
}

SaveChanges Interceptors

Interceptors let you hook into the persistence pipeline without polluting your domain logic:

csharp
public class AuditInterceptor : SaveChangesInterceptor
{
    private readonly ICurrentUserService _currentUser;

    public AuditInterceptor(ICurrentUserService currentUser)
        => _currentUser = currentUser;

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken ct = default)
    {
        var context = eventData.Context!;

        foreach (var entry in context.ChangeTracker
            .Entries<IAuditable>())
        {
            switch (entry.State)
            {
                case EntityState.Added:
                    entry.Entity.CreatedAt = DateTime.UtcNow;
                    entry.Entity.CreatedBy = _currentUser.UserId;
                    break;
                case EntityState.Modified:
                    entry.Entity.ModifiedAt = DateTime.UtcNow;
                    entry.Entity.ModifiedBy = _currentUser.UserId;
                    break;
            }
        }

        return base.SavingChangesAsync(eventData, result, ct);
    }
}

class=class="code-string">"code-comment">// Registration
builder.Services.AddDbContext<AppDbContext>((sp, options) =>
    options.UseSqlServer(connectionString)
           .AddInterceptors(sp.GetRequiredService<AuditInterceptor>()));

This pattern keeps audit concerns completely separate from business logic. Every entity implementing `IAuditable` gets timestamps and user tracking automatically.

Value Converters and Owned Types

Value Converters

Value converters transform data between your domain model and the database. They are essential for storing enums, strongly-typed IDs, and complex value types:

csharp
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        class=class="code-string">"code-comment">// Store enum as string for readability
        builder.Property(o => o.Status)
            .HasConversion<string>()
            .HasMaxLength(class="code-number">30);

        class=class="code-string">"code-comment">// Custom converter for strongly-typed ID
        builder.Property(o => o.Id)
            .HasConversion(
                id => id.Value,
                value => new OrderId(value));

        class=class="code-string">"code-comment">// JSON converter for metadata
        builder.Property(o => o.Metadata)
            .HasConversion(
                v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
                v => JsonSerializer.Deserialize<Dictionary<string, string>>(
                    v, (JsonSerializerOptions?)null)!)
            .HasColumnType(class="code-string">"nvarchar(max)");
    }
}

Owned Types for Value Objects

Owned types map rich domain objects to columns in the parent table without creating separate tables:

csharp
builder.OwnsOne(o => o.ShippingAddress, address =>
{
    address.Property(a => a.Street).HasMaxLength(class="code-number">200).HasColumnName(class="code-string">"Ship_Street");
    address.Property(a => a.City).HasMaxLength(class="code-number">100).HasColumnName(class="code-string">"Ship_City");
    address.Property(a => a.PostalCode).HasMaxLength(class="code-number">20).HasColumnName(class="code-string">"Ship_PostalCode");
    address.Property(a => a.Country).HasMaxLength(class="code-number">60).HasColumnName(class="code-string">"Ship_Country");
});

builder.OwnsOne(o => o.BillingAddress, address =>
{
    address.Property(a => a.Street).HasMaxLength(class="code-number">200).HasColumnName(class="code-string">"Bill_Street");
    address.Property(a => a.City).HasMaxLength(class="code-number">100).HasColumnName(class="code-string">"Bill_City");
    address.Property(a => a.PostalCode).HasMaxLength(class="code-number">20).HasColumnName(class="code-string">"Bill_PostalCode");
    address.Property(a => a.Country).HasMaxLength(class="code-number">60).HasColumnName(class="code-string">"Bill_Country");
});

This keeps your domain model clean while avoiding unnecessary joins. The `Address` value object lives as columns inside the `Orders` table.

Temporal Tables and Soft Delete

SQL Server Temporal Tables

EF Core 6+ has built-in support for SQL Server temporal tables, which automatically track the full history of every row:

csharp
builder.Entity<Product>(b =>
{
    b.ToTable(class="code-string">"Products", tb => tb.IsTemporal(ttb =>
    {
        ttb.HasPeriodStart(class="code-string">"ValidFrom");
        ttb.HasPeriodEnd(class="code-string">"ValidTo");
        ttb.UseHistoryTable(class="code-string">"ProductsHistory");
    }));
});

Querying historical data:

csharp
class=class="code-string">"code-comment">// Get product state at a specific point in time
var productSnapshot = await _context.Products
    .TemporalAsOf(new DateTime(class="code-number">2025, class="code-number">1, class="code-number">15, class="code-number">0, class="code-number">0, class="code-number">0, DateTimeKind.Utc))
    .Where(p => p.Id == productId)
    .SingleOrDefaultAsync(ct);

class=class="code-string">"code-comment">// Get full history of changes
var priceHistory = await _context.Products
    .TemporalAll()
    .Where(p => p.Id == productId)
    .OrderBy(p => EF.Property<DateTime>(p, class="code-string">"ValidFrom"))
    .Select(p => new
    {
        p.Price,
        ValidFrom = EF.Property<DateTime>(p, class="code-string">"ValidFrom"),
        ValidTo = EF.Property<DateTime>(p, class="code-string">"ValidTo")
    })
    .ToListAsync(ct);

Robust Soft Delete Pattern

Soft delete through query filters and interceptors together creates a seamless experience:

csharp
public interface ISoftDeletable
{
    bool IsDeleted { get; set; }
    DateTime? DeletedAt { get; set; }
    string? DeletedBy { get; set; }
}

public class SoftDeleteInterceptor : SaveChangesInterceptor
{
    private readonly ICurrentUserService _currentUser;

    public SoftDeleteInterceptor(ICurrentUserService currentUser)
        => _currentUser = currentUser;

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken ct = default)
    {
        foreach (var entry in eventData.Context!.ChangeTracker
            .Entries<ISoftDeletable>()
            .Where(e => e.State == EntityState.Deleted))
        {
            entry.State = EntityState.Modified;
            entry.Entity.IsDeleted = true;
            entry.Entity.DeletedAt = DateTime.UtcNow;
            entry.Entity.DeletedBy = _currentUser.UserId;
        }

        return base.SavingChangesAsync(eventData, result, ct);
    }
}

The interceptor converts physical deletes into logical deletes transparently. Combined with the query filter, deleted records become invisible to normal queries but remain recoverable.

Concurrency Handling

Optimistic concurrency prevents silent data overwrites in multi-user scenarios:

csharp
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = default!;
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; } = default!;
}

Or with Fluent API:

csharp
builder.Property(p => p.RowVersion)
    .IsRowVersion()
    .IsConcurrencyToken();

Handling the conflict:

csharp
public async Task UpdatePriceAsync(int productId, decimal newPrice, CancellationToken ct)
{
    var product = await _context.Products.FindAsync(new object[] { productId }, ct)
        ?? throw new NotFoundException(productId);

    product.Price = newPrice;

    try
    {
        await _context.SaveChangesAsync(ct);
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry = ex.Entries.Single();
        var databaseValues = await entry.GetDatabaseValuesAsync(ct);

        if (databaseValues is null)
            throw new ConflictException(class="code-string">"Product was deleted by another user.");

        var dbProduct = (Product)databaseValues.ToObject();

        throw new ConflictException(
            $class="code-string">"Product was modified by another user. " +
            $class="code-string">"Current price in database: {dbProduct.Price}. " +
            $class="code-string">"Your attempted price: {newPrice}.");
    }
}

In high-volume data systems, I always add concurrency tokens on entities that multiple users or background processes can modify simultaneously. The alternative -- last write wins -- leads to silent data corruption that is nearly impossible to debug after the fact.

Common Advanced EF Core Mistakes

1. Forgetting to Index Foreign Keys

EF Core creates indexes on foreign keys automatically for relationships it knows about. But if you add foreign key columns manually or use shadow properties, you must create the index yourself:

csharp
class=class="code-string">"code-comment">// Missing index on a manually declared FK
builder.Property(o => o.AssignedAgentId);
builder.HasIndex(o => o.AssignedAgentId); class=class="code-string">"code-comment">// Do not forget this

2. Using DbContext as a Singleton

The `DbContext` is not thread-safe. Registering it as singleton or storing it in a static field causes data corruption and intermittent crashes that are extremely difficult to reproduce:

csharp
class=class="code-string">"code-comment">// WRONG: DbContext is not thread-safe
services.AddSingleton<AppDbContext>();

class=class="code-string">"code-comment">// CORRECT: Scoped lifetime, one per request
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

3. Ignoring Query Filter Interactions

When you have multiple query filters, they combine with AND logic. If you forget that a filter exists, queries silently return incomplete results:

csharp
class=class="code-string">"code-comment">// Both filters apply -- easy to forget the tenant filter exists
class=class="code-string">"code-comment">// when debugging why a record "does not exist"
modelBuilder.Entity<Order>().HasQueryFilter(
    o => !o.IsDeleted && o.TenantId == _tenantProvider.CurrentTenantId);

class=class="code-string">"code-comment">// To debug: temporarily bypass all filters
var allOrders = await _context.Orders
    .IgnoreQueryFilters()
    .Where(o => o.Id == missingOrderId)
    .SingleOrDefaultAsync(ct);

4. Not Disposing DbContext Properly in Background Services

In hosted services or background jobs, you must create and dispose scopes manually:

csharp
public class OrderCleanupService : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;

    public OrderCleanupService(IServiceScopeFactory scopeFactory)
        => _scopeFactory = scopeFactory;

    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        while (!ct.IsCancellationRequested)
        {
            using var scope = _scopeFactory.CreateScope();
            var context = scope.ServiceProvider
                .GetRequiredService<AppDbContext>();

            await context.Orders
                .Where(o => o.IsArchived && o.ArchivedAt < DateTime.UtcNow.AddYears(-class="code-number">3))
                .ExecuteDeleteAsync(ct);

            await Task.Delay(TimeSpan.FromHours(class="code-number">1), ct);
        }
    }
}

5. Mixing Tracked and Untracked Entities

Attaching an untracked entity that has the same key as a tracked entity throws an exception. This commonly happens when deserializing an entity from a cache or message queue and trying to update it:

csharp
class=class="code-string">"code-comment">// This throws if the entity is already tracked
_context.Products.Update(deserializedProduct);

class=class="code-string">"code-comment">// Safe approach: check first, then update properties
var tracked = await _context.Products.FindAsync(deserializedProduct.Id);
if (tracked is not null)
{
    _context.Entry(tracked).CurrentValues.SetValues(deserializedProduct);
}

Conclusion

Advanced EF Core usage is about knowing when LINQ and the change tracker serve you well, and when to step outside them. Compiled queries, split queries, and bulk operations address the performance ceiling. Raw SQL and interceptors handle the edge cases that no ORM can abstract perfectly. Temporal tables and concurrency tokens protect data integrity in ways that application code alone cannot.

The patterns in this article come from production systems processing millions of rows daily. They are not theoretical -- every one of them solved a real problem under real load.

I can help audit your EF Core data layer for performance, correctness, and migration safety.

Related Articles

Entity Framework Core: The Modern Way to Handle Databases

Manage database operations with Entity Framework Core. Code-first approach, migrations, and performance optimization.

Clean Architecture in .NET: Building Scalable Project Structure

Apply Clean Architecture in .NET projects. A guide to layers, dependency management, and testable code.

.NET Performance Optimization: Profiling and Best Practices

Optimize .NET application performance. Profiling tools, memory management, and async patterns.

Have a Flutter Project?

I build high-performance Flutter applications for iOS, Android, and web.

Get in Touch