IndianTechnoEra: Chapter 9: Performance Optimization and Advanced Querying

Welcome to Chapter 9! Performance optimization is critical for building production-ready applications. In this chapter, we'll explore advanced techniques to make your EF Core applications lightning fast. We'll cover everything from query optimization and indexing strategies to bulk operations, caching, and connection resiliency. By the end, you'll have a comprehensive toolkit for identifying and resolving performance bottlenecks.

---

9.1 Understanding EF Core Performance Fundamentals

Before diving into optimization techniques, let's understand where performance issues typically occur and how to measure them.

9.1.1 Common Performance Anti-Patterns

Anti-Pattern	Description	Impact
N+1 Queries	Loading related data one by one in a loop	Exponential database round trips
Client Evaluation	Filtering data in memory instead of database	Excessive data transfer and memory usage
Cartesian Explosion	Including multiple collection navigations	Duplicate data and huge result sets
Over-fetching	Selecting more columns than needed	Network and memory overhead
No Tracking Overhead	Tracking read-only data unnecessarily	Change tracker overhead
Multiple SaveChanges	Saving each entity individually	Multiple database round trips

9.1.2 Setting Up Performance Testing

using Microsoft.EntityFrameworkCore;
using System.Diagnostics;

public class PerformanceTest
{
    private readonly AppDbContext _context;
    private readonly Stopwatch _stopwatch = new();

    public PerformanceTest(AppDbContext context)
    {
        _context = context;
    }

    public void Measure(string operationName, Action action)
    {
        _stopwatch.Restart();
        action();
        _stopwatch.Stop();
        Console.WriteLine($"{operationName}: {_stopwatch.ElapsedMilliseconds}ms");
    }

    public async Task MeasureAsync(string operationName, Func<Task> action)
    {
        _stopwatch.Restart();
        await action();
        _stopwatch.Stop();
        Console.WriteLine($"{operationName}: {_stopwatch.ElapsedMilliseconds}ms");
    }
}

// Usage
using var db = new AppDbContext();
var tester = new PerformanceTest(db);

tester.Measure("Load 1000 books", () =>
{
    var books = db.Books.Take(1000).ToList();
});

tester.Measure("Load 1000 books with AsNoTracking", () =>
{
    var books = db.Books.AsNoTracking().Take(1000).ToList();
});

// Enable detailed logging
db.ChangeTracker.AutoDetectChangesEnabled = false;
db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

---

9.2 Query Optimization Techniques

9.2.1 AsNoTracking for Read-Only Queries

The change tracker adds significant overhead. For read-only queries, disable it.

using var db = new AppDbContext();

// Without tracking - no change tracker overhead
var books = db.Books
    .AsNoTracking()
    .Where(b => b.Price > 20)
    .ToList();

// Without tracking with identity resolution (deduplicates, no change tracking)
var booksWithAuthors = db.Books
    .AsNoTrackingWithIdentityResolution()
    .Include(b => b.Author)
    .ToList();

// Set globally for all queries (in DbContext configuration)
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString)
        .UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);
}

// Performance comparison
Console.WriteLine("With Tracking:");
tester.Measure("1000 books", () => db.Books.Take(1000).ToList());

Console.WriteLine("\nWithout Tracking:");
tester.Measure("1000 books", () => db.Books.AsNoTracking().Take(1000).ToList());

// Typical improvement: 20-40% faster

9.2.2 Projection with Select (Minimize Data Transfer)

Only select the columns you actually need.

using var db = new AppDbContext();

// Bad: Fetches all columns
var allBooks = db.Books.ToList();

// Good: Fetches only needed columns
var bookTitles = db.Books
    .Select(b => new { b.Id, b.Title, b.Price })
    .ToList();

// Even better: Project to DTO
public class BookListDto
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string AuthorName { get; set; }
    public decimal Price { get; set; }
    public int ReviewCount { get; set; }
}

var bookDtos = db.Books
    .Select(b => new BookListDto
    {
        Id = b.Id,
        Title = b.Title,
        AuthorName = b.Author != null ? b.Author.FirstName + " " + b.Author.LastName : null,
        Price = b.Price,
        ReviewCount = b.Reviews.Count
    })
    .ToList();

