Entity Framework Core: Advanced Scenarios

Tip: This section assumes you have a solid understanding of the fundamental concepts of Entity Framework Core.

Introduction to Advanced Scenarios

Entity Framework Core (EF Core) is a powerful Object-Relational Mapper (ORM) that simplifies database access in .NET applications. While basic CRUD operations are straightforward, real-world applications often require more sophisticated approaches to handle complex data interactions, performance optimizations, and integration with existing systems.

This documentation explores several advanced scenarios that can significantly enhance your EF Core development experience and unlock the full potential of the framework.

1. Raw SQL Queries

Sometimes, you need to execute SQL queries directly for performance reasons, to leverage database-specific features, or when EF Core's LINQ translation becomes complex or inefficient. EF Core provides several ways to execute raw SQL.

Executing a SQL Query that Returns Entities

You can execute a raw SQL query and have EF Core map the results to your entity types.


// Using FromSqlInterpolated to execute a SQL query
var products = await _context.Products
    .FromSqlInterpolated($"SELECT * FROM Products WHERE Category = {categoryName}")
    .ToListAsync();

Executing a SQL Query that Returns Scalar Values

For simple queries that return a single value (e.g., a count or sum), you can use ExecuteSqlRaw or ExecuteSqlInterpolated.


int count = await _context.Products.FromSqlRaw("SELECT COUNT(*) FROM Products").CountAsync();

Executing Stored Procedures

EF Core allows you to call stored procedures. The approach depends on whether the stored procedure returns entities or just performs actions.


// Example for a stored procedure that modifies data
_context.Database.ExecuteSqlRaw("EXEC UpdateProductPrice @ProductId, @NewPrice", productId, newPrice);

2. Batch Operations

Executing many individual INSERT, UPDATE, or DELETE statements can be inefficient due to the overhead of round trips to the database. EF Core provides mechanisms for batching these operations.

Using a Batching Provider

Third-party libraries like EntityFrameworkCore.BatchUpdate can significantly improve the performance of bulk updates and deletes.


// Example using a hypothetical batching provider (syntax may vary)
await _context.Products
    .Where(p => p.IsDeleted)
    .BatchDeleteAsync();

3. Concurrency Control

Concurrency occurs when multiple users or processes attempt to modify the same data simultaneously. EF Core offers strategies to detect and handle these conflicts.

Optimistic Concurrency

This is the most common approach, where you add a row versioning property (e.g., a timestamp or a `uint` field) to your entity.

In your entity:


public class Product {
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    [Timestamp] // Or use a RowVersion property
    public byte[] RowVersion { get; set; }
}

When SaveChanges is called and a conflict is detected, an DbUpdateConcurrencyException is thrown. You can catch this exception and implement a conflict resolution strategy.


try {
    await _context.SaveChangesAsync();
} catch (DbUpdateConcurrencyException ex) {
    // Handle concurrency conflict, e.g., reload data, prompt user
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();
    if (databaseValues == null) {
        // The entity was deleted by another user
    } else {
        var databaseProduct = (Product)databaseValues.ToObject();
        // Compare databaseProduct with entry.Entity and decide how to resolve
        entry.OriginalValues.SetValues(databaseValues); // Reload original values
        await _context.SaveChangesAsync(); // Try saving again
    }
}

4. Global Query Filters

Global query filters allow you to apply common filtering logic to all queries for a specific entity type, ensuring that certain data is always included or excluded.

A common use case is soft deletion:


public class MyDbContext : DbContext {
    public DbSet Products { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity().HasQueryFilter(p => !p.IsDeleted);
    }

    // ... other configurations
}

Now, any query on Products will automatically include WHERE IsDeleted = 0 (or equivalent).

5. Shadow Properties

Shadow properties are properties that are not defined in your entity classes but are mapped to the database schema. This is useful for properties that are managed entirely by the database, such as audit fields.


modelBuilder.Entity().Property<DateTime>("LastModified");

You can then set and retrieve these properties using the Entry API.


var productEntry = _context.Entry(product);
productEntry.Property<DateTime>("LastModified").CurrentValue = DateTime.UtcNow;

6. Owned Types

Owned types (formerly Complex Types) allow you to map types that don't have their own primary key to columns in the same table as their owner. This is great for encapsulating related data.

In your entity:


public class Order {
    public int OrderId { get; set; }
    public Address ShippingAddress { get; set; } // ShippingAddress is an owned type
}

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

In OnModelCreating:


modelBuilder.Entity<Order>().OwnsOne(o => o.ShippingAddress, sa => {
    sa.Property(a => a.Street).HasColumnName("ShippingStreet");
    sa.Property(a => a.City).HasColumnName("ShippingCity");
    sa.Property(a => a.ZipCode).HasColumnName("ShippingZipCode");
});

The ShippingAddress properties will be mapped to columns like ShippingStreet, ShippingCity, etc., in the Orders table.

Conclusion

Mastering these advanced scenarios will empower you to build more robust, performant, and maintainable data access layers with Entity Framework Core. Remember to always profile your queries and choose the approach that best suits your application's specific needs.