Interview StacksMaster every tech stack

Entity Framework and Database

Entity Framework Core: loading strategies, migrations, change tracking, transactions, concurrency, and query performance.

Open as page

Entity Framework Core (EF Core) is a lightweight, extensible, open-source, and cross-platform version of Entity Framework, Microsoft's Object-Relational Mapper (ORM) for .NET.

Key Differences:

AspectEF CoreEF 6
PlatformCross-platform (.NET Core, .NET 5+)Windows only (.NET Framework)
PerformanceFaster, more efficientSlower
FeaturesModern, lightweightFeature-rich, mature
LINQ TranslationImproved client/server evaluationLimited
BatchingBetter batch operationsLimited batching
Global Query FiltersSupportedNot supported
Shadow PropertiesEnhanced supportLimited
Owned EntitiesBetter supportLimited
Database ProvidersMore providers availableFewer providers

What EF Core doesn't have (from EF6):

  • Lazy loading by default (needs configuration)
  • Automatic migrations
  • ObjectContext API
  • Entity splitting
  • Some inheritance strategies

Related Resources

Compare EF Core & EF6
Open as page

Code First Approach:

  • Define your domain model classes first
  • EF generates the database schema from your code
  • Uses migrations to evolve the database
  • Better for new projects and version control
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class AppDbContext : DbContext
{
    public DbSet Products { get; set; }
}

// Generate migration: dotnet ef migrations add InitialCreate
// Update database: dotnet ef database update

Database First Approach:

  • Start with an existing database
  • Generate entity classes from the database
  • Uses scaffolding to create models
  • Better for existing databases
# Scaffold from existing database
dotnet ef dbcontext scaffold "ConnectionString" Microsoft.EntityFrameworkCore.SqlServer -o Models

When to use each:

  • Code First: New projects, agile development, domain-driven design
  • Database First: Legacy databases, DBA-controlled schemas, multiple applications sharing one database

Related Resources

Creating and configuring a model
Reverse engineering (scaffolding)
Open as page

1. Eager Loading Load related data as part of the initial query using Include().

// Single level
var orders = context.Orders
    .Include(o => o.Customer)
    .ToList();

// Multiple levels
var orders = context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .ToList();

2. Lazy Loading Related data is automatically loaded when accessed. Requires Microsoft.EntityFrameworkCore.Proxies.

// Enable lazy loading
services.AddDbContext(options =>
    options.UseLazyLoadingProxies()
           .UseSqlServer(connectionString));

// Make navigation properties virtual
public class Order
{
    public int Id { get; set; }
    public virtual Customer Customer { get; set; }
    public virtual ICollection OrderItems { get; set; }
}

// Data loaded when accessed
var order = context.Orders.First();
var customerName = order.Customer.Name; // Triggers database query

3. Explicit Loading Manually load related data when needed using Load().

var order = context.Orders.First();

// Load single reference
context.Entry(order)
    .Reference(o => o.Customer)
    .Load();

// Load collection
context.Entry(order)
    .Collection(o => o.OrderItems)
    .Load();

// Load with filter
context.Entry(order)
    .Collection(o => o.OrderItems)
    .Query()
    .Where(oi => oi.Quantity > 5)
    .Load();

Comparison:

TypeProsConsUse Case
EagerSingle query, no N+1 problemCan over-fetch dataKnown data needs
LazyLoad only what's neededN+1 problem, requires open connectionExploratory operations
ExplicitFine-grained controlMore code, manual managementConditional loading

Related Resources

Loading related data
Open as page

Migrations track changes to your data model and update the database schema.

1. Basic Migration Commands:

# Add a new migration
dotnet ef migrations add MigrationName

# Update database to latest migration
dotnet ef database update

# Update to specific migration
dotnet ef database update MigrationName

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

# Generate SQL script
dotnet ef migrations script

# List all migrations
dotnet ef migrations list

2. Migration Strategies:

A. Automatic Migrations (Development)

public static void Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();
    
    using (var scope = host.Services.CreateScope())
    {
        var context = scope.ServiceProvider.GetRequiredService();
        context.Database.Migrate(); // Apply pending migrations
    }
    
    host.Run();
}

B. Manual SQL Scripts (Production)

# Generate SQL script for deployment
dotnet ef migrations script --idempotent --output migration.sql

C. Custom Migration Code

