Entity Framework Core Advanced Topics

Advanced Querying with LINQ

LINQ Fundamentals and Advanced Patterns

LINQ (Language Integrated Query) provides a consistent query syntax for various data sources. In EF Core, LINQ translates to SQL queries executed against the database.

Basic LINQ Operations

public class ProductRepository
{
    private readonly AppDbContext _context;

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

    // Filtering
    public async Task<IEnumerable<Product>> GetProductsByCategory(string category)
    {
        return await _context.Products
            .Where(p => p.Category.Name == category)
            .ToListAsync();
    }

    // Ordering
    public async Task<IEnumerable<Product>> GetProductsOrderedByPrice()
    {
        return await _context.Products
            .OrderBy(p => p.Price)
            .ThenBy(p => p.Name)
            .ToListAsync();
    }

    // Projection
    public async Task<IEnumerable<ProductSummary>> GetProductSummaries()
    {
        return await _context.Products
            .Select(p => new ProductSummary
            {
                Id = p.Id,
                Name = p.Name,
                Price = p.Price,
                CategoryName = p.Category.Name
            })
            .ToListAsync();
    }
}

Advanced Filtering and Conditional Queries

public async Task<IEnumerable<Product>> GetProductsWithFilters(
    string name = null,
    decimal? minPrice = null,
    decimal? maxPrice = null,
    string category = null,
    bool? inStock = null)
{
    var query = _context.Products.AsQueryable();

    if (!string.IsNullOrEmpty(name))
        query = query.Where(p => p.Name.Contains(name));

    if (minPrice.HasValue)
        query = query.Where(p => p.Price >= minPrice.Value);

    if (maxPrice.HasValue)
        query = query.Where(p => p.Price <= maxPrice.Value);

    if (!string.IsNullOrEmpty(category))
        query = query.Where(p => p.Category.Name == category);

    if (inStock.HasValue)
        query = query.Where(p => p.StockQuantity > 0 == inStock.Value);

    return await query.ToListAsync();
}

Joins and Relationships

// Inner Join
public async Task<IEnumerable<OrderDetail>> GetOrderDetailsWithProducts()
{
    return await _context.OrderDetails
        .Include(od => od.Product)
        .Include(od => od.Order)
        .ToListAsync();
}

// Left Join
public async Task<IEnumerable<Product>> GetProductsWithOptionalCategory()
{
    return await (from p in _context.Products
                  join c in _context.Categories on p.CategoryId equals c.Id into pc
                  from c in pc.DefaultIfEmpty()
                  select new Product
                  {
                      Id = p.Id,
                      Name = p.Name,
                      Category = c
                  })
                  .ToListAsync();
}

// Group Join
public async Task<IEnumerable<CategoryWithProducts>> GetCategoriesWithProducts()
{
    return await _context.Categories
        .GroupJoin(_context.Products,
            c => c.Id,
            p => p.CategoryId,
            (category, products) => new CategoryWithProducts
            {
                Category = category,
                Products = products.ToList()
            })
        .ToListAsync();
}

Grouping and Aggregation

public async Task<IEnumerable<CategorySales>> GetCategorySales()
{
    return await _context.OrderDetails
        .Include(od => od.Product)
        .ThenInclude(p => p.Category)
        .GroupBy(od => od.Product.Category.Name)
        .Select(g => new CategorySales
        {
            CategoryName = g.Key,
            TotalSales = g.Sum(od => od.Quantity * od.UnitPrice),
            TotalOrders = g.Count(),
            AverageOrderValue = g.Average(od => od.Quantity * od.UnitPrice)
        })
        .ToListAsync();
}

public async Task<SalesReport> GetSalesReport(DateTime startDate, DateTime endDate)
{
    var report = await _context.Orders
        .Where(o => o.OrderDate >= startDate && o.OrderDate <= endDate)
        .GroupBy(o => new { o.OrderDate.Year, o.OrderDate.Month })
        .Select(g => new MonthlySales
        {
            Year = g.Key.Year,
            Month = g.Key.Month,
            TotalSales = g.Sum(o => o.TotalAmount),
            OrderCount = g.Count()
        })
        .OrderBy(ms => ms.Year)
        .ThenBy(ms => ms.Month)
        .ToListAsync();

    return new SalesReport { MonthlySales = report };
}

