Entity Framework Core

Entity Framework Core

Trigger On

• working on DbContext, migrations, model configuration, or EF queries
• reviewing tracking, loading, performance, or transaction behavior
• porting data access from EF6 or custom repositories to EF Core
• optimizing slow database queries

Documentation

• EF Core Overview
• Performance
• Efficient Querying
• Migrations
• What's New in EF Core 9

References

• patterns.md - Query patterns, tracking strategies, loading strategies, projections, compiled queries, pagination, and temporal tables
• anti-patterns.md - Common EF Core mistakes including N+1 queries, large contexts, generic repositories, and missing indexes

Workflow

1. Prefer EF Core for new development unless a documented gap requires Dapper or raw SQL 2. Keep `DbContext` lifetime scoped — align with unit of work 3. Review query translation — check generated SQL, avoid N+1 4. Treat migrations as first-class — reviewable, not throwaway 5. Be deliberate about provider behavior — cross-provider but not identical 6. Validate with query inspection — not just in-memory mental model

Current Upstream Notes

• EF Core v9.0.17 is a servicing release tied to the .NET 9.0.17 train. Treat it as dependency and regression validation work unless the release notes or provider package changelog call out a concrete affected query/runtime path.
• The refreshed EF Core vs EF6 comparison page is the first stop for migration decisions. Do not imply EF6-only EDMX/ObjectContext-heavy code should automatically move to EF Core without a feature inventory.

DbContext Patterns

Basic Configuration

public class AppDbContext : DbContext
{
    public DbSet<Product> Products => Set<Product>();
    public DbSet<Order> Orders => Set<Order>();

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

// Entity Configuration (Fluent API)
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.HasKey(p => p.Id);
        builder.Property(p => p.Name).HasMaxLength(200).IsRequired();
        builder.HasIndex(p => p.Sku).IsUnique();
        builder.HasMany(p => p.OrderItems).WithOne(oi => oi.Product);
    }
}

Registration with DI

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .EnableSensitiveDataLogging()  // Dev only
           .EnableDetailedErrors());      // Dev only

// Or with pooling (better performance)
builder.Services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

Query Patterns

Use AsNoTracking for Read-Only

// Bad - tracks entities unnecessarily
var products = await db.Products.ToListAsync();

// Good - no tracking overhead
var products = await db.Products
    .AsNoTracking()
    .ToListAsync();

Project to DTOs

// Bad - loads entire entity graph
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Customer)
    .ToListAsync();

// Good - loads only needed data
var orders = await db.Orders
    .Select(o => new OrderDto
    {
        Id = o.Id,
        CustomerName = o.Customer.Name,
        ItemCount = o.Items.Count,
        Total = o.Items.Sum(i => i.Price)
    })
    .ToListAsync();

Avoid N+1 Queries

// Bad - N+1 problem
foreach (var order in orders)
{
    var items = await db.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToListAsync();
}

// Good - eager loading
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync();

// Good - split query for large graphs
var orders = await db.Orders
    .Include(o => o.Items)
    .AsSplitQuery()
    .ToListAsync();

Compiled Queries (EF Core 9)

// Pre-compiled for frequently used queries
private static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
    EF.CompileAsyncQuery((AppDbContext db, int id) =>
        db.Products.FirstOrDefault(p => p.Id == id));

// Usage
var product = await GetProductById(db, productId);

Migration Patterns

Creating Migrations

# Add migration
dotnet ef migrations add AddProductIndex

# Apply to database
dotnet ef database update

# Generate SQL script
dotnet ef migrations script --idempotent -o migrate.sql

Data Migrations

public partial class AddProductIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateIndex(
            name: "IX_Products_Sku",
            table: "Products",
            column: "Sku",
            unique: true);

        // Data migration (if needed)
        migrationBuilder.Sql(@"
            UPDATE Products
            SET NormalizedName = UPPER(Name)
            WHERE NormalizedName IS NULL");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(
            name: "IX_Products_Sku",
            table: "Products");
    }
}

Anti-Patterns to Avoid

Performance Best Practices

1. Index frequently queried columns:

   builder.HasIndex(p => p.CreatedAt);
   builder.HasIndex(p => new { p.Category, p.Status });