// SQL generated only includes needed columns
// SELECT [b].[Id], [b].[Title], [a].[FirstName], [a].[LastName], [b].[Price], 
//        (SELECT COUNT(*) FROM [Reviews] AS [r] WHERE [b].[Id] = [r].[BookId])
// FROM [Books] AS [b]
// LEFT JOIN [Authors] AS [a] ON [b].[AuthorId] = [a].[Id]

9.2.3 Split Queries to Avoid Cartesian Explosion

When including multiple collections, a single query can cause a Cartesian product explosion.

using var db = new AppDbContext();

// Problem: Single query with multiple joins
var booksWithData = db.Books
    .Include(b => b.Author)
    .Include(b => b.Categories)
    .Include(b => b.Reviews)
    .ToList();
// This creates a Cartesian product: Books × Categories × Reviews
// If a book has 5 categories and 10 reviews, that's 50 rows!

// Solution: Split queries into multiple round trips
var booksSplit = db.Books
    .Include(b => b.Author)
    .Include(b => b.Categories)
    .Include(b => b.Reviews)
    .AsSplitQuery() // Executes separate queries
    .ToList();

// Configure globally
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(connectionString, options =>
        options.UseQuerySplittingBehavior(QuerySplittingBehavior.SplitQuery));
}

// Performance comparison
tester.Measure("Single query with includes", () =>
{
    var books = db.Books
        .Include(b => b.Categories)
        .Include(b => b.Reviews)
        .Take(100)
        .ToList();
});

tester.Measure("Split queries", () =>
{
    var books = db.Books
        .Include(b => b.Categories)
        .Include(b => b.Reviews)
        .AsSplitQuery()
        .Take(100)
        .ToList();
});

9.2.4 Compiled Queries for Repeated Use

Compiled queries eliminate the overhead of query translation.

using Microsoft.EntityFrameworkCore;

// Static compiled query
private static readonly Func<AppDbContext, decimal, List<Book>> _getBooksByPrice =
    EF.CompileQuery((AppDbContext context, decimal minPrice) =>
        context.Books
            .Where(b => b.Price > minPrice)
            .OrderBy(b => b.Title)
            .Take(50)
            .ToList());

// Async version
private static readonly Func<AppDbContext, decimal, Task<List<Book>>> _getBooksByPriceAsync =
    EF.CompileAsyncQuery((AppDbContext context, decimal minPrice) =>
        context.Books
            .Where(b => b.Price > minPrice)
            .OrderBy(b => b.Title)
            .Take(50)
            .ToList());

// With includes
private static readonly Func<<t;AppDbContext, decimal, ListBook>> _getBooksWithAuthor =
    EF.CompileQuery((AppDbContext context, decimal minPrice) =>
        context.Books
            .Include(b => b.Author)
            .Where(b => b.Price > minPrice)
            .ToList());

// Usage
using var db = new AppDbContext();

var cheapBooks = _getBooksByPrice(db, 15);
var cheapBooksAsync = await _getBooksByPriceAsync(db, 15);

// Performance comparison
tester.Measure("Regular query (first run)", () =>
{
    var books = db.Books.Where(b => b.Price > 15).Take(50).ToList();
});

tester.Measure("Regular query (second run)", () =>
{
    var books = db.Books.Where(b => b.Price > 15).Take(50).ToList();
});

tester.Measure("Compiled query", () =>
{
    var books = _getBooksByPrice(db, 15);
});

// Compiled queries are 10-30% faster after first execution

9.2.5 Query Filters and Global Filters

// Soft delete filter
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity()
        .HasQueryFilter(b => !b.IsDeleted);
    
    modelBuilder.Entity()
        .HasQueryFilter(b => b.TenantId == _currentTenantId);
}

// Disable filters when needed
var allBooks = db.Books
    .IgnoreQueryFilters()
    .ToList();

// Multi-tenant filter example
public class TenantDbContext : DbContext
{
    private readonly int _tenantId;
    
    public TenantDbContext(int tenantId)
    {
        _tenantId = tenantId;
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity().HasQueryFilter(b => b.TenantId == _tenantId);
    }
}

