Entity Framework Core Advanced Topics

This section delves into more complex scenarios and advanced features of Entity Framework Core (EF Core) that can help you build more robust, scalable, and performant data access solutions.

1. DbContext Pooling

DbContext pooling is a feature designed to improve the performance of applications that frequently create and dispose of DbContext instances. By pooling DbContexts, EF Core can reuse instances, reducing the overhead associated with their creation and initialization.

To enable DbContext pooling, you need to configure your services using dependency injection:

services.AddDbContextPool<MyDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

2. Raw SQL Queries

EF Core allows you to execute raw SQL queries when you need more control over the generated SQL or when dealing with scenarios not fully covered by LINQ.

Executing Raw SQL for Queries

You can execute raw SQL queries and project the results into a C# type:

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

For queries with parameters, use FromSqlRaw with parameter values:

var blogs = context.Blogs.FromSqlRaw("SELECT * FROM Blogs WHERE Name = {0}", "My Blog");

Executing Raw SQL for Modification

To execute raw SQL commands that modify data (INSERT, UPDATE, DELETE), use ExecuteSqlRaw:

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

3. Query Tagging

Query tagging allows you to assign a descriptive tag to a LINQ query. This tag is included in the generated SQL, making it easier to identify and debug specific queries in your database profiler.

var blogs = context.Blogs
            .Where(b => b.Rating > 3)
            .TagWith("High-rated blogs query")
            .ToList();

This will generate SQL similar to:

-- High-rated blogs query
        SELECT *
        FROM Blogs AS b
        WHERE b.Rating > 3;

4. Global Query Filters

Global query filters are conditions that are applied to every LINQ query for a specific entity type. This is commonly used for implementing soft deletes or multi-tenancy.

You can configure global query filters in your DbContext.OnModelCreating method:

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

With this filter, any query for Blog will automatically include WHERE IsDeleted = 0 (or equivalent).

5. Change Tracking and Concurrency Control

Concurrency Tokens

EF Core provides mechanisms for handling concurrent updates to the same data. A common approach is to use concurrency tokens, often implemented with a row version (like a timestamp) or a version number.

public class Blog
        {
            public int Id { get; set; }
            public string Url { get; set; }

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

When an update occurs, if the RowVersion in the database doesn't match the one EF Core is tracking, an DbUpdateConcurrencyException is thrown.

Handling Concurrency Conflicts

You can catch DbUpdateConcurrencyException and implement strategies to resolve the conflict, such as:

• Reloading the original values from the database.
• Merging changes.
• Aborting the operation.

6. Interceptors

Interceptors allow you to hook into EF Core's pipeline to inspect, modify, or perform actions before or after certain operations, such as query execution, saving changes, or opening connections.

To use interceptors, you need to implement an interface (e.g., DbCommandInterceptor, SaveChangesInterceptor) and register it with your DbContextOptionsBuilder.

services.AddDbContext<MyDbContext>(options =>
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))
           .AddInterceptors(new MyDbCommandInterceptor()));