2. Use pagination:

   var page = await db.Products
       .OrderBy(p => p.Id)
       .Skip(pageSize * pageNumber)
       .Take(pageSize)
       .ToListAsync();

3. Batch updates (EF Core 7+):

   await db.Products
       .Where(p => p.Category == "Obsolete")
       .ExecuteDeleteAsync();

   await db.Products
       .Where(p => p.Category == "Sale")
       .ExecuteUpdateAsync(p => p.SetProperty(x => x.Price, x => x.Price * 0.9m));

4. Minimize network roundtrips:

   // Bad - 3 roundtrips
   var product = await db.Products.FindAsync(id);
   var reviews = await db.Reviews.Where(r => r.ProductId == id).ToListAsync();
   var related = await db.Products.Where(p => p.Category == product.Category).ToListAsync();

   // Good - 1 roundtrip
   var data = await db.Products
       .Where(p => p.Id == id)
       .Select(p => new
       {
           Product = p,
           Reviews = p.Reviews,
           Related = db.Products.Where(r => r.Category == p.Category).Take(5)
       })
       .FirstOrDefaultAsync();

Concurrency Patterns

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }

    [ConcurrencyCheck]
    public int Version { get; set; }

    // Or use RowVersion
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// Handle concurrency conflicts
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();
    // Resolve conflict...
}

Deliver

• EF Core models and queries that match the domain
• safer migrations and lifetime management
• performance-aware data access decisions
• proper indexing and query optimization

Validate

• query behavior is intentional (check SQL logs)
• migrations are reviewable and correct
• no N+1 queries in common paths
• indexes exist for filtered/sorted columns
• DbContext lifetime is scoped properly
• concurrency is handled for critical entities

EF Core Anti-Patterns

N+1 Query Problem

The Problem

Loading related data in a loop causes one query per iteration:

// BAD: N+1 queries - 1 for orders + N for items
var orders = await db.Orders.ToListAsync();
foreach (var order in orders)
{
    // Each iteration executes a new query
    order.Items = await db.OrderItems
        .Where(i => i.OrderId == order.Id)
        .ToListAsync();
}

The Solution

Use eager loading, projection, or explicit loading:

// GOOD: Single query with Include
var orders = await db.Orders
    .Include(o => o.Items)
    .ToListAsync();

// GOOD: Split query for large graphs
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToListAsync();

// GOOD: Projection to DTO
var orderDtos = await db.Orders
    .Select(o => new OrderDto
    {
        Id = o.Id,
        Items = o.Items.Select(i => new OrderItemDto { ... }).ToList()
    })
    .ToListAsync();

Large DbContext with Too Many DbSets

The Problem

A single DbContext with dozens of DbSets becomes hard to maintain and test:

// BAD: Monolithic context
public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Order> Orders { get; set; }
    public DbSet<Product> Products { get; set; }
    public DbSet<Inventory> Inventory { get; set; }
    public DbSet<Shipment> Shipments { get; set; }
    public DbSet<Invoice> Invoices { get; set; }
    // ... 50 more DbSets
}

The Solution

Split into bounded contexts:

// GOOD: Bounded contexts
public class OrderingDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }
}

public class InventoryDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<StockLevel> StockLevels { get; set; }
}

Loading Full Entities When You Need Subsets

The Problem

Fetching entire entity graphs when only a few properties are needed:

// BAD: Loads everything including large blobs
var products = await db.Products
    .Include(p => p.Images)
    .Include(p => p.Reviews)
    .Include(p => p.Specifications)
    .ToListAsync();

// Then only using name and price
var displayList = products.Select(p => $"{p.Name}: {p.Price}");

The Solution

Project to DTOs or anonymous types:

// GOOD: Only fetch what you need
var products = await db.Products
    .Select(p => new { p.Name, p.Price })
    .ToListAsync();

var displayList = products.Select(p => $"{p.Name}: {p.Price}");

Client-Side Evaluation Without Awareness

The Problem

Filtering happens in memory instead of database:

// BAD: Custom method forces client evaluation
var activeProducts = await db.Products
    .Where(p => IsProductActive(p))  // Cannot translate to SQL
    .ToListAsync();