---

9.3 Indexing Strategies

9.3.1 Creating Indexes with Fluent API

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Single column index
    modelBuilder.Entity()
        .HasIndex(b => b.Title)
        .HasDatabaseName("IX_Books_Title");
    
    // Unique index
    modelBuilder.Entity()
        .HasIndex(b => b.ISBN)
        .IsUnique()
        .HasDatabaseName("IX_Books_ISBN_Unique");
    
    // Composite index (multiple columns)
    modelBuilder.Entity()
        .HasIndex(b => new { b.Genre, b.Price })
        .HasDatabaseName("IX_Books_Genre_Price");
    
    // Include columns (cover index)
    modelBuilder.Entity()
        .HasIndex(b => b.Genre)
        .IncludeProperties(b => new { b.Title, b.Price })
        .HasDatabaseName("IX_Books_Genre_Included");
    
    // Filtered index
    modelBuilder.Entity()
        .HasIndex(b => b.PublishedOn)
        .HasFilter("[PublishedOn] IS NOT NULL")
        .HasDatabaseName("IX_Books_PublishedOn_Filtered");
    
    // Clustered index (rarely changed)
    modelBuilder.Entity()
        .HasKey(b => b.Id)
        .HasName("PK_Books");
    
    // Full-text index (requires raw SQL)
}

// Create indexes via migration
public partial class AddIndexes : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Single column index
        migrationBuilder.CreateIndex(
            name: "IX_Books_Title",
            table: "Books",
            column: "Title");
        
        // Unique index
        migrationBuilder.CreateIndex(
            name: "IX_Books_ISBN_Unique",
            table: "Books",
            column: "ISBN",
            unique: true);
        
        // Composite index
        migrationBuilder.CreateIndex(
            name: "IX_Books_Genre_Price",
            table: "Books",
            columns: new[] { "Genre", "Price" });
        
        // Full-text index
        migrationBuilder.Sql(@"
            CREATE FULLTEXT CATALOG BooksCatalog AS DEFAULT;
            CREATE FULLTEXT INDEX ON Books(Title)
            KEY INDEX PK_Books
            WITH STOPLIST = SYSTEM;
        ");
    }
}

9.3.2 Index Usage Guidelines

Column Usage	Index Recommendation	Example
Foreign Keys	Always index	AuthorId, CategoryId
WHERE clause columns	Index frequently filtered columns	Genre, Price, PublishedOn
ORDER BY columns	Index for sorting	Title, Price
JOIN columns	Index foreign keys	BookId in Reviews table
Low cardinality (e.g., boolean)	Consider filtered index	WHERE IsDeleted = 0
Text search	Full-text index	Title, Description

9.3.3 Analyzing Query Performance with SQL Server

// Enable query logging to analyze generated SQL
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(connectionString)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging()
        .EnableDetailedErrors();
}

// Get actual execution plan (run in SSMS with your actual query)
/*
SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT [b].[Id], [b].[Title], [b].[Price], [b].[Genre]
FROM [Books] AS [b]
WHERE [b].[Price] > 20
ORDER BY [b].[Title];
*/

// Check index usage in SQL Server
/*
SELECT 
    OBJECT_NAME(s.object_id) AS TableName,
    i.name AS IndexName,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE database_id = DB_ID('EFCoreBookStore')
ORDER BY TableName, IndexName;
*/

---

9.4 Bulk Operations

9.4.1 ExecuteDelete and ExecuteUpdate (EF Core 7+)

These methods execute SQL directly without loading entities into memory.

using var db = new AppDbContext();

// Bulk delete - delete all cheap books
int deleted = db.Books
    .Where(b => b.Price < 10)
    .ExecuteDelete();

// Bulk update - increase all fantasy book prices by 10%
int updated = db.Books
    .Where(b => b.Genre == "Fantasy")
    .ExecuteUpdate(setters => setters
        .SetProperty(b => b.Price, b => b.Price * 1.1m)
        .SetProperty(b => b.LastUpdated, DateTime.Now));