Advanced Projections and Anonymous Types

public async Task<IEnumerable<dynamic>> GetProductAnalytics()
{
    return await _context.Products
        .Select(p => new
        {
            ProductId = p.Id,
            ProductName = p.Name,
            CategoryName = p.Category.Name,
            Price = p.Price,
            StockValue = p.StockQuantity * p.Price,
            IsHighValue = p.Price > 1000,
            PriceCategory = p.Price switch
            {
                <= 100 => "Budget",
                <= 500 => "Mid-range",
                <= 1000 => "Premium",
                _ => "Luxury"
            }
        })
        .ToListAsync();
}

Subqueries and Exists

// Subquery with Any/All
public async Task<IEnumerable<Customer>> GetCustomersWithRecentOrders()
{
    var recentDate = DateTime.Now.AddDays(-30);

    return await _context.Customers
        .Where(c => _context.Orders
            .Any(o => o.CustomerId == c.Id && o.OrderDate >= recentDate))
        .ToListAsync();
}

// Correlated subquery
public async Task<IEnumerable<Product>> GetProductsAboveAveragePrice()
{
    var averagePrice = await _context.Products.AverageAsync(p => p.Price);

    return await _context.Products
        .Where(p => p.Price > averagePrice)
        .ToListAsync();
}

Window Functions and Ranking

public async Task<IEnumerable<ProductRank>> GetProductRanksByCategory()
{
    return await _context.Products
        .OrderBy(p => p.CategoryId)
        .ThenByDescending(p => p.SalesCount)
        .Select((p, index) => new ProductRank
        {
            ProductId = p.Id,
            ProductName = p.Name,
            CategoryId = p.CategoryId,
            SalesCount = p.SalesCount,
            RankInCategory = index + 1
        })
        .ToListAsync();
}

Complex Queries with Multiple Joins

public async Task<IEnumerable<OrderSummary>> GetOrderSummaries()
{
    return await (from o in _context.Orders
                  join c in _context.Customers on o.CustomerId equals c.Id
                  join od in _context.OrderDetails on o.Id equals od.OrderId into orderDetails
                  from od in orderDetails.DefaultIfEmpty()
                  join p in _context.Products on od.ProductId equals p.Id into products
                  from p in products.DefaultIfEmpty()
                  group new { o, c, od, p } by new
                  {
                      OrderId = o.Id,
                      OrderDate = o.OrderDate,
                      CustomerName = c.Name
                  } into g
                  select new OrderSummary
                  {
                      OrderId = g.Key.OrderId,
                      OrderDate = g.Key.OrderDate,
                      CustomerName = g.Key.CustomerName,
                      TotalAmount = g.Sum(x => (x.od != null ? x.od.Quantity * x.od.UnitPrice : 0)),
                      ItemCount = g.Count(x => x.od != null)
                  })
                  .ToListAsync();
}

LINQ Best Practices

Use AsNoTracking for read-only queries
Prefer Single/First over Where().First()
Use Include/ThenInclude judiciously to avoid over-fetching
Consider query syntax for complex joins
Use async methods for database operations
Profile queries to identify performance issues

Performance Optimization

Loading Strategies

EF Core provides different strategies for loading related data, each with performance implications.

Lazy Loading

Lazy loading loads related data only when accessed. It can cause N+1 query problems.

// Enable lazy loading in DbContext
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseLazyLoadingProxies();
}

// Usage - related data loaded on access
public async Task<Product> GetProductWithCategory(int id)
{
    var product = await _context.Products.FindAsync(id);
    // Category is loaded here when accessed
    var categoryName = product.Category.Name;
    return product;
}

Eager Loading

Eager loading loads related data in the initial query using Include/ThenInclude.

public async Task<IEnumerable<Product>> GetProductsWithCategories()
{
    return await _context.Products
        .Include(p => p.Category)
        .Include(p => p.Supplier)
        .ToListAsync();
}

