Interview preparation · Technical guide

Entity Framework Interview Questions and Answers

EF Core interview material covering mapping, querying, transactions, testing, and operations. The guide calls out DbContext lifecycle, bulk operations, and concurrency boundaries.

Examples are independent teaching snippets and may require application types, imports, packages, schema, and configuration. Framework behavior is version-dependent. Corrections address identified issues; the complete source code collection has not been compiled or integration-tested.

1. EF Core vs EF6 Differences

EF6: - Full .NET Framework only - More mature, complex inheritance support - Better stored procedure support

EF Core: - Cross-platform (.NET Core, .NET 5+) - Better performance, modern architecture - Enhanced async/await support

// EF6 Configuration
public class MyContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .Property(c => c.Name)
            .IsRequired()
            .HasMaxLength(100);
    }
}

// EF Core Configuration
public class MyContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>(entity =>
        {
            entity.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(100);

            entity.HasIndex(e => e.Email).IsUnique();
        });
    }
}

2. Change Tracking

public class ChangeTrackingExample
{
    public void DemonstrateTracking(DbContext context)
    {
        var customer = new Customer { Name = "John Doe" };
        context.Customers.Add(customer);

        var entry = context.Entry(customer);
        Console.WriteLine($"State: {entry.State}"); // Added

        var existing = context.Customers.First();
        existing.Name = "Updated Name";

        var modifiedEntry = context.Entry(existing);
        Console.WriteLine($"State: {modifiedEntry.State}"); // Modified
        Console.WriteLine($"Modified: {string.Join(", ", modifiedEntry.Properties.Where(p => p.IsModified).Select(p => p.Metadata.Name))}");
    }
}

3. Repository Pattern Implementation

DbContext already embodies repository and unit-of-work patterns. Add a repository only when it expresses application-specific query/command boundaries; a generic CRUD wrapper can hide useful EF Core features without adding meaningful abstraction. Keep repository and DbContext lifetimes scoped to the same unit of work.

4. Unit of Work Pattern

SaveChanges groups tracked changes into a unit of work. EF Core wraps a single SaveChanges call in a transaction when the provider supports transactions. A custom Unit of Work wrapper is optional and should not dispose a context owned by dependency injection.

5. Eager vs Lazy Loading

// Eager Loading
public async Task<List<Customer>> EagerLoadingAsync(DbContext context)
{
    return await context.Customers
        .Include(c => c.Orders)
            .ThenInclude(o => o.OrderItems)
        .Include(c => c.Address)
        .ToListAsync();
}

// Lazy Loading (requires navigation properties to be virtual)
public async Task<List<Customer>> LazyLoadingAsync(DbContext context)
{
    var customers = await context.Customers.ToListAsync();

    foreach (var customer in customers)
    {
        var orders = customer.Orders; // Triggers additional query
        foreach (var order in orders)
        {
            var items = order.OrderItems; // Triggers another query
        }
    }

    return customers;
}

// Explicit Loading
public async Task ExplicitLoadingAsync(DbContext context, int customerId)
{
    var customer = await context.Customers.FindAsync(customerId);

    await context.Entry(customer)
        .Collection(c => c.Orders)
        .Query()
        .Where(o => o.OrderDate > DateTime.Today.AddDays(-30))
        .LoadAsync();
}

Performance Optimization

6. Query Performance Optimization

Start by measuring generated SQL, execution plans, round trips, and materialized data. Project only needed columns, limit result sets, use no-tracking reads where appropriate, avoid N+1 patterns, and add indexes for actual query predicates. Optimize after evidence rather than applying every technique mechanically. Reference: Efficient querying.

7. N+1 Query Problem Solutions

public class NPlusOneSolutionExample
{
    // Problem: N+1 queries
    public async Task<List<Customer>> ProblematicQueryAsync(DbContext context)
    {
        var customers = await context.Customers.ToListAsync();

        foreach (var customer in customers)
        {
            var orders = customer.Orders.ToList(); // N additional queries
        }

        return customers;
    }

    // Solution 1: Eager loading
    public async Task<List<Customer>> EagerLoadingSolutionAsync(DbContext context)
    {
        return await context.Customers
            .Include(c => c.Orders)
            .ToListAsync();
    }

    // Solution 2: Projection
    public async Task<List<CustomerOrderSummary>> ProjectionSolutionAsync(DbContext context)
    {
        return await context.Customers
            .Select(c => new CustomerOrderSummary
            {
                CustomerId = c.Id,
                CustomerName = c.Name,
                OrderCount = c.Orders.Count,
                TotalAmount = c.Orders.Sum(o => o.Total)
            })
            .ToListAsync();
    }
}

8. Bulk Operations

For EF Core 7+, ExecuteUpdate and ExecuteDelete perform set-based updates/deletes without loading entities or using the change tracker. They do not automatically synchronize already tracked entities, invoke SaveChanges interceptors, or provide the same concurrency behavior as tracked updates. Third-party bulk libraries have separate semantics. Reference: ExecuteUpdate/ExecuteDelete.

9. Async Operations

Async EF Core APIs improve scalability for I/O-bound database operations. Await each operation before using the same DbContext again: DbContext is not thread-safe. Async does not make one context safe for parallel queries or automatically speed up a database operation. Reference: DbContext lifetime.

10. Entity Framework Interceptors

public class AuditInterceptor : DbCommandInterceptor
{
    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        Console.WriteLine($"Executing query: {command.CommandText}");
        return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutedAsync(
        DbCommand command,
        CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        DbDataReader reader,
        CancellationToken cancellationToken = default)
    {
        Console.WriteLine($"Query executed in {eventData.Duration.TotalMilliseconds}ms");
        return base.ReaderExecutedAsync(command, eventData, result, reader, cancellationToken);
    }
}

public class SoftDeleteInterceptor : SaveChangesInterceptor
{
    public override InterceptionResult<int> SavingChanges(DbContextEventData eventData, InterceptionResult<int> result)
    {
        var context = eventData.Context;

        foreach (var entry in context.ChangeTracker.Entries())
        {
            if (entry.State == EntityState.Deleted && entry.Entity is ISoftDelete softDelete)
            {
                entry.State = EntityState.Modified;
                softDelete.IsDeleted = true;
                softDelete.DeletedDate = DateTime.UtcNow;
            }
        }

        return base.SavingChanges(eventData, result);
    }
}

11. Configuration Approaches

// Data Annotations
public class Customer
{
    [Key]
    public int Id { get; set; }

    [Required]
    [MaxLength(100)]
    public string Name { get; set; }

    [EmailAddress]
    [Index(IsUnique = true)]
    public string Email { get; set; }
}

// Fluent API
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.HasKey(c => c.Id);

        builder.Property(c => c.Name)
            .IsRequired()
            .HasMaxLength(100);

        builder.Property(c => c.Email)
            .IsRequired()
            .HasMaxLength(255);

        builder.HasIndex(c => c.Email)
            .IsUnique();

        builder.HasMany(c => c.Orders)
            .WithOne(o => o.Customer)
            .HasForeignKey(o => o.CustomerId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

12. DbContext Lifecycle Management

Create a DbContext for one unit of work, use it, save as needed, and dispose it. AddDbContext registers a scoped context by default, which fits ordinary HTTP requests. Long-lived UI flows and parallel work need a deliberate scope or IDbContextFactory design. Reference: DbContext lifetime.

13. Query Caching

public class QueryCachingExample
{
    private readonly IMemoryCache _cache;
    private readonly DbContext _context;

    public QueryCachingExample(IMemoryCache cache, DbContext context)
    {
        _cache = cache;
        _context = context;
    }

    public async Task<List<Customer>> GetCustomersWithCacheAsync()
    {
        var cacheKey = "customers_active";

        if (_cache.TryGetValue(cacheKey, out List<Customer> cachedCustomers))
        {
            return cachedCustomers;
        }

        var customers = await _context.Customers
            .AsNoTracking()
            .Where(c => c.IsActive)
            .ToListAsync();

        var cacheOptions = new MemoryCacheEntryOptions()
            .SetSlidingExpiration(TimeSpan.FromMinutes(10))
            .SetAbsoluteExpiration(TimeSpan.FromHours(1));

        _cache.Set(cacheKey, customers, cacheOptions);

        return customers;
    }
}

14. Connection Pooling

public static class ServiceCollectionExtensions
{
    public static IServiceCollection AddDbContextServices(this IServiceCollection services, IConfiguration configuration)
    {
        services.AddDbContext<MyDbContext>(options =>
        {
            var connectionString = configuration.GetConnectionString("DefaultConnection");

            options.UseSqlServer(connectionString, sqlOptions =>
            {
                sqlOptions.EnableRetryOnFailure(
                    maxRetryCount: 3,
                    maxRetryDelay: TimeSpan.FromSeconds(30),
                    errorNumbersToAdd: null);
            });
        });

        return services;
    }
}

// Connection string with pooling configuration
// "Server=.;Database=MyDb;Trusted_Connection=true;Max Pool Size=100;Min Pool Size=10;"

15. Query Splitting

public class QuerySplittingExample
{
    public async Task<List<Customer>> QuerySplittingAsync(DbContext context)
    {
        // Split complex query into multiple simpler queries
        var customers = await context.Customers
            .AsNoTracking()
            .Where(c => c.IsActive)
            .ToListAsync();

        var customerIds = customers.Select(c => c.Id).ToList();

        var orders = await context.Orders
            .AsNoTracking()
            .Where(o => customerIds.Contains(o.CustomerId))
            .ToListAsync();

        // Combine results in memory
        var ordersByCustomer = orders.GroupBy(o => o.CustomerId).ToDictionary(g => g.Key, g => g.ToList());

        foreach (var customer in customers)
        {
            customer.Orders = ordersByCustomer.GetValueOrDefault(customer.Id, new List<Order>());
        }

        return customers;
    }

    public async Task<List<Customer>> UseSplitQueryAsync(DbContext context)
    {
        return await context.Customers
            .AsNoTracking()
            .Include(c => c.Orders)
            .Include(c => c.Address)
            .AsSplitQuery() // Split into multiple queries
            .ToListAsync();
    }
}

16. Memory Usage Optimization

public class MemoryOptimizationExample
{
    public async Task<List<Customer>> StreamLargeDatasetAsync(DbContext context)
    {
        var customers = new List<Customer>();

        await foreach (var customer in context.Customers
            .AsNoTracking()
            .AsAsyncEnumerable())
        {
            customers.Add(customer);

            if (customers.Count % 1000 == 0)
            {
                await ProcessCustomerBatchAsync(customers);
                customers.Clear();
            }
        }

        return customers;
    }

    public async Task ProcessLargeDatasetWithPaginationAsync(DbContext context)
    {
        const int pageSize = 1000;
        int pageNumber = 0;

        while (true)
        {
            var customers = await context.Customers
                .AsNoTracking()
                .Skip(pageNumber * pageSize)
                .Take(pageSize)
                .ToListAsync();

            if (!customers.Any())
                break;

            await ProcessCustomerBatchAsync(customers);
            pageNumber++;
        }
    }
}

17. Error Handling and Resilience

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

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        try
        {
            return await base.SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateConcurrencyException ex)
        {
            // Handle concurrency conflicts
            foreach (var entry in ex.Entries)
            {
                var databaseValues = await entry.GetDatabaseValuesAsync(cancellationToken);
                entry.OriginalValues.SetValues(databaseValues);
            }

            return await SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateException ex)
        {
            throw new ApplicationException("Database update failed", ex);
        }
    }
}

18. Dependency Injection Configuration

public static class ServiceCollectionExtensions
{
    public static IServiceCollection AddEntityFrameworkServices(this IServiceCollection services, IConfiguration configuration)
    {
        // DbContext should be scoped (per request)
        services.AddDbContext<MyDbContext>(options =>
        {
            options.UseSqlServer(configuration.GetConnectionString("DefaultConnection"));
            options.EnableSensitiveDataLogging(Environment.IsDevelopment());
            options.EnableDetailedErrors(Environment.IsDevelopment());
        });

        // Repositories should be scoped
        services.AddScoped<ICustomerRepository, CustomerRepository>();
        services.AddScoped<IOrderRepository, OrderRepository>();

        // Unit of Work should be scoped
        services.AddScoped<IUnitOfWork, UnitOfWork>();

        // Services should be scoped
        services.AddScoped<ICustomerService, CustomerService>();
        services.AddScoped<IOrderService, OrderService>();

        return services;
    }
}

Key Technical Lead Considerations

1. Performance Monitoring: Use interceptors to monitor query performance
2. Connection Management: Proper connection pooling and lifecycle management
3. Memory Management: Use AsNoTracking, pagination, and streaming for large datasets
4. Error Handling: Implement proper exception handling and retry logic
5. Caching Strategy: Implement appropriate caching for frequently accessed data
6. Transaction Management: Use Unit of Work pattern for complex operations
7. Async Operations: Always use async/await for database operations
8. Query Optimization: Avoid N+1 queries, use proper indexing, and optimize queries
9. Security: Use parameterized queries and avoid SQL injection
10. Testing: Implement proper unit tests with in-memory database or test doubles

20. Best Practices for Entity Framework Batch Operations

Batch size and retry behavior are provider and workload dependent. For set-based changes, consider ExecuteUpdate/ExecuteDelete. For inserts or tracked updates, configure the provider and measure command batching. Do not combine an automatic retry strategy with a hand-managed transaction unless the entire unit is executed through the provider’s execution strategy.

21. Implementing Entity Framework Raw SQL Queries

Approaches:

1. FromSqlRaw/FromSqlInterpolated
2. ExecuteSqlRaw/ExecuteSqlInterpolated
3. SqlQuery (EF6)

// Example 1: Raw SQL with LINQ
public async Task<List<User>> GetUsersWithComplexQueryAsync()
{
    var sql = @"
        SELECT u.*, d.Name as DepartmentName 
        FROM Users u 
        INNER JOIN Departments d ON u.DepartmentId = d.Id 
        WHERE u.IsActive = 1 AND u.CreatedDate >= @startDate";

    var users = await _context.Users
        .FromSqlRaw(sql, new SqlParameter("@startDate", DateTime.UtcNow.AddDays(-30)))
        .Include(u => u.Department)
        .ToListAsync();

    return users;
}

// Example 2: Stored Procedure with Raw SQL
public async Task<List<User>> GetUsersByDepartmentAsync(int departmentId)
{
    var sql = "EXEC GetUsersByDepartment @DepartmentId";
    var users = await _context.Users
        .FromSqlRaw(sql, new SqlParameter("@DepartmentId", departmentId))
        .ToListAsync();

    return users;
}

// Example 3: Complex Aggregation with Raw SQL
public async Task<DashboardStats> GetDashboardStatsAsync()
{
    var sql = @"
        SELECT 
            COUNT(*) as TotalUsers,
            COUNT(CASE WHEN IsActive = 1 THEN 1 END) as ActiveUsers,
            AVG(DATEDIFF(day, CreatedDate, GETDATE())) as AvgUserAge
        FROM Users";

    var result = await _context.Set<DashboardStats>()
        .FromSqlRaw(sql)
        .FirstOrDefaultAsync();

    return result;
}

22. Handling Entity Framework Stored Procedures

Implementation Patterns:

// Example 1: Stored Procedure with Output Parameters
public async Task<(List<User> Users, int TotalCount)> GetUsersPagedAsync(
    int pageNumber, int pageSize, string searchTerm)
{
    var totalCountParam = new SqlParameter("@TotalCount", SqlDbType.Int)
    {
        Direction = ParameterDirection.Output
    };

    var sql = "EXEC GetUsersPaged @PageNumber, @PageSize, @SearchTerm, @TotalCount OUTPUT";

    var users = await _context.Users
        .FromSqlRaw(sql,
            new SqlParameter("@PageNumber", pageNumber),
            new SqlParameter("@PageSize", pageSize),
            new SqlParameter("@SearchTerm", searchTerm),
            totalCountParam)
        .ToListAsync();

    var totalCount = (int)totalCountParam.Value;
    return (users, totalCount);
}

// Example 2: Stored Procedure with Multiple Result Sets
public async Task<UserReport> GetUserReportAsync(int userId)
{
    var sql = "EXEC GetUserReport @UserId";

    using var command = _context.Database.GetDbConnection().CreateCommand();
    command.CommandText = sql;
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add(new SqlParameter("@UserId", userId));

    await _context.Database.OpenConnectionAsync();

    using var result = await command.ExecuteReaderAsync();

    var report = new UserReport();

    // Read first result set (user details)
    if (await result.ReadAsync())
    {
        report.User = new User
        {
            Id = result.GetInt32("Id"),
            Name = result.GetString("Name"),
            Email = result.GetString("Email")
        };
    }

    // Read second result set (user activities)
    await result.NextResultAsync();
    report.Activities = new List<UserActivity>();

    while (await result.ReadAsync())
    {
        report.Activities.Add(new UserActivity
        {
            Id = result.GetInt32("Id"),
            ActivityType = result.GetString("ActivityType"),
            Timestamp = result.GetDateTime("Timestamp")
        });
    }

    return report;
}

23. Implementing Entity Framework Database Views

Configuration and Usage:

// Example 1: Database View Entity
[Table("vw_UserSummary")]
public class UserSummary
{
    [Key]
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string DepartmentName { get; set; }
    public int TotalOrders { get; set; }
    public decimal TotalSpent { get; set; }
    public DateTime LastOrderDate { get; set; }
}

// Example 2: View with Complex Query
public async Task<List<UserSummary>> GetUserSummariesAsync()
{
    var summaries = await _context.Set<UserSummary>()
        .Where(us => us.TotalOrders > 0)
        .OrderByDescending(us => us.TotalSpent)
        .ToListAsync();

    return summaries;
}

// Example 3: View with Parameters
public async Task<List<UserSummary>> GetUserSummariesByDepartmentAsync(string departmentName)
{
    var sql = @"
        SELECT * FROM vw_UserSummary 
        WHERE DepartmentName = @departmentName 
        ORDER BY TotalSpent DESC";

    var summaries = await _context.Set<UserSummary>()
        .FromSqlRaw(sql, new SqlParameter("@departmentName", departmentName))
        .ToListAsync();

    return summaries;
}

24. Handling Entity Framework Database Functions

Implementation Approaches:

// Example 1: Scalar Function
public async Task<decimal> CalculateUserDiscountAsync(int userId)
{
    var sql = "SELECT dbo.CalculateUserDiscount(@UserId)";
    var discount = await _context.Database
        .SqlQueryRaw<decimal>(sql, new SqlParameter("@UserId", userId))
        .FirstOrDefaultAsync();

    return discount;
}

// Example 2: Table-Valued Function
public async Task<List<UserOrder>> GetUserOrdersAsync(int userId, DateTime startDate)
{
    var sql = "SELECT * FROM dbo.GetUserOrders(@UserId, @StartDate)";
    var orders = await _context.Set<UserOrder>()
        .FromSqlRaw(sql,
            new SqlParameter("@UserId", userId),
            new SqlParameter("@StartDate", startDate))
        .ToListAsync();

    return orders;
}

// Example 3: Function with Complex Parameters
public async Task<List<ProductRecommendation>> GetProductRecommendationsAsync(
    int userId, int categoryId, decimal maxPrice)
{
    var sql = @"
        SELECT * FROM dbo.GetProductRecommendations(
            @UserId, @CategoryId, @MaxPrice, @UserPreferences)";

    var recommendations = await _context.Set<ProductRecommendation>()
        .FromSqlRaw(sql,
            new SqlParameter("@UserId", userId),
            new SqlParameter("@CategoryId", categoryId),
            new SqlParameter("@MaxPrice", maxPrice),
            new SqlParameter("@UserPreferences", GetUserPreferences(userId)))
        .ToListAsync();

    return recommendations;
}

25. Implementing Entity Framework Database Triggers

Configuration and Handling:

// Example 1: Audit Trigger Configuration
public class AuditConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.ToTable("Users", t => t.HasTrigger("TR_Users_Audit"));

        // Configure audit properties
        builder.Property(u => u.CreatedDate).HasDefaultValueSql("GETDATE()");
        builder.Property(u => u.ModifiedDate).HasDefaultValueSql("GETDATE()");
        builder.Property(u => u.ModifiedBy).HasMaxLength(100);
    }
}

// Example 2: Handling Trigger Results
public async Task<int> CreateUserWithAuditAsync(User user)
{
    _context.Users.Add(user);
    await _context.SaveChangesAsync();

    // Trigger automatically creates audit record
    // You can query the audit table if needed
    var auditRecord = await _context.Set<UserAudit>()
        .Where(ua => ua.UserId == user.Id && ua.Action == "INSERT")
        .FirstOrDefaultAsync();

    return user.Id;
}

// Example 3: Custom Trigger Logic
public async Task UpdateUserWithCustomTriggerAsync(User user)
{
    // Disable change tracking for performance
    _context.ChangeTracker.AutoDetectChangesEnabled = false;

    var existingUser = await _context.Users.FindAsync(user.Id);
    if (existingUser != null)
    {
        _context.Entry(existingUser).CurrentValues.SetValues(user);
        existingUser.ModifiedDate = DateTime.UtcNow;
        existingUser.ModifiedBy = GetCurrentUserId();
    }

    await _context.SaveChangesAsync();
    _context.ChangeTracker.AutoDetectChangesEnabled = true;
}

26. Handling Entity Framework Database Sequences

Implementation Patterns:

// Example 1: Sequence Configuration
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.Property(o => o.OrderNumber)
            .HasDefaultValueSql("NEXT VALUE FOR dbo.OrderNumberSequence");

        builder.HasIndex(o => o.OrderNumber).IsUnique();
    }
}

// Example 2: Custom Sequence Generation
public async Task<string> GenerateOrderNumberAsync()
{
    var sql = "SELECT NEXT VALUE FOR dbo.OrderNumberSequence";
    var sequenceValue = await _context.Database
        .SqlQueryRaw<int>(sql)
        .FirstOrDefaultAsync();

    return $"ORD-{DateTime.Now:yyyyMMdd}-{sequenceValue:D6}";
}

// Example 3: Batch Sequence Generation
public async Task<List<string>> GenerateOrderNumbersAsync(int count)
{
    var orderNumbers = new List<string>();

    for (int i = 0; i < count; i++)
    {
        var sql = "SELECT NEXT VALUE FOR dbo.OrderNumberSequence";
        var sequenceValue = await _context.Database
            .SqlQueryRaw<int>(sql)
            .FirstOrDefaultAsync();

        orderNumbers.Add($"ORD-{DateTime.Now:yyyyMMdd}-{sequenceValue:D6}");
    }

    return orderNumbers;
}

27. Implementing Entity Framework Database Constraints

Configuration Examples:

// Example 1: Check Constraints
public class ProductConfiguration : IEntityTypeConfiguration<Product>
{
    public void Configure(EntityTypeBuilder<Product> builder)
    {
        builder.ToTable("Products", t => t.HasCheckConstraint("CK_Products_Price", "Price >= 0"));
        builder.ToTable("Products", t => t.HasCheckConstraint("CK_Products_Stock", "StockQuantity >= 0"));

        builder.Property(p => p.Price).HasColumnType("decimal(18,2)");
        builder.Property(p => p.StockQuantity).HasColumnType("int");
    }
}

// Example 2: Unique Constraints
public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.HasIndex(u => u.Email).IsUnique();
        builder.HasIndex(u => new { u.Username, u.DomainId }).IsUnique();

        // Composite unique constraint
        builder.HasIndex(u => new { u.FirstName, u.LastName, u.DateOfBirth })
            .IsUnique()
            .HasDatabaseName("IX_Users_FullName_DOB");
    }
}

// Example 3: Foreign Key Constraints
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.HasOne(o => o.User)
            .WithMany(u => u.Orders)
            .HasForeignKey(o => o.UserId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.HasOne(o => o.ShippingAddress)
            .WithMany()
            .HasForeignKey(o => o.ShippingAddressId)
            .OnDelete(DeleteBehavior.SetNull);
    }
}

28. Handling Entity Framework Database Indexes

Index Configuration:

// Example 1: Performance Indexes
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        // Clustered index on primary key
        builder.HasKey(o => o.Id);

        // Non-clustered indexes for common queries
        builder.HasIndex(o => o.UserId).HasDatabaseName("IX_Orders_UserId");
        builder.HasIndex(o => o.OrderDate).HasDatabaseName("IX_Orders_OrderDate");
        builder.HasIndex(o => o.Status).HasDatabaseName("IX_Orders_Status");

        // Composite index for complex queries
        builder.HasIndex(o => new { o.UserId, o.OrderDate, o.Status })
            .HasDatabaseName("IX_Orders_UserId_OrderDate_Status");

        // Include columns for covering queries
        builder.HasIndex(o => o.UserId)
            .IncludeProperties(o => new { o.OrderDate, o.TotalAmount })
            .HasDatabaseName("IX_Orders_UserId_Include");
    }
}

// Example 2: Filtered Indexes
public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        // Filtered index for active users only
        builder.HasIndex(u => u.Email)
            .HasFilter("IsActive = 1")
            .HasDatabaseName("IX_Users_Email_Active");

        // Filtered index for recent orders
        builder.HasIndex(u => u.LastLoginDate)
            .HasFilter("LastLoginDate >= DATEADD(day, -30, GETDATE())")
            .HasDatabaseName("IX_Users_LastLogin_Recent");
    }
}

// Example 3: Spatial Indexes
public class LocationConfiguration : IEntityTypeConfiguration<Location>
{
    public void Configure(EntityTypeBuilder<Location> builder)
    {
        builder.Property(l => l.Coordinates).HasColumnType("geography");

        builder.HasIndex(l => l.Coordinates)
            .HasDatabaseName("IX_Locations_Coordinates_Spatial");
    }
}

29. Implementing Entity Framework Database Partitioning

Partitioning Strategies:

// Example 1: Table Partitioning Configuration
public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Orders", t => t.IsTemporal(
            h => h.HasPeriodStart("ValidFrom")
                 .HasPeriodEnd("ValidTo")
                 .UseHistoryTable("OrdersHistory")));

        // Partition by OrderDate
        builder.Property(o => o.OrderDate).IsRequired();

        // Configure partition function and scheme
        // This requires custom SQL in migrations
    }
}

// Example 2: Partition-Aware Queries
public async Task<List<Order>> GetOrdersByDateRangeAsync(DateTime startDate, DateTime endDate)
{
    // EF Core will automatically use partition elimination
    var orders = await _context.Orders
        .Where(o => o.OrderDate >= startDate && o.OrderDate <= endDate)
        .Include(o => o.OrderItems)
        .ToListAsync();

    return orders;
}

// Example 3: Partition Management
public async Task ArchiveOldOrdersAsync(DateTime cutoffDate)
{
    // Switch out old partition
    var sql = @"
        ALTER TABLE Orders SWITCH PARTITION 1 TO OrdersArchive PARTITION 1";

    await _context.Database.ExecuteSqlRawAsync(sql);

    // Merge empty partitions
    sql = "ALTER PARTITION FUNCTION PF_OrderDate() MERGE RANGE (@cutoffDate)";
    await _context.Database.ExecuteSqlRawAsync(sql, 
        new SqlParameter("@cutoffDate", cutoffDate));
}

30. Handling Entity Framework Database Encryption

Encryption Implementation:

// Example 1: Column-Level Encryption
public class UserConfiguration : IEntityTypeConfiguration<User>
{
    public void Configure(EntityTypeBuilder<User> builder)
    {
        // Encrypt sensitive columns
        builder.Property(u => u.SocialSecurityNumber)
            .HasColumnType("varbinary(256)")
            .HasConversion(
                v => EncryptValue(v),
                v => DecryptValue(v));

        builder.Property(u => u.CreditCardNumber)
            .HasColumnType("varbinary(256)")
            .HasConversion(
                v => EncryptValue(v),
                v => DecryptValue(v));
    }

    private byte[] EncryptValue(string value)
    {
        if (string.IsNullOrEmpty(value)) return null;

        using var aes = Aes.Create();
        aes.Key = GetEncryptionKey();
        aes.IV = GetEncryptionIV();

        using var encryptor = aes.CreateEncryptor();
        var plainBytes = Encoding.UTF8.GetBytes(value);
        return encryptor.TransformFinalBlock(plainBytes, 0, plainBytes.Length);
    }

    private string DecryptValue(byte[] encryptedValue)
    {
        if (encryptedValue == null || encryptedValue.Length == 0) return null;

        using var aes = Aes.Create();
        aes.Key = GetEncryptionKey();
        aes.IV = GetEncryptionIV();

        using var decryptor = aes.CreateDecryptor();
        var decryptedBytes = decryptor.TransformFinalBlock(encryptedValue, 0, encryptedValue.Length);
        return Encoding.UTF8.GetString(decryptedBytes);
    }
}

// Example 2: Always Encrypted Configuration
public class SecureUserConfiguration : IEntityTypeConfiguration<SecureUser>
{
    public void Configure(EntityTypeBuilder<SecureUser> builder)
    {
        // Configure Always Encrypted columns
        builder.Property(u => u.SensitiveData)
            .HasColumnType("varchar(100)")
            .HasAnnotation("SqlServer:AlwaysEncrypted", true);

        builder.Property(u => u.EncryptedField)
            .HasColumnType("varchar(100)")
            .HasAnnotation("SqlServer:AlwaysEncrypted", true);
    }
}

// Example 3: TDE (Transparent Data Encryption) Handling
public class DatabaseEncryptionService
{
    private readonly DbContext _context;

    public DatabaseEncryptionService(DbContext context)
    {
        _context = context;
    }

    public async Task EnableDatabaseEncryptionAsync()
    {
        var sql = @"
            CREATE DATABASE ENCRYPTION KEY
            WITH ALGORITHM = AES_256
            ENCRYPTION BY SERVER CERTIFICATE DatabaseEncryptionCert";

        await _context.Database.ExecuteSqlRawAsync(sql);

        sql = "ALTER DATABASE CurrentDatabase SET ENCRYPTION ON";
        await _context.Database.ExecuteSqlRawAsync(sql);
    }

    public async Task<DatabaseEncryptionStatus> GetEncryptionStatusAsync()
    {
        var sql = @"
            SELECT 
                database_id,
                encryption_state,
                percent_complete,
                encryptor_type
            FROM sys.dm_database_encryption_keys";

        var result = await _context.Database
            .SqlQueryRaw<DatabaseEncryptionStatus>(sql)
            .FirstOrDefaultAsync();

        return result;
    }
}

Key Interview Points:

1. Performance Considerations: Always consider the performance impact of encryption/decryption operations
2. Key Management: Implement proper key rotation and management strategies
3. Compliance: Ensure encryption meets regulatory requirements (GDPR, HIPAA, etc.)
4. Backup Strategy: Encrypted databases require special backup considerations
5. Monitoring: Implement monitoring for encryption performance and key health

31. How do you implement Entity Framework migrations?

Answer: Entity Framework migrations allow you to version your database schema and apply incremental changes. They track database schema changes as code and can be applied to keep the database in sync with your model.

Implementation:

// 1. Enable migrations in Package Manager Console
// Enable-Migrations -ContextTypeName YourDbContext

// 2. Create initial migration
// Add-Migration InitialCreate

// 3. Apply migration to database
// Update-Database

// Migration class example
public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Users",
            columns: table => new
            {
                Id = table.Column<int>(type: "int", nullable: false)
                    .Annotation("SqlServer:Identity", "1, 1"),
                Name = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false),
                Email = table.Column<string>(type: "nvarchar(255)", maxLength: 255, nullable: false),
                CreatedDate = table.Column<DateTime>(type: "datetime2", nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Users", x => x.Id);
            });
    }

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

Programmatic approach:

// In Startup.cs or Program.cs
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    using (var scope = app.ApplicationServices.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();
        context.Database.Migrate();
    }
}

32. How do you handle Entity Framework seed data?

Answer: Seed data is used to populate the database with initial data. This can be done through migrations, the OnModelCreating method, or custom seeding logic.

Implementation:

// Method 1: Using Migration
public partial class SeedData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: "Users",
            columns: new[] { "Name", "Email", "CreatedDate" },
            values: new object[] { "Admin User", "admin@example.com", DateTime.UtcNow });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: "Users",
            keyColumn: "Email",
            keyValue: "admin@example.com");
    }
}

// Method 2: Using DbContext OnModelCreating
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>().HasData(
        new User { Id = 1, Name = "Admin User", Email = "admin@example.com", CreatedDate = DateTime.UtcNow },
        new User { Id = 2, Name = "Test User", Email = "test@example.com", CreatedDate = DateTime.UtcNow }
    );
}

// Method 3: Custom seeding service
public class DatabaseSeeder
{
    private readonly ApplicationDbContext _context;

    public DatabaseSeeder(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task SeedAsync()
    {
        if (!_context.Users.Any())
        {
            var users = new List<User>
            {
                new User { Name = "Admin User", Email = "admin@example.com" },
                new User { Name = "Test User", Email = "test@example.com" }
            };

            await _context.Users.AddRangeAsync(users);
            await _context.SaveChangesAsync();
        }
    }
}

33. How do you implement Entity Framework database initialization?

Answer: Database initialization ensures the database is created and properly configured when the application starts.

Implementation:

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        // Configure model relationships and constraints
        modelBuilder.Entity<User>()
            .HasIndex(u => u.Email)
            .IsUnique();
    }
}

// In Startup.cs
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    using (var scope = app.ApplicationServices.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

        // Ensure database is created
        context.Database.EnsureCreated();

        // Or use migrations
        // context.Database.Migrate();
    }
}

34. How do you handle Entity Framework database updates?

Answer: Database updates can be handled through migrations, direct schema updates, or programmatic approaches.

Implementation:

// Method 1: Using Migrations (Recommended)
// Add-Migration AddUserRoleColumn
public partial class AddUserRoleColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "Role",
            table: "Users",
            type: "nvarchar(50)",
            maxLength: 50,
            nullable: false,
            defaultValue: "User");
    }

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

// Method 2: Programmatic updates
public class DatabaseUpdater
{
    private readonly ApplicationDbContext _context;

    public DatabaseUpdater(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task UpdateDatabaseAsync()
    {
        // Check if column exists
        var columnExists = await _context.Database
            .SqlQueryRaw<bool>("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Users' AND COLUMN_NAME = 'Role'")
            .FirstOrDefaultAsync();

        if (!columnExists)
        {
            await _context.Database.ExecuteSqlRawAsync(
                "ALTER TABLE Users ADD Role NVARCHAR(50) NOT NULL DEFAULT 'User'");
        }
    }
}

35. How do you implement Entity Framework database rollbacks?

Answer: Database rollbacks can be implemented using migration history, database snapshots, or custom rollback logic.

Implementation:

// Method 1: Using EF Migrations
// Update-Database -TargetMigration "PreviousMigrationName"

// Method 2: Programmatic rollback
public class DatabaseRollbackService
{
    private readonly ApplicationDbContext _context;

    public DatabaseRollbackService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task RollbackToMigrationAsync(string targetMigration)
    {
        await _context.Database.MigrateAsync(targetMigration);
    }

    public async Task RollbackLastMigrationAsync()
    {
        var currentMigration = await GetCurrentMigrationAsync();
        var previousMigration = await GetPreviousMigrationAsync(currentMigration);

        if (previousMigration != null)
        {
            await _context.Database.MigrateAsync(previousMigration);
        }
    }

    private async Task<string> GetCurrentMigrationAsync()
    {
        return await _context.Database
            .SqlQueryRaw<string>("SELECT MigrationId FROM __EFMigrationsHistory ORDER BY ProductVersion DESC")
            .FirstOrDefaultAsync();
    }

    private async Task<string> GetPreviousMigrationAsync(string currentMigration)
    {
        return await _context.Database
            .SqlQueryRaw<string>("SELECT MigrationId FROM __EFMigrationsHistory WHERE MigrationId < @current ORDER BY MigrationId DESC",
                new SqlParameter("@current", currentMigration))
            .FirstOrDefaultAsync();
    }
}

36. How do you handle Entity Framework database versioning?

Answer: Database versioning involves tracking schema changes and managing different versions of the database.

Implementation:

public class DatabaseVersioningService
{
    private readonly ApplicationDbContext _context;

    public DatabaseVersioningService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<string> GetCurrentVersionAsync()
    {
        return await _context.Database
            .SqlQueryRaw<string>("SELECT TOP 1 MigrationId FROM __EFMigrationsHistory ORDER BY ProductVersion DESC")
            .FirstOrDefaultAsync();
    }

    public async Task<List<string>> GetMigrationHistoryAsync()
    {
        return await _context.Database
            .SqlQueryRaw<string>("SELECT MigrationId FROM __EFMigrationsHistory ORDER BY ProductVersion")
            .ToListAsync();
    }

    public async Task<bool> IsDatabaseUpToDateAsync()
    {
        var pendingMigrations = await _context.Database.GetPendingMigrationsAsync();
        return !pendingMigrations.Any();
    }

    public async Task<string> GetDatabaseSchemaVersionAsync()
    {
        // Custom version table approach
        var version = await _context.Database
            .SqlQueryRaw<string>("SELECT Version FROM DatabaseVersion WHERE Id = 1")
            .FirstOrDefaultAsync();

        return version ?? "1.0.0";
    }
}

37. How do you implement Entity Framework database snapshots?

Answer: Database snapshots provide point-in-time views of the database for backup and recovery purposes.

Implementation:

public class DatabaseSnapshotService
{
    private readonly string _connectionString;

    public DatabaseSnapshotService(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public async Task<string> CreateSnapshotAsync(string snapshotName)
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        var snapshotPath = GetSnapshotPath();

        var sql = $@"
            CREATE DATABASE {snapshotName} 
            ON (NAME = {databaseName}, FILENAME = '{snapshotPath}')
            AS SNAPSHOT OF {databaseName}";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();

        return snapshotName;
    }

    public async Task RestoreFromSnapshotAsync(string snapshotName)
    {
        var databaseName = GetDatabaseNameFromConnectionString();

        var sql = $@"
            USE master;
            ALTER DATABASE {databaseName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            RESTORE DATABASE {databaseName} FROM DATABASE_SNAPSHOT = '{snapshotName}';
            ALTER DATABASE {databaseName} SET MULTI_USER;";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    public async Task DeleteSnapshotAsync(string snapshotName)
    {
        var sql = $"DROP DATABASE {snapshotName}";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    private string GetDatabaseNameFromConnectionString()
    {
        var builder = new SqlConnectionStringBuilder(_connectionString);
        return builder.InitialCatalog;
    }

    private string GetSnapshotPath()
    {
        return Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), 
            "DatabaseSnapshots", $"snapshot_{DateTime.Now:yyyyMMdd_HHmmss}.ss");
    }
}

38. How do you handle Entity Framework database backups?

Answer: Database backups can be implemented using SQL Server backup commands or file system backups.

Implementation:

public class DatabaseBackupService
{
    private readonly string _connectionString;

    public DatabaseBackupService(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public async Task<string> CreateBackupAsync(string backupPath = null)
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        backupPath ??= GetDefaultBackupPath(databaseName);

        var sql = $@"
            BACKUP DATABASE [{databaseName}] 
            TO DISK = '{backupPath}' 
            WITH FORMAT, INIT, NAME = N'{databaseName}-Full Database Backup', 
            SKIP, NOREWIND, NOUNLOAD, STATS = 10";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();

        return backupPath;
    }

    public async Task CreateDifferentialBackupAsync(string backupPath = null)
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        backupPath ??= GetDefaultBackupPath(databaseName, "diff");

        var sql = $@"
            BACKUP DATABASE [{databaseName}] 
            TO DISK = '{backupPath}' 
            WITH DIFFERENTIAL, FORMAT, INIT, 
            NAME = N'{databaseName}-Differential Database Backup', 
            SKIP, NOREWIND, NOUNLOAD, STATS = 10";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    public async Task RestoreFromBackupAsync(string backupPath)
    {
        var databaseName = GetDatabaseNameFromConnectionString();

        var sql = $@"
            USE master;
            ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            RESTORE DATABASE [{databaseName}] FROM DISK = '{backupPath}' WITH REPLACE;
            ALTER DATABASE [{databaseName}] SET MULTI_USER;";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    private string GetDatabaseNameFromConnectionString()
    {
        var builder = new SqlConnectionStringBuilder(_connectionString);
        return builder.InitialCatalog;
    }

    private string GetDefaultBackupPath(string databaseName, string suffix = "bak")
    {
        var backupDir = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), 
            "DatabaseBackups");
        Directory.CreateDirectory(backupDir);

        return Path.Combine(backupDir, $"{databaseName}_{DateTime.Now:yyyyMMdd_HHmmss}.{suffix}");
    }
}

39. How do you implement Entity Framework database restore?

Answer: Database restore involves recovering the database from backups or snapshots.

Implementation:

public class DatabaseRestoreService
{
    private readonly string _connectionString;

    public DatabaseRestoreService(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public async Task RestoreFromBackupAsync(string backupPath, bool replace = true)
    {
        var databaseName = GetDatabaseNameFromConnectionString();

        var replaceClause = replace ? "WITH REPLACE" : "";
        var sql = $@"
            USE master;
            ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            RESTORE DATABASE [{databaseName}] FROM DISK = '{backupPath}' {replaceClause};
            ALTER DATABASE [{databaseName}] SET MULTI_USER;";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    public async Task RestoreToPointInTimeAsync(string backupPath, DateTime pointInTime)
    {
        var databaseName = GetDatabaseNameFromConnectionString();

        var sql = $@"
            USE master;
            ALTER DATABASE [{databaseName}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
            RESTORE DATABASE [{databaseName}] FROM DISK = '{backupPath}' 
            WITH STOPAT = '{pointInTime:yyyy-MM-dd HH:mm:ss}';
            ALTER DATABASE [{databaseName}] SET MULTI_USER;";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    public async Task<bool> VerifyBackupAsync(string backupPath)
    {
        var sql = $"RESTORE VERIFYONLY FROM DISK = '{backupPath}'";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);

        try
        {
            await command.ExecuteNonQueryAsync();
            return true;
        }
        catch
        {
            return false;
        }
    }

    private string GetDatabaseNameFromConnectionString()
    {
        var builder = new SqlConnectionStringBuilder(_connectionString);
        return builder.InitialCatalog;
    }
}

40. How do you handle Entity Framework database recovery?

Answer: Database recovery involves handling database failures and implementing recovery strategies.

Implementation:

public class DatabaseRecoveryService
{
    private readonly string _connectionString;
    private readonly ILogger<DatabaseRecoveryService> _logger;

    public DatabaseRecoveryService(IConfiguration configuration, ILogger<DatabaseRecoveryService> logger)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
        _logger = logger;
    }

    public async Task<bool> IsDatabaseHealthyAsync()
    {
        try
        {
            using var connection = new SqlConnection(_connectionString);
            await connection.OpenAsync();

            using var command = new SqlCommand("SELECT 1", connection);
            await command.ExecuteScalarAsync();

            return true;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Database health check failed");
            return false;
        }
    }

    public async Task<DatabaseRecoveryResult> AttemptRecoveryAsync()
    {
        var result = new DatabaseRecoveryResult();

        try
        {
            // Step 1: Check if database is in emergency mode
            if (await IsDatabaseInEmergencyModeAsync())
            {
                await SetDatabaseOnlineAsync();
                result.Steps.Add("Set database online");
            }

            // Step 2: Check for corruption
            if (await CheckDatabaseCorruptionAsync())
            {
                await RepairDatabaseAsync();
                result.Steps.Add("Repaired database corruption");
            }

            // Step 3: Verify database integrity
            if (await VerifyDatabaseIntegrityAsync())
            {
                result.Success = true;
                result.Steps.Add("Database integrity verified");
            }
            else
            {
                result.Steps.Add("Database integrity check failed");
            }
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Database recovery failed");
            result.Error = ex.Message;
        }

        return result;
    }

    private async Task<bool> IsDatabaseInEmergencyModeAsync()
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        var sql = $"SELECT state_desc FROM sys.databases WHERE name = '{databaseName}'";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        var state = await command.ExecuteScalarAsync() as string;

        return state == "EMERGENCY";
    }

    private async Task SetDatabaseOnlineAsync()
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        var sql = $"ALTER DATABASE [{databaseName}] SET ONLINE";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    private async Task<bool> CheckDatabaseCorruptionAsync()
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        var sql = $"DBCC CHECKDB('{databaseName}')";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);

        try
        {
            await command.ExecuteNonQueryAsync();
            return false; // No corruption found
        }
        catch
        {
            return true; // Corruption detected
        }
    }

    private async Task RepairDatabaseAsync()
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        var sql = $"DBCC CHECKDB('{databaseName}', REPAIR_ALLOW_DATA_LOSS)";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);
        await command.ExecuteNonQueryAsync();
    }

    private async Task<bool> VerifyDatabaseIntegrityAsync()
    {
        var databaseName = GetDatabaseNameFromConnectionString();
        var sql = $"DBCC CHECKDB('{databaseName}')";

        using var connection = new SqlConnection(_connectionString);
        await connection.OpenAsync();
        using var command = new SqlCommand(sql, connection);

        try
        {
            await command.ExecuteNonQueryAsync();
            return true;
        }
        catch
        {
            return false;
        }
    }

    private string GetDatabaseNameFromConnectionString()
    {
        var builder = new SqlConnectionStringBuilder(_connectionString);
        return builder.InitialCatalog;
    }
}

public class DatabaseRecoveryResult
{
    public bool Success { get; set; }
    public List<string> Steps { get; set; } = new List<string>();
    public string Error { get; set; }
}

41. How do you configure Entity Framework relationships?

Answer: Entity Framework relationships can be configured using Fluent API or data annotations.

Implementation:

// Data Annotations approach
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }

    // One-to-Many
    public virtual ICollection<Order> Orders { get; set; }

    // One-to-One
    public virtual UserProfile Profile { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }

    // Foreign key
    public int UserId { get; set; }
    public virtual User User { get; set; }
}

public class UserProfile
{
    public int Id { get; set; }
    public string Bio { get; set; }

    // One-to-One
    public int UserId { get; set; }
    public virtual User User { get; set; }
}

// Fluent API approach
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // One-to-Many relationship
    modelBuilder.Entity<User>()
        .HasMany(u => u.Orders)
        .WithOne(o => o.User)
        .HasForeignKey(o => o.UserId)
        .OnDelete(DeleteBehavior.Cascade);

    // One-to-One relationship
    modelBuilder.Entity<User>()
        .HasOne(u => u.Profile)
        .WithOne(p => p.User)
        .HasForeignKey<UserProfile>(p => p.UserId);

    // Many-to-Many relationship
    modelBuilder.Entity<User>()
        .HasMany(u => u.Roles)
        .WithMany(r => r.Users)
        .UsingEntity(j => j.ToTable("UserRoles"));
}

42. How do you handle Entity Framework inheritance mapping?

Answer: Entity Framework supports three inheritance strategies: Table-per-Hierarchy (TPH), Table-per-Type (TPT), and Table-per-Concrete-Type (TPC).

Implementation:

// Base class
public abstract class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

// Derived classes
public class Employee : Person
{
    public string EmployeeNumber { get; set; }
    public decimal Salary { get; set; }
}

public class Customer : Person
{
    public string CustomerNumber { get; set; }
    public DateTime RegistrationDate { get; set; }
}

// TPH Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Person>()
        .HasDiscriminator<string>("PersonType")
        .HasValue<Employee>("Employee")
        .HasValue<Customer>("Customer");

    // Configure properties for TPH
    modelBuilder.Entity<Employee>()
        .Property(e => e.EmployeeNumber)
        .HasColumnName("EmployeeNumber");

    modelBuilder.Entity<Customer>()
        .Property(c => c.CustomerNumber)
        .HasColumnName("CustomerNumber");
}

// TPT Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>().ToTable("Employees");
    modelBuilder.Entity<Customer>().ToTable("Customers");
}

// TPC Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>().ToTable("Employees");
    modelBuilder.Entity<Customer>().ToTable("Customers");

    // Configure key generation for TPC
    modelBuilder.Entity<Employee>()
        .Property(e => e.Id)
        .ValueGeneratedOnAdd();

    modelBuilder.Entity<Customer>()
        .Property(c => c.Id)
        .ValueGeneratedOnAdd();
}

43. How do you implement Entity Framework value objects?

Answer: Value objects are immutable objects that represent a concept in your domain without identity.

Implementation:

// Value Object
public class Address : ValueObject
{
    public string Street { get; private set; }
    public string City { get; private set; }
    public string State { get; private set; }
    public string ZipCode { get; private set; }

    public Address(string street, string city, string state, string zipCode)
    {
        Street = street;
        City = city;
        State = state;
        ZipCode = zipCode;
    }

    protected override IEnumerable<object> GetEqualityComponents()
    {
        yield return Street;
        yield return City;
        yield return State;
        yield return ZipCode;
    }
}

// Entity using value object
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address Address { get; set; }
}

// Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>(entity =>
    {
        entity.OwnsOne(u => u.Address, address =>
        {
            address.Property(a => a.Street).HasColumnName("Street");
            address.Property(a => a.City).HasColumnName("City");
            address.Property(a => a.State).HasColumnName("State");
            address.Property(a => a.ZipCode).HasColumnName("ZipCode");
        });
    });
}

// Base ValueObject class
public abstract class ValueObject
{
    protected abstract IEnumerable<object> GetEqualityComponents();

    public override bool Equals(object obj)
    {
        if (obj == null || obj.GetType() != GetType())
            return false;

        var other = (ValueObject)obj;
        return GetEqualityComponents().SequenceEqual(other.GetEqualityComponents());
    }

    public override int GetHashCode()
    {
        return GetEqualityComponents()
            .Select(x => x != null ? x.GetHashCode() : 0)
            .Aggregate((x, y) => x ^ y);
    }

    public static bool operator ==(ValueObject left, ValueObject right)
    {
        return EqualOperator(left, right);
    }

    public static bool operator !=(ValueObject left, ValueObject right)
    {
        return NotEqualOperator(left, right);
    }

    protected static bool EqualOperator(ValueObject left, ValueObject right)
    {
        if (left is null ^ right is null)
            return false;
        return left is null || left.Equals(right);
    }

    protected static bool NotEqualOperator(ValueObject left, ValueObject right)
    {
        return !EqualOperator(left, right);
    }
}

44. How do you handle Entity Framework complex types?

Answer: Complex types are similar to value objects but are configured differently in Entity Framework.

Implementation:

// Complex type
public class ContactInfo
{
    public string Phone { get; set; }
    public string Email { get; set; }
    public string Website { get; set; }
}

// Entity using complex type
public class Company
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ContactInfo ContactInfo { get; set; }
}

// Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Company>(entity =>
    {
        entity.OwnsOne(c => c.ContactInfo, contact =>
        {
            contact.Property(c => c.Phone).HasColumnName("Phone");
            contact.Property(c => c.Email).HasColumnName("Email");
            contact.Property(c => c.Website).HasColumnName("Website");
        });
    });
}

45. How do you implement Entity Framework shadow properties?

Answer: Shadow properties are properties that exist in the database but not in the entity class.

Implementation:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>(entity =>
    {
        // Add shadow property
        entity.Property<DateTime>("CreatedDate")
            .HasDefaultValueSql("GETUTCDATE()");

        entity.Property<string>("CreatedBy")
            .HasMaxLength(100);

        entity.Property<DateTime>("LastModifiedDate")
            .HasDefaultValueSql("GETUTCDATE()");

        entity.Property<string>("LastModifiedBy")
            .HasMaxLength(100);
    });
}

// Using shadow properties
public class UserService
{
    private readonly ApplicationDbContext _context;

    public UserService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<User> CreateUserAsync(User user, string createdBy)
    {
        _context.Entry(user).Property("CreatedBy").CurrentValue = createdBy;
        _context.Entry(user).Property("LastModifiedBy").CurrentValue = createdBy;

        _context.Users.Add(user);
        await _context.SaveChangesAsync();

        return user;
    }

    public async Task UpdateUserAsync(User user, string modifiedBy)
    {
        _context.Entry(user).Property("LastModifiedBy").CurrentValue = modifiedBy;
        _context.Entry(user).Property("LastModifiedDate").CurrentValue = DateTime.UtcNow;

        _context.Users.Update(user);
        await _context.SaveChangesAsync();
    }
}

46. How do you handle Entity Framework owned entities?

Answer: Owned entities are entities that are part of another entity and don't have their own identity.

Implementation:

// Owned entity
public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
}

// Entity with owned entity
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Address HomeAddress { get; set; }
    public Address WorkAddress { get; set; }
}

// Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>(entity =>
    {
        entity.OwnsOne(u => u.HomeAddress, address =>
        {
            address.Property(a => a.Street).HasColumnName("HomeStreet");
            address.Property(a => a.City).HasColumnName("HomeCity");
            address.Property(a => a.State).HasColumnName("HomeState");
            address.Property(a => a.ZipCode).HasColumnName("HomeZipCode");
        });

        entity.OwnsOne(u => u.WorkAddress, address =>
        {
            address.Property(a => a.Street).HasColumnName("WorkStreet");
            address.Property(a => a.City).HasColumnName("WorkCity");
            address.Property(a => a.State).HasColumnName("WorkState");
            address.Property(a => a.ZipCode).HasColumnName("WorkZipCode");
        });
    });
}

47. How do you implement Entity Framework table-per-hierarchy?

Answer: TPH stores all entities in a single table with a discriminator column.

Implementation:

// Base class
public abstract class Vehicle
{
    public int Id { get; set; }
    public string Make { get; set; }
    public string Model { get; set; }
    public int Year { get; set; }
}

// Derived classes
public class Car : Vehicle
{
    public int NumberOfDoors { get; set; }
    public string FuelType { get; set; }
}

public class Motorcycle : Vehicle
{
    public int EngineSize { get; set; }
    public bool HasSidecar { get; set; }
}

// TPH Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Vehicle>()
        .HasDiscriminator<string>("VehicleType")
        .HasValue<Car>("Car")
        .HasValue<Motorcycle>("Motorcycle");

    // Configure properties for derived types
    modelBuilder.Entity<Car>()
        .Property(c => c.NumberOfDoors)
        .HasColumnName("NumberOfDoors");

    modelBuilder.Entity<Car>()
        .Property(c => c.FuelType)
        .HasColumnName("FuelType");

    modelBuilder.Entity<Motorcycle>()
        .Property(m => m.EngineSize)
        .HasColumnName("EngineSize");

    modelBuilder.Entity<Motorcycle>()
        .Property(m => m.HasSidecar)
        .HasColumnName("HasSidecar");
}

