Entity Framework Core Guide

Blogger2/15/24About 3 min

Entity Framework Core Guide

Overview

Entity Framework Core (EF Core) is a lightweight, extensible, open-source object-relational mapping (ORM) framework for .NET applications.

1. Getting Started

1.1 Installing Dependencies

# Install EF Core
dotnet add package Microsoft.EntityFrameworkCore

# Install a database provider (SQL Server example)
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

# Install tools
dotnet tool install --global dotnet-ef

1.2 Creating a DbContext

public class AppDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=localhost;Database=MyDb;Trusted_Connection=True;");
    }
}

// Entity classes
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    public int CategoryId { get; set; }
    
    public Category Category { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    public ICollection<Product> Products { get; set; }
}

1.3 Dependency Injection Configuration

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<AppDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
}

2. Data Migrations

2.1 Creating Migrations

# Create the initial migration
dotnet ef migrations add InitialCreate

# Update the database
dotnet ef database update

# Create a new migration
dotnet ef migrations add AddProductDescription

# Roll back a migration
dotnet ef migrations remove

2.2 Migration File Structure

Migrations/
├── 20240101000000_InitialCreate.cs
├── 20240102000000_AddProductDescription.cs
└── AppDbContextModelSnapshot.cs

3. Querying Data

3.1 Basic Queries

// Query all records
var products = await _context.Products.ToListAsync();

// Conditional query
var cheapProducts = await _context.Products
    .Where(p => p.Price < 100)
    .ToListAsync();

// Single-record query
var product = await _context.Products
    .FirstOrDefaultAsync(p => p.Id == id);

// Include related data (Eager Loading)
var productsWithCategory = await _context.Products
    .Include(p => p.Category)
    .ToListAsync();

// Multi-level relationships
var orders = await _context.Orders
    .Include(o => o.Customer)
    .Include(o => o.OrderItems)
        .ThenInclude(oi => oi.Product)
    .ToListAsync();

// Lazy loading (requires LazyLoadingProxies)
services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(connectionString)
           .UseLazyLoadingProxies());

3.3 Projection Queries

// Select specific fields
var productNames = await _context.Products
    .Select(p => p.Name)
    .ToListAsync();

// Anonymous types
var productInfo = await _context.Products
    .Select(p => new { p.Id, p.Name, p.Price })
    .ToListAsync();

// DTO projection
var dtos = await _context.Products
    .Select(p => new ProductDto
    {
        Id = p.Id,
        Name = p.Name,
        Price = p.Price,
        CategoryName = p.Category.Name
    })
    .ToListAsync();

4. Data Operations

4.1 Adding Data

// Add a single record
var product = new Product { Name = "iPhone", Price = 5999 };
_context.Products.Add(product);
await _context.SaveChangesAsync();

// Add multiple records
var products = new List<Product>
{
    new Product { Name = "iPad", Price = 3999 },
    new Product { Name = "MacBook", Price = 9999 }
};
_context.Products.AddRange(products);
await _context.SaveChangesAsync();

4.2 Updating Data

// Method 1: Query first, then update
var product = await _context.Products.FindAsync(id);
if (product != null)
{
    product.Price = 6999;
    await _context.SaveChangesAsync();
}

// Method 2: Update directly (no query required)
_context.Products.Update(product);
await _context.SaveChangesAsync();

// Bulk update
await _context.Products
    .Where(p => p.CategoryId == 1)
    .ExecuteUpdateAsync(s => s.SetProperty(p => p.Price, p => p.Price * 1.1m));

4.3 Deleting Data

// Method 1: Query first, then delete
var product = await _context.Products.FindAsync(id);
if (product != null)
{
    _context.Products.Remove(product);
    await _context.SaveChangesAsync();
}

// Bulk delete
await _context.Products
    .Where(p => p.Price < 100)
    .ExecuteDeleteAsync();

5. Advanced Queries

5.1 Paging

int pageNumber = 1;
int pageSize = 10;

var pagedProducts = await _context.Products
    .OrderBy(p => p.Price)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

5.2 Aggregation Queries

// Aggregation
var count = await _context.Products.CountAsync();
var avgPrice = await _context.Products.AverageAsync(p => p.Price);
var maxPrice = await _context.Products.MaxAsync(p => p.Price);

// Grouped aggregation
var categoryStats = await _context.Products
    .GroupBy(p => p.CategoryId)
    .Select(g => new 
    {
        CategoryId = g.Key,
        ProductCount = g.Count(),
        AvgPrice = g.Average(p => p.Price)
    })
    .ToListAsync();

5.3 Raw SQL Queries

// Query
var products = await _context.Products
    .FromSqlRaw("SELECT * FROM Products WHERE Price > {0}", 100)
    .ToListAsync();

// Execute command
await _context.Database.ExecuteSqlRawAsync(
    "UPDATE Products SET Price = Price * 1.1 WHERE CategoryId = {0}", categoryId);

6. Performance Optimization

6.1 Tracking vs. No Tracking

// Tracked query (default) - supports change detection
var tracked = await _context.Products.FirstAsync(p => p.Id == id);

// No-tracking query - better performance for read-only scenarios
var untracked = await _context.Products.AsNoTracking().FirstAsync(p => p.Id == id);

6.2 Compiled Queries

// Define a compiled query
private static readonly Func<AppDbContext, int, Product> GetProductById =
    EF.CompileQuery((AppDbContext context, int id) =>
        context.Products.FirstOrDefault(p => p.Id == id));

// Usage
var product = GetProductById(_context, id);

6.3 Query Optimization Tips

Use Include instead of multiple queries
Avoid N+1 query problems
Use AsNoTracking for read-only operations
Compile frequently executed queries
Use indexes appropriately

7. Concurrency Control

7.1 Optimistic Concurrency

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
    
    // Concurrency token
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// Check concurrency on update
try
{
    var product = await _context.Products.FindAsync(id);
    product.Price = newPrice;
    await _context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    // Handle concurrency conflict
}

Summary

Entity Framework Core is the most popular ORM framework in the .NET ecosystem. Mastering its core features is essential for efficiently developing database-driven applications.

Author: Lei Tao
Date: February 15, 2024