// Multiple levels
public async Task<Order> GetOrderWithDetails(int orderId)
{
    return await _context.Orders
        .Include(o => o.Customer)
        .Include(o => o.OrderDetails)
            .ThenInclude(od => od.Product)
                .ThenInclude(p => p.Category)
        .FirstOrDefaultAsync(o => o.Id == orderId);
}

Explicit Loading

Explicit loading loads related data explicitly when needed.

public async Task<Product> GetProductWithCategoryExplicit(int id)
{
    var product = await _context.Products.FindAsync(id);

    // Load category explicitly
    await _context.Entry(product)
        .Reference(p => p.Category)
        .LoadAsync();

    // Load collection explicitly
    await _context.Entry(product)
        .Collection(p => p.Reviews)
        .LoadAsync();

    return product;
}

Select Loading (Projection)

Select loading loads only the required data using projections.

public async Task<IEnumerable<ProductSummary>> GetProductSummaries()
{
    return await _context.Products
        .Select(p => new ProductSummary
        {
            Id = p.Id,
            Name = p.Name,
            Price = p.Price,
            CategoryName = p.Category.Name,
            ReviewCount = p.Reviews.Count
        })
        .ToListAsync();
}

Query Optimization Techniques

Avoiding N+1 Queries

// Bad - N+1 queries
public async Task<IEnumerable<OrderSummary>> GetOrderSummariesBad()
{
    var orders = await _context.Orders.ToListAsync();

    foreach (var order in orders)
    {
        order.Customer = await _context.Customers
            .FindAsync(order.CustomerId); // N queries
    }

    return orders;
}

// Good - Single query with Include
public async Task<IEnumerable<OrderSummary>> GetOrderSummariesGood()
{
    return await _context.Orders
        .Include(o => o.Customer)
        .Select(o => new OrderSummary
        {
            OrderId = o.Id,
            CustomerName = o.Customer.Name,
            TotalAmount = o.TotalAmount
        })
        .ToListAsync();
}

Tracking vs No-Tracking Queries

// Tracking queries (default) - entities are tracked for changes
public async Task<Product> GetProductForUpdate(int id)
{
    return await _context.Products.FindAsync(id);
}

// No-tracking queries - better performance for read-only
public async Task<IEnumerable<Product>> GetProductsReadOnly()
{
    return await _context.Products
        .AsNoTracking()
        .ToListAsync();
}

// No-tracking with identity resolution
public async Task<IEnumerable<Product>> GetProductsWithIdentityResolution()
{
    return await _context.Products
        .AsNoTrackingWithIdentityResolution()
        .ToListAsync();
}

Query Splitting

// Split queries for better performance with multiple includes
public async Task<Order> GetOrderWithSplitQuery(int orderId)
{
    return await _context.Orders
        .Include(o => o.Customer)
        .Include(o => o.OrderDetails)
        .ThenInclude(od => od.Product)
        .AsSplitQuery()
        .FirstOrDefaultAsync(o => o.Id == orderId);
}

Batch Operations

public async Task BatchUpdateProducts()
{
    await _context.Products
        .Where(p => p.CategoryId == 1)
        .ExecuteUpdateAsync(p => p.SetProperty(
            x => x.Discount,
            x => x.Discount + 0.1));
}

public async Task BatchDeleteOldOrders()
{
    await _context.Orders
        .Where(o => o.OrderDate < DateTime.Now.AddYears(-2))
        .ExecuteDeleteAsync();
}

Compiled Queries

public static class CompiledQueries
{
    public static readonly Func<AppDbContext, int, Task<Product>> GetProductById =
        EF.CompileQuery((AppDbContext context, int id) =>
            context.Products
                .Include(p => p.Category)
                .FirstOrDefault(p => p.Id == id));
}

// Usage
var product = await CompiledQueries.GetProductById(_context, productId);

Connection Management and Pooling

// Configure connection pooling
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(connectionString,
        sqlOptions => sqlOptions.MaxBatchSize(100));
});

Performance Monitoring

public class PerformanceMonitoringContext : DbContext
{
    private readonly ILogger<PerformanceMonitoringContext> _logger;