// Update with multiple properties
int updated2 = db.Books
    .Where(b => b.Price < 15)
    .ExecuteUpdate(setters => setters
        .SetProperty(b => b.Price, 14.99m)
        .SetProperty(b => b.Genre, "Budget")
        .SetProperty(b => b.Status, BookStatus.Discounted));

// Conditional updates
int updated3 = db.Books
    .Where(b => b.Genre == "Horror")
    .ExecuteUpdate(setters => setters
        .SetProperty(b => b.Price, b => b.Price > 20 ? b.Price * 0.9m : b.Price * 0.95m));

// Generated SQL:
// UPDATE [Books] SET [Price] = [Price] * 1.1, [LastUpdated] = GETDATE()
// WHERE [Genre] = N'Fantasy'

9.4.2 Batch Operations with Third-Party Libraries

For more complex bulk operations, consider libraries like EFCore.BulkExtensions.

// Install: dotnet add package EFCore.BulkExtensions

using EFCore.BulkExtensions;

using var db = new AppDbContext();

// Bulk insert
var books = Enumerable.Range(1, 10000).Select(i => new Book
{
    Title = $"Book {i}",
    Price = i * 10,
    Genre = "Test"
}).ToList();

await db.BulkInsertAsync(books);

// Bulk update
books.ForEach(b => b.Price *= 1.1m);
await db.BulkUpdateAsync(books);

// Bulk delete
await db.BulkDeleteAsync(books);

// Bulk insert/update (upsert)
await db.BulkInsertOrUpdateAsync(books);

// Performance comparison
tester.MeasureAsync("1000 inserts with SaveChanges", async () =>
{
    for (int i = 0; i < 1000; i++)
    {
        db.Books.Add(new Book { Title = $"Book {i}", Price = i });
        await db.SaveChangesAsync();
    }
});

tester.MeasureAsync("1000 inserts with AddRange", async () =>
{
    var booksList = Enumerable.Range(1, 1000).Select(i => new Book
    {
        Title = $"Book {i}",
        Price = i
    }).ToList();
    
    db.Books.AddRange(booksList);
    await db.SaveChangesAsync();
});

tester.MeasureAsync("1000 inserts with BulkInsert", async () =>
{
    var booksList = Enumerable.Range(1, 1000).Select(i => new Book
    {
        Title = $"Book {i}",
        Price = i
    }).ToList();
    
    await db.BulkInsertAsync(booksList);
});

// Typical results:
// SaveChanges (1000 times): ~5000ms
// AddRange + SaveChanges: ~200ms
// BulkInsert: ~50ms

9.4.3 Upsert Operations

using var db = new AppDbContext();

// Custom upsert using ExecuteSql
var book = new Book { Id = 1, Title = "Updated Title", Price = 29.99m };

await db.Database.ExecuteSqlInterpolatedAsync($@"
    MERGE INTO Books AS Target
    USING (SELECT {book.Id} AS Id, {book.Title} AS Title, {book.Price} AS Price) AS Source
    ON Target.Id = Source.Id
    WHEN MATCHED THEN
        UPDATE SET Title = Source.Title, Price = Source.Price
    WHEN NOT MATCHED THEN
        INSERT (Title, Price) VALUES (Source.Title, Source.Price);
");

// Using EFCore.BulkExtensions for upsert
await db.BulkInsertOrUpdateAsync(new List<Book> { book });

---

9.5 Connection Resiliency and Retry Logic

For production applications, configure retry logic to handle transient failures.

9.5.1 Configuring Retry Patterns

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        connectionString,
        options => options
            .EnableRetryOnFailure(
                maxRetryCount: 3,
                maxRetryDelay: TimeSpan.FromSeconds(30),
                errorNumbersToAdd: null)
            .CommandTimeout(60));
}

// Or in Program.cs for ASP.NET Core
builder.Services.AddDbContextA<ppDbContext>(options =>
    options.UseSqlServer(connectionString, sqlOptions =>
    {
        sqlOptions.EnableRetryOnFailure(
            maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: new[] { 4060 }); // Database not found
        sqlOptions.CommandTimeout(60);
    }));

9.5.2 Custom Retry Logic with Execution Strategy

using Microsoft.EntityFrameworkCore.Storage;