48. How do you handle Entity Framework table-per-type?

Answer: TPT creates separate tables for each type in the inheritance hierarchy.

Implementation:

// Base class
public abstract class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

// Derived classes
public class Employee : Person
{
    public string EmployeeNumber { get; set; }
    public decimal Salary { get; set; }
    public DateTime HireDate { get; set; }
}

public class Customer : Person
{
    public string CustomerNumber { get; set; }
    public DateTime RegistrationDate { get; set; }
    public bool IsActive { get; set; }
}

// TPT Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>().ToTable("Employees");
    modelBuilder.Entity<Customer>().ToTable("Customers");

    // Configure relationships
    modelBuilder.Entity<Employee>()
        .HasOne<Person>()
        .WithOne()
        .HasForeignKey<Employee>(e => e.Id);

    modelBuilder.Entity<Customer>()
        .HasOne<Person>()
        .WithOne()
        .HasForeignKey<Customer>(c => c.Id);
}

49. How do you implement Entity Framework table-per-concrete-type?

Answer: TPC creates separate tables for each concrete type without a base table.

Implementation:

// Base class
public abstract class Payment
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public DateTime PaymentDate { get; set; }
    public string Description { get; set; }
}

// Concrete classes
public class CreditCardPayment : Payment
{
    public string CardNumber { get; set; }
    public string CardType { get; set; }
    public DateTime ExpiryDate { get; set; }
}

public class BankTransferPayment : Payment
{
    public string AccountNumber { get; set; }
    public string BankName { get; set; }
    public string RoutingNumber { get; set; }
}

// TPC Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<CreditCardPayment>().ToTable("CreditCardPayments");
    modelBuilder.Entity<BankTransferPayment>().ToTable("BankTransferPayments");

    // Configure key generation for each table
    modelBuilder.Entity<CreditCardPayment>()
        .Property(p => p.Id)
        .ValueGeneratedOnAdd();

    modelBuilder.Entity<BankTransferPayment>()
        .Property(p => p.Id)
        .ValueGeneratedOnAdd();
}

50. How do you handle Entity Framework many-to-many relationships?

Answer: Many-to-many relationships can be configured with or without a join entity.

Implementation:

// Simple many-to-many
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
}

public class Role
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

// Many-to-many with join entity
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<UserRole> UserRoles { get; set; }
}

public class Role
{
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<UserRole> UserRoles { get; set; }
}

public class UserRole
{
    public int UserId { get; set; }
    public int RoleId { get; set; }
    public DateTime AssignedDate { get; set; }
    public string AssignedBy { get; set; }

    public virtual User User { get; set; }
    public virtual Role Role { get; set; }
}

// Configuration
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Simple many-to-many
    modelBuilder.Entity<User>()
        .HasMany(u => u.Roles)
        .WithMany(r => r.Users)
        .UsingEntity(j => j.ToTable("UserRoles"));

    // Many-to-many with join entity
    modelBuilder.Entity<UserRole>()
        .HasKey(ur => new { ur.UserId, ur.RoleId });

    modelBuilder.Entity<UserRole>()
        .HasOne(ur => ur.User)
        .WithMany(u => u.UserRoles)
        .HasForeignKey(ur => ur.UserId);

    modelBuilder.Entity<UserRole>()
        .HasOne(ur => ur.Role)
        .WithMany(r => r.UserRoles)
        .HasForeignKey(ur => ur.RoleId);
}

// Usage examples
public class UserService
{
    private readonly ApplicationDbContext _context;

    public UserService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task AssignRoleToUserAsync(int userId, int roleId, string assignedBy)
    {
        var userRole = new UserRole
        {
            UserId = userId,
            RoleId = roleId,
            AssignedDate = DateTime.UtcNow,
            AssignedBy = assignedBy
        };

        _context.UserRoles.Add(userRole);
        await _context.SaveChangesAsync();
    }

    public async Task<List<Role>> GetUserRolesAsync(int userId)
    {
        return await _context.Users
            .Where(u => u.Id == userId)
            .SelectMany(u => u.Roles)
            .ToListAsync();
    }
}

Entity Framework Technical Interview Answers (Technical Lead Level)

51. How do you implement Entity Framework transactions?

Use the transaction scope already created by one SaveChanges call when it covers the required work. For several SaveChanges calls, begin a transaction and handle errors; EF automatically creates savepoints in many cases. Distributed transactions and TransactionScope have provider and deployment constraints, so validate them in the target environment. Reference: EF Core transactions.

52. How do you handle Entity Framework concurrency conflicts?

Optimistic concurrency detects a conflicting update by using a concurrency token such as rowversion. Catch DbUpdateConcurrencyException, load or report current values, and choose a business-specific resolution; blindly retrying a user edit can overwrite another update. Pessimistic locking is database-specific and should be brief.

53. How do you implement Entity Framework optimistic concurrency?

Answer: Optimistic concurrency assumes conflicts are rare and checks for conflicts at commit time:

Implementation with RowVersion

public class OptimisticConcurrencyService
{
    private readonly ApplicationDbContext _context;

    public async Task<bool> UpdateWithOptimisticConcurrencyAsync(Product product)
    {
        var retryCount = 0;
        const int maxRetries = 3;

        while (retryCount < maxRetries)
        {
            try
            {
                var existingProduct = await _context.Products
                    .FirstOrDefaultAsync(p => p.Id == product.Id);

                if (existingProduct == null)
                    return false;

                // Check if data has changed since we loaded it
                if (existingProduct.RowVersion.SequenceEqual(product.RowVersion))
                {
                    // Update the entity
                    _context.Entry(existingProduct).CurrentValues.SetValues(product);
                    await _context.SaveChangesAsync();
                    return true;
                }
                else
                {
                    // Data has changed, refresh and retry
                    await _context.Entry(existingProduct).ReloadAsync();
                    retryCount++;
                }
            }
            catch (DbUpdateConcurrencyException)
            {
                retryCount++;
                if (retryCount >= maxRetries)
                    throw;
            }
        }

        return false;
    }
}

Custom Concurrency Token

public class Document
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }
    public DateTime LastModified { get; set; } // Custom concurrency token
}

// In DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Document>()
        .Property(d => d.LastModified)
        .IsConcurrencyToken();
}

54. How do you handle Entity Framework pessimistic concurrency?

Answer: Pessimistic concurrency locks records to prevent conflicts:

Using Database Locks

public class PessimisticConcurrencyService
{
    private readonly ApplicationDbContext _context;

    public async Task<Product> GetProductWithLockAsync(int productId)
    {
        // Use SQL Server UPDLOCK hint for pessimistic locking
        var product = await _context.Products
            .FromSqlRaw("SELECT * FROM Products WITH (UPDLOCK) WHERE Id = {0}", productId)
            .FirstOrDefaultAsync();

        return product;
    }

    public async Task<bool> UpdateProductWithLockAsync(int productId, Action<Product> updateAction)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();
        try
        {
            // Lock the record
            var product = await GetProductWithLockAsync(productId);
            if (product == null)
                return false;

            // Apply updates
            updateAction(product);
            await _context.SaveChangesAsync();

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

Using Row-Level Locking

public async Task<bool> ReserveInventoryAsync(int productId, int quantity)
{
    using var transaction = await _context.Database.BeginTransactionAsync();
    try
    {
        // Lock the specific row
        var product = await _context.Products
            .FromSqlRaw("SELECT * FROM Products WITH (ROWLOCK, UPDLOCK) WHERE Id = {0}", productId)
            .FirstOrDefaultAsync();

        if (product == null || product.StockQuantity < quantity)
            return false;

        product.StockQuantity -= quantity;
        await _context.SaveChangesAsync();

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

55. How do you implement Entity Framework distributed transactions?

Answer: Distributed transactions span multiple databases or services:

Using TransactionScope

public class DistributedTransactionService
{
    private readonly ApplicationDbContext _orderContext;
    private readonly PaymentDbContext _paymentContext;
    private readonly InventoryDbContext _inventoryContext;

    public async Task<bool> ProcessOrderAsync(Order order, Payment payment)
    {
        using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
        try
        {
            // Step 1: Create order
            _orderContext.Orders.Add(order);
            await _orderContext.SaveChangesAsync();

            // Step 2: Process payment
            _paymentContext.Payments.Add(payment);
            await _paymentContext.SaveChangesAsync();

            // Step 3: Update inventory
            var inventory = await _inventoryContext.Products
                .FirstOrDefaultAsync(p => p.Id == order.ProductId);
            inventory.StockQuantity -= order.Quantity;
            await _inventoryContext.SaveChangesAsync();

            scope.Complete();
            return true;
        }
        catch (Exception)
        {
            // TransactionScope automatically rolls back all changes
            throw;
        }
    }
}

Using Saga Pattern (Alternative to Distributed Transactions)

public class OrderSaga
{
    private readonly IServiceProvider _serviceProvider;

    public async Task<bool> ProcessOrderSagaAsync(Order order)
    {
        var saga = new SagaBuilder()
            .AddStep("CreateOrder", async () => await CreateOrderAsync(order))
            .AddCompensation("CancelOrder", async () => await CancelOrderAsync(order.Id))
            .AddStep("ProcessPayment", async () => await ProcessPaymentAsync(order))
            .AddCompensation("RefundPayment", async () => await RefundPaymentAsync(order.Id))
            .AddStep("UpdateInventory", async () => await UpdateInventoryAsync(order))
            .AddCompensation("RestoreInventory", async () => await RestoreInventoryAsync(order))
            .Build();

        return await saga.ExecuteAsync();
    }
}

56. How do you handle Entity Framework transaction isolation levels?

Answer: Isolation levels control how transactions interact with each other:

Setting Isolation Levels

public class IsolationLevelService
{
    private readonly ApplicationDbContext _context;

    public async Task<bool> UpdateWithIsolationLevelAsync(int productId, decimal newPrice)
    {
        // Use Serializable isolation level for maximum consistency
        using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.Serializable);
        try
        {
            var product = await _context.Products
                .FirstOrDefaultAsync(p => p.Id == productId);

            if (product != null)
            {
                product.Price = newPrice;
                await _context.SaveChangesAsync();
            }

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

    public async Task<List<Product>> GetProductsWithReadCommittedAsync()
    {
        using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted);
        try
        {
            var products = await _context.Products.ToListAsync();
            await transaction.CommitAsync();
            return products;
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

Different Isolation Levels Usage

public class IsolationLevelExamples
{
    private readonly ApplicationDbContext _context;

    // Read Uncommitted - Lowest isolation, allows dirty reads
    public async Task<decimal> GetPriceWithReadUncommittedAsync(int productId)
    {
        using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.ReadUncommitted);
        var product = await _context.Products.FindAsync(productId);
        await transaction.CommitAsync();
        return product?.Price ?? 0;
    }

    // Read Committed - Prevents dirty reads
    public async Task<decimal> GetPriceWithReadCommittedAsync(int productId)
    {
        using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted);
        var product = await _context.Products.FindAsync(productId);
        await transaction.CommitAsync();
        return product?.Price ?? 0;
    }

    // Repeatable Read - Prevents non-repeatable reads
    public async Task<bool> ValidatePriceConsistencyAsync(int productId)
    {
        using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.RepeatableRead);

        var price1 = await _context.Products.Where(p => p.Id == productId).Select(p => p.Price).FirstAsync();

        // Simulate some processing time
        await Task.Delay(100);

        var price2 = await _context.Products.Where(p => p.Id == productId).Select(p => p.Price).FirstAsync();

        await transaction.CommitAsync();
        return price1 == price2;
    }

    // Serializable - Highest isolation, prevents phantom reads
    public async Task<bool> ProcessCriticalUpdateAsync(int productId, decimal newPrice)
    {
        using var transaction = await _context.Database.BeginTransactionAsync(IsolationLevel.Serializable);
        try
        {
            var product = await _context.Products.FindAsync(productId);
            if (product != null)
            {
                product.Price = newPrice;
                await _context.SaveChangesAsync();
            }
            await transaction.CommitAsync();
            return true;
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

57. How do you implement Entity Framework transaction rollbacks?

Answer: Transaction rollbacks can be implemented using try-catch blocks or automatic rollback mechanisms:

Manual Rollback Implementation

public class TransactionRollbackService
{
    private readonly ApplicationDbContext _context;

    public async Task<bool> ProcessOrderWithRollbackAsync(Order order, List<OrderItem> items)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();
        try
        {
            // Step 1: Create order
            _context.Orders.Add(order);
            await _context.SaveChangesAsync();

            // Step 2: Validate inventory
            foreach (var item in items)
            {
                var product = await _context.Products.FindAsync(item.ProductId);
                if (product.StockQuantity < item.Quantity)
                {
                    throw new InvalidOperationException($"Insufficient stock for product {item.ProductId}");
                }
            }

            // Step 3: Add order items
            foreach (var item in items)
            {
                item.OrderId = order.Id;
                _context.OrderItems.Add(item);
            }
            await _context.SaveChangesAsync();

            // Step 4: Update inventory
            foreach (var item in items)
            {
                var product = await _context.Products.FindAsync(item.ProductId);
                product.StockQuantity -= item.Quantity;
            }
            await _context.SaveChangesAsync();

            await transaction.CommitAsync();
            return true;
        }
        catch (Exception ex)
        {
            // Manual rollback
            await transaction.RollbackAsync();
            _logger.LogError($"Transaction rolled back: {ex.Message}");
            return false;
        }
    }
}

Automatic Rollback with Using Statement

public class AutomaticRollbackService
{
    private readonly ApplicationDbContext _context;

    public async Task<bool> ProcessOrderAsync(Order order)
    {
        // Transaction automatically rolls back if not committed
        using var transaction = await _context.Database.BeginTransactionAsync();

        _context.Orders.Add(order);
        await _context.SaveChangesAsync();

        // If any exception occurs here, transaction automatically rolls back
        await ValidateOrderAsync(order);

        await transaction.CommitAsync();
        return true;
    }

    private async Task ValidateOrderAsync(Order order)
    {
        // Simulate validation that might fail
        if (order.TotalAmount > 10000)
        {
            throw new InvalidOperationException("Order amount exceeds limit");
        }
    }
}

58. How do you handle Entity Framework deadlock scenarios?

Answer: Deadlocks occur when two transactions wait for each other's resources. Here are strategies to handle them:

Deadlock Detection and Retry

public class DeadlockHandlingService
{
    private readonly ApplicationDbContext _context;
    private readonly ILogger<DeadlockHandlingService> _logger;

    public async Task<bool> UpdateProductWithDeadlockHandlingAsync(int productId, decimal newPrice)
    {
        var maxRetries = 3;
        var retryCount = 0;

        while (retryCount < maxRetries)
        {
            try
            {
                using var transaction = await _context.Database.BeginTransactionAsync();

                var product = await _context.Products
                    .FirstOrDefaultAsync(p => p.Id == productId);

                if (product != null)
                {
                    product.Price = newPrice;
                    await _context.SaveChangesAsync();
                }

                await transaction.CommitAsync();
                return true;
            }
            catch (SqlException ex) when (ex.Number == 1205) // Deadlock error number
            {
                retryCount++;
                _logger.LogWarning($"Deadlock detected on attempt {retryCount} for product {productId}");

                if (retryCount >= maxRetries)
                {
                    _logger.LogError($"Max retries exceeded for product {productId}");
                    throw;
                }

                // Exponential backoff
                await Task.Delay(TimeSpan.FromMilliseconds(Math.Pow(2, retryCount) * 100));
            }
        }

        return false;
    }
}

Deadlock Prevention Strategies

public class DeadlockPreventionService
{
    private readonly ApplicationDbContext _context;

    // Strategy 1: Consistent ordering of operations
    public async Task<bool> UpdateMultipleProductsAsync(List<int> productIds, decimal newPrice)
    {
        using var transaction = await _context.Database.BeginTransactionAsync();
        try
        {
            // Sort IDs to ensure consistent ordering
            var sortedIds = productIds.OrderBy(id => id).ToList();

            foreach (var id in sortedIds)
            {
                var product = await _context.Products.FindAsync(id);
                if (product != null)
                {
                    product.Price = newPrice;
                }
            }

            await _context.SaveChangesAsync();
            await transaction.CommitAsync();
            return true;
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
            throw;
        }
    }

    // Strategy 2: Use shorter transactions
    public async Task<bool> UpdateProductWithShortTransactionAsync(int productId, decimal newPrice)
    {
        // Load data outside transaction
        var product = await _context.Products.FindAsync(productId);
        if (product == null) return false;

        // Short transaction for update only
        using var transaction = await _context.Database.BeginTransactionAsync();
        try
        {
            product.Price = newPrice;
            await _context.SaveChangesAsync();
            await transaction.CommitAsync();
            return true;
        }
        catch (Exception)
        {
            await transaction.RollbackAsync();
            throw;
        }
    }
}

59. How do you implement Entity Framework retry policies?

Answer: Retry policies help handle transient failures and improve application resilience:

Custom Retry Policy Implementation

public class RetryPolicyService
{
    private readonly ApplicationDbContext _context;
    private readonly ILogger<RetryPolicyService> _logger;

    public async Task<bool> ExecuteWithRetryAsync(Func<Task<bool>> operation)
    {
        var maxRetries = 3;
        var retryCount = 0;

        while (retryCount < maxRetries)
        {
            try
            {
                return await operation();
            }
            catch (SqlException ex) when (IsTransientError(ex))
            {
                retryCount++;
                _logger.LogWarning($"Transient error on attempt {retryCount}: {ex.Message}");

                if (retryCount >= maxRetries)
                {
                    _logger.LogError($"Max retries exceeded: {ex.Message}");
                    throw;
                }

                // Exponential backoff with jitter
                var delay = TimeSpan.FromMilliseconds(Math.Pow(2, retryCount) * 100 + Random.Shared.Next(50));
                await Task.Delay(delay);
            }
        }

        return false;
    }

    private bool IsTransientError(SqlException ex)
    {
        // SQL Server transient error numbers
        var transientErrors = new[] { 1205, 1222, 8645, 8651, 2, 53, 64, 233, 10053, 10054, 10060, 40197, 40501, 40613, 49918, 49919, 49920 };
        return transientErrors.Contains(ex.Number);
    }

    public async Task<bool> UpdateProductWithRetryAsync(int productId, decimal newPrice)
    {
        return await ExecuteWithRetryAsync(async () =>
        {
            var product = await _context.Products.FindAsync(productId);
            if (product != null)
            {
                product.Price = newPrice;
                await _context.SaveChangesAsync();
                return true;
            }
            return false;
        });
    }
}

Using Polly for Advanced Retry Policies

public class PollyRetryService
{
    private readonly ApplicationDbContext _context;
    private readonly IAsyncPolicy<int> _retryPolicy;

    public PollyRetryService(ApplicationDbContext context)
    {
        _context = context;

        // Configure retry policy with Polly
        _retryPolicy = Policy<int>
            .Handle<SqlException>(ex => IsTransientError(ex))
            .Or<TimeoutException>()
            .WaitAndRetryAsync(
                retryCount: 3,
                sleepDurationProvider: retryAttempt => 
                    TimeSpan.FromMilliseconds(Math.Pow(2, retryAttempt) * 100),
                onRetry: (exception, timeSpan, retryCount, context) =>
                {
                    // Log retry attempt
                    Console.WriteLine($"Retry {retryCount} after {timeSpan.TotalMilliseconds}ms due to {exception.Message}");
                }
            );
    }

    public async Task<bool> UpdateProductWithPollyRetryAsync(int productId, decimal newPrice)
    {
        return await _retryPolicy.ExecuteAsync(async () =>
        {
            var product = await _context.Products.FindAsync(productId);
            if (product != null)
            {
                product.Price = newPrice;
                await _context.SaveChangesAsync();
                return 1; // Success
            }
            return 0; // Not found
        }) > 0;
    }

    private bool IsTransientError(SqlException ex)
    {
        var transientErrors = new[] { 1205, 1222, 8645, 8651, 2, 53, 64, 233, 10053, 10054, 10060, 40197, 40501, 40613, 49918, 49919, 49920 };
        return transientErrors.Contains(ex.Number);
    }
}

60. How do you handle Entity Framework transaction timeouts?

Answer: Transaction timeouts prevent long-running transactions from blocking resources:

Setting Transaction Timeouts

public class TransactionTimeoutService
{
    private readonly ApplicationDbContext _context;

    public async Task<bool> ExecuteWithTimeoutAsync(Func<Task<bool>> operation, TimeSpan timeout)
    {
        using var cts = new CancellationTokenSource(timeout);

        try
        {
            using var transaction = await _context.Database.BeginTransactionAsync();

            // Set command timeout
            _context.Database.SetCommandTimeout((int)timeout.TotalSeconds);

            var result = await operation();

            await transaction.CommitAsync();
            return result;
        }
        catch (OperationCanceledException)
        {
            throw new TimeoutException($"Operation timed out after {timeout.TotalSeconds} seconds");
        }
    }

    public async Task<bool> UpdateProductWithTimeoutAsync(int productId, decimal newPrice)
    {
        return await ExecuteWithTimeoutAsync(async () =>
        {
            var product = await _context.Products.FindAsync(productId);
            if (product != null)
            {
                product.Price = newPrice;
                await _context.SaveChangesAsync();
                return true;
            }
            return false;
        }, TimeSpan.FromSeconds(30));
    }
}

Global Timeout Configuration

public class ApplicationDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connectionString, options =>
        {
            options.CommandTimeout(30); // 30 seconds timeout
        });
    }
}

---

Testing & Mocking

61. How do you implement Entity Framework unit testing?

Answer: Unit testing EF Core involves using in-memory databases or mocking:

Using In-Memory Database

[TestClass]
public class ProductServiceTests
{
    private ApplicationDbContext _context;
    private ProductService _service;

    [TestInitialize]
    public void Setup()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        _context = new ApplicationDbContext(options);
        _service = new ProductService(_context);
    }

    [TestCleanup]
    public void Cleanup()
    {
        _context.Dispose();
    }

    [TestMethod]
    public async Task CreateProduct_ShouldAddProductToDatabase()
    {
        // Arrange
        var product = new Product { Name = "Test Product", Price = 100.00m };

        // Act
        var result = await _service.CreateProductAsync(product);

        // Assert
        Assert.IsTrue(result);
        var savedProduct = await _context.Products.FirstOrDefaultAsync(p => p.Name == "Test Product");
        Assert.IsNotNull(savedProduct);
        Assert.AreEqual(100.00m, savedProduct.Price);
    }

    [TestMethod]
    public async Task GetProductById_ShouldReturnCorrectProduct()
    {
        // Arrange
        var product = new Product { Name = "Test Product", Price = 100.00m };
        _context.Products.Add(product);
        await _context.SaveChangesAsync();

        // Act
        var result = await _service.GetProductByIdAsync(product.Id);

        // Assert
        Assert.IsNotNull(result);
        Assert.AreEqual("Test Product", result.Name);
    }
}

Using Mocking with Moq

[TestClass]
public class ProductServiceMockTests
{
    private Mock<ApplicationDbContext> _mockContext;
    private Mock<DbSet<Product>> _mockProductDbSet;
    private ProductService _service;

    [TestInitialize]
    public void Setup()
    {
        _mockContext = new Mock<ApplicationDbContext>();
        _mockProductDbSet = new Mock<DbSet<Product>>();
        _mockContext.Setup(c => c.Products).Returns(_mockProductDbSet.Object);
        _service = new ProductService(_mockContext.Object);
    }

    [TestMethod]
    public async Task CreateProduct_ShouldCallAddAndSaveChanges()
    {
        // Arrange
        var product = new Product { Name = "Test Product", Price = 100.00m };
        _mockProductDbSet.Setup(d => d.Add(It.IsAny<Product>())).Returns((EntityEntry<Product>)null);
        _mockContext.Setup(c => c.SaveChangesAsync(It.IsAny<CancellationToken>()))
            .ReturnsAsync(1);

        // Act
        var result = await _service.CreateProductAsync(product);

        // Assert
        Assert.IsTrue(result);
        _mockProductDbSet.Verify(d => d.Add(It.IsAny<Product>()), Times.Once);
        _mockContext.Verify(c => c.SaveChangesAsync(It.IsAny<CancellationToken>()), Times.Once);
    }
}

62. How do you handle Entity Framework integration testing?

Answer: Integration testing uses real databases to test the complete data flow:

Integration Test Setup

[TestClass]
public class ProductServiceIntegrationTests
{
    private ApplicationDbContext _context;
    private ProductService _service;
    private string _connectionString;

    [TestInitialize]
    public void Setup()
    {
        // Use test database connection
        _connectionString = "Server=(localdb)\\mssqllocaldb;Database=TestDb;Trusted_Connection=true;";

        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseSqlServer(_connectionString)
            .Options;

        _context = new ApplicationDbContext(options);
        _context.Database.EnsureCreated();
        _service = new ProductService(_context);
    }

    [TestCleanup]
    public void Cleanup()
    {
        _context.Database.EnsureDeleted();
        _context.Dispose();
    }

    [TestMethod]
    public async Task CreateAndRetrieveProduct_IntegrationTest()
    {
        // Arrange
        var product = new Product { Name = "Integration Test Product", Price = 150.00m };

        // Act
        var createResult = await _service.CreateProductAsync(product);
        var retrievedProduct = await _service.GetProductByIdAsync(product.Id);

        // Assert
        Assert.IsTrue(createResult);
        Assert.IsNotNull(retrievedProduct);
        Assert.AreEqual("Integration Test Product", retrievedProduct.Name);
        Assert.AreEqual(150.00m, retrievedProduct.Price);
    }

    [TestMethod]
    public async Task UpdateProduct_IntegrationTest()
    {
        // Arrange
        var product = new Product { Name = "Original Name", Price = 100.00m };
        await _service.CreateProductAsync(product);

        // Act
        product.Name = "Updated Name";
        product.Price = 200.00m;
        var updateResult = await _service.UpdateProductAsync(product);
        var updatedProduct = await _service.GetProductByIdAsync(product.Id);

        // Assert
        Assert.IsTrue(updateResult);
        Assert.AreEqual("Updated Name", updatedProduct.Name);
        Assert.AreEqual(200.00m, updatedProduct.Price);
    }
}

63. How do you implement Entity Framework in-memory database testing?

Answer: In-memory databases provide fast, isolated testing without external dependencies:

In-Memory Database Test Implementation

[TestClass]
public class InMemoryDatabaseTests
{
    private ApplicationDbContext _context;
    private ProductService _service;

    [TestInitialize]
    public void Setup()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(databaseName: $"TestDb_{Guid.NewGuid()}")
            .Options;

        _context = new ApplicationDbContext(options);
        _service = new ProductService(_context);
    }

    [TestCleanup]
    public void Cleanup()
    {
        _context.Dispose();
    }

    [TestMethod]
    public async Task InMemoryDatabase_ShouldPersistData()
    {
        // Arrange
        var product1 = new Product { Name = "Product 1", Price = 100.00m };
        var product2 = new Product { Name = "Product 2", Price = 200.00m };

        // Act
        await _service.CreateProductAsync(product1);
        await _service.CreateProductAsync(product2);

        var allProducts = await _service.GetAllProductsAsync();

        // Assert
        Assert.AreEqual(2, allProducts.Count);
        Assert.IsTrue(allProducts.Any(p => p.Name == "Product 1"));
        Assert.IsTrue(allProducts.Any(p => p.Name == "Product 2"));
    }

    [TestMethod]
    public async Task InMemoryDatabase_ShouldHandleTransactions()
    {
        // Arrange
        var product = new Product { Name = "Test Product", Price = 100.00m };

        // Act & Assert
        using var transaction = await _context.Database.BeginTransactionAsync();

        await _service.CreateProductAsync(product);

        // Data should be visible within transaction
        var productInTransaction = await _service.GetProductByIdAsync(product.Id);
        Assert.IsNotNull(productInTransaction);

        await transaction.RollbackAsync();

        // Data should not be visible after rollback
        var productAfterRollback = await _service.GetProductByIdAsync(product.Id);
        Assert.IsNull(productAfterRollback);
    }
}

64. How do you handle Entity Framework test data setup?

Answer: Test data setup ensures consistent test conditions:

Test Data Builder Pattern

public class ProductBuilder
{
    private string _name = "Test Product";
    private decimal _price = 100.00m;
    private string _description = "Test Description";
    private bool _isActive = true;

