MSDN Documentation

Advanced Entity Framework Core Topics

This section delves into more complex and advanced features of Entity Framework Core, equipping you with the knowledge to tackle challenging scenarios and optimize your data access layer.

1. Raw SQL Queries

While EF Core provides a rich querying API, sometimes you need to drop down to raw SQL. EF Core allows you to execute raw SQL queries and commands directly.

Executing Raw SQL Queries

You can execute a raw SQL query that returns entities of a specific type:

var blogs = context.Blogs
                .FromSqlRaw("SELECT * FROM Blogs")
                .ToList();
            

Or a query that returns a specific shape:

var blogNames = context.Database.SqlQueryRaw(
                "SELECT Name FROM Blogs WHERE Url LIKE {0}", "%github%")
                .ToList();
            

Executing Raw SQL Commands

For non-query operations like INSERT, UPDATE, or DELETE:

var rowAffected = context.Database.ExecuteSqlRaw(
                "UPDATE Blogs SET Url = {0} WHERE Id = {1}",
                "newurl.com", 1);
            

Important: Be cautious when constructing SQL queries with user input to prevent SQL injection vulnerabilities. Always use parameterized queries.

2. Change Tracking and State Management

EF Core's change tracker is fundamental to how it detects and saves changes. Understanding its nuances is key to efficient data management.

Entity States

Entities can be in several states:

• Added: The entity is new and will be inserted.
• Unchanged: The entity has not been modified since it was last tracked.
• Modified: The entity's property values have changed.
• Deleted: The entity has been deleted.
• Detached: The entity is not currently tracked by the context.

Manual State Configuration

You can manually set the state of an entity:

var existingBlog = context.Blogs.Find(1);
if (existingBlog != null)
{
    context.Entry(existingBlog).State = EntityState.Detached; // Or Modified, Deleted, etc.
}
            

3. Concurrency Handling

Concurrency occurs when multiple users or processes try to modify the same data simultaneously. EF Core offers strategies to manage this.

Optimistic Concurrency

This is the most common approach. You typically add a version number or timestamp property to your entity. EF Core checks this property when saving changes.

public class Blog
{
    public int Id { get; set; }
    public string Url { get; set; }
    public byte[] RowVersion { get; set; } // Concurrency token
}
            

When saving changes, if the `RowVersion` in the database doesn't match the one in the tracked entity, EF Core throws a `DbUpdateConcurrencyException`.

Handling Concurrency Conflicts

You can catch the exception and decide how to resolve it, e.g., by overwriting the database values, merging changes, or letting the user decide.

try
{
    await context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Handle concurrency conflict - e.g., refresh original values from DB
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();

    if (databaseValues == null)
    {
        // The entity has been deleted by another user.
        throw new Exception("The entity has been deleted by another user.");
    }

    // Restore the original values and re-throw the exception
    entry.OriginalValues.SetValues(databaseValues);
    throw;
}
            

4. Transaction Management

EF Core automatically wraps operations within `SaveChangesAsync` in a database transaction. However, you can manage transactions explicitly for more control.

Explicit Transaction Management

using (var transaction = context.Database.BeginTransaction())
{
    try
    {
        // Perform operations
        context.Blogs.Add(new Blog { Url = "blog1.com" });
        context.Posts.Add(new Post { Title = "Post 1" });

        await context.SaveChangesAsync();

        // Commit the transaction
        transaction.Commit();
    }
    catch (Exception)
    {
        // Rollback the transaction
        transaction.Rollback();
        throw; // Re-throw the exception
    }
}
            

You can also specify the isolation level for your transactions.

5. Global Query Filters

Global query filters are a powerful way to automatically apply a `WHERE` clause to all LINQ queries for a specific entity type. This is often used for soft deletes or multi-tenancy.

Implementing Soft Deletes

public class Blog
{
    public int Id { get; set; }
    public string Url { get; set; }
    public bool IsDeleted { get; set; } = false; // Soft delete flag
}
            

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

With this filter, any query on `Blog` will automatically include `WHERE IsDeleted = 0` (or `FALSE`). To query deleted entities, you can use `IgnoreQueryFilters()`.

var allBlogs = context.Blogs.IgnoreQueryFilters().ToList();
            

6. Interceptors

Interceptors allow you to hook into EF Core's pipeline and modify or observe operations as they happen. This is useful for logging, auditing, or modifying queries/commands.

Example: Logging SQL

You can implement `SaveChangesInterceptor` or `CommandInterceptor` to log executed SQL.

public class SqlLoggingInterceptor : DbCommandInterceptor
{
    public override InterceptionResult ReaderExecuting(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult result)
    {
        Console.WriteLine($"Executing SQL: {command.CommandText}");
        return result;
    }
}
            

Register the interceptor in your `DbContextOptionsBuilder`:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.AddInterceptors(new SqlLoggingInterceptor());
}
            

7. Value Objects and Owned Types

EF Core supports modeling complex types that don't have their own identity but are owned by another entity. This is useful for encapsulating related properties.

Owned Types

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

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address ShippingAddress { get; set; } // Owned entity
}
            

By default, EF Core will map `ShippingAddress` to columns within the `Customer` table. You can also configure them to be in a separate table.

Exploring these advanced topics will empower you to build more robust, efficient, and maintainable data access solutions with Entity Framework Core.