public class ResilientQueryExecutor
{
    private readonly AppDbContext _context;
    private readonly IExecutionStrategy _strategy;

    public ResilientQueryExecutor(AppDbContext context)
    {
        _context = context;
        _strategy = _context.Database.CreateExecutionStrategy();
    }

    public async Task<T> ExecuteAsync<T>(Func<Task<T>> operation)
    {
        return await _strategy.ExecuteAsync(async () =>
        {
            try
            {
                return await operation();
            }
            catch (Exception ex) when (IsTransientException(ex))
            {
                Console.WriteLine($"Transient error: {ex.Message}");
                throw; // Let execution strategy handle retry
            }
        });
    }

    private bool IsTransientException(Exception ex)
    {
        // Check for SQL Server transient errors
        return ex is SqlException sqlEx && 
               (sqlEx.Number == 1205 || // Deadlock
                sqlEx.Number == 1222 || // Lock request timeout
                sqlEx.Number == 49918 || // Resource governor
                sqlEx.Number == 49919); // Throttling
    }
} 

// Usage
var executor = new ResilientQueryExecutor(db);
var books = await executor.ExecuteAsync(async () =>
{
    return await db.Books.AsNoTracking().ToListAsync();
});

9.5.3 Handling Deadlocks

public async Task UpdateWithRetryAsync(int bookId, decimal newPrice)
{
    var executionStrategy = db.Database.CreateExecutionStrategy();
    
    await executionStrategy.ExecuteAsync(async () =>
    {
        using var transaction = await db.Database.BeginTransactionAsync(
            IsolationLevel.ReadCommitted);
        
        try
        {
            var book = await db.Books.FindAsync(bookId);
            if (book != null)
            {
                book.Price = newPrice;
                await db.SaveChangesAsync();
            }
            
            await transaction.CommitAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            // Handle concurrency
            await transaction.RollbackAsync();
            throw;
        }
    });
}

---

9.6 Caching Strategies

9.6.1 In-Memory Caching

using Microsoft.Extensions.Caching.Memory;

public class CachedBookService
{
    private readonly AppDbContext _context;
    private readonly IMemoryCache _cache;
    private readonly TimeSpan _cacheDuration = TimeSpan.FromMinutes(5);

    public CachedBookService(AppDbContext context, IMemoryCache cache)
    {
        _context = context;
        _cache = cache;
    }

    public async Task<List<Book>> GetPopularBooksAsync()
    {
        string cacheKey = "popular_books";
        
        // Try to get from cache
        if (_cache.TryGetValue(cacheKey, out List<Book> cachedBooks))
        {
            return cachedBooks;
        }
        
        // Not in cache, query database
        var books = await _context.Books
            .AsNoTracking()
            .Where(b => b.Reviews.Any(r => r.Rating >= 4))
            .OrderByDescending(b => b.Reviews.Count)
            .Take(10)
            .ToListAsync();
        
        // Store in cache
        _cache.Set(cacheKey, books, new MemoryCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = _cacheDuration,
            SlidingExpiration = TimeSpan.FromMinutes(1),
            Priority = CacheItemPriority.High
        });
        
        return books;
    }

    public async Task<Book> GetBookWithCacheAsync(int id)
    {
        string cacheKey = $"book_{id}";
        
        if (!_cache.TryGetValue(cacheKey, out Book book))
        {
            book = await _context.Books
                .AsNoTracking()
                .Include(b => b.Author)
                .Include(b => b.Reviews)
                .FirstOrDefaultAsync(b => b.Id == id);
            
            if (book != null)
            {
                _cache.Set(cacheKey, book, TimeSpan.FromMinutes(10));
            }
        }
        
        return book;
    }

    public void InvalidateBookCache(int id)
    {
        _cache.Remove($"book_{id}");
        _cache.Remove("popular_books");
    }
}

9.6.2 Distributed Caching with Redis

// Install: dotnet add package Microsoft.Extensions.Caching.StackExchangeRedis
// Install: dotnet add package Newtonsoft.Json

using Microsoft.Extensions.Caching.Distributed;
using System.Text.Json;

public class RedisCachedBookService
{
    private readonly AppDbContext _context;
    private readonly IDistributedCache _cache;
    private readonly DistributedCacheEntryOptions _cacheOptions;