    public ProductBuilder WithName(string name)
    {
        _name = name;
        return this;
    }

    public ProductBuilder WithPrice(decimal price)
    {
        _price = price;
        return this;
    }

    public ProductBuilder WithDescription(string description)
    {
        _description = description;
        return this;
    }

    public ProductBuilder IsActive(bool isActive)
    {
        _isActive = isActive;
        return this;
    }

    public Product Build()
    {
        return new Product
        {
            Name = _name,
            Price = _price,
            Description = _description,
            IsActive = _isActive
        };
    }
}

[TestClass]
public class TestDataSetupTests
{
    private ApplicationDbContext _context;
    private ProductService _service;

    [TestInitialize]
    public void Setup()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        _context = new ApplicationDbContext(options);
        _service = new ProductService(_context);
    }

    [TestMethod]
    public async Task TestWithBuilderPattern()
    {
        // Arrange
        var product = new ProductBuilder()
            .WithName("Custom Product")
            .WithPrice(250.00m)
            .WithDescription("Custom Description")
            .IsActive(false)
            .Build();

        // Act
        await _service.CreateProductAsync(product);

        // Assert
        var savedProduct = await _service.GetProductByIdAsync(product.Id);
        Assert.AreEqual("Custom Product", savedProduct.Name);
        Assert.AreEqual(250.00m, savedProduct.Price);
        Assert.IsFalse(savedProduct.IsActive);
    }
}

Test Data Factory

public static class TestDataFactory
{
    public static Product CreateProduct(string name = null, decimal? price = null)
    {
        return new Product
        {
            Name = name ?? $"Test Product {Guid.NewGuid()}",
            Price = price ?? 100.00m,
            Description = "Test Description",
            IsActive = true
        };
    }

    public static List<Product> CreateProducts(int count)
    {
        var products = new List<Product>();
        for (int i = 0; i < count; i++)
        {
            products.Add(CreateProduct($"Product {i + 1}", 100.00m + (i * 10)));
        }
        return products;
    }

    public static Order CreateOrder(int customerId, List<OrderItem> items = null)
    {
        return new Order
        {
            CustomerId = customerId,
            OrderDate = DateTime.UtcNow,
            TotalAmount = items?.Sum(i => i.Quantity * i.UnitPrice) ?? 0,
            OrderItems = items ?? new List<OrderItem>()
        };
    }
}

65. How do you implement Entity Framework test data cleanup?

Answer: Test data cleanup ensures test isolation and prevents data pollution:

Automatic Cleanup with TestInitialize/TestCleanup

[TestClass]
public class TestDataCleanupTests
{
    private ApplicationDbContext _context;
    private ProductService _service;

    [TestInitialize]
    public void Setup()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
            .Options;

        _context = new ApplicationDbContext(options);
        _service = new ProductService(_context);
    }

    [TestCleanup]
    public void Cleanup()
    {
        // Clean up all data
        _context.Products.RemoveRange(_context.Products);
        _context.Orders.RemoveRange(_context.Orders);
        _context.OrderItems.RemoveRange(_context.OrderItems);
        _context.SaveChanges();
        _context.Dispose();
    }

    [TestMethod]
    public async Task TestWithCleanup()
    {
        // Arrange
        var product = TestDataFactory.CreateProduct();

        // Act
        await _service.CreateProductAsync(product);

        // Assert
        var count = await _context.Products.CountAsync();
        Assert.AreEqual(1, count);
    }

    [TestMethod]
    public async Task TestIsolation_ShouldNotSeeDataFromOtherTests()
    {
        // This test should start with a clean database
        var count = await _context.Products.CountAsync();
        Assert.AreEqual(0, count);
    }
}

Database Transaction Rollback for Cleanup

[TestClass]
public class TransactionRollbackTests
{
    private ApplicationDbContext _context;
    private ProductService _service;
    private IDbContextTransaction _transaction;

    [TestInitialize]
    public void Setup()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(databaseName: "SharedTestDb")
            .Options;

        _context = new ApplicationDbContext(options);
        _service = new ProductService(_context);

        // Start transaction for rollback
        _transaction = _context.Database.BeginTransaction();
    }

    [TestCleanup]
    public void Cleanup()
    {
        // Rollback transaction to undo all changes
        _transaction?.Rollback();
        _transaction?.Dispose();
        _context.Dispose();
    }

    [TestMethod]
    public async Task TestWithTransactionRollback()
    {
        // Arrange
        var product = TestDataFactory.CreateProduct();

        // Act
        await _service.CreateProductAsync(product);

        // Assert
        var savedProduct = await _service.GetProductByIdAsync(product.Id);
        Assert.IsNotNull(savedProduct);

        // Transaction will be rolled back in cleanup, so data won't persist
    }
}

66. How do you handle Entity Framework test isolation?

Answer: Test isolation ensures tests don't interfere with each other:

Unique Database Names for Isolation

[TestClass]
public class TestIsolationTests
{
    private ApplicationDbContext _context;
    private ProductService _service;

    [TestInitialize]
    public void Setup()
    {
        // Each test gets its own database instance
        var databaseName = $"TestDb_{Guid.NewGuid()}";
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(databaseName)
            .Options;

        _context = new ApplicationDbContext(options);
        _service = new ProductService(_context);
    }

    [TestCleanup]
    public void Cleanup()
    {
        _context.Dispose();
    }

    [TestMethod]
    public async Task Test1_ShouldNotAffectTest2()
    {
        // Arrange
        var product = TestDataFactory.CreateProduct("Test1 Product");

        // Act
        await _service.CreateProductAsync(product);

        // Assert
        var count = await _context.Products.CountAsync();
        Assert.AreEqual(1, count);
    }

    [TestMethod]
    public async Task Test2_ShouldStartWithCleanDatabase()
    {
        // This test should start with an empty database
        var count = await _context.Products.CountAsync();
        Assert.AreEqual(0, count);
    }
}

Test Class Isolation with TestCategory

[TestClass]
[TestCategory("Integration")]
public class IntegrationTests
{
    private static ApplicationDbContext _sharedContext;
    private ProductService _service;

    [ClassInitialize]
    public static void ClassSetup(TestContext context)
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase("IntegrationTestDb")
            .Options;

        _sharedContext = new ApplicationDbContext(options);
    }

    [ClassCleanup]
    public static void ClassCleanup()
    {
        _sharedContext?.Dispose();
    }

    [TestInitialize]
    public void Setup()
    {
        _service = new ProductService(_sharedContext);
        // Clean up before each test
        _sharedContext.Products.RemoveRange(_sharedContext.Products);
        _sharedContext.SaveChanges();
    }

    [TestMethod]
    public async Task IntegrationTest1()
    {
        // Test implementation
    }

    [TestMethod]
    public async Task IntegrationTest2()
    {
        // Test implementation
    }
}

67. How do you implement Entity Framework mocking strategies?

Answer: Entity Framework mocking strategies involve creating test doubles for DbContext and DbSet to isolate unit tests from the database.

Key Strategies: - In-Memory Database: Use EF Core's in-memory provider - Mocking Frameworks: Use Moq, NSubstitute, or similar - Repository Pattern: Abstract data access behind interfaces - Test Doubles: Create fake implementations

// 1. In-Memory Database Strategy
public class TestDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseInMemoryDatabase("TestDb");
    }
}

// 2. Repository Pattern with Mocking
public interface IUserRepository
{
    Task<User> GetByIdAsync(int id);
    Task<IEnumerable<User>> GetAllAsync();
    Task AddAsync(User user);
}

public class UserRepository : IUserRepository
{
    private readonly ApplicationDbContext _context;

    public UserRepository(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<User> GetByIdAsync(int id)
    {
        return await _context.Users.FindAsync(id);
    }
}

// 3. Mocking with Moq
[Test]
public async Task GetUserById_ShouldReturnUser_WhenUserExists()
{
    // Arrange
    var mockRepository = new Mock<IUserRepository>();
    var expectedUser = new User { Id = 1, Name = "John Doe" };

    mockRepository.Setup(r => r.GetByIdAsync(1))
                 .ReturnsAsync(expectedUser);

    var userService = new UserService(mockRepository.Object);

    // Act
    var result = await userService.GetUserById(1);

    // Assert
    Assert.AreEqual(expectedUser, result);
    mockRepository.Verify(r => r.GetByIdAsync(1), Times.Once);
}

// 4. Test Double Implementation
public class FakeUserRepository : IUserRepository
{
    private readonly List<User> _users = new();

    public async Task<User> GetByIdAsync(int id)
    {
        return await Task.FromResult(_users.FirstOrDefault(u => u.Id == id));
    }

    public async Task<IEnumerable<User>> GetAllAsync()
    {
        return await Task.FromResult(_users.AsEnumerable());
    }

    public async Task AddAsync(User user)
    {
        _users.Add(user);
        await Task.CompletedTask;
    }
}

68. How do you handle Entity Framework test performance?

Answer: Entity Framework test performance optimization focuses on reducing database calls, using appropriate test data strategies, and optimizing test execution.

Performance Strategies: - Test Data Isolation: Use separate databases per test - Bulk Operations: Use AddRange for multiple entities - Async/Await: Ensure proper async testing - Database Cleanup: Efficient cleanup strategies

// 1. Test Data Isolation with Database Per Test
public class UserServiceTests : IDisposable
{
    private readonly DbContextOptions<ApplicationDbContext> _options;
    private readonly ApplicationDbContext _context;

    public UserServiceTests()
    {
        _options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(Guid.NewGuid().ToString()) // Unique DB per test
            .Options;
        _context = new ApplicationDbContext(_options);
    }

    public void Dispose()
    {
        _context.Database.EnsureDeleted();
        _context.Dispose();
    }
}

// 2. Bulk Data Setup
public class TestDataFactory
{
    public static async Task SeedUsersAsync(ApplicationDbContext context, int count = 100)
    {
        var users = Enumerable.Range(1, count)
            .Select(i => new User 
            { 
                Id = i, 
                Name = $"User{i}", 
                Email = $"user{i}@example.com" 
            })
            .ToList();

        await context.Users.AddRangeAsync(users);
        await context.SaveChangesAsync();
    }
}

// 3. Optimized Test with Minimal Database Calls
[Test]
public async Task GetActiveUsers_ShouldReturnOnlyActiveUsers()
{
    // Arrange - Single setup call
    await TestDataFactory.SeedUsersAsync(_context, 50);
    await TestDataFactory.SeedInactiveUsersAsync(_context, 25);

    var userService = new UserService(_context);

    // Act - Single query
    var activeUsers = await userService.GetActiveUsersAsync();

    // Assert
    Assert.AreEqual(50, activeUsers.Count());
}

// 4. Parallel Test Execution with Isolation
[TestFixture]
public class ParallelUserTests
{
    [Test]
    [Parallelizable(ParallelScope.Self)]
    public async Task Test1()
    {
        using var context = CreateUniqueContext();
        // Test implementation
    }

    [Test]
    [Parallelizable(ParallelScope.Self)]
    public async Task Test2()
    {
        using var context = CreateUniqueContext();
        // Test implementation
    }

    private ApplicationDbContext CreateUniqueContext()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(Guid.NewGuid().ToString())
            .Options;
        return new ApplicationDbContext(options);
    }
}

69. How do you implement Entity Framework test data factories?

Answer: Test data factories provide reusable, maintainable test data creation with fluent APIs and builder patterns.

Factory Patterns: - Builder Pattern: Fluent API for complex objects - Factory Methods: Static methods for common scenarios - AutoFixture Integration: Automated test data generation - Faker Libraries: Realistic test data generation

// 1. Basic Test Data Factory
public static class UserFactory
{
    public static User CreateUser(
        int id = 1,
        string name = "John Doe",
        string email = "john@example.com",
        bool isActive = true)
    {
        return new User
        {
            Id = id,
            Name = name,
            Email = email,
            IsActive = isActive,
            CreatedAt = DateTime.UtcNow
        };
    }

    public static List<User> CreateUsers(int count)
    {
        return Enumerable.Range(1, count)
            .Select(i => CreateUser(i, $"User{i}", $"user{i}@example.com"))
            .ToList();
    }
}

// 2. Builder Pattern Implementation
public class UserBuilder
{
    private User _user = new();

    public UserBuilder WithId(int id)
    {
        _user.Id = id;
        return this;
    }

    public UserBuilder WithName(string name)
    {
        _user.Name = name;
        return this;
    }

    public UserBuilder WithEmail(string email)
    {
        _user.Email = email;
        return this;
    }

    public UserBuilder AsActive()
    {
        _user.IsActive = true;
        return this;
    }

    public UserBuilder AsInactive()
    {
        _user.IsActive = false;
        return this;
    }

    public User Build() => _user;

    public static UserBuilder Default() => new UserBuilder();
}

// 3. AutoFixture Integration
public class AutoFixtureUserFactory
{
    private readonly Fixture _fixture;

    public AutoFixtureUserFactory()
    {
        _fixture = new Fixture();
        _fixture.Behaviors.OfType<ThrowingRecursionBehavior>().ToList()
                .ForEach(b => _fixture.Behaviors.Remove(b));
        _fixture.Behaviors.Add(new OmitOnRecursionBehavior());
    }

    public User CreateUser()
    {
        return _fixture.Create<User>();
    }

    public List<User> CreateUsers(int count)
    {
        return _fixture.CreateMany<User>(count).ToList();
    }
}

// 4. Faker Integration for Realistic Data
public class FakerUserFactory
{
    private readonly Faker<User> _userFaker;

    public FakerUserFactory()
    {
        _userFaker = new Faker<User>()
            .RuleFor(u => u.Id, f => f.IndexFaker + 1)
            .RuleFor(u => u.Name, f => f.Name.FullName())
            .RuleFor(u => u.Email, (f, u) => f.Internet.Email(u.Name))
            .RuleFor(u => u.IsActive, f => f.Random.Bool())
            .RuleFor(u => u.CreatedAt, f => f.Date.Past());
    }

    public User CreateUser() => _userFaker.Generate();
    public List<User> CreateUsers(int count) => _userFaker.Generate(count);
}

// 5. Usage in Tests
[Test]
public async Task CreateUser_ShouldSaveToDatabase()
{
    // Arrange
    var user = UserBuilder.Default()
        .WithName("Jane Smith")
        .WithEmail("jane@example.com")
        .AsActive()
        .Build();

    var userService = new UserService(_context);

    // Act
    await userService.CreateUserAsync(user);

    // Assert
    var savedUser = await _context.Users.FirstOrDefaultAsync(u => u.Email == "jane@example.com");
    Assert.IsNotNull(savedUser);
    Assert.AreEqual("Jane Smith", savedUser.Name);
}

70. How do you handle Entity Framework test data seeding?

Answer: Test data seeding involves populating the database with initial data for testing scenarios, ensuring consistent test environments.

Seeding Strategies: - Model Configuration: Use HasData in OnModelCreating - Custom Seed Methods: Manual seeding in test setup - JSON/CSV Files: External data files - Database Migrations: Seed data in migrations

// 1. Model Configuration Seeding
public class ApplicationDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasData(
            new User { Id = 1, Name = "Admin User", Email = "admin@example.com", IsActive = true },
            new User { Id = 2, Name = "Test User", Email = "test@example.com", IsActive = true }
        );

        modelBuilder.Entity<Role>().HasData(
            new Role { Id = 1, Name = "Admin" },
            new Role { Id = 2, Name = "User" }
        );
    }
}

// 2. Custom Seed Method
public static class DatabaseSeeder
{
    public static async Task SeedAsync(ApplicationDbContext context)
    {
        if (!context.Users.Any())
        {
            var users = new List<User>
            {
                new User { Name = "Admin", Email = "admin@example.com", IsActive = true },
                new User { Name = "Manager", Email = "manager@example.com", IsActive = true },
                new User { Name = "Employee", Email = "employee@example.com", IsActive = true }
            };

            await context.Users.AddRangeAsync(users);
            await context.SaveChangesAsync();
        }

        if (!context.Roles.Any())
        {
            var roles = new List<Role>
            {
                new Role { Name = "Administrator" },
                new Role { Name = "Manager" },
                new Role { Name = "Employee" }
            };

            await context.Roles.AddRangeAsync(roles);
            await context.SaveChangesAsync();
        }
    }
}

// 3. Test-Specific Seeding
public class TestDataSeeder
{
    private readonly ApplicationDbContext _context;

    public TestDataSeeder(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task SeedUsersForAuthenticationTests()
    {
        var users = new List<User>
        {
            new User { Name = "ValidUser", Email = "valid@example.com", IsActive = true },
            new User { Name = "InactiveUser", Email = "inactive@example.com", IsActive = false },
            new User { Name = "LockedUser", Email = "locked@example.com", IsActive = true, IsLocked = true }
        };

        await _context.Users.AddRangeAsync(users);
        await _context.SaveChangesAsync();
    }

    public async Task SeedUsersForPerformanceTests(int count = 1000)
    {
        var users = Enumerable.Range(1, count)
            .Select(i => new User
            {
                Name = $"User{i}",
                Email = $"user{i}@example.com",
                IsActive = true,
                CreatedAt = DateTime.UtcNow.AddDays(-i)
            });

        await _context.Users.AddRangeAsync(users);
        await _context.SaveChangesAsync();
    }
}

// 4. JSON File Seeding
public class JsonDataSeeder
{
    public static async Task SeedFromJsonAsync<T>(ApplicationDbContext context, string jsonFilePath) 
        where T : class
    {
        var json = await File.ReadAllTextAsync(jsonFilePath);
        var entities = JsonSerializer.Deserialize<List<T>>(json);

        var dbSet = context.Set<T>();
        await dbSet.AddRangeAsync(entities);
        await context.SaveChangesAsync();
    }
}

// 5. Migration-Based Seeding
public partial class SeedInitialData : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: "Users",
            columns: new[] { "Id", "Name", "Email", "IsActive" },
            values: new object[,]
            {
                { 1, "Admin User", "admin@example.com", true },
                { 2, "Test User", "test@example.com", true }
            });
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: "Users",
            keyColumn: "Id",
            keyValues: new object[] { 1, 2 });
    }
}

// 6. Test Usage
[TestFixture]
public class UserServiceTests
{
    private ApplicationDbContext _context;
    private TestDataSeeder _seeder;

    [SetUp]
    public async Task Setup()
    {
        var options = new DbContextOptionsBuilder<ApplicationDbContext>()
            .UseInMemoryDatabase(Guid.NewGuid().ToString())
            .Options;

        _context = new ApplicationDbContext(options);
        _seeder = new TestDataSeeder(_context);

        // Seed test data
        await _seeder.SeedUsersForAuthenticationTests();
    }

    [TearDown]
    public void Cleanup()
    {
        _context.Database.EnsureDeleted();
        _context.Dispose();
    }
}

71. How do you implement Entity Framework data validation?

Answer: Entity Framework data validation ensures data integrity through multiple layers: model validation, database constraints, and business rule validation.

Validation Layers: - Model Validation: Data annotations and Fluent API - Business Logic Validation: Custom validation logic - Database Constraints: Foreign keys, unique constraints - Real-time Validation: Validation during save operations

// 1. Model Validation with Data Annotations
public class User
{
    public int Id { get; set; }

    [Required(ErrorMessage = "Name is required")]
    [StringLength(100, MinimumLength = 2, ErrorMessage = "Name must be between 2 and 100 characters")]
    public string Name { get; set; }

    [Required(ErrorMessage = "Email is required")]
    [EmailAddress(ErrorMessage = "Invalid email format")]
    [StringLength(255)]
    public string Email { get; set; }

    [Range(0, 120, ErrorMessage = "Age must be between 0 and 120")]
    public int? Age { get; set; }

    [Phone(ErrorMessage = "Invalid phone number format")]
    public string PhoneNumber { get; set; }

    [Url(ErrorMessage = "Invalid URL format")]
    public string Website { get; set; }

    [RegularExpression(@"^(?=.*[a-z])(?=.*[A-Z])(?=.*\d).{8,}$", 
        ErrorMessage = "Password must contain at least 8 characters, one uppercase, one lowercase, and one digit")]
    public string Password { get; set; }
}

// 2. Fluent API Validation
public class ApplicationDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(e => e.Id);

            entity.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(100);

            entity.Property(e => e.Email)
                .IsRequired()
                .HasMaxLength(255);

            entity.HasIndex(e => e.Email)
                .IsUnique();

            entity.Property(e => e.Age)
                .HasDefaultValue(18);

            entity.Property(e => e.CreatedAt)
                .HasDefaultValueSql("GETUTCDATE()");

            // Custom validation
            entity.HasCheckConstraint("CK_User_Age", "Age >= 0 AND Age <= 120");
        });
    }
}

// 3. Custom Validation Attributes
public class UniqueEmailAttribute : ValidationAttribute
{
    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        var email = value as string;
        if (string.IsNullOrEmpty(email))
            return ValidationResult.Success;

        var context = (ApplicationDbContext)validationContext.GetService(typeof(ApplicationDbContext));
        var user = validationContext.ObjectInstance as User;

        var existingUser = context.Users
            .FirstOrDefault(u => u.Email == email && u.Id != user.Id);

        if (existingUser != null)
            return new ValidationResult("Email address is already in use");

        return ValidationResult.Success;
    }
}

// 4. Business Logic Validation Service
public interface IValidationService
{
    Task<ValidationResult> ValidateUserAsync(User user);
    Task<ValidationResult> ValidateUserCreationAsync(User user);
    Task<ValidationResult> ValidateUserUpdateAsync(User user);
}

public class ValidationService : IValidationService
{
    private readonly ApplicationDbContext _context;

    public ValidationService(ApplicationDbContext context)
    {
        _context = context;
    }

    public async Task<ValidationResult> ValidateUserAsync(User user)
    {
        var errors = new List<ValidationError>();

        // Business rule validations
        if (user.Age < 13)
            errors.Add(new ValidationError("Age", "User must be at least 13 years old"));

        if (user.Email.EndsWith("@temp.com"))
            errors.Add(new ValidationError("Email", "Temporary email addresses are not allowed"));

        // Domain-specific validations
        if (user.Role == "Admin" && user.Age < 18)
            errors.Add(new ValidationError("Role", "Admin users must be at least 18 years old"));

        return new ValidationResult(errors);
    }

    public async Task<ValidationResult> ValidateUserCreationAsync(User user)
    {
        var result = await ValidateUserAsync(user);

        if (result.IsValid)
        {
            // Check for duplicate email
            var existingUser = await _context.Users
                .FirstOrDefaultAsync(u => u.Email == user.Email);

            if (existingUser != null)
                result.AddError("Email", "Email address is already registered");
        }

        return result;
    }
}

// 5. Real-time Validation in Service Layer
public class UserService
{
    private readonly ApplicationDbContext _context;
    private readonly IValidationService _validationService;

    public UserService(ApplicationDbContext context, IValidationService validationService)
    {
        _context = context;
        _validationService = validationService;
    }

    public async Task<ServiceResult<User>> CreateUserAsync(User user)
    {
        // Validate model
        var validationContext = new ValidationContext(user);
        var validationResults = new List<ValidationResult>();

        if (!Validator.TryValidateObject(user, validationContext, validationResults, true))
        {
            var errors = validationResults.Select(v => new ValidationError(v.MemberNames.FirstOrDefault(), v.ErrorMessage));
            return ServiceResult<User>.Failure(errors);
        }

        // Business logic validation
        var businessValidation = await _validationService.ValidateUserCreationAsync(user);
        if (!businessValidation.IsValid)
        {
            return ServiceResult<User>.Failure(businessValidation.Errors);
        }

        // Save to database
        _context.Users.Add(user);
        await _context.SaveChangesAsync();

        return ServiceResult<User>.Success(user);
    }
}

// 6. Validation Result Classes
public class ValidationResult
{
    public bool IsValid => !Errors.Any();
    public List<ValidationError> Errors { get; } = new();

    public void AddError(string property, string message)
    {
        Errors.Add(new ValidationError(property, message));
    }
}

public class ValidationError
{
    public string Property { get; set; }
    public string Message { get; set; }

    public ValidationError(string property, string message)
    {
        Property = property;
        Message = message;
    }
}

public class ServiceResult<T>
{
    public bool IsSuccess { get; set; }
    public T Data { get; set; }
    public List<ValidationError> Errors { get; set; } = new();

    public static ServiceResult<T> Success(T data) => new() { IsSuccess = true, Data = data };
    public static ServiceResult<T> Failure(List<ValidationError> errors) => new() { IsSuccess = false, Errors = errors };
}

72. How do you handle Entity Framework SQL injection prevention?

Use FromSql or FromSqlInterpolated for parameterized values; do not interpolate untrusted values into raw SQL text. SQL identifiers cannot generally be parameterized, so allow-list or sanitize dynamic identifiers. Raw query results must satisfy mapped entity requirements unless projected to a suitable unmapped type.

73. How do you implement Entity Framework data encryption?

Answer: Entity Framework data encryption involves encrypting sensitive data at rest and in transit using various encryption strategies.

Encryption Strategies: - Column-Level Encryption: Encrypt specific columns - Always Encrypted: SQL Server feature for client-side encryption - Custom Encryption: Application-level encryption - Transparent Data Encryption (TDE): Database-level encryption

// 1. Custom Column Encryption with Value Converters
public class EncryptedStringConverter : ValueConverter<string, string>
{
    public EncryptedStringConverter() : base(
        v => Encrypt(v),
        v => Decrypt(v))
    {
    }

    private static string Encrypt(string value)
    {
        if (string.IsNullOrEmpty(value))
            return value;

        // Use AES encryption
        using var aes = Aes.Create();
        aes.Key = GetEncryptionKey();
        aes.IV = GetEncryptionIV();

        using var encryptor = aes.CreateEncryptor();
        var plainBytes = Encoding.UTF8.GetBytes(value);
        var encryptedBytes = encryptor.TransformFinalBlock(plainBytes, 0, plainBytes.Length);

        return Convert.ToBase64String(encryptedBytes);
    }

    private static string Decrypt(string value)
    {
        if (string.IsNullOrEmpty(value))
            return value;

        try
        {
            using var aes = Aes.Create();
            aes.Key = GetEncryptionKey();
            aes.IV = GetEncryptionIV();

            using var decryptor = aes.CreateDecryptor();
            var encryptedBytes = Convert.FromBase64String(value);
            var decryptedBytes = decryptor.TransformFinalBlock(encryptedBytes, 0, encryptedBytes.Length);

            return Encoding.UTF8.GetString(decryptedBytes);
        }
        catch
        {
            return value; // Return original if decryption fails
        }
    }