    public PerformanceMonitoringContext(DbContextOptions options, ILogger<PerformanceMonitoringContext> logger)
        : base(options)
    {
        _logger = logger;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.LogTo(message => _logger.LogInformation(message),
            new[] { DbLoggerCategory.Database.Command.Name },
            LogLevel.Information,
            DbContextLoggerOptions.SingleLine | DbContextLoggerOptions.Level);
    }
}

Caching Strategies

public class CachedProductRepository
{
    private readonly AppDbContext _context;
    private readonly IDistributedCache _cache;

    public async Task<Product> GetProductById(int id)
    {
        var cacheKey = $"product_{id}";

        var cachedProduct = await _cache.GetStringAsync(cacheKey);
        if (cachedProduct != null)
        {
            return JsonSerializer.Deserialize<Product>(cachedProduct);
        }

        var product = await _context.Products
            .Include(p => p.Category)
            .FirstOrDefaultAsync(p => p.Id == id);

        if (product != null)
        {
            await _cache.SetStringAsync(cacheKey,
                JsonSerializer.Serialize(product),
                new DistributedCacheEntryOptions
                {
                    AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(10)
                });
        }

        return product;
    }
}

Performance Best Practices

Use AsNoTracking for read-only queries
Avoid N+1 queries with proper Include usage
Use projections to fetch only needed data
Implement caching for frequently accessed data
Monitor query performance with logging
Use batch operations for bulk updates/deletes
Consider compiled queries for frequently executed queries

Database Migrations and Seeding

Understanding Migrations

Migrations allow you to evolve your database schema over time while preserving data.

Creating and Applying Migrations

// Create a migration
dotnet ef migrations add InitialCreate

// Apply migrations
dotnet ef database update

// Revert to specific migration
dotnet ef database update PreviousMigration

// Remove last migration (if not applied)
dotnet ef migrations remove

Custom Migration Operations

[Migration("20231230120000_CustomMigration")]
public partial class CustomMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Create table with specific options
        migrationBuilder.CreateTable(
            name: "Products",
            columns: table => new
            {
                Id = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(maxLength: 100, nullable: false),
                Price = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
                CreatedAt = table.Column<DateTime>(nullable: false,
                    defaultValueSql: "GETUTCDATE()")
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Products", x => x.Id);
            });

        // Create index
        migrationBuilder.CreateIndex(
            name: "IX_Products_Name",
            table: "Products",
            column: "Name");

        // Add check constraint
        migrationBuilder.Sql("ALTER TABLE Products ADD CONSTRAINT CK_Products_Price_Positive CHECK (Price > 0)");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "Products");
    }
}

Migration Bundles

// Create migration bundle for production deployment
dotnet ef migrations bundle --output ./efbundle.exe

// Run bundle
./efbundle.exe --connection "Server=prod-server;Database=prod-db;..."

Handling Data Changes in Migrations

[Migration("20231230130000_AddCategoryColumn")]
public partial class AddCategoryColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Add column as nullable first
        migrationBuilder.AddColumn<string>(
            name: "Category",
            table: "Products",
            nullable: true);

        // Update existing data
        migrationBuilder.Sql("UPDATE Products SET Category = 'Uncategorized' WHERE Category IS NULL");

        // Make column non-nullable
        migrationBuilder.AlterColumn<string>(
            name: "Category",
            table: "Products",
            nullable: false,
            defaultValue: "Uncategorized");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "Category",
            table: "Products");
    }
}

Data Seeding

Data seeding populates the database with initial data.

Basic Seeding

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Seed data
        modelBuilder.Entity<Category>().HasData(
            new Category { Id = 1, Name = "Electronics", Description = "Electronic devices" },
            new Category { Id = 2, Name = "Books", Description = "Books and publications" },
            new Category { Id = 3, Name = "Clothing", Description = "Clothing and accessories" }
        );

        modelBuilder.Entity<Product>().HasData(
            new Product { Id = 1, Name = "Laptop", Price = 999.99m, CategoryId = 1 },
            new Product { Id = 2, Name = "Smartphone", Price = 699.99m, CategoryId = 1 }
        );
    }
}

Advanced Seeding with IEntityTypeConfiguration