    public RedisCachedBookService(AppDbContext context, IDistributedCache cache)
    {
        _context = context;
        _cache = cache;
        _cacheOptions = new DistributedCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10),
            SlidingExpiration = TimeSpan.FromMinutes(2)
        };
    }

    public async Task<List<Book>> GetBooksByGenreAsync(string genre)
    {
        string cacheKey = $"books_genre_{genre}";
        
        // Try to get from Redis
        var cachedData = await _cache.GetStringAsync(cacheKey);
        
        if (cachedData != null)
        {
            return JsonSerializer.Deserialize<List<Book>>(cachedData);
        }
        
        // Not in cache, query database
        var books = await _context.Books
            .AsNoTracking()
            .Where(b => b.Genre == genre)
            .ToListAsync();
        
        // Store in Redis
        var serialized = JsonSerializer.Serialize(books);
        await _cache.SetStringAsync(cacheKey, serialized, _cacheOptions);
        
        return books;
    }

    public async Task InvalidateCacheByPatternAsync(string pattern)
    {
        // Redis doesn't support pattern deletion directly
        // Use Redis keys command (but be careful in production)
        var server = GetRedisServer(); // Get from connection multiplexer
        var keys = server.Keys(pattern: $"*{pattern}*").ToList();
        
        foreach (var key in keys)
        {
            await _cache.RemoveAsync(key);
        }
    }
}

9.6.3 Second-Level Cache with EF Core Plus

// Install: dotnet add package Z.EntityFramework.Plus.EFCore

using Z.EntityFramework.Plus;

// Configure query cache
public class CachedQueryService
{
    public async Task<List<Book>> GetExpensiveBooksAsync()
    {
        return await db.Books
            .Where(b => b.Price > 50)
            .FromCacheAsync(CacheExpirationMode.Absolute, TimeSpan.FromMinutes(5));
    }
    
    // Configure cache tags for invalidation
    public async Task<List<Book>> GetBooksByAuthorAsync(int authorId)
    {
        return await db.Books
            .Where(b => b.AuthorId == authorId)
            .FromCacheAsync(new [] { $"author_{authorId}_books" });
    }
    
    // Invalidate cache by tag
    public void InvalidateAuthorCache(int authorId)
    {
        QueryCacheManager.ExpireTag($"author_{authorId}_books");
    }
}

---

9.7 Advanced Query Techniques

9.7.1 Raw SQL for Complex Queries

using var db = new AppDbContext();

// Execute raw SQL for complex scenarios
var books = db.Books
    .FromSqlRaw(@"
        SELECT b.*, a.FirstName, a.LastName
        FROM Books b
        INNER JOIN Authors a ON b.AuthorId = a.Id
        WHERE b.PublishedOn > @p0
        ORDER BY b.Price DESC",
        new SqlParameter("@p0", new DateTime(2000, 1, 1)))
    .ToList();

// With interpolated (safer)
var cutoffDate = new DateTime(2000, 1, 1);
var books2 = db.Books
    .FromSqlInterpolated($@"
        SELECT * FROM Books 
        WHERE PublishedOn > {cutoffDate}
        ORDER BY Price DESC")
    .ToList();

// Execute non-query
int affected = db.Database.ExecuteSqlRaw(
    "UPDATE Books SET Price = Price * 1.1 WHERE Genre = 'Fantasy'");

// Execute scalar
var averagePrice = db.Database
    .SqlQueryRaw<decimal>("SELECT AVG(Price) FROM Books")
    .FirstOrDefault();

// Stored procedure
var popularBooks = db.Books
    .FromSqlRaw("EXEC GetPopularBooks @minRating = {0}, @limit = {1}", 4, 10)
    .ToList();

9.7.2 Common Table Expressions (CTEs)

// Recursive CTE for hierarchical data
var categoryHierarchy = db.Categories
    .FromSqlRaw(@"
        WITH CategoryHierarchy AS (
            SELECT Id, Name, ParentCategoryId, 0 AS Level
            FROM Categories
            WHERE ParentCategoryId IS NULL
            
            UNION ALL
            
            SELECT c.Id, c.Name, c.ParentCategoryId, ch.Level + 1
            FROM Categories c
            INNER JOIN CategoryHierarchy ch ON c.ParentCategoryId = ch.Id
        )
        SELECT * FROM CategoryHierarchy
        ORDER BY Level, Name")
    .ToList();

// CTE for pagination with total count
var query = @"
    WITH OrderedBooks AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY Title) AS RowNum
        FROM Books
        WHERE Genre = @Genre
    )
    SELECT * FROM OrderedBooks
    WHERE RowNum BETWEEN @Start AND @End";