    private static byte[] GetEncryptionKey()
    {
        // In production, get from secure configuration
        var key = Environment.GetEnvironmentVariable("ENCRYPTION_KEY");
        return Convert.FromBase64String(key ?? "default-key-32-chars-long-key-here");
    }

    private static byte[] GetEncryptionIV()
    {
        // In production, get from secure configuration
        var iv = Environment.GetEnvironmentVariable("ENCRYPTION_IV");
        return Convert.FromBase64String(iv ?? "default-iv-16-chars");
    }
}

// 2. Encrypted Entity Model
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }

    // Encrypted email
    public string Email { get; set; }

    // Encrypted phone number
    public string PhoneNumber { get; set; }

    // Encrypted SSN
    public string SocialSecurityNumber { get; set; }

    // Encrypted credit card
    public string CreditCardNumber { get; set; }

    public bool IsActive { get; set; }
    public DateTime CreatedAt { get; set; }
}

// 3. DbContext Configuration for Encryption
public class ApplicationDbContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(e => e.Id);

            // Configure encrypted columns
            entity.Property(e => e.Email)
                .HasConversion(new EncryptedStringConverter());

            entity.Property(e => e.PhoneNumber)
                .HasConversion(new EncryptedStringConverter());

            entity.Property(e => e.SocialSecurityNumber)
                .HasConversion(new EncryptedStringConverter());

            entity.Property(e => e.CreditCardNumber)
                .HasConversion(new EncryptedStringConverter());
        });
    }
}

// 4. Always Encrypted Configuration
public class AlwaysEncryptedDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionString = "Server=...;Database=...;Column Encryption Setting=Enabled;";
        optionsBuilder.UseSqlServer(connectionString);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            // Configure Always Encrypted columns
            entity.Property(e => e.SocialSecurityNumber)
                .HasColumnType("varchar(11)")
                .HasAnnotation("SqlServer:ColumnEncryptionType", "Deterministic");

            entity.Property(e => e.CreditCardNumber)
                .HasColumnType("varchar(16)")
                .HasAnnotation("SqlServer:ColumnEncryptionType", "Randomized");
        });
    }
}

// 5. Encryption Service
public interface IEncryptionService
{
    string Encrypt(string plainText);
    string Decrypt(string cipherText);
    string Hash(string value);
    bool VerifyHash(string value, string hash);
}

public class EncryptionService : IEncryptionService
{
    private readonly byte[] _key;
    private readonly byte[] _iv;

    public EncryptionService(IConfiguration configuration)
    {
        _key = Convert.FromBase64String(configuration["Encryption:Key"]);
        _iv = Convert.FromBase64String(configuration["Encryption:IV"]);
    }

    public string Encrypt(string plainText)
    {
        if (string.IsNullOrEmpty(plainText))
            return plainText;

        using var aes = Aes.Create();
        aes.Key = _key;
        aes.IV = _iv;

        using var encryptor = aes.CreateEncryptor();
        var plainBytes = Encoding.UTF8.GetBytes(plainText);
        var encryptedBytes = encryptor.TransformFinalBlock(plainBytes, 0, plainBytes.Length);

        return Convert.ToBase64String(encryptedBytes);
    }

    public string Decrypt(string cipherText)
    {
        if (string.IsNullOrEmpty(cipherText))
            return cipherText;

        try
        {
            using var aes = Aes.Create();
            aes.Key = _key;
            aes.IV = _iv;

            using var decryptor = aes.CreateDecryptor();
            var encryptedBytes = Convert.FromBase64String(cipherText);
            var decryptedBytes = decryptor.TransformFinalBlock(encryptedBytes, 0, encryptedBytes.Length);

            return Encoding.UTF8.GetString(decryptedBytes);
        }
        catch
        {
            return cipherText;
        }
    }

    public string Hash(string value)
    {
        using var sha256 = SHA256.Create();
        var bytes = Encoding.UTF8.GetBytes(value);
        var hash = sha256.ComputeHash(bytes);
        return Convert.ToBase64String(hash);
    }

    public bool VerifyHash(string value, string hash)
    {
        var computedHash = Hash(value);
        return computedHash == hash;
    }
}

// 6. Encrypted Repository Pattern
public class EncryptedUserRepository
{
    private readonly ApplicationDbContext _context;
    private readonly IEncryptionService _encryptionService;

    public EncryptedUserRepository(ApplicationDbContext context, IEncryptionService encryptionService)
    {
        _context = context;
        _encryptionService = encryptionService;
    }

    public async Task<User> CreateUserAsync(User user)
    {
        // Encrypt sensitive data before saving
        user.Email = _encryptionService.Encrypt(user.Email);
        user.PhoneNumber = _encryptionService.Encrypt(user.PhoneNumber);
        user.SocialSecurityNumber = _encryptionService.Encrypt(user.SocialSecurityNumber);

        _context.Users.Add(user);
        await _context.SaveChangesAsync();

        // Decrypt for return
        user.Email = _encryptionService.Decrypt(user.Email);
        user.PhoneNumber = _encryptionService.Decrypt(user.PhoneNumber);
        user.SocialSecurityNumber = _encryptionService.Decrypt(user.SocialSecurityNumber);

        return user;
    }

    public async Task<User> GetUserByIdAsync(int id)
    {
        var user = await _context.Users.FindAsync(id);

        if (user != null)
        {
            // Decrypt sensitive data
            user.Email = _encryptionService.Decrypt(user.Email);
            user.PhoneNumber = _encryptionService.Decrypt(user.PhoneNumber);
            user.SocialSecurityNumber = _encryptionService.Decrypt(user.SocialSecurityNumber);
        }

        return user;
    }
}

// 7. Secure Configuration
public class SecureConfiguration
{
    public static void ConfigureEncryption(IServiceCollection services, IConfiguration configuration)
    {
        // Validate encryption settings
        var key = configuration["Encryption:Key"];
        var iv = configuration["Encryption:IV"];

        if (string.IsNullOrEmpty(key) || string.IsNullOrEmpty(iv))
        {
            throw new InvalidOperationException("Encryption key and IV must be configured");
        }

        // Register encryption service
        services.AddSingleton<IEncryptionService, EncryptionService>();

        // Configure database with encryption
        services.AddDbContext<ApplicationDbContext>(options =>
        {
            var connectionString = configuration.GetConnectionString("DefaultConnection");
            options.UseSqlServer(connectionString, sqlOptions =>
            {
                sqlOptions.EnableRetryOnFailure();
            });
        });
    }
}

// 8. Usage Example
public class UserController : ControllerBase
{
    private readonly EncryptedUserRepository _userRepository;

    public UserController(EncryptedUserRepository userRepository)
    {
        _userRepository = userRepository;
    }

    [HttpPost]
    public async Task<IActionResult> CreateUser([FromBody] User user)
    {
        // Data is automatically encrypted when saved
        var createdUser = await _userRepository.CreateUserAsync(user);

        return CreatedAtAction(nameof(GetUser), new { id = createdUser.Id }, createdUser);
    }

    [HttpGet("{id}")]
    public async Task<IActionResult> GetUser(int id)
    {
        // Data is automatically decrypted when retrieved
        var user = await _userRepository.GetUserByIdAsync(id);

        if (user == null)
            return NotFound();

        return Ok(user);
    }
}

74. How do you handle Entity Framework audit logging?

Answer: Implement audit logging using interceptors, change tracking, and dedicated audit tables.

SQL Schema:

CREATE TABLE AuditLogs (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(128) NOT NULL,
    KeyValues NVARCHAR(MAX) NOT NULL,
    OldValues NVARCHAR(MAX) NULL,
    NewValues NVARCHAR(MAX) NULL,
    Action NVARCHAR(10) NOT NULL, -- INSERT, UPDATE, DELETE
    UserId NVARCHAR(128) NULL,
    Timestamp DATETIME2 DEFAULT GETUTCDATE(),
    IpAddress NVARCHAR(45) NULL
);

CREATE INDEX IX_AuditLogs_TableName ON AuditLogs(TableName);
CREATE INDEX IX_AuditLogs_Timestamp ON AuditLogs(Timestamp);

C# Implementation:

public class AuditLog
{
    public long Id { get; set; }
    public string TableName { get; set; }
    public string KeyValues { get; set; }
    public string OldValues { get; set; }
    public string NewValues { get; set; }
    public string Action { get; set; }
    public string UserId { get; set; }
    public DateTime Timestamp { get; set; }
    public string IpAddress { get; set; }
}

public class AuditInterceptor : IDbCommandInterceptor
{
    private readonly IHttpContextAccessor _httpContextAccessor;

    public AuditInterceptor(IHttpContextAccessor httpContextAccessor)
    {
        _httpContextAccessor = httpContextAccessor;
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        if (interceptionContext.Exception == null)
        {
            var auditLog = new AuditLog
            {
                TableName = GetTableName(command),
                Action = GetAction(command),
                KeyValues = ExtractKeyValues(command),
                OldValues = ExtractOldValues(command),
                NewValues = ExtractNewValues(command),
                UserId = _httpContextAccessor.HttpContext?.User?.Identity?.Name,
                Timestamp = DateTime.UtcNow,
                IpAddress = _httpContextAccessor.HttpContext?.Connection?.RemoteIpAddress?.ToString()
            };

            // Save to audit table
            SaveAuditLog(auditLog);
        }
    }
}

// Registration in Startup.cs
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.AddInterceptors(new AuditInterceptor(httpContextAccessor));
});

75. How do you implement Entity Framework data masking?

Answer: Use computed columns, views, and custom value converters for data masking.

SQL Implementation:

-- Create masked view
CREATE VIEW MaskedCustomers AS
SELECT 
    Id,
    CONCAT(LEFT(FirstName, 1), REPLICATE('*', LEN(FirstName) - 1)) AS MaskedFirstName,
    CONCAT(LEFT(LastName, 1), REPLICATE('*', LEN(LastName) - 1)) AS MaskedLastName,
    CONCAT('****-****-', RIGHT(CreditCardNumber, 4)) AS MaskedCreditCard,
    Email -- Keep email for business logic
FROM Customers;

-- Computed column for SSN masking
ALTER TABLE Customers 
ADD MaskedSSN AS CONCAT('***-**-', RIGHT(SSN, 4)) PERSISTED;

C# Implementation:

public class DataMaskingValueConverter : ValueConverter<string, string>
{
    public DataMaskingValueConverter() : base(
        v => MaskSensitiveData(v),
        v => v)
    {
    }

    private static string MaskSensitiveData(string value)
    {
        if (string.IsNullOrEmpty(value)) return value;

        if (value.Length <= 2) return value;

        return value[0] + new string('*', value.Length - 2) + value[^1];
    }
}

public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }

    [Column(TypeName = "varchar(20)")]
    public string CreditCardNumber { get; set; }

    [Column(TypeName = "varchar(11)")]
    public string SSN { get; set; }
}

// In DbContext
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>()
        .Property(c => c.CreditCardNumber)
        .HasConversion(new DataMaskingValueConverter());

    modelBuilder.Entity<Customer>()
        .Property(c => c.SSN)
        .HasConversion(new DataMaskingValueConverter());
}

76. How do you handle Entity Framework access control?

Answer: Implement row-level security, column-level security, and application-level authorization.

SQL Row-Level Security:

-- Enable RLS
ALTER TABLE Orders ENABLE ROW LEVEL SECURITY;

-- Create policy for user-based access
CREATE POLICY UserOrdersPolicy ON Orders
    FOR ALL
    USING (UserId = CAST(SESSION_CONTEXT('UserId') AS INT));

-- Create policy for role-based access
CREATE POLICY ManagerOrdersPolicy ON Orders
    FOR ALL
    USING (
        EXISTS (
            SELECT 1 FROM UserRoles ur 
            WHERE ur.UserId = CAST(SESSION_CONTEXT('UserId') AS INT)
            AND ur.Role = 'Manager'
        )
    );

-- Set session context
EXEC sp_set_session_context 'UserId', @UserId;

C# Implementation:

public class Order
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public decimal Amount { get; set; }
    public string Status { get; set; }
}

public class SecureDbContext : DbContext
{
    private readonly ICurrentUserService _currentUserService;

    public SecureDbContext(DbContextOptions<SecureDbContext> options, 
                          ICurrentUserService currentUserService) : base(options)
    {
        _currentUserService = currentUserService;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Global query filter for row-level security
        modelBuilder.Entity<Order>()
            .HasQueryFilter(order => 
                order.UserId == _currentUserService.UserId || 
                _currentUserService.IsInRole("Manager"));
    }

    public override int SaveChanges()
    {
        // Ensure users can only modify their own data
        var entries = ChangeTracker.Entries<Order>()
            .Where(e => e.State == EntityState.Modified || e.State == EntityState.Deleted);

        foreach (var entry in entries)
        {
            if (entry.Entity.UserId != _currentUserService.UserId && 
                !_currentUserService.IsInRole("Manager"))
            {
                throw new UnauthorizedAccessException("Access denied");
            }
        }

        return base.SaveChanges();
    }
}

77. How do you implement Entity Framework data sanitization?

Answer: Use value converters, validation attributes, and custom model binders for data sanitization.

C# Implementation:

public class DataSanitizationValueConverter : ValueConverter<string, string>
{
    public DataSanitizationValueConverter() : base(
        v => SanitizeInput(v),
        v => v)
    {
    }

    private static string SanitizeInput(string input)
    {
        if (string.IsNullOrEmpty(input)) return input;

        // Remove HTML tags
        input = Regex.Replace(input, "<[^>]*>", string.Empty);

        // Remove SQL injection patterns
        input = Regex.Replace(input, @"(\b(union|select|insert|delete|update|drop|create|alter)\b)", "", 
            RegexOptions.IgnoreCase);

        // Trim whitespace
        input = input.Trim();

        // Limit length
        return input.Length > 1000 ? input.Substring(0, 1000) : input;
    }
}

public class SanitizedStringAttribute : ValidationAttribute
{
    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        if (value is string stringValue)
        {
            var sanitized = DataSanitizationValueConverter.SanitizeInput(stringValue);
            if (sanitized != stringValue)
            {
                return new ValidationResult("Input contains invalid characters");
            }
        }

        return ValidationResult.Success;
    }
}

public class Product
{
    public int Id { get; set; }

    [SanitizedString]
    [MaxLength(1000)]
    public string Description { get; set; }

    [SanitizedString]
    public string Name { get; set; }
}

78. How do you handle Entity Framework security compliance?

Answer: Implement encryption, audit trails, and compliance frameworks like GDPR, SOX, or HIPAA.

SQL Encryption:

-- Create master key for encryption
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword123!';

-- Create certificate
CREATE CERTIFICATE DataEncryptionCert 
WITH SUBJECT = 'Data Encryption Certificate';

-- Create symmetric key
CREATE SYMMETRIC KEY DataEncryptionKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE DataEncryptionCert;

-- Encrypt sensitive columns
ALTER TABLE Customers 
ADD EncryptedSSN VARBINARY(MAX);

-- Encrypt data
OPEN SYMMETRIC KEY DataEncryptionKey
DECRYPTION BY CERTIFICATE DataEncryptionCert;

UPDATE Customers 
SET EncryptedSSN = ENCRYPTBYKEY(KEY_GUID('DataEncryptionKey'), SSN);

-- Remove plain text column
ALTER TABLE Customers DROP COLUMN SSN;

C# Implementation:

public class ComplianceDbContext : DbContext
{
    private readonly IEncryptionService _encryptionService;

    public ComplianceDbContext(DbContextOptions<ComplianceDbContext> options,
                              IEncryptionService encryptionService) : base(options)
    {
        _encryptionService = encryptionService;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .Property(c => c.SSN)
            .HasConversion(
                v => _encryptionService.Encrypt(v),
                v => _encryptionService.Decrypt(v));
    }
}

public class EncryptionService : IEncryptionService
{
    private readonly string _key;

    public EncryptionService(IConfiguration configuration)
    {
        _key = configuration["EncryptionKey"];
    }

    public string Encrypt(string plainText)
    {
        if (string.IsNullOrEmpty(plainText)) return plainText;

        using var aes = Aes.Create();
        aes.Key = Convert.FromBase64String(_key);
        aes.GenerateIV();

        using var encryptor = aes.CreateEncryptor();
        using var msEncrypt = new MemoryStream();
        using var csEncrypt = new CryptoStream(msEncrypt, encryptor, CryptoStreamMode.Write);
        using var swEncrypt = new StreamWriter(csEncrypt);

        swEncrypt.Write(plainText);
        swEncrypt.Flush();
        csEncrypt.FlushFinalBlock();

        var encrypted = msEncrypt.ToArray();
        var result = new byte[aes.IV.Length + encrypted.Length];
        Buffer.BlockCopy(aes.IV, 0, result, 0, aes.IV.Length);
        Buffer.BlockCopy(encrypted, 0, result, aes.IV.Length, encrypted.Length);

        return Convert.ToBase64String(result);
    }

    public string Decrypt(string cipherText)
    {
        if (string.IsNullOrEmpty(cipherText)) return cipherText;

        var fullCipher = Convert.FromBase64String(cipherText);
        var iv = new byte[16];
        var cipher = new byte[fullCipher.Length - 16];

        Buffer.BlockCopy(fullCipher, 0, iv, 0, iv.Length);
        Buffer.BlockCopy(fullCipher, iv.Length, cipher, 0, cipher.Length);

        using var aes = Aes.Create();
        aes.Key = Convert.FromBase64String(_key);
        aes.IV = iv;

        using var decryptor = aes.CreateDecryptor();
        using var msDecrypt = new MemoryStream(cipher);
        using var csDecrypt = new CryptoStream(msDecrypt, decryptor, CryptoStreamMode.Read);
        using var srDecrypt = new StreamReader(csDecrypt);

        return srDecrypt.ReadToEnd();
    }
}

79. How do you implement Entity Framework data integrity checks?

Answer: Use database constraints, validation attributes, and custom validation logic.

SQL Constraints:

-- Check constraints
ALTER TABLE Products 
ADD CONSTRAINT CK_Products_Price 
CHECK (Price > 0);

ALTER TABLE Orders 
ADD CONSTRAINT CK_Orders_Status 
CHECK (Status IN ('Pending', 'Processing', 'Shipped', 'Delivered', 'Cancelled'));

-- Unique constraints
ALTER TABLE Customers 
ADD CONSTRAINT UQ_Customers_Email 
UNIQUE (Email);

-- Foreign key constraints with cascade
ALTER TABLE OrderItems 
ADD CONSTRAINT FK_OrderItems_Orders 
FOREIGN KEY (OrderId) REFERENCES Orders(Id) 
ON DELETE CASCADE;

-- Default values
ALTER TABLE Orders 
ADD CONSTRAINT DF_Orders_CreatedDate 
DEFAULT GETUTCDATE() FOR CreatedDate;

C# Implementation:

public class Product
{
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }

    [Range(0.01, double.MaxValue, ErrorMessage = "Price must be greater than 0")]
    public decimal Price { get; set; }

    [Range(0, int.MaxValue)]
    public int StockQuantity { get; set; }

    [Required]
    public string Category { get; set; }
}

public class Order
{
    public int Id { get; set; }

    [Required]
    public int CustomerId { get; set; }

    public Customer Customer { get; set; }

    [Required]
    public DateTime CreatedDate { get; set; } = DateTime.UtcNow;

    [Required]
    [EnumDataType(typeof(OrderStatus))]
    public OrderStatus Status { get; set; }

    public ICollection<OrderItem> OrderItems { get; set; }

    // Custom validation
    public bool ValidateOrder()
    {
        return OrderItems?.Any() == true && 
               OrderItems.All(item => item.Quantity > 0 && item.UnitPrice > 0);
    }
}

public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

// Custom validation attribute
public class ValidOrderAttribute : ValidationAttribute
{
    protected override ValidationResult IsValid(object value, ValidationContext validationContext)
    {
        if (value is Order order)
        {
            if (!order.ValidateOrder())
            {
                return new ValidationResult("Order must have valid items");
            }
        }

        return ValidationResult.Success;
    }
}

80. How do you handle Entity Framework data privacy?

Answer: Implement data anonymization, pseudonymization, and privacy-by-design patterns.

SQL Data Anonymization:

-- Create anonymized view
CREATE VIEW AnonymizedCustomers AS
SELECT 
    Id,
    CONCAT('Customer_', Id) AS AnonymizedId,
    CONCAT(LEFT(FirstName, 1), '***') AS AnonymizedFirstName,
    CONCAT(LEFT(LastName, 1), '***') AS AnonymizedLastName,
    CONCAT('***-***-', RIGHT(Phone, 4)) AS AnonymizedPhone,
    CONCAT('***@***.', RIGHT(Email, 4)) AS AnonymizedEmail,
    City,
    Country
FROM Customers;

-- Pseudonymization function
CREATE FUNCTION GetPseudonym(@originalValue NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @hash NVARCHAR(MAX)
    SET @hash = CONVERT(NVARCHAR(MAX), HASHBYTES('SHA2_256', @originalValue), 2)
    RETURN CONCAT('PSEUDO_', LEFT(@hash, 8))
END

C# Implementation:

public class PrivacyService
{
    private readonly Dictionary<string, string> _pseudonymMap = new();

    public string AnonymizeEmail(string email)
    {
        if (string.IsNullOrEmpty(email)) return email;

        var parts = email.Split('@');
        if (parts.Length != 2) return email;

        var username = parts[0];
        var domain = parts[1];

        var anonymizedUsername = username.Length > 2 
            ? username[0] + new string('*', username.Length - 2) + username[^1]
            : username;

        return $"{anonymizedUsername}@{domain}";
    }

    public string Pseudonymize(string originalValue)
    {
        if (string.IsNullOrEmpty(originalValue)) return originalValue;

        if (_pseudonymMap.TryGetValue(originalValue, out var pseudonym))
        {
            return pseudonym;
        }

        using var sha256 = SHA256.Create();
        var hash = sha256.ComputeHash(Encoding.UTF8.GetBytes(originalValue));
        var hashString = Convert.ToHexString(hash).Substring(0, 8);

        pseudonym = $"PSEUDO_{hashString}";
        _pseudonymMap[originalValue] = pseudonym;

        return pseudonym;
    }
}

public class PrivacyAwareDbContext : DbContext
{
    private readonly PrivacyService _privacyService;

    public PrivacyAwareDbContext(DbContextOptions<PrivacyAwareDbContext> options,
                                PrivacyService privacyService) : base(options)
    {
        _privacyService = privacyService;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .Property(c => c.Email)
            .HasConversion(
                v => _privacyService.AnonymizeEmail(v),
                v => v);
    }
}

81. How do you implement Entity Framework CQRS pattern?

Answer: Separate read and write operations using different models and contexts.

SQL Read Models:

-- Read-optimized tables
CREATE TABLE CustomerReadModel (
    Id INT PRIMARY KEY,
    FullName NVARCHAR(200),
    Email NVARCHAR(100),
    TotalOrders INT,
    TotalSpent DECIMAL(18,2),
    LastOrderDate DATETIME2,
    IsActive BIT,
    CreatedDate DATETIME2
);

-- Index for fast reads
CREATE INDEX IX_CustomerReadModel_Email ON CustomerReadModel(Email);
CREATE INDEX IX_CustomerReadModel_IsActive ON CustomerReadModel(IsActive);

-- Materialized view for reporting
CREATE VIEW CustomerSummary AS
SELECT 
    c.Id,
    c.FullName,
    c.Email,
    COUNT(o.Id) AS OrderCount,
    SUM(oi.Quantity * oi.UnitPrice) AS TotalSpent,
    MAX(o.CreatedDate) AS LastOrderDate
FROM CustomerReadModel c
LEFT JOIN Orders o ON c.Id = o.CustomerId
LEFT JOIN OrderItems oi ON o.Id = oi.OrderId
GROUP BY c.Id, c.FullName, c.Email;

C# Implementation:

// Write Model
public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public DateTime CreatedDate { get; set; }
    public bool IsActive { get; set; }

    public ICollection<Order> Orders { get; set; }
}

// Read Model
public class CustomerReadModel
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public string Email { get; set; }
    public int TotalOrders { get; set; }
    public decimal TotalSpent { get; set; }
    public DateTime? LastOrderDate { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedDate { get; set; }
}

// Write Context
public class WriteDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Email).IsRequired();
            entity.HasIndex(e => e.Email).IsUnique();
        });
    }
}

// Read Context
public class ReadDbContext : DbContext
{
    public DbSet<CustomerReadModel> CustomerReadModels { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<CustomerReadModel>(entity =>
        {
            entity.ToTable("CustomerReadModel");
            entity.HasKey(e => e.Id);
            entity.HasIndex(e => e.Email);
            entity.HasIndex(e => e.IsActive);
        });
    }
}

// Commands
public class CreateCustomerCommand
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

public class CreateCustomerCommandHandler
{
    private readonly WriteDbContext _writeContext;
    private readonly ReadDbContext _readContext;

    public CreateCustomerCommandHandler(WriteDbContext writeContext, ReadDbContext readContext)
    {
        _writeContext = writeContext;
        _readContext = readContext;
    }

    public async Task<int> Handle(CreateCustomerCommand command)
    {
        var customer = new Customer
        {
            FirstName = command.FirstName,
            LastName = command.LastName,
            Email = command.Email,
            CreatedDate = DateTime.UtcNow,
            IsActive = true
        };

        _writeContext.Customers.Add(customer);
        await _writeContext.SaveChangesAsync();

        // Update read model
        var readModel = new CustomerReadModel
        {
            Id = customer.Id,
            FullName = $"{customer.FirstName} {customer.LastName}",
            Email = customer.Email,
            TotalOrders = 0,
            TotalSpent = 0,
            IsActive = customer.IsActive,
            CreatedDate = customer.CreatedDate
        };

        _readContext.CustomerReadModels.Add(readModel);
        await _readContext.SaveChangesAsync();

        return customer.Id;
    }
}

// Queries
public class GetCustomerQuery
{
    public int Id { get; set; }
}

public class GetCustomerQueryHandler
{
    private readonly ReadDbContext _readContext;

    public GetCustomerQueryHandler(ReadDbContext readContext)
    {
        _readContext = readContext;
    }

    public async Task<CustomerReadModel> Handle(GetCustomerQuery query)
    {
        return await _readContext.CustomerReadModels
            .FirstOrDefaultAsync(c => c.Id == query.Id);
    }
}

82. How do you handle Entity Framework event sourcing?

Answer: Store domain events and rebuild aggregates from event streams.

SQL Event Store:

CREATE TABLE EventStore (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    AggregateId NVARCHAR(50) NOT NULL,
    AggregateType NVARCHAR(100) NOT NULL,
    EventType NVARCHAR(100) NOT NULL,
    EventData NVARCHAR(MAX) NOT NULL,
    Version INT NOT NULL,
    Timestamp DATETIME2 DEFAULT GETUTCDATE(),
    UserId NVARCHAR(128) NULL
);

CREATE INDEX IX_EventStore_AggregateId ON EventStore(AggregateId);
CREATE INDEX IX_EventStore_AggregateType ON EventStore(AggregateType);
CREATE INDEX IX_EventStore_Timestamp ON EventStore(Timestamp);