public partial class AddProductIndex : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateIndex(
            name: "IX_Products_Name",
            table: "Products",
            column: "Name");
            
        // Custom SQL
        migrationBuilder.Sql(@"
            UPDATE Products 
            SET Price = Price * 1.1 
            WHERE CategoryId = 1
        ");
    }

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

D. Data Seeding in Migrations

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.InsertData(
        table: "Categories",
        columns: new[] { "Id", "Name" },
        values: new object[,]
        {
            { 1, "Electronics" },
            { 2, "Books" },
            { 3, "Clothing" }
        });
}

Best Practices:

  • Keep migrations small and focused
  • Test migrations in development first
  • Use --idempotent scripts for production
  • Never modify applied migrations
  • Keep migration history in source control

Related Resources

Migrations overview
Open as page

Repository Pattern Abstracts data access logic and provides a collection-like interface for accessing domain objects.

public interface IRepository where T : class
{
    Task GetByIdAsync(int id);
    Task<IEnumerable> GetAllAsync();
    Task<IEnumerable> FindAsync(Expression<Func> predicate);
    Task AddAsync(T entity);
    void Update(T entity);
    void Remove(T entity);
}

public class Repository : IRepository where T : class
{
    protected readonly DbContext _context;
    protected readonly DbSet _dbSet;

    public Repository(DbContext context)
    {
        _context = context;
        _dbSet = context.Set();
    }

    public async Task GetByIdAsync(int id)
    {
        return await _dbSet.FindAsync(id);
    }

    public async Task<IEnumerable> GetAllAsync()
    {
        return await _dbSet.ToListAsync();
    }

    public async Task<IEnumerable> FindAsync(Expression<Func> predicate)
    {
        return await _dbSet.Where(predicate).ToListAsync();
    }

    public async Task AddAsync(T entity)
    {
        await _dbSet.AddAsync(entity);
    }

    public void Update(T entity)
    {
        _dbSet.Update(entity);
    }

    public void Remove(T entity)
    {
        _dbSet.Remove(entity);
    }
}

Unit of Work Pattern Maintains a list of objects affected by a business transaction and coordinates writing changes to the database.

public interface IUnitOfWork : IDisposable
{
    IRepository Products { get; }
    IRepository Categories { get; }
    IRepository Orders { get; }
    
    Task SaveChangesAsync();
    Task BeginTransactionAsync();
    Task CommitTransactionAsync();
    Task RollbackTransactionAsync();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;
    private IDbContextTransaction _transaction;

    public UnitOfWork(AppDbContext context)
    {
        _context = context;
        Products = new Repository(_context);
        Categories = new Repository(_context);
        Orders = new Repository(_context);
    }

    public IRepository Products { get; private set; }
    public IRepository Categories { get; private set; }
    public IRepository Orders { get; private set; }

    public async Task SaveChangesAsync()
    {
        return await _context.SaveChangesAsync();
    }

    public async Task BeginTransactionAsync()
    {
        _transaction = await _context.Database.BeginTransactionAsync();
    }

    public async Task CommitTransactionAsync()
    {
        await _transaction.CommitAsync();
    }

    public async Task RollbackTransactionAsync()
    {
        await _transaction.RollbackAsync();
    }

    public void Dispose()
    {
        _transaction?.Dispose();
        _context.Dispose();
    }
}

Usage Example:

public class ProductService
{
    private readonly IUnitOfWork _unitOfWork;

    public ProductService(IUnitOfWork unitOfWork)
    {
        _unitOfWork = unitOfWork;
    }

    public async Task CreateProductWithCategoryAsync(Product product, Category category)
    {
        await _unitOfWork.BeginTransactionAsync();
        
        try
        {
            await _unitOfWork.Categories.AddAsync(category);
            await _unitOfWork.SaveChangesAsync();
            
            product.CategoryId = category.Id;
            await _unitOfWork.Products.AddAsync(product);
            await _unitOfWork.SaveChangesAsync();
            
            await _unitOfWork.CommitTransactionAsync();
        }
        catch
        {
            await _unitOfWork.RollbackTransactionAsync();
            throw;
        }
    }
}

Benefits:

  • Separation of concerns
  • Testability (easy to mock)
  • Centralized data access logic
  • Transaction management
  • Reduced coupling

Note: DbContext already implements Unit of Work pattern, so this is often considered over-engineering for simple applications.

Related Resources

DbContext as Unit of Work / Repository