var pagedBooks = db.Books
    .FromSqlRaw(query, 
        new SqlParameter("@Genre", "Fantasy"),
        new SqlParameter("@Start", 21),
        new SqlParameter("@End", 40))
    .ToList();

9.7.3 Window Functions

// Rank books by price within genre
var rankedBooks = db.Books
    .FromSqlRaw(@"
        SELECT 
            Id,
            Title,
            Genre,
            Price,
            RANK() OVER (PARTITION BY Genre ORDER BY Price DESC) AS PriceRank
        FROM Books")
    .Select(b => new
    {
        b.Id,
        b.Title,
        b.Genre,
        b.Price,
        Rank = b.PriceRank
    })
    .ToList();

// Get top 3 per genre
var topByGenre = db.Books
    .FromSqlRaw(@"
        SELECT * FROM (
            SELECT *,
                ROW_NUMBER() OVER (PARTITION BY Genre ORDER BY Price DESC) AS RowNum
            FROM Books
        ) AS Ranked
        WHERE RowNum <= 3")
    .ToList();

---

9.8 Memory Management and Large Result Sets

9.8.1 Streaming with AsAsyncEnumerable

// Process large result sets without loading all into memory
await foreach (var book in db.Books.AsAsyncEnumerable())
{
    // Process each book one by one
    ProcessBook(book);
}

// With filtering and projection
await foreach (var bookTitle in db.Books
    .Where(b => b.Price > 20)
    .Select(b => b.Title)
    .AsAsyncEnumerable())
{
    Console.WriteLine(bookTitle);
}

// Manual streaming with ExecuteReader
using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

using var command = new SqlCommand("SELECT * FROM Books WHERE Price > 20", connection);
using var reader = await command.ExecuteReaderAsync();

while (await reader.ReadAsync())
{
    var book = new Book
    {
        Id = reader.GetInt32(0),
        Title = reader.GetString(1),
        Price = reader.GetDecimal(2)
    };
    ProcessBook(book);
}

9.8.2 Pagination Strategies

// Keyset pagination (better than OFFSET/FETCH for large datasets)
public async Task<List<Book>> GetBooksKeysetPaginationAsync(
    string lastTitle, 
    decimal lastPrice, 
    int pageSize)
{
    return await db.Books
        .Where(b => b.Price > lastPrice || 
                    (b.Price == lastPrice && string.Compare(b.Title, lastTitle) > 0))
        .OrderBy(b => b.Price)
        .ThenBy(b => b.Title)
        .Take(pageSize)
        .ToListAsync();
}

// Traditional OFFSET/FETCH (simpler but slower for deep pagination)
public async Task<PagedResult<Book>> GetBooksOffsetPaginationAsync(
    int pageNumber, 
    int pageSize)
{
    var query = db.Books.OrderBy(b => b.Title);
    
    var totalCount = await query.CountAsync();
    
    var items = await query
        .Skip((pageNumber - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();
    
    return new PagedResult<Book>
    {
        Items = items,
        PageNumber = pageNumber,
        PageSize = pageSize,
        TotalCount = totalCount
    };
}

---

9.9 Profiling and Monitoring

9.9.1 Query Logging with MiniProfiler

// Install: dotnet add package MiniProfiler.EntityFrameworkCore

using StackExchange.Profiling;

public class ProfiledDbContext : AppDbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(connectionString)
            .UseMiniProfiler();
    }
}

// In ASP.NET Core
app.UseMiniProfiler();

// In code
using (MiniProfiler.Current.Step("Loading Books"))
{
    var books = db.Books
        .Where(b => b.Price > 20)
        .ToList();
}

9.9.2 Performance Monitoring with Application Insights

// In ASP.NET Core Program.cs
builder.Services.AddApplicationInsightsTelemetry();

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
        .EnableSensitiveDataLogging()
        .EnableDetailedErrors());

// Track dependencies automatically
// Query times will appear in Application Insights

9.9.3 Custom Performance Counters

public class PerformanceMonitor
{
    private readonly Stopwatch _stopwatch = new();
    private int _queryCount;
    private long _totalQueryTimeMs;

    public void RecordQuery(Action query)
    {
        _stopwatch.Restart();
        query();
        _stopwatch.Stop();
        
        _queryCount++;
        _totalQueryTimeMs += _stopwatch.ElapsedMilliseconds;
        
        if (_stopwatch.ElapsedMilliseconds > 1000)
        {
            Console.WriteLine($"Slow query detected: {_stopwatch.ElapsedMilliseconds}ms");
        }
    }

    public void Report()
    {
        Console.WriteLine($"Total Queries: {_queryCount}");
        Console.WriteLine($"Average Query Time: {_totalQueryTimeMs / _queryCount:F2}ms");
        Console.WriteLine($"Total Query Time: {_totalQueryTimeMs}ms");
    }
}

---

9.10 Performance Checklist and Best Practices

Quick Reference Checklist

Category	Best Practice	Impact
Querying	Use AsNoTracking for read-only queries	Medium-High
Querying	Project with Select instead of loading entire entities	High
Querying	Use AsSplitQuery when including multiple collections	High
Indexing	Create indexes on foreign keys and WHERE clauses	Very High
Bulk Operations	Use ExecuteDelete/ExecuteUpdate for bulk changes	Very High
Bulk Operations	Use AddRange instead of multiple Add calls	Medium
Transactions	Group SaveChanges calls	Medium
Caching	Cache frequently accessed read-only data	High
Connections	Configure retry logic for transient failures	Medium
Memory	Use streaming for large result sets	High

Performance Optimization Summary

• Always start with AsNoTracking() for read-only queries - eliminates change tracker overhead
• Project only needed columns with Select() - reduces data transfer and memory usage
• Use AsSplitQuery() when including multiple collections - prevents Cartesian explosion
• Add indexes on columns used in WHERE, ORDER BY, and JOIN clauses
• Use ExecuteDelete/ExecuteUpdate for bulk operations instead of loading entities
• Implement pagination for large result sets - never load all records at once
• Cache frequently accessed data with IMemoryCache or IDistributedCache
• Monitor and log slow queries to identify bottlenecks
• Configure connection resiliency for production environments
• Use compiled queries for frequently executed parameterized queries

---

Chapter Summary

Technique	Key Points
AsNoTracking	Eliminates change tracker overhead for read-only queries. Essential for high-performance reads.
Projection	Select only needed columns. Reduces network traffic and memory usage.
Split Queries	Prevents Cartesian explosion when including multiple collections. More round trips but less data duplication.
Compiled Queries	Cache query translation. Best for frequently executed parameterized queries.
Indexes	Critical for query performance. Create on FK, WHERE, ORDER BY columns.
Bulk Operations	ExecuteDelete/ExecuteUpdate for direct SQL. 10-100x faster than loading entities.
Caching	Cache frequently accessed read-only data. Reduces database load significantly.
Retry Logic	Handle transient failures in cloud environments. Essential for production resiliency.
Streaming	Process large result sets without loading all into memory. Use AsAsyncEnumerable.
Pagination	Use keyset pagination for large datasets. Avoid deep OFFSET pagination.

---

What's Next?

Congratulations! You've now mastered performance optimization in EF Core. You have the tools to build fast, scalable, production-ready applications.

In Chapter 10: Production Best Practices and Architecture, we will:

• Design clean architecture with EF Core
• Implement repository and unit of work patterns
• Manage DbContext lifetime in different application types
• Handle database migrations in production
• Secure your application against SQL injection
• Implement audit logging and soft delete
• Configure logging and monitoring in production
• Deploy and maintain EF Core applications

Get ready to become a true EF Core expert!

---