MSDN Documentation

Concurrency Control

Concurrency arises when multiple users or processes attempt to modify the same data simultaneously. EF Core provides mechanisms to handle these situations gracefully and prevent data loss.

Optimistic Concurrency

This is the most common approach, where conflicts are detected and handled at the time of saving changes. EF Core supports this through:

• Row Versioning: Using a timestamp or version number column that is updated on every modification.
• Property Checksums: Comparing the values of specific properties before and after an operation.

Example using a timestamp property:

public class Product
{
    public int ProductId { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; }
}
                

When saving changes, EF Core will compare the current `RowVersion` in the database with the one tracked by the entity. If they differ, an DbUpdateConcurrencyException is thrown.

Handling Concurrency Conflicts

You can catch the DbUpdateConcurrencyException and implement strategies such as:

• Reloading the data from the database and re-applying user changes.
• Informing the user about the conflict and letting them resolve it.
• Ignoring the conflict (last write wins - generally not recommended).

try
{
    await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Refresh entities that caused the concurrency problem
    foreach (var entry in ex.Entries)
    {
        var databaseValues = await entry.GetDatabaseValuesAsync();

        if (databaseValues == null)
        {
            // The entity was deleted by another user
            Console.WriteLine("The entity you were trying to update was deleted by another user.");
            return;
        }

        var proposedValues = entry.CurrentValues;

        // Replace with your resolution logic
        var proposedProductName = proposedValues.GetValue("Name");
        var proposedProductPrice = proposedValues.GetValue("Price");

        var databaseProductName = databaseValues.GetValue("Name");
        var databaseProductPrice = databaseValues.GetValue("Price");

        Console.WriteLine($"Database values: Name={databaseProductName}, Price={databaseProductPrice}");
        Console.WriteLine($"Your values: Name={proposedProductName}, Price={proposedProductPrice}");

        // Example: User chooses to keep their changes and overwrite database
        entry.OriginalValues.SetValues(databaseValues);
        entry.CurrentValues.SetValues(proposedValues); // Re-apply your changes
    }

    // Save the changes again after resolving conflicts
    await _context.SaveChangesAsync();
}
                

Transactions

Transactions ensure that a series of database operations are treated as a single unit of work. Either all operations succeed, or none of them do, maintaining data consistency.

Using DbContext.Database.BeginTransactionAsync()

EF Core allows you to explicitly manage database transactions.