public class CategoryConfiguration : IEntityTypeConfiguration<Category>
{
    public void Configure(EntityTypeBuilder<Category> builder)
    {
        builder.HasData(
            new Category
            {
                Id = 1,
                Name = "Electronics",
                Description = "Electronic devices and accessories",
                CreatedAt = DateTime.UtcNow
            },
            new Category
            {
                Id = 2,
                Name = "Books",
                Description = "Books, magazines, and publications",
                CreatedAt = DateTime.UtcNow
            }
        );
    }
}

// In DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new CategoryConfiguration());
    // Other configurations...
}

Conditional Seeding

public static class SeedData
{
    public static void Initialize(IServiceProvider serviceProvider)
    {
        using var context = new AppDbContext(
            serviceProvider.GetRequiredService<DbContextOptions<AppDbContext>>());

        // Only seed if no data exists
        if (!context.Categories.Any())
        {
            context.Categories.AddRange(
                new Category { Name = "Electronics" },
                new Category { Name = "Books" }
            );
            context.SaveChanges();
        }
    }
}

// In Program.cs
using (var scope = app.Services.CreateScope())
{
    var services = scope.ServiceProvider;
    SeedData.Initialize(services);
}

Seeding Large Datasets

public class LargeDataSeeder
{
    private readonly AppDbContext _context;

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

    public async Task SeedLargeDatasetAsync()
    {
        if (await _context.Products.AnyAsync())
            return;

        var categories = await _context.Categories.ToListAsync();
        var products = new List<Product>();

        foreach (var category in categories)
        {
            for (int i = 1; i <= 1000; i++)
            {
                products.Add(new Product
                {
                    Name = $"{category.Name} Product {i}",
                    Price = Random.Shared.Next(10, 1000),
                    CategoryId = category.Id
                });

                // Batch insert every 100 products
                if (products.Count >= 100)
                {
                    await _context.Products.AddRangeAsync(products);
                    await _context.SaveChangesAsync();
                    products.Clear();
                }
            }
        }

        // Insert remaining products
        if (products.Any())
        {
            await _context.Products.AddRangeAsync(products);
            await _context.SaveChangesAsync();
        }
    }
}

Environment-Specific Seeding

public class DevelopmentSeeder : ISeeder
{
    public async Task SeedAsync(AppDbContext context)
    {
        // Development-specific seed data
        context.Users.Add(new User
        {
            Email = "admin@example.com",
            Role = "Administrator"
        });

        await context.SaveChangesAsync();
    }
}

public class ProductionSeeder : ISeeder
{
    public async Task SeedAsync(AppDbContext context)
    {
        // Production seed data - minimal
        await context.SaveChangesAsync();
    }
}

// Usage
public static async Task SeedDatabaseAsync(IServiceProvider services, IHostEnvironment environment)
{
    using var scope = services.CreateScope();
    var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

    ISeeder seeder = environment.IsDevelopment()
        ? new DevelopmentSeeder()
        : new ProductionSeeder();

    await seeder.SeedAsync(context);
}

Migration Best Practices

Test migrations on a copy of production data
Use descriptive names for migrations
Keep migrations small and focused
Include data migration logic when needed
Version control migration files
Backup before applying to production

Raw SQL Queries and Stored Procedures

Raw SQL Queries

EF Core allows executing raw SQL queries when LINQ is insufficient.

FromSql and FromSqlInterpolated

public async Task<IEnumerable<Product>> GetProductsByPriceRange(decimal minPrice, decimal maxPrice)
{
    return await _context.Products
        .FromSqlInterpolated($"SELECT * FROM Products WHERE Price BETWEEN {minPrice} AND {maxPrice}")
        .ToListAsync();
}

public async Task<IEnumerable<Product>> GetTopSellingProducts(int count)
{
    return await _context.Products
        .FromSqlRaw("SELECT TOP (@count) * FROM Products ORDER BY SalesCount DESC", count)
        .ToListAsync();
}

Raw SQL with Joins