// BAD: Complex string operations may not translate
var products = await db.Products
    .Where(p => p.Name.Contains(searchTerm, StringComparison.OrdinalIgnoreCase))
    .ToListAsync();

The Solution

Use translatable expressions or explicit client evaluation:

// GOOD: Use EF.Functions for database operations
var products = await db.Products
    .Where(p => EF.Functions.Like(p.Name, $"%{searchTerm}%"))
    .ToListAsync();

// GOOD: Explicit about client evaluation
var allProducts = await db.Products.ToListAsync();
var activeProducts = allProducts.Where(p => IsProductActive(p));

SaveChanges in Loops

The Problem

Calling SaveChanges repeatedly causes many database roundtrips:

// BAD: N roundtrips
foreach (var product in products)
{
    product.Price *= 1.1m;
    await db.SaveChangesAsync();  // Roundtrip each iteration
}

The Solution

Batch changes and save once:

// GOOD: Single roundtrip
foreach (var product in products)
{
    product.Price *= 1.1m;
}
await db.SaveChangesAsync();

// BETTER: Use ExecuteUpdate for bulk operations (EF Core 7+)
await db.Products
    .Where(p => p.Category == category)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.1m));

Incorrect DbContext Lifetime

The Problem

Long-lived or singleton DbContext causes memory leaks and stale data:

// BAD: Singleton - accumulates tracked entities
services.AddSingleton<AppDbContext>();

// BAD: Static or field-level context
public class ProductService
{
    private static readonly AppDbContext _db = new AppDbContext();
}

The Solution

Use scoped lifetime aligned with unit of work:

// GOOD: Scoped lifetime (default for AddDbContext)
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

// GOOD: Pooled for better performance
services.AddDbContextPool<AppDbContext>(options =>
    options.UseSqlServer(connectionString));

// For background services, create scope explicitly
public class BackgroundProcessor : BackgroundService
{
    private readonly IServiceScopeFactory _scopeFactory;

    protected override async Task ExecuteAsync(CancellationToken ct)
    {
        using var scope = _scopeFactory.CreateScope();
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        // Use db within this scope
    }
}

Generic Repository Anti-Pattern

The Problem

Wrapping DbContext in a generic repository hides EF Core's power:

// BAD: Generic repository loses query composition
public interface IRepository<T>
{
    Task<T> GetByIdAsync(int id);
    Task<IEnumerable<T>> GetAllAsync();
    Task AddAsync(T entity);
    Task UpdateAsync(T entity);
    Task DeleteAsync(T entity);
}

// Usage - can't compose queries efficiently
var products = await _productRepo.GetAllAsync();
var filtered = products.Where(p => p.Price > 100);  // Client-side!

The Solution

Use DbContext directly or create specific query methods:

// GOOD: Direct DbContext usage
var products = await db.Products
    .Where(p => p.Price > 100)
    .OrderBy(p => p.Name)
    .Take(20)
    .ToListAsync();

// GOOD: Specific repository with meaningful methods
public class ProductRepository
{
    private readonly AppDbContext _db;

    public async Task<List<Product>> GetExpensiveProductsAsync(decimal minPrice)
    {
        return await _db.Products
            .Where(p => p.Price >= minPrice)
            .OrderByDescending(p => p.Price)
            .ToListAsync();
    }
}

Ignoring Query Translation

The Problem

Not verifying that LINQ translates to efficient SQL:

// May not translate as expected
var results = await db.Products
    .Where(p => SomeComplexMethod(p))
    .ToListAsync();
// Could load ALL products to memory!

The Solution

Enable logging and verify SQL:

// In development
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .EnableSensitiveDataLogging()
           .EnableDetailedErrors()
           .LogTo(Console.WriteLine, LogLevel.Information));

// In tests - use ToQueryString()
var query = db.Products.Where(p => p.Price > 100);
var sql = query.ToQueryString();
Console.WriteLine(sql);

Lazy Loading Without Understanding

The Problem

Enabling lazy loading without realizing the N+1 implications:

// Configuration enables lazy loading
services.AddDbContext<AppDbContext>(o => o.UseLazyLoadingProxies());

// BAD: Hidden N+1 queries in views/serialization
@foreach (var order in orders)
{
    <p>@order.Customer.Name</p>  // Query per iteration!
    @foreach (var item in order.Items)  // Another query per order!
    {
        <p>@item.Product.Name</p>  // Yet another query!
    }
}