-- Snapshot table for performance
CREATE TABLE Snapshots (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    AggregateId NVARCHAR(50) NOT NULL,
    AggregateType NVARCHAR(100) NOT NULL,
    SnapshotData NVARCHAR(MAX) NOT NULL,
    Version INT NOT NULL,
    Timestamp DATETIME2 DEFAULT GETUTCDATE()
);

CREATE UNIQUE INDEX IX_Snapshots_AggregateId_Version ON Snapshots(AggregateId, Version);

C# Implementation:

public abstract class DomainEvent
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string AggregateId { get; set; }
    public int Version { get; set; }
    public DateTime Timestamp { get; set; } = DateTime.UtcNow;
    public string UserId { get; set; }
}

public class CustomerCreatedEvent : DomainEvent
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
}

public class CustomerEmailChangedEvent : DomainEvent
{
    public string OldEmail { get; set; }
    public string NewEmail { get; set; }
}

public abstract class AggregateRoot
{
    private readonly List<DomainEvent> _uncommittedEvents = new();

    public string Id { get; protected set; }
    public int Version { get; protected set; }

    protected void Apply(DomainEvent @event)
    {
        @event.AggregateId = Id;
        @event.Version = Version + 1;

        When(@event);
        _uncommittedEvents.Add(@event);
        Version++;
    }

    protected abstract void When(DomainEvent @event);

    public IEnumerable<DomainEvent> GetUncommittedEvents()
    {
        return _uncommittedEvents.AsReadOnly();
    }

    public void MarkEventsAsCommitted()
    {
        _uncommittedEvents.Clear();
    }
}

public class Customer : AggregateRoot
{
    public string FirstName { get; private set; }
    public string LastName { get; private set; }
    public string Email { get; private set; }
    public bool IsActive { get; private set; }

    public Customer(string firstName, string lastName, string email)
    {
        Id = Guid.NewGuid().ToString();
        Apply(new CustomerCreatedEvent
        {
            FirstName = firstName,
            LastName = lastName,
            Email = email
        });
    }

    public void ChangeEmail(string newEmail)
    {
        if (Email != newEmail)
        {
            Apply(new CustomerEmailChangedEvent
            {
                OldEmail = Email,
                NewEmail = newEmail
            });
        }
    }

    protected override void When(DomainEvent @event)
    {
        switch (@event)
        {
            case CustomerCreatedEvent e:
                FirstName = e.FirstName;
                LastName = e.LastName;
                Email = e.Email;
                IsActive = true;
                break;
            case CustomerEmailChangedEvent e:
                Email = e.NewEmail;
                break;
        }
    }
}

public class EventStore
{
    private readonly DbContext _context;

    public EventStore(DbContext context)
    {
        _context = context;
    }

    public async Task SaveEventsAsync(string aggregateId, IEnumerable<DomainEvent> events, int expectedVersion)
    {
        var eventList = events.ToList();
        var lastEvent = eventList.LastOrDefault();

        if (lastEvent != null && lastEvent.Version != expectedVersion + eventList.Count)
        {
            throw new ConcurrencyException();
        }

        foreach (var @event in eventList)
        {
            var eventData = new EventStore
            {
                AggregateId = aggregateId,
                AggregateType = @event.GetType().Name,
                EventType = @event.GetType().Name,
                EventData = JsonSerializer.Serialize(@event),
                Version = @event.Version,
                UserId = @event.UserId
            };

            _context.Set<EventStore>().Add(eventData);
        }

        await _context.SaveChangesAsync();
    }

    public async Task<IEnumerable<DomainEvent>> GetEventsAsync(string aggregateId)
    {
        var events = await _context.Set<EventStore>()
            .Where(e => e.AggregateId == aggregateId)
            .OrderBy(e => e.Version)
            .ToListAsync();

        return events.Select(e => JsonSerializer.Deserialize<DomainEvent>(e.EventData));
    }
}

public class CustomerRepository
{
    private readonly EventStore _eventStore;

    public CustomerRepository(EventStore eventStore)
    {
        _eventStore = eventStore;
    }

    public async Task<Customer> GetByIdAsync(string id)
    {
        var events = await _eventStore.GetEventsAsync(id);
        var customer = new Customer(); // Factory method would be better

        foreach (var @event in events)
        {
            customer.When(@event);
        }

        return customer;
    }

    public async Task SaveAsync(Customer customer)
    {
        var events = customer.GetUncommittedEvents();
        await _eventStore.SaveEventsAsync(customer.Id, events, customer.Version);
        customer.MarkEventsAsCommitted();
    }
}

83. How do you implement Entity Framework domain-driven design?

Answer: Use aggregates, value objects, domain services, and repositories with proper boundaries.

SQL Schema:

-- Aggregate root
CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    CustomerId UNIQUEIDENTIFIER NOT NULL,
    OrderNumber NVARCHAR(20) UNIQUE NOT NULL,
    Status NVARCHAR(20) NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    CreatedDate DATETIME2 NOT NULL,
    ModifiedDate DATETIME2 NOT NULL
);

-- Value object as complex type
CREATE TABLE OrderAddresses (
    OrderId UNIQUEIDENTIFIER PRIMARY KEY,
    Street NVARCHAR(100) NOT NULL,
    City NVARCHAR(50) NOT NULL,
    State NVARCHAR(50) NOT NULL,
    ZipCode NVARCHAR(10) NOT NULL,
    Country NVARCHAR(50) NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(Id)
);

-- Aggregate children
CREATE TABLE OrderItems (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    OrderId UNIQUEIDENTIFIER NOT NULL,
    ProductId UNIQUEIDENTIFIER NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18,2) NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(Id) ON DELETE CASCADE
);

-- Domain service table
CREATE TABLE Inventory (
    ProductId UNIQUEIDENTIFIER PRIMARY KEY,
    AvailableQuantity INT NOT NULL,
    ReservedQuantity INT NOT NULL,
    LastUpdated DATETIME2 NOT NULL
);

C# Implementation:

// Value Objects
public class Address : ValueObject
{
    public string Street { get; private set; }
    public string City { get; private set; }
    public string State { get; private set; }
    public string ZipCode { get; private set; }
    public string Country { get; private set; }

    public Address(string street, string city, string state, string zipCode, string country)
    {
        Street = street;
        City = city;
        State = state;
        ZipCode = zipCode;
        Country = country;
    }

    protected override IEnumerable<object> GetEqualityComponents()
    {
        yield return Street;
        yield return City;
        yield return State;
        yield return ZipCode;
        yield return Country;
    }
}

public class Money : ValueObject
{
    public decimal Amount { get; private set; }
    public string Currency { get; private set; }

    public Money(decimal amount, string currency = "USD")
    {
        Amount = amount;
        Currency = currency;
    }

    public static Money operator +(Money left, Money right)
    {
        if (left.Currency != right.Currency)
            throw new InvalidOperationException("Cannot add different currencies");

        return new Money(left.Amount + right.Amount, left.Currency);
    }

    protected override IEnumerable<object> GetEqualityComponents()
    {
        yield return Amount;
        yield return Currency;
    }
}

// Domain Entities
public class OrderItem : Entity
{
    public Guid ProductId { get; private set; }
    public int Quantity { get; private set; }
    public Money UnitPrice { get; private set; }

    public Money TotalPrice => new Money(UnitPrice.Amount * Quantity, UnitPrice.Currency);

    private OrderItem() { }

    public OrderItem(Guid productId, int quantity, Money unitPrice)
    {
        ProductId = productId;
        Quantity = quantity;
        UnitPrice = unitPrice;
    }
}

// Aggregate Root
public class Order : AggregateRoot
{
    public Guid CustomerId { get; private set; }
    public string OrderNumber { get; private set; }
    public OrderStatus Status { get; private set; }
    public Address ShippingAddress { get; private set; }
    public Money TotalAmount { get; private set; }
    public DateTime CreatedDate { get; private set; }
    public DateTime ModifiedDate { get; private set; }

    private readonly List<OrderItem> _orderItems = new();
    public IReadOnlyCollection<OrderItem> OrderItems => _orderItems.AsReadOnly();

    private Order() { }

    public Order(Guid customerId, string orderNumber, Address shippingAddress)
    {
        Id = Guid.NewGuid();
        CustomerId = customerId;
        OrderNumber = orderNumber;
        Status = OrderStatus.Created;
        ShippingAddress = shippingAddress;
        CreatedDate = DateTime.UtcNow;
        ModifiedDate = DateTime.UtcNow;

        AddDomainEvent(new OrderCreatedEvent(this));
    }

    public void AddItem(Guid productId, int quantity, Money unitPrice)
    {
        var item = new OrderItem(productId, quantity, unitPrice);
        _orderItems.Add(item);

        RecalculateTotal();
        ModifiedDate = DateTime.UtcNow;

        AddDomainEvent(new OrderItemAddedEvent(this, item));
    }

    public void Confirm()
    {
        if (Status != OrderStatus.Created)
            throw new InvalidOperationException("Order can only be confirmed when in Created status");

        if (!_orderItems.Any())
            throw new InvalidOperationException("Order must have at least one item");

        Status = OrderStatus.Confirmed;
        ModifiedDate = DateTime.UtcNow;

        AddDomainEvent(new OrderConfirmedEvent(this));
    }

    private void RecalculateTotal()
    {
        TotalAmount = _orderItems.Aggregate(
            new Money(0), 
            (total, item) => total + item.TotalPrice);
    }
}

// Domain Service
public interface IInventoryService
{
    Task<bool> ReserveInventoryAsync(Guid productId, int quantity);
    Task ReleaseInventoryAsync(Guid productId, int quantity);
}

public class InventoryService : IInventoryService
{
    private readonly DbContext _context;

    public InventoryService(DbContext context)
    {
        _context = context;
    }

    public async Task<bool> ReserveInventoryAsync(Guid productId, int quantity)
    {
        var inventory = await _context.Set<Inventory>()
            .FirstOrDefaultAsync(i => i.ProductId == productId);

        if (inventory == null || inventory.AvailableQuantity < quantity)
            return false;

        inventory.AvailableQuantity -= quantity;
        inventory.ReservedQuantity += quantity;
        inventory.LastUpdated = DateTime.UtcNow;

        await _context.SaveChangesAsync();
        return true;
    }

    public async Task ReleaseInventoryAsync(Guid productId, int quantity)
    {
        var inventory = await _context.Set<Inventory>()
            .FirstOrDefaultAsync(i => i.ProductId == productId);

        if (inventory != null)
        {
            inventory.AvailableQuantity += quantity;
            inventory.ReservedQuantity -= quantity;
            inventory.LastUpdated = DateTime.UtcNow;

            await _context.SaveChangesAsync();
        }
    }
}

// Repository
public interface IOrderRepository
{
    Task<Order> GetByIdAsync(Guid id);
    Task<Order> GetByOrderNumberAsync(string orderNumber);
    Task<IEnumerable<Order>> GetByCustomerIdAsync(Guid customerId);
    Task SaveAsync(Order order);
}

public class OrderRepository : IOrderRepository
{
    private readonly DbContext _context;

    public OrderRepository(DbContext context)
    {
        _context = context;
    }

    public async Task<Order> GetByIdAsync(Guid id)
    {
        return await _context.Set<Order>()
            .Include(o => o.OrderItems)
            .FirstOrDefaultAsync(o => o.Id == id);
    }

    public async Task SaveAsync(Order order)
    {
        _context.Set<Order>().Update(order);
        await _context.SaveChangesAsync();
    }
}

84. How do you handle Entity Framework microservices integration?

Answer: Use database-per-service pattern, event-driven communication, and API gateways.

SQL Per-Service Databases:

-- Order Service Database
CREATE DATABASE OrderServiceDB;
USE OrderServiceDB;

CREATE TABLE Orders (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    CustomerId UNIQUEIDENTIFIER NOT NULL,
    OrderNumber NVARCHAR(20) UNIQUE NOT NULL,
    Status NVARCHAR(20) NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    CreatedDate DATETIME2 NOT NULL
);

CREATE TABLE OrderItems (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    OrderId UNIQUEIDENTIFIER NOT NULL,
    ProductId UNIQUEIDENTIFIER NOT NULL,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(18,2) NOT NULL,
    FOREIGN KEY (OrderId) REFERENCES Orders(Id)
);

-- Integration Events
CREATE TABLE IntegrationEvents (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    EventType NVARCHAR(100) NOT NULL,
    EventData NVARCHAR(MAX) NOT NULL,
    CreatedDate DATETIME2 NOT NULL,
    ProcessedDate DATETIME2 NULL,
    Status NVARCHAR(20) NOT NULL
);

-- Customer Service Database
CREATE DATABASE CustomerServiceDB;
USE CustomerServiceDB;

CREATE TABLE Customers (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    Email NVARCHAR(100) UNIQUE NOT NULL,
    CreatedDate DATETIME2 NOT NULL
);

-- Product Service Database
CREATE DATABASE ProductServiceDB;
USE ProductServiceDB;

CREATE TABLE Products (
    Id UNIQUEIDENTIFIER PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Price DECIMAL(18,2) NOT NULL,
    StockQuantity INT NOT NULL,
    CreatedDate DATETIME2 NOT NULL
);

C# Implementation:

// Integration Events
public abstract class IntegrationEvent
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public DateTime CreationDate { get; set; } = DateTime.UtcNow;
}

public class OrderCreatedEvent : IntegrationEvent
{
    public Guid OrderId { get; set; }
    public Guid CustomerId { get; set; }
    public string OrderNumber { get; set; }
    public decimal TotalAmount { get; set; }
}

public class OrderStatusChangedEvent : IntegrationEvent
{
    public Guid OrderId { get; set; }
    public string OldStatus { get; set; }
    public string NewStatus { get; set; }
}

// Order Service
public class OrderServiceDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }
    public DbSet<IntegrationEvent> IntegrationEvents { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.OrderNumber).IsRequired().HasMaxLength(20);
            entity.HasIndex(e => e.OrderNumber).IsUnique();
        });

        modelBuilder.Entity<IntegrationEvent>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.EventData).HasColumnType("nvarchar(max)");
        });
    }
}

public class OrderService
{
    private readonly OrderServiceDbContext _context;
    private readonly IEventBus _eventBus;
    private readonly ICustomerServiceClient _customerService;
    private readonly IProductServiceClient _productService;

    public OrderService(OrderServiceDbContext context, 
                       IEventBus eventBus,
                       ICustomerServiceClient customerService,
                       IProductServiceClient productService)
    {
        _context = context;
        _eventBus = eventBus;
        _customerService = customerService;
        _productService = productService;
    }

    public async Task<Order> CreateOrderAsync(CreateOrderRequest request)
    {
        // Validate customer exists
        var customer = await _customerService.GetCustomerAsync(request.CustomerId);
        if (customer == null)
            throw new ValidationException("Customer not found");

        // Validate products and get prices
        var orderItems = new List<OrderItem>();
        foreach (var item in request.Items)
        {
            var product = await _productService.GetProductAsync(item.ProductId);
            if (product == null)
                throw new ValidationException($"Product {item.ProductId} not found");

            if (product.StockQuantity < item.Quantity)
                throw new ValidationException($"Insufficient stock for product {item.ProductId}");

            orderItems.Add(new OrderItem
            {
                ProductId = item.ProductId,
                Quantity = item.Quantity,
                UnitPrice = product.Price
            });
        }

        // Create order
        var order = new Order
        {
            Id = Guid.NewGuid(),
            CustomerId = request.CustomerId,
            OrderNumber = GenerateOrderNumber(),
            Status = "Created",
            TotalAmount = orderItems.Sum(i => i.UnitPrice * i.Quantity),
            CreatedDate = DateTime.UtcNow
        };

        _context.Orders.Add(order);

        foreach (var item in orderItems)
        {
            item.OrderId = order.Id;
            _context.OrderItems.Add(item);
        }

        // Publish integration event
        var integrationEvent = new OrderCreatedEvent
        {
            OrderId = order.Id,
            CustomerId = order.CustomerId,
            OrderNumber = order.OrderNumber,
            TotalAmount = order.TotalAmount
        };

        _context.IntegrationEvents.Add(new IntegrationEvent
        {
            Id = integrationEvent.Id,
            EventType = integrationEvent.GetType().Name,
            EventData = JsonSerializer.Serialize(integrationEvent),
            CreatedDate = integrationEvent.CreationDate,
            Status = "Pending"
        });

        await _context.SaveChangesAsync();

        // Publish to event bus
        await _eventBus.PublishAsync(integrationEvent);

        return order;
    }
}

// API Gateway
public class ApiGatewayController : ControllerBase
{
    private readonly IOrderServiceClient _orderService;
    private readonly ICustomerServiceClient _customerService;
    private readonly IProductServiceClient _productService;

    public ApiGatewayController(IOrderServiceClient orderService,
                               ICustomerServiceClient customerService,
                               IProductServiceClient productService)
    {
        _orderService = orderService;
        _customerService = customerService;
        _productService = productService;
    }

    [HttpGet("orders/{id}")]
    public async Task<IActionResult> GetOrder(Guid id)
    {
        var order = await _orderService.GetOrderAsync(id);
        if (order == null)
            return NotFound();

        var customer = await _customerService.GetCustomerAsync(order.CustomerId);
        var orderItems = await _orderService.GetOrderItemsAsync(id);

        var productIds = orderItems.Select(i => i.ProductId).ToList();
        var products = await _productService.GetProductsAsync(productIds);

        var result = new OrderDetailsResponse
        {
            Order = order,
            Customer = customer,
            Items = orderItems.Select(item => new OrderItemResponse
            {
                Product = products.First(p => p.Id == item.ProductId),
                Quantity = item.Quantity,
                UnitPrice = item.UnitPrice
            }).ToList()
        };

        return Ok(result);
    }
}

// Event Bus
public interface IEventBus
{
    Task PublishAsync<T>(T @event) where T : IntegrationEvent;
    Task SubscribeAsync<T, TH>() where T : IntegrationEvent where TH : IIntegrationEventHandler<T>;
}

public class RabbitMQEventBus : IEventBus
{
    private readonly IConnection _connection;
    private readonly IModel _channel;

    public RabbitMQEventBus(IConnectionFactory connectionFactory)
    {
        _connection = connectionFactory.CreateConnection();
        _channel = _connection.CreateModel();
    }

    public async Task PublishAsync<T>(T @event) where T : IntegrationEvent
    {
        var eventName = @event.GetType().Name;
        var message = JsonSerializer.Serialize(@event);
        var body = Encoding.UTF8.GetBytes(message);

        _channel.ExchangeDeclare(eventName, ExchangeType.Fanout);
        _channel.BasicPublish(eventName, "", null, body);

        await Task.CompletedTask;
    }
}

Domain-Driven Design & Patterns (Questions 85-90)

85. How do you implement Entity Framework saga patterns?

Answer: Saga patterns in EF handle distributed transactions across multiple services. Here's how to implement them:

// Saga Coordinator
public class OrderSagaCoordinator
{
    private readonly IServiceProvider _serviceProvider;
    private readonly ILogger<OrderSagaCoordinator> _logger;

    public OrderSagaCoordinator(IServiceProvider serviceProvider, ILogger<OrderSagaCoordinator> logger)
    {
        _serviceProvider = serviceProvider;
        _logger = logger;
    }

    public async Task<OrderSagaResult> ExecuteOrderSagaAsync(OrderRequest request)
    {
        var saga = new OrderSaga(request);
        var compensations = new Stack<Func<Task>>();

        try
        {
            // Step 1: Create Order
            var orderResult = await CreateOrderAsync(request);
            compensations.Push(() => CancelOrderAsync(orderResult.OrderId));

            // Step 2: Reserve Inventory
            var inventoryResult = await ReserveInventoryAsync(orderResult.OrderId, request.Items);
            compensations.Push(() => ReleaseInventoryAsync(inventoryResult.ReservationId));

            // Step 3: Process Payment
            var paymentResult = await ProcessPaymentAsync(orderResult.OrderId, request.PaymentInfo);
            compensations.Push(() => RefundPaymentAsync(paymentResult.PaymentId));

            // Step 4: Confirm Order
            await ConfirmOrderAsync(orderResult.OrderId);

            return new OrderSagaResult { Success = true, OrderId = orderResult.OrderId };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Saga execution failed, starting compensation");

            // Execute compensations in reverse order
            while (compensations.Count > 0)
            {
                var compensation = compensations.Pop();
                try
                {
                    await compensation();
                }
                catch (Exception compEx)
                {
                    _logger.LogError(compEx, "Compensation step failed");
                }
            }

            throw;
        }
    }
}

// Saga State Entity
public class OrderSagaState
{
    public Guid SagaId { get; set; }
    public string CurrentStep { get; set; }
    public string Status { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime? CompletedAt { get; set; }
    public string CompensationData { get; set; } // JSON serialized compensation steps
}

86. How do you handle Entity Framework eventual consistency?

Answer: Eventual consistency is handled through event sourcing and CQRS patterns:

// Event Store
public class EventStore
{
    private readonly DbContext _context;
    private readonly IEventSerializer _serializer;

    public async Task SaveEventsAsync(Guid aggregateId, IEnumerable<IDomainEvent> events, int expectedVersion)
    {
        var eventList = events.ToList();
        var version = expectedVersion;

        foreach (var @event in eventList)
        {
            version++;
            @event.Version = version;

            var eventData = new EventData
            {
                AggregateId = aggregateId,
                Version = version,
                EventType = @event.GetType().Name,
                Data = _serializer.Serialize(@event),
                CreatedAt = DateTime.UtcNow
            };

            _context.Events.Add(eventData);
        }

        await _context.SaveChangesAsync();
    }
}

// Read Model Projection
public class OrderReadModelProjection
{
    private readonly DbContext _context;

    public async Task HandleAsync(OrderCreatedEvent @event)
    {
        var readModel = new OrderReadModel
        {
            OrderId = @event.OrderId,
            CustomerId = @event.CustomerId,
            TotalAmount = @event.TotalAmount,
            Status = "Created",
            CreatedAt = @event.CreatedAt
        };

        _context.OrderReadModels.Add(readModel);
        await _context.SaveChangesAsync();
    }

    public async Task HandleAsync(OrderStatusChangedEvent @event)
    {
        var readModel = await _context.OrderReadModels
            .FirstOrDefaultAsync(o => o.OrderId == @event.OrderId);

        if (readModel != null)
        {
            readModel.Status = @event.NewStatus;
            readModel.UpdatedAt = @event.CreatedAt;
            await _context.SaveChangesAsync();
        }
    }
}

87. How do you implement Entity Framework read models?

Answer: Read models are optimized for querying and can be denormalized:

// Read Model Entity
public class CustomerOrderReadModel
{
    public Guid OrderId { get; set; }
    public Guid CustomerId { get; set; }
    public string CustomerName { get; set; }
    public string CustomerEmail { get; set; }
    public decimal TotalAmount { get; set; }
    public string OrderStatus { get; set; }
    public DateTime OrderDate { get; set; }
    public List<OrderItemReadModel> Items { get; set; } = new();
}

public class OrderItemReadModel
{
    public Guid ProductId { get; set; }
    public string ProductName { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal TotalPrice { get; set; }
}

// Read Model Repository
public class CustomerOrderReadModelRepository
{
    private readonly DbContext _context;

    public async Task<List<CustomerOrderReadModel>> GetCustomerOrdersAsync(
        Guid customerId, 
        DateTime? fromDate = null,
        DateTime? toDate = null)
    {
        var query = _context.CustomerOrderReadModels
            .Include(o => o.Items)
            .Where(o => o.CustomerId == customerId);

        if (fromDate.HasValue)
            query = query.Where(o => o.OrderDate >= fromDate.Value);

        if (toDate.HasValue)
            query = query.Where(o => o.OrderDate <= toDate.Value);

        return await query
            .OrderByDescending(o => o.OrderDate)
            .ToListAsync();
    }

    public async Task<CustomerOrderReadModel> GetOrderWithDetailsAsync(Guid orderId)
    {
        return await _context.CustomerOrderReadModels
            .Include(o => o.Items)
            .FirstOrDefaultAsync(o => o.OrderId == orderId);
    }
}

88. How do you handle Entity Framework write models?

Answer: Write models focus on business logic and data integrity:

// Write Model (Aggregate Root)
public class Order : AggregateRoot
{
    private readonly List<OrderItem> _items = new();
    private readonly List<OrderHistory> _history = new();

    public Guid Id { get; private set; }
    public Guid CustomerId { get; private set; }
    public OrderStatus Status { get; private set; }
    public decimal TotalAmount { get; private set; }
    public DateTime CreatedAt { get; private set; }
    public DateTime? UpdatedAt { get; private set; }

    public IReadOnlyCollection<OrderItem> Items => _items.AsReadOnly();
    public IReadOnlyCollection<OrderHistory> History => _history.AsReadOnly();

    private Order() { } // For EF

    public Order(Guid customerId, List<OrderItem> items)
    {
        Id = Guid.NewGuid();
        CustomerId = customerId;
        Status = OrderStatus.Created;
        CreatedAt = DateTime.UtcNow;

        foreach (var item in items)
        {
            AddItem(item);
        }

        CalculateTotal();
        AddHistoryEntry("Order created");
    }

    public void AddItem(OrderItem item)
    {
        if (Status != OrderStatus.Created)
            throw new InvalidOperationException("Cannot add items to non-created order");

        _items.Add(item);
        AddDomainEvent(new OrderItemAddedEvent(Id, item));
    }

    public void ConfirmOrder()
    {
        if (Status != OrderStatus.Created)
            throw new InvalidOperationException("Order cannot be confirmed");

        Status = OrderStatus.Confirmed;
        UpdatedAt = DateTime.UtcNow;
        AddHistoryEntry("Order confirmed");
        AddDomainEvent(new OrderConfirmedEvent(Id));
    }

    public void CancelOrder(string reason)
    {
        if (Status == OrderStatus.Cancelled)
            throw new InvalidOperationException("Order is already cancelled");

        Status = OrderStatus.Cancelled;
        UpdatedAt = DateTime.UtcNow;
        AddHistoryEntry($"Order cancelled: {reason}");
        AddDomainEvent(new OrderCancelledEvent(Id, reason));
    }

    private void CalculateTotal()
    {
        TotalAmount = _items.Sum(item => item.TotalPrice);
    }

    private void AddHistoryEntry(string description)
    {
        _history.Add(new OrderHistory(description, DateTime.UtcNow));
    }
}

// Write Model Repository
public class OrderRepository : IOrderRepository
{
    private readonly DbContext _context;

    public async Task<Order> GetByIdAsync(Guid id)
    {
        return await _context.Orders
            .Include(o => o.Items)
            .Include(o => o.History)
            .FirstOrDefaultAsync(o => o.Id == id);
    }

    public async Task SaveAsync(Order order)
    {
        var existingOrder = await _context.Orders
            .Include(o => o.Items)
            .Include(o => o.History)
            .FirstOrDefaultAsync(o => o.Id == order.Id);

        if (existingOrder == null)
        {
            _context.Orders.Add(order);
        }
        else
        {
            _context.Entry(existingOrder).CurrentValues.SetValues(order);

            // Handle collections
            _context.Entry(existingOrder).Collection(o => o.Items).CurrentValue = order.Items;
            _context.Entry(existingOrder).Collection(o => o.History).CurrentValue = order.History;
        }

        await _context.SaveChangesAsync();
    }
}

89. How do you implement Entity Framework aggregate patterns?

Answer: Aggregate patterns ensure data consistency and business rules:

// Aggregate Root Base
public abstract class AggregateRoot
{
    private readonly List<IDomainEvent> _domainEvents = new();