using (var transaction = await _context.Database.BeginTransactionAsync())
{
    try
    {
        // Perform multiple operations
        var order = new Order { CustomerId = 1, OrderDate = DateTime.UtcNow };
        _context.Orders.Add(order);
        await _context.SaveChangesAsync();

        var orderItem = new OrderItem { OrderId = order.OrderId, ProductId = 101, Quantity = 2 };
        _context.OrderItems.Add(orderItem);
        await _context.SaveChangesAsync();

        // If all operations are successful
        await transaction.CommitAsync();
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (Exception ex)
    {
        // If any operation fails, roll back the transaction
        await transaction.RollbackAsync();
        Console.WriteLine($"Transaction rolled back: {ex.Message}");
        // Handle the exception appropriately
    }
}
                

Automatic Transactions

When you call SaveChangesAsync() multiple times within a single call to SaveChangesAsync() (e.g., by saving changes to multiple DbContext instances or within a loop without intermediate saves), EF Core may implicitly manage transactions depending on the provider.

However, for critical operations involving multiple distinct save operations, explicit transaction management is highly recommended for clarity and control.

Query Performance Optimization

Efficient querying is crucial for application performance. EF Core offers several features to help you optimize how you retrieve data.

Lazy Loading vs. Eager Loading vs. Explicit Loading

• Lazy Loading: Navigation properties are loaded on demand when accessed. Requires proxy objects and is enabled via configuration. Can lead to N+1 query problems if not managed carefully.
• Eager Loading: Related data is loaded along with the principal entity using Include() or ThenInclude(). This is often the preferred method for performance when you know you'll need related data.
• Explicit Loading: Related data is loaded manually after the principal entity has been loaded, using Load() or LoadAsync() on a navigation property.

Example of Eager Loading:

var customersWithOrders = await _context.Customers
    .Include(c => c.Orders)
        .ThenInclude(o => o.OrderItems)
    .ToListAsync();
                

Projection

Select only the columns you need using Select(). This avoids loading unnecessary data into memory and reduces database load.

var customerNamesAndOrderCount = await _context.Customers
    .Select(c => new { c.Name, OrderCount = c.Orders.Count() })
    .ToListAsync();
                

AsNoTracking()

For read-only queries where you don't intend to update the entities, use AsNoTracking(). This bypasses EF Core's change tracking mechanism, which can significantly improve performance by reducing overhead.

var products = await _context.Products.AsNoTracking().ToListAsync();
                

Batching Operations

For large numbers of inserts, updates, or deletes, consider using batching strategies. EF Core doesn't have built-in efficient batching for all operations, but libraries like EntityFrameworkCore.BulkExtensions can provide significant performance gains.

Raw SQL Queries

Sometimes, you might need to execute raw SQL queries for performance-critical operations, to use database-specific features, or when EF Core's LINQ translation doesn't cover your needs.

Executing Raw SQL Queries

Use FromSqlRaw() or ExecuteSqlRaw().

// Querying data
var products = await _context.Products.FromSqlRaw("SELECT * FROM dbo.GetProducts()").ToListAsync();

// Executing commands
await _context.Database.ExecuteSqlRawAsync("UPDATE dbo.Products SET Price = Price * 1.1 WHERE CategoryId = 1");
                

Important: Be extremely careful with SQL injection vulnerabilities when building SQL queries from user input. Always use parameterized queries.

var categoryId = 1;
var products = await _context.Products
    .FromSqlInterpolated($"SELECT * FROM dbo.Products WHERE CategoryId = {categoryId}")
    .ToListAsync();
                

Advanced Migrations

EF Core Migrations help you evolve your database schema over time. Advanced usage involves more complex scenarios.

Customizing Migrations

You can create custom migration operations to perform specific database tasks not covered by EF Core's standard operations.

public class MyCustomMigrationOperation : MigrationOperation
{
    public override void Generate(MigrationCommandListBuilder builder, IModel mi)
    {
        builder.Append("CREATE PROCEDURE usp_UpdateProductInventory AS BEGIN ... END");
    }
}

public partial class AddStoredProcedure : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Operations.Add(new MyCustomMigrationOperation());
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Operations.Add(new MigrationOperation(
            "DROP PROCEDURE usp_UpdateProductInventory"
        ));
    }
}
                

Conditional Migrations

You might need migrations that only run under certain conditions (e.g., only in production). This can be achieved using custom logic within your migration's Up and Down methods, often checking environment variables or configuration.

Ignoring Migrations

In some cases, you might want to "seed" data or perform manual database changes outside of EF Core's migration process. You can use migrationBuilder.Sql() to execute arbitrary SQL, or if you need to completely bypass EF Core's tracking for certain schema changes, you might need to manually manage the __EFMigrationsHistory table (use with extreme caution).

Advanced Data Loading

Beyond basic eager and lazy loading, EF Core offers fine-grained control over how related data is fetched.

Query Tagging

Assign names to your queries using .TagWith(). This is incredibly useful for debugging and monitoring, as it helps identify specific queries in your database profiler.

var products = await _context.Products
    .Include(p => p.Category)
    .TagWith("LoadingProductsWithCategories")
    .ToListAsync();
                

Filtering Included Collections

You can filter collections loaded via Include().

var customersWithRecentOrders = await _context.Customers
    .Include(c => c.Orders.Where(o => o.OrderDate > DateTime.UtcNow.AddMonths(-1)))
    .ToListAsync();
                

Note that this can sometimes translate into separate queries depending on the provider and complexity.

Change Tracking Customization

EF Core's change tracker is powerful but can be customized for specific needs.

DbContextFactory

For scenarios like background tasks or web jobs where you need to create DbContext instances without dependency injection, use IDbContextFactory.

// In Startup.cs or Program.cs
services.AddDbContextFactory(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

// In a service or background task
var factory = serviceProvider.GetRequiredService<IDbContextFactory<MyDbContext>>();
using (var context = await factory.CreateDbContextAsync())
{
    // Use context here
}
                

Ignoring Properties

You can configure EF Core to ignore certain properties from being persisted or tracked.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().Ignore(u => u.TemporaryPassword);
}