The Solution

Disable lazy loading and use explicit strategies:

// GOOD: Explicit eager loading for known needs
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToListAsync();

// Or project to view model
var orderViews = await db.Orders
    .Select(o => new OrderViewModel
    {
        CustomerName = o.Customer.Name,
        Items = o.Items.Select(i => new ItemViewModel
        {
            ProductName = i.Product.Name
        }).ToList()
    })
    .ToListAsync();

Missing Indexes

The Problem

Queries filter on columns without indexes:

// If no index on Email, this scans entire table
var user = await db.Users
    .FirstOrDefaultAsync(u => u.Email == email);

// Composite filter without composite index
var orders = await db.Orders
    .Where(o => o.CustomerId == customerId && o.Status == status)
    .ToListAsync();

The Solution

Add indexes for queried columns:

public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasIndex(u => u.Email).IsUnique();
    }
}

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        // Composite index matches query pattern
        builder.HasIndex(o => new { o.CustomerId, o.Status });

        // Filtered index for common queries
        builder.HasIndex(o => o.CreatedAt)
            .HasFilter("[Status] = 'Pending'");
    }
}

Unbounded Queries

The Problem

Queries that can return unlimited results:

// BAD: Could return millions of rows
var products = await db.Products.ToListAsync();

// BAD: User-controlled search without limits
var results = await db.Products
    .Where(p => p.Name.Contains(searchTerm))
    .ToListAsync();

The Solution

Always apply limits and pagination:

// GOOD: Bounded results
var products = await db.Products
    .Take(100)
    .ToListAsync();

// GOOD: Paginated
var results = await db.Products
    .Where(p => p.Name.Contains(searchTerm))
    .OrderBy(p => p.Name)
    .Skip(page * pageSize)
    .Take(pageSize)
    .ToListAsync();

Mixing Tracked and Untracked Entities

The Problem

Attaching or mixing entities from different tracking contexts:

// BAD: Entity from one context used in another
var product = await db1.Products.FindAsync(id);
db2.Products.Update(product);  // Confusion and potential errors
await db2.SaveChangesAsync();

// BAD: Mixing tracked and untracked
var product = await db.Products.AsNoTracking().FirstAsync(p => p.Id == id);
product.Price = newPrice;
await db.SaveChangesAsync();  // Nothing saved - not tracked!

The Solution

Be consistent with tracking and context usage:

// GOOD: Clear ownership
var product = await db.Products.FindAsync(id);
product.Price = newPrice;
await db.SaveChangesAsync();

// GOOD: Explicit attach for disconnected scenarios
var product = GetProductFromDto(dto);  // Untracked
db.Products.Update(product);  // Marks all as modified
await db.SaveChangesAsync();

// BETTER: Only update changed properties
var product = await db.Products.FindAsync(dto.Id);
product.Price = dto.Price;  // Only this is marked modified
await db.SaveChangesAsync();

Not Using Transactions for Multi-Step Operations

The Problem

Multiple SaveChanges without transaction can leave data inconsistent:

// BAD: Partial failure possible
order.Status = OrderStatus.Completed;
await db.SaveChangesAsync();

inventory.Quantity -= order.Quantity;
await db.SaveChangesAsync();  // If this fails, order status is wrong

payment.Status = PaymentStatus.Captured;
await db.SaveChangesAsync();

The Solution

Use explicit transactions:

// GOOD: All-or-nothing
using var transaction = await db.Database.BeginTransactionAsync();
try
{
    order.Status = OrderStatus.Completed;
    await db.SaveChangesAsync();

    inventory.Quantity -= order.Quantity;
    await db.SaveChangesAsync();

    payment.Status = PaymentStatus.Captured;
    await db.SaveChangesAsync();

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

// BETTER: Single SaveChanges when possible
order.Status = OrderStatus.Completed;
inventory.Quantity -= order.Quantity;
payment.Status = PaymentStatus.Captured;
await db.SaveChangesAsync();  // All in one transaction

String-Based Includes

The Problem

Using string-based includes loses compile-time safety:

// BAD: Typos not caught at compile time
var orders = await db.Orders
    .Include("Cusotmer")  // Typo - runtime error
    .Include("Items.Prodcut")  // Another typo
    .ToListAsync();

The Solution

Use strongly-typed lambda expressions:

// GOOD: Compile-time safety
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
    .ToListAsync();

EF Core Query Patterns

Query Tracking Strategies

No-Tracking Queries

Use AsNoTracking() for read-only scenarios to reduce memory overhead and improve performance:

// Single query
var products = await db.Products
    .AsNoTracking()
    .Where(p => p.IsActive)
    .ToListAsync();

// Context-wide default (useful for read-heavy contexts)
db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

No-Tracking with Identity Resolution

When you need consistent object references without change tracking:

var orders = await db.Orders
    .AsNoTrackingWithIdentityResolution()
    .Include(o => o.Customer)
    .Include(o => o.Items)
    .ToListAsync();
// Same customer object returned for orders with same customer

Tracking Queries

Use tracking only when you intend to modify entities:

var product = await db.Products.FindAsync(id);
product.Price = newPrice;
await db.SaveChangesAsync();

Loading Strategies

Eager Loading

Load related data in a single query using Include():

// Single level
var orders = await db.Orders
    .Include(o => o.Customer)
    .ToListAsync();

// Nested includes
var orders = await db.Orders
    .Include(o => o.Items)
        .ThenInclude(i => i.Product)
            .ThenInclude(p => p.Category)
    .ToListAsync();

// Multiple includes
var orders = await db.Orders
    .Include(o => o.Customer)
    .Include(o => o.ShippingAddress)
    .Include(o => o.Items)
    .ToListAsync();

Filtered Includes (EF Core 5+)

Include only specific related entities:

var orders = await db.Orders
    .Include(o => o.Items.Where(i => i.Quantity > 0))
    .ToListAsync();

// With ordering and limiting
var customers = await db.Customers
    .Include(c => c.Orders
        .OrderByDescending(o => o.CreatedAt)
        .Take(5))
    .ToListAsync();

Split Queries

Avoid cartesian explosion with large entity graphs:

// Without split - one large query with cartesian product
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .ToListAsync();

// With split - multiple smaller queries
var orders = await db.Orders
    .Include(o => o.Items)
    .Include(o => o.Payments)
    .AsSplitQuery()
    .ToListAsync();

// Configure as default
optionsBuilder.UseSqlServer(connectionString, o => o.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));

Explicit Loading

Load related data on demand after the principal entity:

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

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

// Load reference
await db.Entry(order)
    .Reference(o => o.Customer)
    .LoadAsync();

// Query into loaded navigation
var highValueItems = await db.Entry(order)
    .Collection(o => o.Items)
    .Query()
    .Where(i => i.Price > 100)
    .ToListAsync();

Lazy Loading

Requires proxy setup and virtual navigation properties:

// Setup
services.AddDbContext<AppDbContext>(options =>
    options.UseLazyLoadingProxies()
           .UseSqlServer(connectionString));

// Entity
public class Order
{
    public int Id { get; set; }
    public virtual Customer Customer { get; set; }  // virtual required
    public virtual ICollection<OrderItem> Items { get; set; }
}

Warning: Lazy loading often leads to N+1 queries. Prefer explicit strategies.

Projection Patterns

Project to DTOs

Always project when you need a subset of data:

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

Anonymous Projections

For internal use without creating DTOs:

var stats = await db.Products
    .GroupBy(p => p.Category)
    .Select(g => new
    {
        Category = g.Key,
        Count = g.Count(),
        AvgPrice = g.Average(p => p.Price),
        MaxPrice = g.Max(p => p.Price)
    })
    .ToListAsync();

Conditional Projection

var products = await db.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        DisplayPrice = p.IsOnSale ? p.SalePrice : p.RegularPrice,
        StockStatus = p.Stock > 10 ? "In Stock" : p.Stock > 0 ? "Low Stock" : "Out of Stock"
    })
    .ToListAsync();

Compiled Queries

Pre-compile frequently used queries for better performance:

public static class CompiledQueries
{
    public static readonly Func<AppDbContext, int, Task<Product?>> GetProductById =
        EF.CompileAsyncQuery((AppDbContext db, int id) =>
            db.Products.FirstOrDefault(p => p.Id == id));