    public IReadOnlyCollection<IDomainEvent> DomainEvents => _domainEvents.AsReadOnly();

    protected void AddDomainEvent(IDomainEvent domainEvent)
    {
        _domainEvents.Add(domainEvent);
    }

    public void ClearDomainEvents()
    {
        _domainEvents.Clear();
    }
}

// Product Aggregate
public class Product : AggregateRoot
{
    public Guid Id { get; private set; }
    public string Name { get; private set; }
    public string Description { get; private set; }
    public decimal Price { get; private set; }
    public int StockQuantity { get; private set; }
    public ProductStatus Status { get; private set; }
    private readonly List<ProductReview> _reviews = new();

    public IReadOnlyCollection<ProductReview> Reviews => _reviews.AsReadOnly();

    private Product() { }

    public Product(string name, string description, decimal price, int initialStock)
    {
        Id = Guid.NewGuid();
        Name = name;
        Description = description;
        Price = price;
        StockQuantity = initialStock;
        Status = ProductStatus.Active;

        AddDomainEvent(new ProductCreatedEvent(Id, name, price));
    }

    public void UpdateStock(int quantity)
    {
        if (quantity < 0)
            throw new ArgumentException("Stock quantity cannot be negative");

        StockQuantity = quantity;

        if (StockQuantity == 0)
        {
            Status = ProductStatus.OutOfStock;
            AddDomainEvent(new ProductOutOfStockEvent(Id));
        }
        else if (Status == ProductStatus.OutOfStock)
        {
            Status = ProductStatus.Active;
            AddDomainEvent(new ProductBackInStockEvent(Id));
        }

        AddDomainEvent(new ProductStockUpdatedEvent(Id, StockQuantity));
    }

    public void AddReview(ProductReview review)
    {
        if (Status != ProductStatus.Active)
            throw new InvalidOperationException("Cannot add review to inactive product");

        _reviews.Add(review);
        AddDomainEvent(new ProductReviewAddedEvent(Id, review.Id));
    }

    public void Deactivate()
    {
        Status = ProductStatus.Inactive;
        AddDomainEvent(new ProductDeactivatedEvent(Id));
    }
}

// Aggregate Repository
public class ProductRepository : IProductRepository
{
    private readonly DbContext _context;
    private readonly IEventStore _eventStore;

    public async Task<Product> GetByIdAsync(Guid id)
    {
        return await _context.Products
            .Include(p => p.Reviews)
            .FirstOrDefaultAsync(p => p.Id == id);
    }

    public async Task SaveAsync(Product product)
    {
        // Save to database
        var existingProduct = await _context.Products
            .Include(p => p.Reviews)
            .FirstOrDefaultAsync(p => p.Id == product.Id);

        if (existingProduct == null)
        {
            _context.Products.Add(product);
        }
        else
        {
            _context.Entry(existingProduct).CurrentValues.SetValues(product);
            _context.Entry(existingProduct).Collection(p => p.Reviews).CurrentValue = product.Reviews;
        }

        await _context.SaveChangesAsync();

        // Save domain events
        if (product.DomainEvents.Any())
        {
            await _eventStore.SaveEventsAsync(product.Id, product.DomainEvents, 0);
            product.ClearDomainEvents();
        }
    }
}

90. How do you handle Entity Framework bounded contexts?

Answer: Bounded contexts separate different business domains:

// Order Bounded Context
public class OrderDbContext : DbContext
{
    public DbSet<Order> Orders { get; set; }
    public DbSet<OrderItem> OrderItems { get; set; }
    public DbSet<OrderHistory> OrderHistory { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.TotalAmount).HasColumnType("decimal(18,2)");
            entity.HasMany(e => e.Items).WithOne().HasForeignKey("OrderId");
            entity.HasMany(e => e.History).WithOne().HasForeignKey("OrderId");
        });

        modelBuilder.Entity<OrderItem>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.UnitPrice).HasColumnType("decimal(18,2)");
            entity.Property(e => e.TotalPrice).HasColumnType("decimal(18,2)");
        });
    }
}

// Customer Bounded Context
public class CustomerDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<CustomerAddress> CustomerAddresses { get; set; }
    public DbSet<CustomerPreference> CustomerPreferences { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Email).IsRequired().HasMaxLength(255);
            entity.HasIndex(e => e.Email).IsUnique();
            entity.HasMany(e => e.Addresses).WithOne().HasForeignKey("CustomerId");
            entity.HasMany(e => e.Preferences).WithOne().HasForeignKey("CustomerId");
        });
    }
}

// Integration Context (for cross-context communication)
public class IntegrationDbContext : DbContext
{
    public DbSet<IntegrationEvent> IntegrationEvents { get; set; }
    public DbSet<OutboxMessage> OutboxMessages { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<IntegrationEvent>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.EventType).IsRequired();
            entity.Property(e => e.EventData).IsRequired();
            entity.Property(e => e.CreatedAt).IsRequired();
        });
    }
}

// Bounded Context Service
public class OrderBoundedContextService
{
    private readonly OrderDbContext _orderContext;
    private readonly IntegrationDbContext _integrationContext;

    public async Task<Order> CreateOrderAsync(CreateOrderRequest request)
    {
        using var transaction = await _orderContext.Database.BeginTransactionAsync();
        try
        {
            var order = new Order(request.CustomerId, request.Items);

            _orderContext.Orders.Add(order);
            await _orderContext.SaveChangesAsync();

            // Publish integration event
            var integrationEvent = new IntegrationEvent
            {
                Id = Guid.NewGuid(),
                EventType = "OrderCreated",
                EventData = JsonSerializer.Serialize(new { order.Id, order.CustomerId }),
                CreatedAt = DateTime.UtcNow
            };

            _integrationContext.IntegrationEvents.Add(integrationEvent);
            await _integrationContext.SaveChangesAsync();

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

Monitoring & Diagnostics (Questions 91-100)

91. How do you implement Entity Framework logging?

Answer: Implement comprehensive logging for EF operations:

// EF Logging Configuration
public class EfLoggingService : ILogger
{
    private readonly ILogger<EfLoggingService> _logger;
    private readonly IConfiguration _configuration;

    public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
    {
        if (logLevel == LogLevel.Information && state is IEnumerable<KeyValuePair<string, object>> keyValuePairs)
        {
            var sql = keyValuePairs.FirstOrDefault(kvp => kvp.Key == "sql").Value?.ToString();
            var parameters = keyValuePairs.FirstOrDefault(kvp => kvp.Key == "parameters").Value;
            var duration = keyValuePairs.FirstOrDefault(kvp => kvp.Key == "duration").Value;

            _logger.LogInformation("SQL: {Sql} | Parameters: {@Parameters} | Duration: {Duration}ms", 
                sql, parameters, duration);
        }
    }

    public bool IsEnabled(LogLevel logLevel) => true;
    public IDisposable BeginScope<TState>(TState state) => null;
}

// Startup Configuration
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
    {
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
        options.EnableSensitiveDataLogging();
        options.EnableDetailedErrors();
        options.LogTo(Console.WriteLine, LogLevel.Information);
    });

    services.AddLogging(builder =>
    {
        builder.AddConsole();
        builder.AddDebug();
        builder.AddApplicationInsights();
    });
}

// Custom EF Logger
public class CustomEfLogger : ILogger
{
    private readonly ILogger<CustomEfLogger> _logger;
    private readonly IMetricsCollector _metrics;

    public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
    {
        if (state is IEnumerable<KeyValuePair<string, object>> keyValuePairs)
        {
            var sql = keyValuePairs.FirstOrDefault(kvp => kvp.Key == "sql").Value?.ToString();
            var duration = keyValuePairs.FirstOrDefault(kvp => kvp.Key == "duration").Value;

            if (duration is long durationMs)
            {
                _metrics.RecordQueryDuration(durationMs);

                if (durationMs > 1000) // Log slow queries
                {
                    _logger.LogWarning("Slow query detected: {Sql} | Duration: {Duration}ms", sql, durationMs);
                }
            }

            // Log query performance metrics
            _metrics.IncrementQueryCount();
        }
    }
}

92. How do you handle Entity Framework performance monitoring?

Answer: Implement comprehensive performance monitoring:

// Performance Monitor
public class EfPerformanceMonitor
{
    private readonly ILogger<EfPerformanceMonitor> _logger;
    private readonly IMetricsCollector _metrics;
    private readonly IConfiguration _configuration;

    public async Task<T> MonitorQueryAsync<T>(Func<Task<T>> query, string operationName)
    {
        var stopwatch = Stopwatch.StartNew();
        var startMemory = GC.GetTotalMemory(false);

        try
        {
            var result = await query();

            stopwatch.Stop();
            var endMemory = GC.GetTotalMemory(false);
            var memoryUsed = endMemory - startMemory;

            // Record metrics
            _metrics.RecordQueryDuration(operationName, stopwatch.ElapsedMilliseconds);
            _metrics.RecordMemoryUsage(operationName, memoryUsed);
            _metrics.IncrementQueryCount(operationName);

            // Log performance data
            _logger.LogInformation("Query completed: {Operation} | Duration: {Duration}ms | Memory: {Memory}KB", 
                operationName, stopwatch.ElapsedMilliseconds, memoryUsed / 1024);

            return result;
        }
        catch (Exception ex)
        {
            stopwatch.Stop();
            _metrics.RecordQueryError(operationName);
            _logger.LogError(ex, "Query failed: {Operation} | Duration: {Duration}ms", 
                operationName, stopwatch.ElapsedMilliseconds);
            throw;
        }
    }
}

// Repository with Performance Monitoring
public class MonitoredOrderRepository : IOrderRepository
{
    private readonly ApplicationDbContext _context;
    private readonly EfPerformanceMonitor _monitor;

    public async Task<Order> GetByIdAsync(Guid id)
    {
        return await _monitor.MonitorQueryAsync(async () =>
        {
            return await _context.Orders
                .Include(o => o.Items)
                .Include(o => o.History)
                .FirstOrDefaultAsync(o => o.Id == id);
        }, "GetOrderById");
    }

    public async Task<List<Order>> GetOrdersByCustomerAsync(Guid customerId)
    {
        return await _monitor.MonitorQueryAsync(async () =>
        {
            return await _context.Orders
                .Include(o => o.Items)
                .Where(o => o.CustomerId == customerId)
                .OrderByDescending(o => o.CreatedAt)
                .ToListAsync();
        }, "GetOrdersByCustomer");
    }
}

// Performance Metrics Collector
public class MetricsCollector : IMetricsCollector
{
    private readonly ApplicationInsightsTelemetryClient _telemetryClient;
    private readonly ILogger<MetricsCollector> _logger;

    public void RecordQueryDuration(string operation, long durationMs)
    {
        _telemetryClient.TrackMetric($"EF.Query.Duration.{operation}", durationMs);

        if (durationMs > 1000)
        {
            _telemetryClient.TrackEvent("SlowQuery", new Dictionary<string, string>
            {
                ["Operation"] = operation,
                ["Duration"] = durationMs.ToString()
            });
        }
    }

    public void RecordMemoryUsage(string operation, long memoryBytes)
    {
        _telemetryClient.TrackMetric($"EF.Query.Memory.{operation}", memoryBytes);
    }

    public void IncrementQueryCount(string operation)
    {
        _telemetryClient.TrackMetric($"EF.Query.Count.{operation}", 1);
    }

    public void RecordQueryError(string operation)
    {
        _telemetryClient.TrackEvent("QueryError", new Dictionary<string, string>
        {
            ["Operation"] = operation
        });
    }
}

93. How do you implement Entity Framework query analysis?

Answer: Implement query analysis to identify performance issues:

// Query Analyzer
public class EfQueryAnalyzer
{
    private readonly ILogger<EfQueryAnalyzer> _logger;
    private readonly IMetricsCollector _metrics;

    public async Task<QueryAnalysisResult> AnalyzeQueryAsync<T>(IQueryable<T> query, string operationName)
    {
        var analysis = new QueryAnalysisResult
        {
            OperationName = operationName,
            Timestamp = DateTime.UtcNow
        };

        try
        {
            // Analyze query plan
            var queryPlan = await AnalyzeQueryPlanAsync(query);
            analysis.QueryPlan = queryPlan;

            // Check for N+1 queries
            analysis.HasNPlusOneQueries = DetectNPlusOneQueries(query);

            // Analyze includes
            analysis.IncludeAnalysis = AnalyzeIncludes(query);

            // Check for potential performance issues
            analysis.PerformanceIssues = DetectPerformanceIssues(query, queryPlan);

            // Log analysis results
            _logger.LogInformation("Query analysis for {Operation}: {@Analysis}", operationName, analysis);

            return analysis;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Failed to analyze query: {Operation}", operationName);
            throw;
        }
    }

    private async Task<QueryPlanInfo> AnalyzeQueryPlanAsync<T>(IQueryable<T> query)
    {
        // This would integrate with SQL Server Query Store or similar
        var sql = query.ToQueryString();

        return new QueryPlanInfo
        {
            Sql = sql,
            EstimatedRows = await GetEstimatedRowsAsync(sql),
            EstimatedCost = await GetEstimatedCostAsync(sql)
        };
    }

    private bool DetectNPlusOneQueries<T>(IQueryable<T> query)
    {
        var sql = query.ToQueryString();
        // Analyze SQL for patterns that indicate N+1 queries
        return sql.Contains("SELECT") && sql.Split("SELECT").Length > 2;
    }

    private IncludeAnalysis AnalyzeIncludes<T>(IQueryable<T> query)
    {
        // Analyze Include statements for potential over-fetching
        return new IncludeAnalysis
        {
            IncludeCount = 0, // Would analyze actual includes
            HasUnnecessaryIncludes = false,
            Recommendations = new List<string>()
        };
    }

    private List<string> DetectPerformanceIssues<T>(IQueryable<T> query, QueryPlanInfo queryPlan)
    {
        var issues = new List<string>();

        if (queryPlan.EstimatedRows > 10000)
            issues.Add("Large result set detected");

        if (queryPlan.EstimatedCost > 100)
            issues.Add("High query cost detected");

        return issues;
    }
}

// Query Analysis Result
public class QueryAnalysisResult
{
    public string OperationName { get; set; }
    public DateTime Timestamp { get; set; }
    public QueryPlanInfo QueryPlan { get; set; }
    public bool HasNPlusOneQueries { get; set; }
    public IncludeAnalysis IncludeAnalysis { get; set; }
    public List<string> PerformanceIssues { get; set; } = new();
}

public class QueryPlanInfo
{
    public string Sql { get; set; }
    public long EstimatedRows { get; set; }
    public decimal EstimatedCost { get; set; }
}

public class IncludeAnalysis
{
    public int IncludeCount { get; set; }
    public bool HasUnnecessaryIncludes { get; set; }
    public List<string> Recommendations { get; set; } = new();
}

94. How do you handle Entity Framework error tracking?

Answer: Implement comprehensive error tracking and handling:

// Error Tracking Service
public class EfErrorTrackingService
{
    private readonly ILogger<EfErrorTrackingService> _logger;
    private readonly ITelemetryClient _telemetryClient;
    private readonly IConfiguration _configuration;

    public async Task<T> TrackErrorsAsync<T>(Func<Task<T>> operation, string operationName)
    {
        try
        {
            return await operation();
        }
        catch (DbUpdateException ex)
        {
            await HandleDbUpdateExceptionAsync(ex, operationName);
            throw;
        }
        catch (DbUpdateConcurrencyException ex)
        {
            await HandleConcurrencyExceptionAsync(ex, operationName);
            throw;
        }
        catch (SqlException ex)
        {
            await HandleSqlExceptionAsync(ex, operationName);
            throw;
        }
        catch (Exception ex)
        {
            await HandleGenericExceptionAsync(ex, operationName);
            throw;
        }
    }

    private async Task HandleDbUpdateExceptionAsync(DbUpdateException ex, string operationName)
    {
        var errorInfo = new
        {
            Operation = operationName,
            ErrorType = "DbUpdateException",
            Message = ex.Message,
            InnerException = ex.InnerException?.Message,
            Entries = ex.Entries.Select(e => new
            {
                EntityType = e.Entity.GetType().Name,
                State = e.State.ToString()
            }).ToList()
        };

        _logger.LogError(ex, "Database update error in {Operation}: {@ErrorInfo}", operationName, errorInfo);

        _telemetryClient.TrackException(ex, new Dictionary<string, string>
        {
            ["Operation"] = operationName,
            ["ErrorType"] = "DbUpdateException"
        });

        // Send alert for critical errors
        if (IsCriticalError(ex))
        {
            await SendAlertAsync("Critical database error", errorInfo);
        }
    }

    private async Task HandleConcurrencyExceptionAsync(DbUpdateConcurrencyException ex, string operationName)
    {
        _logger.LogWarning(ex, "Concurrency conflict in {Operation}", operationName);

        _telemetryClient.TrackEvent("ConcurrencyConflict", new Dictionary<string, string>
        {
            ["Operation"] = operationName
        });
    }

    private async Task HandleSqlExceptionAsync(SqlException ex, string operationName)
    {
        var errorInfo = new
        {
            Operation = operationName,
            ErrorNumber = ex.Number,
            ErrorMessage = ex.Message,
            Server = ex.Server,
            Database = ex.Database
        };

        _logger.LogError(ex, "SQL error in {Operation}: {@ErrorInfo}", operationName, errorInfo);

        _telemetryClient.TrackException(ex, new Dictionary<string, string>
        {
            ["Operation"] = operationName,
            ["ErrorNumber"] = ex.Number.ToString(),
            ["Server"] = ex.Server
        });
    }

    private async Task HandleGenericExceptionAsync(Exception ex, string operationName)
    {
        _logger.LogError(ex, "Unexpected error in {Operation}", operationName);

        _telemetryClient.TrackException(ex, new Dictionary<string, string>
        {
            ["Operation"] = operationName,
            ["ErrorType"] = ex.GetType().Name
        });
    }

    private bool IsCriticalError(DbUpdateException ex)
    {
        // Define what constitutes a critical error
        return ex.Entries.Any(e => e.State == EntityState.Added || e.State == EntityState.Modified);
    }

    private async Task SendAlertAsync(string title, object data)
    {
        // Implementation for sending alerts (email, Slack, etc.)
        _logger.LogWarning("Alert: {Title} - {@Data}", title, data);
    }
}

// Repository with Error Tracking
public class ErrorTrackedOrderRepository : IOrderRepository
{
    private readonly ApplicationDbContext _context;
    private readonly EfErrorTrackingService _errorTracker;

    public async Task<Order> GetByIdAsync(Guid id)
    {
        return await _errorTracker.TrackErrorsAsync(async () =>
        {
            return await _context.Orders
                .Include(o => o.Items)
                .FirstOrDefaultAsync(o => o.Id == id);
        }, "GetOrderById");
    }

    public async Task<Order> SaveAsync(Order order)
    {
        return await _errorTracker.TrackErrorsAsync(async () =>
        {
            if (order.Id == Guid.Empty)
            {
                _context.Orders.Add(order);
            }
            else
            {
                _context.Orders.Update(order);
            }

            await _context.SaveChangesAsync();
            return order;
        }, "SaveOrder");
    }
}

95. How do you implement Entity Framework health checks?

Answer: Implement health checks for EF and database connectivity:

// EF Health Check
public class EfHealthCheck : IHealthCheck
{
    private readonly ApplicationDbContext _context;
    private readonly ILogger<EfHealthCheck> _logger;

    public async Task<HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken = default)
    {
        try
        {
            var stopwatch = Stopwatch.StartNew();

            // Test database connectivity
            var canConnect = await _context.Database.CanConnectAsync(cancellationToken);
            if (!canConnect)
            {
                return HealthCheckResult.Unhealthy("Cannot connect to database");
            }

            // Test simple query
            var result = await _context.Database.SqlQueryRaw<int>("SELECT 1").FirstOrDefaultAsync(cancellationToken);
            stopwatch.Stop();

            var data = new Dictionary<string, object>
            {
                ["ResponseTime"] = stopwatch.ElapsedMilliseconds,
                ["Database"] = _context.Database.GetDbConnection().Database
            };

            if (stopwatch.ElapsedMilliseconds > 1000)
            {
                return HealthCheckResult.Degraded("Database response time is slow", data: data);
            }

            return HealthCheckResult.Healthy("Database is healthy", data: data);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Health check failed");
            return HealthCheckResult.Unhealthy("Health check failed", ex);
        }
    }
}

// Comprehensive Health Check Service
public class DatabaseHealthCheckService : IHealthCheck
{
    private readonly ApplicationDbContext _context;
    private readonly ILogger<DatabaseHealthCheckService> _logger;

    public async Task<HealthCheckResult> CheckHealthAsync(HealthCheckContext context, CancellationToken cancellationToken = default)
    {
        var healthData = new Dictionary<string, object>();
        var issues = new List<string>();

        try
        {
            // Check connectivity
            var canConnect = await _context.Database.CanConnectAsync(cancellationToken);
            healthData["CanConnect"] = canConnect;

            if (!canConnect)
            {
                issues.Add("Cannot connect to database");
            }

            // Check connection pool
            var connection = _context.Database.GetDbConnection();
            healthData["ConnectionState"] = connection.State.ToString();
            healthData["Database"] = connection.Database;

            // Test query performance
            var stopwatch = Stopwatch.StartNew();
            var testResult = await _context.Database.SqlQueryRaw<int>("SELECT 1").FirstOrDefaultAsync(cancellationToken);
            stopwatch.Stop();

            healthData["QueryResponseTime"] = stopwatch.ElapsedMilliseconds;

            if (stopwatch.ElapsedMilliseconds > 1000)
            {
                issues.Add($"Slow query response: {stopwatch.ElapsedMilliseconds}ms");
            }

            // Check database size
            var dbSize = await GetDatabaseSizeAsync();
            healthData["DatabaseSize"] = dbSize;

            // Check active connections
            var activeConnections = await GetActiveConnectionsAsync();
            healthData["ActiveConnections"] = activeConnections;

            if (activeConnections > 100)
            {
                issues.Add($"High number of active connections: {activeConnections}");
            }

            // Check for long-running queries
            var longRunningQueries = await GetLongRunningQueriesAsync();
            healthData["LongRunningQueries"] = longRunningQueries.Count;

            if (longRunningQueries.Any())
            {
                issues.Add($"Found {longRunningQueries.Count} long-running queries");
            }

            if (issues.Any())
            {
                return HealthCheckResult.Degraded(
                    $"Database has issues: {string.Join(", ", issues)}", 
                    data: healthData);
            }

            return HealthCheckResult.Healthy("Database is healthy", data: healthData);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Database health check failed");
            return HealthCheckResult.Unhealthy("Database health check failed", ex, healthData);
        }
    }

    private async Task<long> GetDatabaseSizeAsync()
    {
        var sql = @"
            SELECT SUM(size * 8.0 / 1024) as DatabaseSizeMB
            FROM sys.database_files";

        return await _context.Database.SqlQueryRaw<long>(sql).FirstOrDefaultAsync();
    }

    private async Task<int> GetActiveConnectionsAsync()
    {
        var sql = @"
            SELECT COUNT(*) 
            FROM sys.dm_exec_sessions 
            WHERE database_id = DB_ID()";

        return await _context.Database.SqlQueryRaw<int>(sql).FirstOrDefaultAsync();
    }

    private async Task<List<string>> GetLongRunningQueriesAsync()
    {
        var sql = @"
            SELECT 
                s.session_id,
                r.start_time,
                r.total_elapsed_time,
                r.command,
                r.sql_handle
            FROM sys.dm_exec_requests r
            JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
            WHERE r.total_elapsed_time > 30000"; // 30 seconds

        var results = await _context.Database.SqlQueryRaw<dynamic>(sql).ToListAsync();
        return results.Select(r => r.ToString()).ToList();
    }
}

// Startup Configuration
public void ConfigureServices(IServiceCollection services)
{
    services.AddHealthChecks()
        .AddCheck<EfHealthCheck>("database", tags: new[] { "database", "ef" })
        .AddCheck<DatabaseHealthCheckService>("database_comprehensive", tags: new[] { "database", "comprehensive" });
}

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    app.UseHealthChecks("/health", new HealthCheckOptions
    {
        ResponseWriter = async (context, report) =>
        {
            context.Response.ContentType = "application/json";
            var result = JsonSerializer.Serialize(new
            {
                status = report.Status.ToString(),
                checks = report.Entries.Select(e => new
                {
                    name = e.Key,
                    status = e.Value.Status.ToString(),
                    description = e.Value.Description,
                    data = e.Value.Data
                })
            });
            await context.Response.WriteAsync(result);
        }
    });
}

96. How do you handle Entity Framework metrics collection?

Answer: Entity Framework metrics collection involves monitoring query performance, connection usage, and database operations to identify bottlenecks and optimize performance.

Key Metrics to Collect: - Query execution time - Number of queries per request - Connection pool usage - Memory consumption - N+1 query detection - Slow query identification

Implementation Examples:

// Custom DbContext with metrics collection
public class MetricsDbContext : DbContext
{
    private readonly IMetricsCollector _metricsCollector;

    public MetricsDbContext(DbContextOptions<MetricsDbContext> options, 
                           IMetricsCollector metricsCollector) : base(options)
    {
        _metricsCollector = metricsCollector;
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        var stopwatch = Stopwatch.StartNew();
        try
        {
            var result = await base.SaveChangesAsync(cancellationToken);
            stopwatch.Stop();

            _metricsCollector.RecordSaveChanges(stopwatch.ElapsedMilliseconds, 
                                              ChangeTracker.Entries().Count());
            return result;
        }
        catch (Exception ex)
        {
            _metricsCollector.RecordError("SaveChanges", ex);
            throw;
        }
    }
}

// Metrics Collector Interface
public interface IMetricsCollector
{
    void RecordQuery(string query, long executionTime);
    void RecordSaveChanges(long executionTime, int entitiesChanged);
    void RecordError(string operation, Exception exception);
    void RecordConnectionUsage(int activeConnections);
}

// Application Insights Implementation
public class ApplicationInsightsMetricsCollector : IMetricsCollector
{
    private readonly TelemetryClient _telemetryClient;

    public ApplicationInsightsMetricsCollector(TelemetryClient telemetryClient)
    {
        _telemetryClient = telemetryClient;
    }

    public void RecordQuery(string query, long executionTime)
    {
        _telemetryClient.TrackDependency("SQL", "Query", query, 
                                        DateTimeOffset.UtcNow, 
                                        TimeSpan.FromMilliseconds(executionTime), 
                                        true);
    }

    public void RecordSaveChanges(long executionTime, int entitiesChanged)
    {
        _telemetryClient.TrackMetric("EF_SaveChanges_Duration", executionTime);
        _telemetryClient.TrackMetric("EF_Entities_Changed", entitiesChanged);
    }
}

Using Interceptors for Query Metrics:

public class QueryMetricsInterceptor : DbCommandInterceptor
{
    private readonly IMetricsCollector _metricsCollector;