public async Task<IEnumerable<OrderSummary>> GetOrderSummariesRaw()
{
    var query = @"
        SELECT o.Id, o.OrderDate, c.Name as CustomerName,
               SUM(od.Quantity * od.UnitPrice) as TotalAmount,
               COUNT(od.Id) as ItemCount
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.Id
        LEFT JOIN OrderDetails od ON o.Id = od.OrderId
        GROUP BY o.Id, o.OrderDate, c.Name";

    return await _context.OrderSummaries
        .FromSqlRaw(query)
        .ToListAsync();
}

Non-Entity Types from Raw SQL

public async Task<IEnumerable<ProductStats>> GetProductStats()
{
    var query = @"
        SELECT
            COUNT(*) as TotalProducts,
            AVG(Price) as AveragePrice,
            MIN(Price) as MinPrice,
            MAX(Price) as MaxPrice,
            SUM(StockQuantity) as TotalStock
        FROM Products";

    return await _context.Database
        .SqlQueryRaw<ProductStats>(query)
        .ToListAsync();
}

Stored Procedures

Calling Stored Procedures

public async Task<int> UpdateProductPrice(int productId, decimal newPrice)
{
    return await _context.Database
        .ExecuteSqlInterpolatedAsync($"EXEC UpdateProductPrice {productId}, {newPrice}");
}

public async Task<IEnumerable<Product>> GetProductsByCategory(string categoryName)
{
    return await _context.Products
        .FromSqlInterpolated($"EXEC GetProductsByCategory {categoryName}")
        .ToListAsync();
}

Complex Stored Procedure Calls

public async Task<SalesReport> GenerateSalesReport(DateTime startDate, DateTime endDate)
{
    var parameters = new[]
    {
        new SqlParameter("@StartDate", startDate),
        new SqlParameter("@EndDate", endDate),
        new SqlParameter("@TotalSales", SqlDbType.Decimal) { Direction = ParameterDirection.Output },
        new SqlParameter("@OrderCount", SqlDbType.Int) { Direction = ParameterDirection.Output }
    };

    await _context.Database.ExecuteSqlRawAsync(
        "EXEC GenerateSalesReport @StartDate, @EndDate, @TotalSales OUTPUT, @OrderCount OUTPUT",
        parameters);

    return new SalesReport
    {
        TotalSales = (decimal)parameters[2].Value,
        OrderCount = (int)parameters[3].Value
    };
}

Mapping Stored Procedure Results to Entities