    public static readonly Func<AppDbContext, string, IAsyncEnumerable<Product>> GetProductsByCategory =
        EF.CompileAsyncQuery((AppDbContext db, string category) =>
            db.Products.Where(p => p.Category == category));

    public static readonly Func<AppDbContext, decimal, int, IAsyncEnumerable<Product>> GetExpensiveProducts =
        EF.CompileAsyncQuery((AppDbContext db, decimal minPrice, int take) =>
            db.Products
                .Where(p => p.Price >= minPrice)
                .OrderByDescending(p => p.Price)
                .Take(take));
}

// Usage
var product = await CompiledQueries.GetProductById(db, productId);

await foreach (var p in CompiledQueries.GetProductsByCategory(db, "Electronics"))
{
    // Process product
}

Raw SQL Patterns

FromSql for Entity Queries

var products = await db.Products
    .FromSql($"SELECT * FROM Products WHERE Price > {minPrice}")
    .ToListAsync();

// Composable - can add LINQ operators
var products = await db.Products
    .FromSql($"SELECT * FROM Products WHERE Category = {category}")
    .Where(p => p.IsActive)
    .OrderBy(p => p.Name)
    .ToListAsync();

SqlQuery for Arbitrary Results (EF Core 8+)

var totals = await db.Database
    .SqlQuery<decimal>($"SELECT SUM(Price) FROM Products WHERE Category = {category}")
    .ToListAsync();

var stats = await db.Database
    .SqlQuery<CategoryStats>($@"
        SELECT Category, COUNT(*) as ProductCount, AVG(Price) as AvgPrice
        FROM Products
        GROUP BY Category")
    .ToListAsync();

ExecuteSql for Non-Query Operations

var affected = await db.Database
    .ExecuteSqlAsync($"UPDATE Products SET Price = Price * {multiplier} WHERE Category = {category}");

Pagination Patterns

Offset-Based Pagination

public async Task<PagedResult<T>> GetPageAsync<T>(
    IQueryable<T> query,
    int pageNumber,
    int pageSize)
{
    var totalCount = await query.CountAsync();
    var items = await query
        .Skip(pageNumber * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<T>
    {
        Items = items,
        TotalCount = totalCount,
        PageNumber = pageNumber,
        PageSize = pageSize,
        TotalPages = (int)Math.Ceiling(totalCount / (double)pageSize)
    };
}

Keyset Pagination (Better for Large Datasets)

// More efficient for deep pages - uses index instead of offset
public async Task<List<Product>> GetNextPageAsync(int lastId, int pageSize)
{
    return await db.Products
        .Where(p => p.Id > lastId)
        .OrderBy(p => p.Id)
        .Take(pageSize)
        .ToListAsync();
}

// Bidirectional keyset pagination
public async Task<List<Product>> GetPreviousPageAsync(int firstId, int pageSize)
{
    return await db.Products
        .Where(p => p.Id < firstId)
        .OrderByDescending(p => p.Id)
        .Take(pageSize)
        .OrderBy(p => p.Id)  // Restore ascending order
        .ToListAsync();
}

Global Query Filters

Apply filters automatically to all queries:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Soft delete filter
    modelBuilder.Entity<Product>()
        .HasQueryFilter(p => !p.IsDeleted);

    // Multi-tenant filter
    modelBuilder.Entity<Order>()
        .HasQueryFilter(o => o.TenantId == _tenantId);
}

// Bypass when needed
var allProducts = await db.Products
    .IgnoreQueryFilters()
    .ToListAsync();

Temporal Tables (EF Core 6+)

Query historical data:

// Configure temporal table
modelBuilder.Entity<Product>()
    .ToTable("Products", b => b.IsTemporal());

// Query as of specific time
var historicalProducts = await db.Products
    .TemporalAsOf(specificDateTime)
    .ToListAsync();

// Query between time range
var productHistory = await db.Products
    .TemporalBetween(startDate, endDate)
    .Where(p => p.Id == productId)
    .ToListAsync();

// Get all changes
var allChanges = await db.Products
    .TemporalAll()
    .Where(p => p.Id == productId)
    .OrderBy(p => EF.Property<DateTime>(p, "PeriodStart"))
    .ToListAsync();