    public QueryMetricsInterceptor(IMetricsCollector metricsCollector)
    {
        _metricsCollector = metricsCollector;
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result, 
        CancellationToken cancellationToken = default)
    {
        var stopwatch = Stopwatch.StartNew();

        var readerResult = await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);

        stopwatch.Stop();
        _metricsCollector.RecordQuery(command.CommandText, stopwatch.ElapsedMilliseconds);

        return readerResult;
    }
}

// Registration in Startup.cs
services.AddDbContext<ApplicationDbContext>(options =>
{
    options.UseSqlServer(connectionString);
    options.AddInterceptors(new QueryMetricsInterceptor(metricsCollector));
});

97. How do you implement Entity Framework debugging tools?

Answer: Entity Framework debugging tools help developers understand query execution, identify performance issues, and troubleshoot problems during development and production.

Implementation Examples:

// Custom Debug Logger
public class EFDebugLogger : ILogger
{
    private readonly ILogger<EFDebugLogger> _logger;
    private readonly bool _enableQueryLogging;

    public EFDebugLogger(ILogger<EFDebugLogger> logger, IConfiguration configuration)
    {
        _logger = logger;
        _enableQueryLogging = configuration.GetValue<bool>("EF:EnableQueryLogging");
    }

    public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, 
                           Exception exception, Func<TState, Exception, string> formatter)
    {
        if (!_enableQueryLogging) return;

        var message = formatter(state, exception);

        switch (logLevel)
        {
            case LogLevel.Information when message.Contains("Executed DbCommand"):
                _logger.LogInformation("EF Query: {Query}", message);
                break;
            case LogLevel.Warning when message.Contains("Microsoft.EntityFrameworkCore.Query"):
                _logger.LogWarning("EF Query Warning: {Warning}", message);
                break;
            case LogLevel.Error:
                _logger.LogError(exception, "EF Error: {Error}", message);
                break;
        }
    }

    public bool IsEnabled(LogLevel logLevel) => true;
    public IDisposable BeginScope<TState>(TState state) => null;
}

// Query Debug Helper
public static class EFQueryDebugHelper
{
    public static string GetGeneratedSql<T>(this IQueryable<T> query, DbContext context)
    {
        var queryString = query.ToQueryString();
        return queryString;
    }

    public static void LogQueryPlan<T>(this IQueryable<T> query, DbContext context)
    {
        var sql = query.ToQueryString();
        var logger = context.GetService<ILogger<DbContext>>();
        logger.LogInformation("Generated SQL: {SQL}", sql);
    }
}

// Debug Context with Enhanced Logging
public class DebugDbContext : DbContext
{
    private readonly ILogger<DebugDbContext> _logger;

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);

        // Enable detailed logging
        optionsBuilder.EnableSensitiveDataLogging()
                     .EnableDetailedErrors()
                     .LogTo(message => _logger.LogDebug("EF: {Message}", message), 
                           LogLevel.Information);
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        var entries = ChangeTracker.Entries()
            .Where(e => e.State == EntityState.Added || 
                       e.State == EntityState.Modified || 
                       e.State == EntityState.Deleted)
            .ToList();

        foreach (var entry in entries)
        {
            _logger.LogDebug("Entity {EntityType} - State: {State}", 
                           entry.Entity.GetType().Name, entry.State);
        }

        return await base.SaveChangesAsync(cancellationToken);
    }
}

Usage Example:

// In your service
public async Task<List<Customer>> GetCustomersWithDebuggingAsync()
{
    var query = _context.Customers
        .Include(c => c.Orders)
        .Where(c => c.IsActive);

    // Debug the generated SQL
    var sql = query.GetGeneratedSql(_context);
    _logger.LogInformation("Generated SQL: {SQL}", sql);

    // Log query plan
    query.LogQueryPlan(_context);

    return await query.ToListAsync();
}

98. How do you handle Entity Framework profiling?

Answer: Entity Framework profiling involves analyzing query performance, identifying bottlenecks, and optimizing database operations using specialized tools and techniques.

Implementation Examples:

// Custom Profiler
public class EFProfiler
{
    private readonly ILogger<EFProfiler> _logger;
    private readonly ConcurrentDictionary<string, QueryProfile> _queryProfiles;

    public EFProfiler(ILogger<EFProfiler> logger)
    {
        _logger = logger;
        _queryProfiles = new ConcurrentDictionary<string, QueryProfile>();
    }

    public void StartProfiling(string operationName)
    {
        var profile = new QueryProfile
        {
            OperationName = operationName,
            StartTime = DateTime.UtcNow,
            Stopwatch = Stopwatch.StartNew()
        };

        _queryProfiles.TryAdd(operationName, profile);
    }

    public void EndProfiling(string operationName, int? rowsAffected = null)
    {
        if (_queryProfiles.TryRemove(operationName, out var profile))
        {
            profile.Stopwatch.Stop();
            profile.Duration = profile.Stopwatch.ElapsedMilliseconds;
            profile.RowsAffected = rowsAffected;

            LogProfile(profile);
        }
    }

    private void LogProfile(QueryProfile profile)
    {
        var logLevel = profile.Duration > 1000 ? LogLevel.Warning : LogLevel.Information;

        _logger.Log(logLevel, 
                   "EF Profile - Operation: {Operation}, Duration: {Duration}ms, Rows: {Rows}", 
                   profile.OperationName, profile.Duration, profile.RowsAffected);
    }
}

public class QueryProfile
{
    public string OperationName { get; set; }
    public DateTime StartTime { get; set; }
    public Stopwatch Stopwatch { get; set; }
    public long Duration { get; set; }
    public int? RowsAffected { get; set; }
}

// Profiling Interceptor
public class ProfilingInterceptor : DbCommandInterceptor
{
    private readonly EFProfiler _profiler;

    public ProfilingInterceptor(EFProfiler profiler)
    {
        _profiler = profiler;
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result, 
        CancellationToken cancellationToken = default)
    {
        var operationName = $"Query_{command.CommandText.GetHashCode()}";
        _profiler.StartProfiling(operationName);

        var readerResult = await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);

        _profiler.EndProfiling(operationName);
        return readerResult;
    }

    public override async ValueTask<InterceptionResult<int>> NonQueryExecutingAsync(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<int> result, 
        CancellationToken cancellationToken = default)
    {
        var operationName = $"NonQuery_{command.CommandText.GetHashCode()}";
        _profiler.StartProfiling(operationName);

        var nonQueryResult = await base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);

        _profiler.EndProfiling(operationName, nonQueryResult.Result);
        return nonQueryResult;
    }
}

// Performance Monitoring Service
public class EFPerformanceMonitor : IEFPerformanceMonitor
{
    private readonly ILogger<EFPerformanceMonitor> _logger;
    private readonly ConcurrentQueue<PerformanceMetric> _metrics;

    public EFPerformanceMonitor(ILogger<EFPerformanceMonitor> logger)
    {
        _logger = logger;
        _metrics = new ConcurrentQueue<PerformanceMetric>();
    }

    public void RecordMetric(string operation, long duration, int? rowsAffected = null)
    {
        var metric = new PerformanceMetric
        {
            Operation = operation,
            Duration = duration,
            RowsAffected = rowsAffected,
            Timestamp = DateTime.UtcNow
        };

        _metrics.Enqueue(metric);

        // Keep only last 1000 metrics
        while (_metrics.Count > 1000)
        {
            _metrics.TryDequeue(out _);
        }

        // Log slow operations
        if (duration > 1000)
        {
            _logger.LogWarning("Slow EF operation detected: {Operation} took {Duration}ms", 
                             operation, duration);
        }
    }

    public PerformanceReport GenerateReport()
    {
        var metrics = _metrics.ToArray();

        return new PerformanceReport
        {
            TotalOperations = metrics.Length,
            AverageDuration = metrics.Average(m => m.Duration),
            MaxDuration = metrics.Max(m => m.Duration),
            MinDuration = metrics.Min(m => m.Duration),
            SlowOperations = metrics.Where(m => m.Duration > 1000).Count(),
            TopSlowOperations = metrics.OrderByDescending(m => m.Duration).Take(10).ToArray()
        };
    }
}

public class PerformanceMetric
{
    public string Operation { get; set; }
    public long Duration { get; set; }
    public int? RowsAffected { get; set; }
    public DateTime Timestamp { get; set; }
}

public class PerformanceReport
{
    public int TotalOperations { get; set; }
    public double AverageDuration { get; set; }
    public long MaxDuration { get; set; }
    public long MinDuration { get; set; }
    public int SlowOperations { get; set; }
    public PerformanceMetric[] TopSlowOperations { get; set; }
}

99. How do you implement Entity Framework alerting?

Answer: Entity Framework alerting involves setting up automated notifications for performance issues, errors, and anomalies in database operations.

Implementation Examples:

// Alert Configuration
public class EFAlertConfiguration
{
    public long SlowQueryThresholdMs { get; set; } = 1000;
    public int MaxQueriesPerRequest { get; set; } = 50;
    public int MaxConnectionsThreshold { get; set; } = 100;
    public bool EnableEmailAlerts { get; set; } = true;
    public bool EnableSlackAlerts { get; set; } = false;
    public string[] AlertRecipients { get; set; } = Array.Empty<string>();
}

// Alert Service
public interface IEFAlertService
{
    Task SendAlertAsync(EFAlert alert);
    Task SendSlowQueryAlertAsync(string query, long duration);
    Task SendConnectionPoolAlertAsync(int activeConnections);
    Task SendErrorAlertAsync(string operation, Exception exception);
}

public class EFAlertService : IEFAlertService
{
    private readonly ILogger<EFAlertService> _logger;
    private readonly EFAlertConfiguration _config;
    private readonly IEmailService _emailService;
    private readonly ISlackService _slackService;

    public EFAlertService(ILogger<EFAlertService> logger, 
                         EFAlertConfiguration config,
                         IEmailService emailService,
                         ISlackService slackService)
    {
        _logger = logger;
        _config = config;
        _emailService = emailService;
        _slackService = slackService;
    }

    public async Task SendAlertAsync(EFAlert alert)
    {
        _logger.LogWarning("EF Alert: {AlertType} - {Message}", alert.Type, alert.Message);

        if (_config.EnableEmailAlerts)
        {
            await SendEmailAlertAsync(alert);
        }

        if (_config.EnableSlackAlerts)
        {
            await SendSlackAlertAsync(alert);
        }
    }

    public async Task SendSlowQueryAlertAsync(string query, long duration)
    {
        var alert = new EFAlert
        {
            Type = EFAlertType.SlowQuery,
            Message = $"Slow query detected: {duration}ms",
            Details = new Dictionary<string, object>
            {
                ["Query"] = query,
                ["Duration"] = duration,
                ["Threshold"] = _config.SlowQueryThresholdMs
            },
            Severity = EFAlertSeverity.Warning,
            Timestamp = DateTime.UtcNow
        };

        await SendAlertAsync(alert);
    }

    public async Task SendConnectionPoolAlertAsync(int activeConnections)
    {
        if (activeConnections > _config.MaxConnectionsThreshold)
        {
            var alert = new EFAlert
            {
                Type = EFAlertType.ConnectionPool,
                Message = $"High connection pool usage: {activeConnections}",
                Details = new Dictionary<string, object>
                {
                    ["ActiveConnections"] = activeConnections,
                    ["Threshold"] = _config.MaxConnectionsThreshold
                },
                Severity = EFAlertSeverity.Critical,
                Timestamp = DateTime.UtcNow
            };

            await SendAlertAsync(alert);
        }
    }

    public async Task SendErrorAlertAsync(string operation, Exception exception)
    {
        var alert = new EFAlert
        {
            Type = EFAlertType.Error,
            Message = $"EF Error in {operation}: {exception.Message}",
            Details = new Dictionary<string, object>
            {
                ["Operation"] = operation,
                ["Exception"] = exception.ToString(),
                ["StackTrace"] = exception.StackTrace
            },
            Severity = EFAlertSeverity.Critical,
            Timestamp = DateTime.UtcNow
        };

        await SendAlertAsync(alert);
    }

    private async Task SendEmailAlertAsync(EFAlert alert)
    {
        var email = new EmailMessage
        {
            Subject = $"EF Alert: {alert.Type}",
            Body = FormatAlertEmail(alert),
            Recipients = _config.AlertRecipients
        };

        await _emailService.SendAsync(email);
    }

    private async Task SendSlackAlertAsync(EFAlert alert)
    {
        var message = new SlackMessage
        {
            Channel = "#alerts",
            Text = FormatSlackAlert(alert),
            Color = GetSeverityColor(alert.Severity)
        };

        await _slackService.SendAsync(message);
    }

    private string FormatAlertEmail(EFAlert alert)
    {
        return $@"
            <h2>Entity Framework Alert</h2>
            <p><strong>Type:</strong> {alert.Type}</p>
            <p><strong>Severity:</strong> {alert.Severity}</p>
            <p><strong>Message:</strong> {alert.Message}</p>
            <p><strong>Time:</strong> {alert.Timestamp:yyyy-MM-dd HH:mm:ss}</p>
            <h3>Details:</h3>
            <pre>{JsonSerializer.Serialize(alert.Details, new JsonSerializerOptions { WriteIndented = true })}</pre>
        ";
    }

    private string FormatSlackAlert(EFAlert alert)
    {
        return $"*EF Alert: {alert.Type}*\n" +
               $"Severity: {alert.Severity}\n" +
               $"Message: {alert.Message}\n" +
               $"Time: {alert.Timestamp:yyyy-MM-dd HH:mm:ss}";
    }

    private string GetSeverityColor(EFAlertSeverity severity)
    {
        return severity switch
        {
            EFAlertSeverity.Info => "#36a64f",
            EFAlertSeverity.Warning => "#ffa500",
            EFAlertSeverity.Critical => "#ff0000",
            _ => "#808080"
        };
    }
}

public class EFAlert
{
    public EFAlertType Type { get; set; }
    public string Message { get; set; }
    public Dictionary<string, object> Details { get; set; }
    public EFAlertSeverity Severity { get; set; }
    public DateTime Timestamp { get; set; }
}

public enum EFAlertType
{
    SlowQuery,
    ConnectionPool,
    Error,
    NPlusOneQuery,
    MemoryUsage
}

public enum EFAlertSeverity
{
    Info,
    Warning,
    Critical
}

// Alerting Interceptor
public class AlertingInterceptor : DbCommandInterceptor
{
    private readonly IEFAlertService _alertService;
    private readonly EFAlertConfiguration _config;

    public AlertingInterceptor(IEFAlertService alertService, EFAlertConfiguration config)
    {
        _alertService = alertService;
        _config = config;
    }

    public override async ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, 
        CommandEventData eventData, 
        InterceptionResult<DbDataReader> result, 
        CancellationToken cancellationToken = default)
    {
        var stopwatch = Stopwatch.StartNew();

        try
        {
            var readerResult = await base.ReaderExecutingAsync(command, eventData, result, cancellationToken);
            stopwatch.Stop();

            if (stopwatch.ElapsedMilliseconds > _config.SlowQueryThresholdMs)
            {
                await _alertService.SendSlowQueryAlertAsync(command.CommandText, stopwatch.ElapsedMilliseconds);
            }

            return readerResult;
        }
        catch (Exception ex)
        {
            await _alertService.SendErrorAlertAsync("Query Execution", ex);
            throw;
        }
    }
}

100. How do you handle Entity Framework troubleshooting?

Answer: Entity Framework troubleshooting involves systematic approaches to identify, diagnose, and resolve issues related to performance, errors, and unexpected behavior.

Implementation Examples:

// Troubleshooting Service
public interface IEFTroubleshootingService
{
    Task<TroubleshootingReport> AnalyzePerformanceAsync();
    Task<TroubleshootingReport> DiagnoseErrorAsync(Exception exception);
    Task<TroubleshootingReport> CheckConnectionHealthAsync();
    Task<TroubleshootingReport> AnalyzeQueryPatternsAsync();
}

public class EFTroubleshootingService : IEFTroubleshootingService
{
    private readonly ILogger<EFTroubleshootingService> _logger;
    private readonly ApplicationDbContext _context;
    private readonly IEFPerformanceMonitor _performanceMonitor;
    private readonly IConfiguration _configuration;

    public EFTroubleshootingService(ILogger<EFTroubleshootingService> logger,
                                   ApplicationDbContext context,
                                   IEFPerformanceMonitor performanceMonitor,
                                   IConfiguration configuration)
    {
        _logger = logger;
        _context = context;
        _performanceMonitor = performanceMonitor;
        _configuration = configuration;
    }

    public async Task<TroubleshootingReport> AnalyzePerformanceAsync()
    {
        var report = new TroubleshootingReport
        {
            AnalysisType = "Performance",
            Timestamp = DateTime.UtcNow,
            Issues = new List<TroubleshootingIssue>()
        };

        try
        {
            // Check for slow queries
            var performanceReport = _performanceMonitor.GenerateReport();
            if (performanceReport.AverageDuration > 500)
            {
                report.Issues.Add(new TroubleshootingIssue
                {
                    Type = IssueType.Performance,
                    Severity = IssueSeverity.Warning,
                    Description = "High average query duration detected",
                    Recommendations = new[]
                    {
                        "Review and optimize slow queries",
                        "Consider adding database indexes",
                        "Implement query result caching"
                    },
                    Metrics = new Dictionary<string, object>
                    {
                        ["AverageDuration"] = performanceReport.AverageDuration,
                        ["MaxDuration"] = performanceReport.MaxDuration
                    }
                });
            }

            // Check connection pool
            var connectionString = _configuration.GetConnectionString("DefaultConnection");
            var connectionHealth = await CheckConnectionHealthAsync();
            if (connectionHealth.Issues.Any(i => i.Type == IssueType.Connection))
            {
                report.Issues.AddRange(connectionHealth.Issues);
            }

            // Check for N+1 queries
            var nPlusOneIssues = await DetectNPlusOneQueriesAsync();
            report.Issues.AddRange(nPlusOneIssues);

            return report;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error during performance analysis");
            report.Issues.Add(new TroubleshootingIssue
            {
                Type = IssueType.System,
                Severity = IssueSeverity.Critical,
                Description = "Error during troubleshooting analysis",
                Recommendations = new[] { "Check system logs for details" }
            });
            return report;
        }
    }

    public async Task<TroubleshootingReport> DiagnoseErrorAsync(Exception exception)
    {
        var report = new TroubleshootingReport
        {
            AnalysisType = "Error Diagnosis",
            Timestamp = DateTime.UtcNow,
            Issues = new List<TroubleshootingIssue>()
        };

        try
        {
            var issue = new TroubleshootingIssue
            {
                Type = IssueType.Error,
                Severity = IssueSeverity.Critical,
                Description = exception.Message,
                Details = exception.ToString()
            };

            // Analyze exception type and provide specific recommendations
            switch (exception)
            {
                case SqlException sqlEx:
                    issue.Recommendations = GetSqlExceptionRecommendations(sqlEx);
                    break;
                case InvalidOperationException invalidOpEx:
                    issue.Recommendations = GetInvalidOperationRecommendations(invalidOpEx);
                    break;
                case DbUpdateException dbUpdateEx:
                    issue.Recommendations = GetDbUpdateRecommendations(dbUpdateEx);
                    break;
                default:
                    issue.Recommendations = new[] { "Review application logs for more details" };
                    break;
            }

            report.Issues.Add(issue);
            return report;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error during error diagnosis");
            return report;
        }
    }

    public async Task<TroubleshootingReport> CheckConnectionHealthAsync()
    {
        var report = new TroubleshootingReport
        {
            AnalysisType = "Connection Health",
            Timestamp = DateTime.UtcNow,
            Issues = new List<TroubleshootingIssue>()
        };

        try
        {
            // Test database connectivity
            var canConnect = await _context.Database.CanConnectAsync();
            if (!canConnect)
            {
                report.Issues.Add(new TroubleshootingIssue
                {
                    Type = IssueType.Connection,
                    Severity = IssueSeverity.Critical,
                    Description = "Cannot connect to database",
                    Recommendations = new[]
                    {
                        "Check database server status",
                        "Verify connection string",
                        "Check network connectivity",
                        "Verify firewall settings"
                    }
                });
            }

            // Check connection pool status
            var connectionString = _context.Database.GetConnectionString();
            if (connectionString != null)
            {
                var poolSize = GetConnectionPoolSize(connectionString);
                if (poolSize > 100)
                {
                    report.Issues.Add(new TroubleshootingIssue
                    {
                        Type = IssueType.Connection,
                        Severity = IssueSeverity.Warning,
                        Description = "High connection pool usage",
                        Recommendations = new[]
                        {
                            "Review connection disposal patterns",
                            "Consider increasing pool size",
                            "Implement connection pooling best practices"
                        },
                        Metrics = new Dictionary<string, object>
                        {
                            ["PoolSize"] = poolSize
                        }
                    });
                }
            }

            return report;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error during connection health check");
            report.Issues.Add(new TroubleshootingIssue
            {
                Type = IssueType.System,
                Severity = IssueSeverity.Critical,
                Description = "Error during connection health check",
                Recommendations = new[] { "Check system logs for details" }
            });
            return report;
        }
    }

    public async Task<TroubleshootingReport> AnalyzeQueryPatternsAsync()
    {
        var report = new TroubleshootingReport
        {
            AnalysisType = "Query Patterns",
            Timestamp = DateTime.UtcNow,
            Issues = new List<TroubleshootingIssue>()
        };

        try
        {
            // Analyze common query patterns
            var queryPatterns = await GetQueryPatternsAsync();

            foreach (var pattern in queryPatterns)
            {
                if (pattern.ExecutionCount > 1000 && pattern.AverageDuration > 100)
                {
                    report.Issues.Add(new TroubleshootingIssue
                    {
                        Type = IssueType.Performance,
                        Severity = IssueSeverity.Warning,
                        Description = $"Frequent slow query pattern detected: {pattern.Pattern}",
                        Recommendations = new[]
                        {
                            "Consider adding database indexes",
                            "Review query optimization",
                            "Implement caching for frequently accessed data"
                        },
                        Metrics = new Dictionary<string, object>
                        {
                            ["Pattern"] = pattern.Pattern,
                            ["ExecutionCount"] = pattern.ExecutionCount,
                            ["AverageDuration"] = pattern.AverageDuration
                        }
                    });
                }
            }

            return report;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error during query pattern analysis");
            return report;
        }
    }

    private async Task<List<TroubleshootingIssue>> DetectNPlusOneQueriesAsync()
    {
        var issues = new List<TroubleshootingIssue>();

        // This would typically involve analyzing query logs
        // For demonstration, we'll create a simple check
        var queryCount = GetRecentQueryCount();
        var requestCount = GetRecentRequestCount();

        if (queryCount > requestCount * 10) // Potential N+1
        {
            issues.Add(new TroubleshootingIssue
            {
                Type = IssueType.Performance,
                Severity = IssueSeverity.Warning,
                Description = "Potential N+1 query pattern detected",
                Recommendations = new[]
                {
                    "Use Include() for related entities",
                    "Consider using projection queries",
                    "Implement proper eager loading strategies"
                },
                Metrics = new Dictionary<string, object>
                {
                    ["QueryCount"] = queryCount,
                    ["RequestCount"] = requestCount
                }
            });
        }

        return issues;
    }

    private string[] GetSqlExceptionRecommendations(SqlException sqlEx)
    {
        return sqlEx.Number switch
        {
            18456 => new[] { "Check database credentials", "Verify user permissions" },
            4060 => new[] { "Check database name", "Verify connection string" },
            53 => new[] { "Check network connectivity", "Verify server address" },
            _ => new[] { "Review SQL Server error logs", "Check database configuration" }
        };
    }

    private string[] GetInvalidOperationRecommendations(InvalidOperationException ex)
    {
        if (ex.Message.Contains("No database provider"))
        {
            return new[] { "Configure database provider", "Check DbContext configuration" };
        }

        return new[] { "Review Entity Framework configuration", "Check model definitions" };
    }

    private string[] GetDbUpdateRecommendations(DbUpdateException ex)
    {
        return new[]
        {
            "Check data validation rules",
            "Verify foreign key constraints",
            "Review entity state management",
            "Check for concurrent modification conflicts"
        };
    }

    // Helper methods (implementations would depend on your monitoring infrastructure)
    private int GetConnectionPoolSize(string connectionString) => 0;
    private int GetRecentQueryCount() => 0;
    private int GetRecentRequestCount() => 0;
    private async Task<List<QueryPattern>> GetQueryPatternsAsync() => new List<QueryPattern>();
}

public class TroubleshootingReport
{
    public string AnalysisType { get; set; }
    public DateTime Timestamp { get; set; }
    public List<TroubleshootingIssue> Issues { get; set; } = new();
    public string Summary => $"{Issues.Count} issues found during {AnalysisType}";
}

public class TroubleshootingIssue
{
    public IssueType Type { get; set; }
    public IssueSeverity Severity { get; set; }
    public string Description { get; set; }
    public string Details { get; set; }
    public string[] Recommendations { get; set; } = Array.Empty<string>();
    public Dictionary<string, object> Metrics { get; set; } = new();
}

public enum IssueType
{
    Performance,
    Connection,
    Error,
    System
}

public enum IssueSeverity
{
    Info,
    Warning,
    Critical
}

public class QueryPattern
{
    public string Pattern { get; set; }
    public int ExecutionCount { get; set; }
    public double AverageDuration { get; set; }
}

// Troubleshooting Controller
[ApiController]
[Route("api/[controller]")]
public class TroubleshootingController : ControllerBase
{
    private readonly IEFTroubleshootingService _troubleshootingService;

    public TroubleshootingController(IEFTroubleshootingService troubleshootingService)
    {
        _troubleshootingService = troubleshootingService;
    }

    [HttpGet("performance")]
    public async Task<ActionResult<TroubleshootingReport>> AnalyzePerformance()
    {
        var report = await _troubleshootingService.AnalyzePerformanceAsync();
        return Ok(report);
    }

    [HttpPost("diagnose-error")]
    public async Task<ActionResult<TroubleshootingReport>> DiagnoseError([FromBody] ExceptionInfo exceptionInfo)
    {
        var exception = new Exception(exceptionInfo.Message);
        var report = await _troubleshootingService.DiagnoseErrorAsync(exception);
        return Ok(report);
    }

    [HttpGet("connection-health")]
    public async Task<ActionResult<TroubleshootingReport>> CheckConnectionHealth()
    {
        var report = await _troubleshootingService.CheckConnectionHealthAsync();
        return Ok(report);
    }
}

public class ExceptionInfo
{
    public string Message { get; set; }
    public string StackTrace { get; set; }
}

Key Points for Technical Lead Interviews:

1. Metrics Collection: Focus on business-relevant metrics, not just technical ones
2. Debugging Tools: Emphasize the importance of development productivity and production debugging
3. Profiling: Discuss both development-time and production profiling strategies
4. Alerting: Highlight the importance of proactive monitoring and automated responses
5. Troubleshooting: Demonstrate systematic problem-solving approaches

Best Practices to Mention:

• Use structured logging for better analysis
• Implement circuit breakers for database operations
• Set up automated health checks
• Create runbooks for common issues
• Use distributed tracing for complex queries
• Implement query result caching strategies
• Monitor connection pool usage
• Set up automated performance regression testing

Official references

• DbContext lifetime
• Efficient EF Core querying
• ExecuteUpdate and ExecuteDelete
• EF Core transactions