public async Task<IEnumerable<OrderDetail>> GetOrderDetailsByDateRange(DateTime startDate, DateTime endDate)
{
    return await _context.OrderDetails
        .FromSqlInterpolated($@"
            EXEC GetOrderDetailsByDateRange
                @StartDate = {startDate},
                @EndDate = {endDate}")
        .Include(od => od.Product)
        .Include(od => od.Order)
        .ToListAsync();
}

SQL Injection Prevention

// Safe - parameterized query
public async Task<Product> GetProductByName(string name)
{
    return await _context.Products
        .FromSqlInterpolated($"SELECT * FROM Products WHERE Name = {name}")
        .FirstOrDefaultAsync();
}

// Avoid - vulnerable to SQL injection
public async Task<Product> GetProductByNameUnsafe(string name)
{
    return await _context.Database
        .SqlQueryRaw<Product>($"SELECT * FROM Products WHERE Name = '{name}'")
        .FirstOrDefaultAsync();
}

Bulk Operations with Raw SQL

public async Task BulkUpdateProductPrices(decimal percentageIncrease)
{
    var sql = "UPDATE Products SET Price = Price * @Multiplier WHERE Discontinued = 0";
    var multiplier = 1 + (percentageIncrease / 100);

    await _context.Database.ExecuteSqlInterpolatedAsync(sql, multiplier);
}

public async Task BulkInsertProducts(IEnumerable<Product> products)
{
    using var transaction = await _context.Database.BeginTransactionAsync();

    try
    {
        foreach (var batch in products.Chunk(1000))
        {
            var values = string.Join(",",
                batch.Select(p => $"({p.Id}, '{p.Name.Replace("'", "''")}', {p.Price})"));

            var sql = $"INSERT INTO Products (Id, Name, Price) VALUES {values}";

            await _context.Database.ExecuteSqlRawAsync(sql);
        }

        await transaction.CommitAsync();
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Performance Considerations

// Use raw SQL for complex queries that LINQ can't optimize well
public async Task<IEnumerable<ComplexReport>> GetComplexReport()
{
    var query = @"
        SELECT
            p.CategoryId,
            c.Name as CategoryName,
            COUNT(*) as ProductCount,
            AVG(p.Price) as AveragePrice,
            SUM(p.StockQuantity * p.Price) as TotalValue
        FROM Products p
        INNER JOIN Categories c ON p.CategoryId = c.Id
        GROUP BY p.CategoryId, c.Name
        HAVING COUNT(*) > 5
        ORDER BY TotalValue DESC";

    return await _context.Database
        .SqlQueryRaw<ComplexReport>(query)
        .ToListAsync();
}

Best Practices for Raw SQL

Use parameterized queries to prevent SQL injection
Prefer LINQ when possible for maintainability
Use stored procedures for complex business logic
Test raw SQL queries thoroughly
Consider performance implications
Use transactions for multiple operations

Interview Questions

Basic Level (1-2 years experience)

Q1. What is Entity Framework Core?

Answer: Entity Framework Core (EF Core) is a modern object-database mapper for .NET that enables .NET developers to work with a database using .NET objects. It eliminates the need for most of the data-access code that developers usually need to write. EF Core supports LINQ queries, change tracking, updates, and schema migrations.

Q2. What is the difference between lazy loading and eager loading?

Answer: Lazy loading loads related data only when it's accessed, which can lead to N+1 query problems. Eager loading loads related data immediately with the main query using Include() method. Lazy loading is enabled by default in EF Core but can cause performance issues, while eager loading requires explicit configuration but provides better performance.

Q3. How do you create and apply database migrations?

Answer: Use dotnet ef migrations add <MigrationName> to create a migration, then dotnet ef database update to apply it to the database. Migrations are C# classes that describe changes to the database schema.

Q4. What is the purpose of AsNoTracking()?

Answer: AsNoTracking() tells EF Core not to track changes to the entities returned by the query. This improves performance for read-only queries since EF doesn't need to maintain change tracking information in memory.

Q5. How do you execute raw SQL queries in EF Core?

Answer: Use FromSqlRaw() or FromSqlInterpolated() for queries that return entities, and ExecuteSqlRaw() or ExecuteSqlInterpolated() for non-query operations. Always use parameterized queries to prevent SQL injection.

Intermediate Level (2-3 years experience)

Q6. How do you handle the N+1 query problem?

Answer: The N+1 problem occurs when lazy loading causes additional queries. Solutions include:

Use eager loading with Include()
Use projections to select only needed data
Use AsNoTracking() for read-only queries
Use explicit loading when needed
Consider denormalization for frequently accessed data

Q7. What are the different loading strategies in EF Core?

Answer:

Lazy Loading: Loads related data when accessed (can cause N+1 queries)
Eager Loading: Loads related data with Include/ThenInclude
Explicit Loading: Manually loads related data with Load/LoadAsync
Select Loading: Uses projections to load only needed data

Q8. How do you implement data seeding in EF Core?

Answer: Use HasData() in OnModelCreating() for static seed data, or implement custom seeders that run during application startup. For large datasets, consider bulk insert operations. Use conditional seeding to avoid duplicate data.

Q9. What are compiled queries and when should you use them?

Answer: Compiled queries cache the translation from LINQ to SQL, improving performance for frequently executed queries. Use EF.CompileQuery() to create compiled queries. They're beneficial for queries executed many times with different parameters.

Q10. How do you handle database schema changes in production?

Answer: Create migrations for schema changes, test them thoroughly, backup production data, apply migrations in a transaction, and have a rollback plan. Use migration bundles for deployment. Consider blue-green deployments for zero-downtime updates.

Advanced Level (3+ years experience)

Q11. How would you optimize a slow EF Core query?

Answer:

Analyze the generated SQL with logging
Check for N+1 queries and fix with Include/projections
Use AsNoTracking for read-only queries
Consider indexing on frequently queried columns
Use compiled queries for repeated executions
Implement caching for expensive queries
Consider denormalization for complex reports
Use raw SQL for complex queries LINQ can't optimize

Q12. What strategies do you use for handling large datasets?

Answer:

Use pagination with Skip/Take
Implement cursor-based pagination for large tables
Use projections to select only needed columns
Consider table partitioning
Use streaming for very large result sets
Implement archiving strategies for old data
Use batch operations for bulk updates

Q13. How do you implement database transactions in EF Core?

Answer: Use Database.BeginTransaction() for explicit transactions, or rely on EF's automatic transaction wrapping for SaveChanges(). For distributed transactions, use TransactionScope. Always handle rollbacks in exception scenarios. Consider transaction isolation levels for concurrent access.

Q14. What are the challenges of database migrations in a team environment?

Answer: Merge conflicts in migration files, ensuring migration order, handling branching strategies, testing migrations, managing different environments, and coordinating deployments. Solutions include clear branching strategies, automated testing, and migration bundling.

Q15. How do you implement caching with EF Core?

Answer: Use IDistributedCache or IMemoryCache to cache query results. Implement cache invalidation strategies (time-based, event-based). Cache entity data, query results, or computed values. Consider cache stampede problems and implement cache warming for critical data.

Q16. What are the performance implications of change tracking?

Answer: Change tracking maintains entity state in memory, which has overhead. For read-only queries, use AsNoTracking() to disable tracking. For bulk operations, consider disabling change tracking globally. Be aware of the memory footprint for large result sets.

Q17. How do you handle concurrency conflicts in EF Core?

Answer: Use concurrency tokens (rowversion/timestamp columns), handle DbUpdateConcurrencyException, implement retry logic, or use optimistic concurrency with conflict resolution strategies. Consider pessimistic locking for critical sections.

Q18. What are the considerations for multi-tenant applications with EF Core?

Answer: Implement tenant isolation through database-per-tenant, schema-per-tenant, or row-level security. Use query filters for automatic tenant filtering. Handle migrations across multiple tenants. Consider performance implications of tenant-specific queries.

Q19. How do you implement soft deletes with EF Core?

Answer: Add IsDeleted column, use query filters to exclude deleted records, override SaveChanges to set IsDeleted instead of actually deleting, create indexes on IsDeleted, and implement cleanup jobs for hard deletes when needed.

Q20. What monitoring and diagnostics do you implement for EF Core?

Answer: Enable query logging, use Application Insights or EF Core diagnostics, monitor query performance, track change tracking statistics, implement health checks for database connectivity, and set up alerts for slow queries or connection pool exhaustion.

Additional Interview Tips

EF Core Best Practices

Profile queries regularly to identify performance issues
Use appropriate loading strategies based on use case
Implement proper indexing on frequently queried columns
Test migrations thoroughly before production deployment
Use transactions for data consistency
Consider caching for improved performance
Monitor database performance and optimize slow queries

Common Anti-Patterns to Avoid

N+1 query problem - always fix lazy loading issues
Loading unnecessary data - use projections
Ignoring change tracking overhead - use AsNoTracking when appropriate
Large migrations - keep them small and focused
Blocking operations - use async methods
Ignoring concurrency - handle concurrent updates properly
Over-normalization - consider read model optimization

Testing Strategies

Unit test repositories with in-memory database
Integration test with actual database
Test migrations on database copies
Performance test critical queries
Test concurrency scenarios with multiple threads
Mock external dependencies in unit tests

Performance Optimization Checklist

[ ] Enable query logging and analyze generated SQL
[ ] Check for N+1 queries and fix with Include/projections
[ ] Use AsNoTracking for read-only operations
[ ] Implement proper database indexing
[ ] Use compiled queries for repeated operations
[ ] Implement caching strategies
[ ] Consider denormalization for complex queries
[ ] Use raw SQL when LINQ can't be optimized
[ ] Monitor connection pool usage
[ ] Profile memory usage for large result sets

Migration Safety

[ ] Always backup before applying migrations
[ ] Test migrations on production-like data
[ ] Have rollback plans for failed migrations
[ ] Use transactions for migration operations
[ ] Coordinate with team for concurrent changes
[ ] Document breaking changes and data migrations
[ ] Use migration bundles for consistent deployment