Advanced Entity Framework Core Topics

Advanced Querying and Performance

Explore techniques to optimize query performance and handle complex data retrieval scenarios.

Raw SQL Queries

Sometimes, you need to execute raw SQL queries for performance or to access database-specific features not supported by LINQ.

var products = context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Category = {0}", "Electronics");

foreach (var product in products)
{
    Console.WriteLine(product.Name);
}
                

ExecuteUpdate and ExecuteDelete

Efficiently update or delete multiple rows in a single database command without loading entities into memory.

var updatedCount = await context.Products
    .Where(p => p.Price < 10.0m)
    .ExecuteUpdateAsync(p => p.SetProperty(p => p.Price, p => p.Price * 1.05m));

Console.WriteLine($"Updated {updatedCount} products.");
                

Batching Changes

EF Core automatically batches changes when calling SaveChanges, but understanding how it works can help in complex scenarios.

Connection Resiliency and Retries

Configure EF Core to automatically retry database operations in case of transient failures.

var options = new DbContextOptionsBuilder()
    .UseSqlServer("Server=.;Database=MyDatabase;Trusted_Connection=True;MultipleActiveResultSets=true",
        sqlServerOptions => sqlServerOptions.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: null))
    .Options;
                

Mapping and Model Configuration

Go beyond basic conventions with advanced mapping strategies and model customization.

Owned Types

Map complex properties in your entities without a foreign key relationship, often used for address or contact information.

public class Order
{
    public int Id { get; set; }
    public ShippingAddress ShippingAddress { get; set; }
}

public class ShippingAddress
{
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
}

// In DbContext.OnModelCreating:
modelBuilder.Entity().OwnsOne(o => o.ShippingAddress);
                

TPH, TPT, and TPC Table Mapping

Understand different strategies for mapping inheritance hierarchies to database tables.

Strategy	Description	Database Structure
TPH (Table-Per-Hierarchy)	A single table stores all entities, with discriminator columns.	Single table with discriminator.
TPT (Table-Per-Type)	Each concrete entity type maps to its own table.	Separate tables for each entity, linked by foreign keys.
TPC (Table-Per-Concrete Type)	Each concrete entity type maps to its own table, only storing columns specific to that type. Base type columns are duplicated.	Separate tables for each concrete entity, base type columns duplicated.

Value Objects

Represent concepts that have value but no identity, like Money or DateRange.

Complex Type Mapping

Map properties of complex types to different columns within the same table.

Interceptors and Extension Methods

Hook into the EF Core pipeline to customize behavior.

SaveChangesInterceptor

Intercept the saving process to add auditing, validation, or modify entities before they are saved.

public class AuditInterceptor : DbCommandInterceptor
{
    public override InterceptionResult SavingChanges(
        DbContextEventData eventData,
        InterceptionResult result)
    {
        // Logic to audit changes
        return result;
    }
}

// In DbContextOptionsBuilder:
.AddInterceptors(new AuditInterceptor());
                

DbCommandInterceptor

Intercept database commands before they are executed.

Custom Provider Features

Leverage database-specific features through custom provider implementations or extension methods.

Other Advanced Topics

Concurrency Control

Handle concurrent updates to the same data using row versioning or optimistic concurrency.

Batching and Bulk Operations

For very large operations, consider specialized libraries for bulk inserts, updates, and deletes.

Transactions

Understand how EF Core manages transactions and how to control them explicitly.

using var transaction = await context.Database.BeginTransactionAsync();
try
{
    // Perform operations
    await context.SaveChangesAsync();
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}
                

Database-Specific Functionality

Utilize SQL Server's JSON functions, PostgreSQL's array types, or other provider-specific features.