Cheatsheet Entity Framework

dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools
dotnet add package Microsoft.EntityFrameworkCore.Design

The EntityFrameworkCore (core), SqlServer (provider) and Tools/Design (CLI and migrations) packages are essential. Install via dotnet add package or NuGet Manager.

protected override void OnModelCreating(
    ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Client>(e =>
    {
        e.HasKey(c => c.Id);
        e.Property(c => c.Name)
            .HasMaxLength(100)
            .IsRequired();
    });
}

The OnModelCreating configures entities via the Fluent API. It is called once at initialization. It takes priority over Data Annotations.

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

The EnableRetryOnFailure() automatically retries on transient network failures. Essential for the cloud (Azure SQL). Configure the attempts and delay.

public class AppDbContext : DbContext
{
    public DbSet<Client> Clients { get; set; }
    public DbSet<Product> Products { get; set; }

    protected override void OnConfiguring(
        DbContextOptionsBuilder options)
    {
        options.UseSqlServer(connectionString);
    }
}

The DbContext is the main data access class. Each DbSet<T> represents a table. The OnConfiguring defines the provider and the connection string.

// SQL Server
options.UseSqlServer(conn);

// PostgreSQL
options.UseNpgsql(conn);

// SQLite
options.UseSqlite("Data Source=app.db");

// InMemory (tests)
options.UseInMemoryDatabase("TestDb");

EF Core supports multiple providers: SqlServer, Npgsql (PostgreSQL), Sqlite and InMemory (tests). Each one has its own NuGet package.

options.UseSqlServer(conn, sqlOptions =>
{
    sqlOptions.CommandTimeout(60); // seconds
});

// Or per query:
context.Database.SetCommandTimeout(120);

The CommandTimeout sets the maximum time (seconds) for SQL commands. The default is 30s. Increase it for heavy queries or reports.

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration.GetConnectionString("Default")
    )
);

// Inject into the controller:
public class ClientesController : ControllerBase
{
    private readonly AppDbContext _context;
    public ClientesController(AppDbContext context)
        => _context = context;
}

The AddDbContext registers the context in the DI container with a scope per request. Inject it via the constructor into controllers and services.

builder.Services.AddDbContextPool<AppDbContext>(
    options => options.UseSqlServer(conn),
    poolSize: 128
);

The AddDbContextPool reuses DbContext instances instead of creating new ones. It improves performance in high-throughput scenarios. The pool is thread-safe.

// Separate configuration class:
public class ClienteConfig
    : IEntityTypeConfiguration<Client>
{
    public void Configure(EntityTypeBuilder<Client> e)
    {
        e.HasKey(c => c.Id);
        e.Property(c => c.Name).HasMaxLength(100);
    }
}

// In OnModelCreating:
modelBuilder.ApplyConfigurationsFromAssembly(
    typeof(AppDbContext).Assembly);

The IEntityTypeConfiguration<T> separates the configuration into its own classes. The ApplyConfigurationsFromAssembly() registers them all automatically.

// appsettings.json
"ConnectionStrings": {
    "Default": "Server=localhost;Database=MyDb;Trusted_Connection=True;TrustServerCertificate=True"
}

// Access:
var conn = builder.Configuration
    .GetConnectionString("Default");

The connection string lives in appsettings.json. The GetConnectionString() reads it by name. For SQL Server use Trusted_Connection or user/password.

options.UseSqlServer(conn)
    .LogTo(Console.WriteLine, LogLevel.Information)
    .EnableSensitiveDataLogging()
    .EnableDetailedErrors();

The LogTo() shows the generated SQL in the console. The EnableSensitiveDataLogging() includes the parameter values. Use it only in development.

builder.Services.AddDbContext<ReadOnlyContext>(
    options => options.UseSqlServer(readReplicaConn),
    ServiceLifetime.Transient
);

You can have multiple DbContext to separate reads/writes or connect to different databases. The ServiceLifetime controls the lifetime.

public class Client
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime CreatedAt { get; set; }
}

An entity is a simple POCO class. EF maps it automatically by convention: Id is the key, properties become columns.

public enum State { Active, Inactive, Suspended }

// Fluent API:
e.Property(p => p.State)
    .HasConversion<string>()
    .HasMaxLength(20);

// Or with a Data Annotation:
[EnumDataType(typeof(State))]
public string State { get; set; }

The HasConversion<string>() stores the enum the readable text in the DB instead of a number. It makes direct queries and debugging easier.

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

e.Property(p => p.State)
    .HasDefaultValue("active");

e.Property(p => p.Order)
    .HasDefaultValue(0);

The HasDefaultValueSql() sets SQL values (e.g. GETUTCDATE()). The HasDefaultValue() uses literal values. Applied in the migration.

modelBuilder.Entity<Dog>().ToTable("Caes");
modelBuilder.Entity<Cat>().ToTable("Gatos");

The TPT creates one table per type in the hierarchy, linked by an FK. It normalizes the data but the queries are more complex (JOINs). Use ToTable() on each subtype.

public class Product
{
    [Key]
    public int Id { get; set; }

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

    [Column(TypeName = "decimal(18,2)")]
    public decimal Price { get; set; }

    [NotMapped]
    public string FullName => $"{Name} ({Price})";
}

The Data Annotations configure via attributes: [Key], [Required], [MaxLength], [Column], [NotMapped]. Simpler than the Fluent API.

e.Property(p => p.Tags)
    .HasConversion(
        v => string.Join(",", v),       // when saving
        v => v.Split(",",
            StringSplitOptions.None).ToList()  // when reading
    );

// Convert DateTime to Unix:
e.Property(p => p.Data)
    .HasConversion<long>();

The Value Conversions transform types when saving/reading. The first lambda converts to the DB, the second converts from the DB. Useful for lists, JSON, etc.

e.Property(p => p.FullName)
    .HasComputedColumnSql("[Name] + ' ' + [Surname]");

// Stored (persisted):
e.Property(p => p.Total)
    .HasComputedColumnSql("[Price] * [Quantity]",
        stored: true);

The HasComputedColumnSql() creates calculated columns in the DB. With stored: true the value is persisted (better for frequent reading).

[Timestamp]
public byte[] RowVersion { get; set; }

// Or Fluent API:
e.Property(p => p.RowVersion)
    .IsRowVersion();

// Manual token:
e.Property(p => p.Version)
    .IsConcurrencyToken();

The IsRowVersion() creates an optimistic concurrency token. SQL Server updates it automatically. If another user modifies it, it throws DbUpdateConcurrencyException.

modelBuilder.Entity<Product>(e =>
{
    e.ToTable("Products");
    e.Property(p => p.Name)
        .HasMaxLength(200)
        .IsRequired();
    e.Property(p => p.Price)
        .HasPrecision(18, 2);
    e.HasIndex(p => p.Name).IsUnique();
});

The Fluent API offers more control: ToTable() sets the name, HasPrecision() for decimals, HasIndex() creates indexes. It takes priority over annotations.

modelBuilder.Entity<Post>()
    .Property<DateTime>("LastModified");

// Access:
context.Entry(post)
    .Property("LastModified").CurrentValue = DateTime.Now;

// In queries:
context.Posts.OrderBy("LastModified");

The Shadow Properties exist in the DB but not in the class. Useful for auditing. Access them via Entry().Property() or a string in queries.

public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string CodigoPostal { get; set; }
}

// Configuration:
e.OwnsOne(c => c.Address, m =>
{
    m.Property(x => x.Street).HasMaxLength(200);
    m.Property(x => x.City).HasMaxLength(100);
});

The Owned Types map value objects into the same table. They do not have their own identity. Ideal for Address, Coordenadas, etc.

public class Order
{
    private readonly List<Item> _items = new();
    public IReadOnlyCollection<Item> Items
        => _items.AsReadOnly();

    public void AddItem(Item item) => _items.Add(item);
}

// Config:
e.Metadata.FindNavigation(nameof(Order.Items))!
    .SetPropertyAccessMode(PropertyAccessMode.Field);

The Backing Fields allow encapsulating collections. EF accesses the private field directly. Expose it the an IReadOnlyCollection and control mutations via methods.

public class ItemPedido
{
    public int PedidoId { get; set; }
    public int ProdutoId { get; set; }
    public int Quantity { get; set; }
}

// Fluent API:
modelBuilder.Entity<ItemPedido>()
    .HasKey(i => new { i.PedidoId, i.ProdutoId });

A composite key uses multiple columns the the PK. Define it with HasKey() passing an anonymous type. It cannot be auto-increment.

// Simple index:
e.HasIndex(p => p.Email).IsUnique();

// Composite index:
e.HasIndex(p => new { p.Name, p.City });

// With a filter (SQL Server):
e.HasIndex(p => p.Email)
    .HasFilter("[Email] IS NOT NULL");

The HasIndex() creates indexes for performance. The IsUnique() prevents duplicates. Composite indexes accept anonymous types. The HasFilter() creates filtered indexes.

public abstract class Animal
{
    public int Id { get; set; }
    public string Name { get; set; }
}
public class Dog : Animal { public string Raca { get; set; } }
public class Cat : Animal { public bool Interior { get; set; } }

// Discriminator:
e.HasDiscriminator<string>("Type")
    .HasValue<Dog>("dog")
    .HasValue<Cat>("cat");

The TPH (default) stores the whole hierarchy in one table with a Discriminator. Simple and fast. Subtype columns become nullable.

public class RelatorioVendas
{
    public string Month { get; set; }
    public decimal Total { get; set; }
}

// Config:
modelBuilder.Entity<RelatorioVendas>(e =>
{
    e.HasNoKey();
    e.ToView("vw_RelatorioVendas");
});

// Query:
var data = await context.Set<RelatorioVendas>().ToListAsync();

The Keyless Entities map views or queries without a PK. Use HasNoKey() and ToView(). They are read-only and do not support tracking.

var client = new Client
{
    Name = "Anna",
    Email = "ana@mail.com"
};

context.Clients.Add(client);
await context.SaveChangesAsync();

// Automatically generated ID:
Console.WriteLine(client.Id);

The Add() marks the entity the new and the SaveChangesAsync() runs the INSERT. The auto-increment ID is filled in after saving.

var client = await context.Clients.FindAsync(1);
context.Clients.Remove(client);
await context.SaveChangesAsync();

// Multiple:
context.Clients.RemoveRange(inativos);
await context.SaveChangesAsync();

The Remove() marks for deletion and the SaveChangesAsync() runs the DELETE. The RemoveRange() removes several at once.

// EF Core has no native upsert — manual pattern:
var existing = await context.Products
    .FirstOrDefaultAsync(p => p.Sku == sku);

if (existing != null)
{
    existing.Price = novoPreco;
}
else
{
    context.Products.Add(new Product { Sku = sku, Price = novoPreco });
}
await context.SaveChangesAsync();

EF Core has no native upsert. The pattern is to look it up and decide between update or insert. Alternative: ExecuteUpdate + fallback to insert.

try
{
    await context.SaveChangesAsync();
}
catch (DbUpdateException ex)
{
    // DB error (constraint, etc.)
    Console.WriteLine(ex.InnerException?.Message);
}
catch (DbUpdateConcurrencyException ex)
{
    // Concurrency conflict
    var entry = ex.Entries.Single();
}

The SaveChangesAsync() throws DbUpdateException on DB errors and DbUpdateConcurrencyException on conflicts. Handle both for robustness.

// By primary key (uses the local cache):
var client = await context.Clients.FindAsync(1);

// With a composite key:
var item = await context.Items.FindAsync(pedidoId, produtoId);

// Returns null if it does not exist

The FindAsync() looks up by primary key. It checks the Change Tracker (local cache) first before going to the DB. It returns null if it does not exist.

var products = new List<Product>
{
    new() { Name = "Keyboard", Price = 49.99m },
    new() { Name = "Mouse", Price = 29.99m },
    new() { Name = "Monitor", Price = 299.99m },
};

context.Products.AddRange(products);
await context.SaveChangesAsync();

The AddRange() adds several entities at once. More efficient than Add() in a loop because it generates a single batch of INSERTs.

// Attach (Unchanged state):
context.Clients.Attach(client);

// Mark the modified:
context.Entry(client).State = EntityState.Modified;

// Possible states:
// Detached, Unchanged, Added, Modified, Deleted

The Attach() links a disconnected entity to the context. The Entry().State sets the state manually. Useful in stateless APIs.

// Find: uses the local cache, generates no SQL if already tracked
var c1 = await context.Clients.FindAsync(1);

// FirstOrDefault: always goes to the DB
var c2 = await context.Clients
    .FirstOrDefaultAsync(c => c.Id == 1);

// Find does not accept Include

The FindAsync() checks the local cache first (faster). The FirstOrDefaultAsync() always generates SQL. The Find does not support Include().

// First with a condition:
var c = await context.Clients
    .FirstOrDefaultAsync(c => c.Email == email);

// Single (throws an exception if multiple):
var c2 = await context.Clients
    .SingleAsync(c => c.Id == 1);

// All:
var all = await context.Clients.ToListAsync();

The FirstOrDefaultAsync() returns the first one or null. The SingleAsync() throws an exception if there is more than one. The ToListAsync() brings them all.

int afetados = await context.Clients
    .Where(c => c.Active == false)
    .ExecuteUpdupTosync(s => s
        .SetProperty(c => c.State, "arquivado")
        .SetProperty(c => c.ArquivadoEm, DateTime.Now)
    );

The ExecuteUpdupTosync() (EF Core 7+) does bulk updates without loading entities. It generates a direct SQL UPDATE. It returns the number of affected rows.

// See pending changes:
var entries = context.ChangeTracker.Entries()
    .Where(e => e.State == EntityState.Modified);

foreach (var entry in entries)
{
    Console.WriteLine(entry.Entity.GetType().Name);
    foreach (var prop in entry.Properties)
    {
        if (prop.IsModified)
            Console.WriteLine($"  {prop.Metadata.Name}: {prop.OriginalValue} -> {prop.CurrentValue}");
    }
}

The Change Tracker detects changes automatically. The Entries() lists tracked entities and the Properties shows the original vs current values.

var client = await context.Clients.FindAsync(1);
client.Name = "Anna Silva";
client.Email = "ana.silva@mail.com";
await context.SaveChangesAsync();

// Or attach + state:
context.Clients.Update(client);
await context.SaveChangesAsync();

With active tracking, just modify the properties and call SaveChangesAsync(). The Update() marks all properties the modified.

int removidos = await context.Logs
    .Where(l => l.Data < DateTime.Now.AddYears(-1))
    .ExecuteDeleteAsync();

The ExecuteDeleteAsync() deletes in bulk without loading. It generates a direct SQL DELETE. Much faster than RemoveRange() for thousands of records.

// Disable auto-detect (performance in bulk):
context.ChangeTracker.AutoDetectChangesEnabled = false;

// Detect manually:
context.ChangeTracker.DetectChanges();

// Clear tracking:
context.ChangeTracker.Clear();

The AutoDetectChangesEnabled = false disables automatic detection (better in bulk operations). The Clear() clears the tracker without saving.

var ativos = await context.Clients
    .Where(c => c.Active && c.City == "Lisbon")
    .ToListAsync();

// Multiple conditions:
var result = await context.Products
    .Where(p => p.Price > 10 && p.Price < 100)
    .Where(p => p.Stock > 0)
    .ToListAsync();

The Where() filters with lambda expressions. Chain several Where() (implicit AND). It translates to SQL WHERE.

bool exists = await context.Clients
    .AnyAsync(c => c.Email == email);

bool todosAtivos = await context.Orders
    .AllAsync(p => p.State == "completed");

// SQL equivalent: EXISTS / NOT EXISTS

The AnyAsync() checks for existence (generates EXISTS). The AllAsync() checks whether they all meet the condition. More efficient than Count() > 0.

var products = await context.Products
    .AsNoTracking()
    .Where(p => p.Active)
    .ToListAsync();

// Or globally:
context.ChangeTracker.QueryTrackingBehavior =
    QueryTrackingBehavior.NoTracking;

The AsNoTracking() disables tracking (read-only). Faster and less memory. Ideal for read queries. You can set it globally.

var result = await context.Products
    .Select(p => new
    {
        p.Name,
        Classification = p.Price > 100 ? "expensive" : "cheap"
    })
    .ToListAsync();

// Generates: CASE WHEN Price > 100 THEN 'expensive' ELSE 'cheap' END

Ternary expressions in the Select() translate to CASE WHEN in SQL. Useful for classifications and formatting in the query.

var sorted = await context.Products
    .OrderBy(p => p.Category)
    .ThenByDescending(p => p.Price)
    .ToListAsync();

// Descending:
var recent = await context.Clients
    .OrderByDescending(c => c.CreatedAt)
    .ToListAsync();

The OrderBy() sorts ascending and OrderByDescending() descending. The ThenBy() adds a secondary sort.

int page = 2, size = 10;

var items = await context.Products
    .OrderBy(p => p.Name)
    .Skip((page - 1) * size)
    .Take(size)
    .ToListAsync();

// Total for the UI:
int total = await context.Products.CountAsync();

The Skip() skips records and the Take() limits them. It requires OrderBy() for a consistent order. It generates OFFSET/FETCH in SQL Server.

var ids = new List<int> { 1, 2, 3, 5 };

var products = await context.Products
    .Where(p => ids.Contains(p.Id))
    .ToListAsync();

// Generates: WHERE Id IN (1, 2, 3, 5)

The Contains() with a list generates WHERE IN (...). Useful for filtering by multiple values. The list can come from another query.

var clients = await context.Clients
    .Include(c => c.Orders)
        .ThenInclude(p => p.Items)
    .AsSplitQuery()
    .ToListAsync();

The AsSplitQuery() splits the Include into multiple SQL queries instead of one huge JOIN. It avoids cartesian explosion with multiple collections.

// Anonymous type:
var names = await context.Clients
    .Select(c => new { c.Name, c.Email })
    .ToListAsync();

// DTO:
var dtos = await context.Products
    .Select(p => new ProdutoDto(p.Name, p.Price))
    .ToListAsync();

The Select() projects into anonymous types or DTOs. It brings only the needed columns — much more efficient than loading the whole entity.

var categories = await context.Products
    .Select(p => p.Category)
    .Distinct()
    .ToListAsync();

// DistinctBy (EF Core 6+):
var uniques = await context.Clients
    .DistinctBy(c => c.City)
    .ToListAsync();

The Distinct() removes duplicates. The DistinctBy() (EF Core 6+) removes them by a specific field. It translates to SELECT DISTINCT.

// First (or exception):
var first = await context.Products
    .OrderBy(p => p.Price)
    .FirstAsync();

// Single (exception if 0 or >1):
var unico = await context.Clients
    .SingleAsync(c => c.Email == email);

// "OrDefault" versions return null:
var maybe = await context.Products
    .FirstOrDefaultAsync(p => p.Id == 999);

The FirstAsync() throws if empty. The SingleAsync() throws if it is not exactly one. The OrDefault versions return null instead of throwing.

IQueryable<Product> query = context.Products;

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

if (precoMin > 0)
    query = query.Where(p => p.Price >= precoMin);

if (category != null)
    query = query.Where(p => p.Category == category);

var result = await query.ToListAsync();

Build queries dynamically with IQueryable. Each Where() is only applied if the filter exists. The final SQL only includes the active conditions.

int total = await context.Products.CountAsync();
decimal max = await context.Products.MaxAsync(p => p.Price);
decimal min = await context.Products.MinAsync(p => p.Price);
decimal sum = await context.Products.SumAsync(p => p.Price);
double average = await context.Products.AverageAsync(p => p.Price);

Asynchronous aggregation methods: CountAsync(), MaxAsync(), MinAsync(), SumAsync(), AverageAsync(). They generate aggregated SQL.

var groups = await context.Products
    .GroupBy(p => p.Category)
    .Select(g => new
    {
        Category = g.Key,
        Total = g.Count(),
        PrecoMedio = g.Average(p => p.Price)
    })
    .ToListAsync();

The GroupBy() groups and the Select() projects the aggregates. It generates GROUP BY with COUNT/AVG in the SQL.

var results = await context.Products
    .Where(p => p.Name.Contains("mouse"))
    .ToListAsync();

// StartsWith / EndsWith:
var porPrefixo = await context.Clients
    .Where(c => c.Name.StartsWith("Anna"))
    .ToListAsync();

// EF.Functions.Like:
var like = await context.Products
    .Where(p => EF.Functions.Like(p.Name, "%port%"))
    .ToListAsync();

The Contains(), StartsWith() and EndsWith() translate to LIKE. The EF.Functions.Like() gives full control of the pattern.

var clients = await context.Clients
    .FromSql($"SELECT * FROM Clients WHERE City = {city}")
    .ToListAsync();

// Compose with LINQ:
var ativos = await context.Clients
    .FromSql($"SELECT * FROM Clients")
    .Where(c => c.Active)
    .OrderBy(c => c.Name)
    .ToListAsync();

The FromSql() (interpolated, safe) runs raw SQL and lets you compose with LINQ. The FromSqlRaw() uses {0} placeholders for parameters.

public class Client
{
    public int Id { get; set; }
    public List<Order> Orders { get; set; } = new();
}

public class Order
{
    public int Id { get; set; }
    public int ClienteId { get; set; }  // FK
    public Client Client { get; set; }
}

The One-to-Many relationship uses an FK (ClienteId) on the "many" side. The navigation properties (Orders, Client) let you access the related ones.

var client = await context.Clients
    .Include(c => c.Orders)
        .ThenInclude(p => p.Items)
            .ThenInclude(i => i.Product)
    .FirstOrDefaultAsync(c => c.Id == 1);

The Include() loads relationships eagerly in a single query. The ThenInclude() chains sub-relationships. It avoids N+1 but can generate large JOINs.

var clients = await context.Clients
    .Include(c => c.Orders
        .Where(p => p.State == "pending")
        .OrderByDescending(p => p.Data)
        .Take(5))
    .ToListAsync();

The Filtered Include (EF Core 5+) applies Where(), OrderBy() and Take() inside the Include. It loads only the related ones that matter.

public class Post
{
    public int Id { get; set; }
    public int AutorId { get; set; }
    public int RevisorId { get; set; }

    [ForeignKey("AutorId")]
    public User Author { get; set; }

    [ForeignKey("RevisorId")]
    public User Revisor { get; set; }
}

When there are multiple relationships between the same entities, use [ForeignKey] or HasOne().WithMany().HasForeignKey() to disambiguate which FK belongs to which navigation.

public class Person
{
    public int Id { get; set; }
    public Passaporte Passaporte { get; set; }
}

public class Passaporte
{
    public int Id { get; set; }
    public int PessoaId { get; set; }
    public Person Person { get; set; }
}

// Fluent API:
e.HasOne(p => p.Passaporte)
    .WithOne(p => p.Person)
    .HasForeignKey<Passaporte>(p => p.PessoaId);

The One-to-One relationship uses HasOne().WithOne() and defines the FK with HasForeignKey<T>(). The FK goes in one of the tables.

var order = await context.Orders
    .Include(p => p.Client)
    .Include(p => p.Items)
        .ThenInclude(i => i.Product)
    .Include(p => p.Cupao)
    .FirstOrDefaultAsync(p => p.Id == id);

Multiple Include() load several relationships from the same level. Each one adds a JOIN or split query. Combine it with ThenInclude() for depth.

// FK only, without a navigation property:
public class Order
{
    public int Id { get; set; }
    public int ClienteId { get; set; }
}

// Config:
e.HasOne<Client>()
    .WithMany()
    .HasForeignKey(p => p.ClienteId);

You can have only the FK without a navigation property. The HasOne<T>().WithMany() without arguments defines the relationship. Useful to simplify the model.

// Manual join with Select:
var data = await context.Orders
    .Select(p => new
    {
        PedidoId = p.Id,
        ClienteNome = p.Client.Name,
        TotalItens = p.Items.Count
    })
    .ToListAsync();

Instead of Include(), project with Select() and access the navigations directly. It generates optimized JOINs and brings only what is needed.

public class Student
{
    public int Id { get; set; }
    public List<Course> Courses { get; set; } = new();
}

public class Course
{
    public int Id { get; set; }
    public List<Student> Students { get; set; } = new();
}
// EF Core 5+ creates the join table automatically

The Many-to-Many relationship (EF Core 5+) creates the join table implicitly. Just have a List<T> on both sides. No explicit FK.

var client = await context.Clients.FindAsync(1);

// Load a collection:
await context.Entry(client)
    .Collection(c => c.Orders)
    .LoadAsync();

// Load a reference:
await context.Entry(order)
    .Reference(p => p.Client)
    .LoadAsync();

The explicit loading uses Entry().Collection().LoadAsync() for collections and Reference().LoadAsync() for a single navigation. It loads on demand.

e.HasOne(p => p.Client)
    .WithMany(c => c.Orders)
    .HasForeignKey(p => p.ClienteId)
    .OnDelete(DeleteBehavior.Cascade);

// Options:
// Cascade    → deletes children
// Restrict   → prevents deletion
// SetNull    → FK becomes null
// ClientCascade → cascade on the client

The OnDelete() defines the behavior when deleting the parent. Cascade deletes children, Restrict prevents it, SetNull nulls the FK.

public class StudentCourse
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public DateTime EnrollmentDate { get; set; }
    public Student Student { get; set; }
    public Course Course { get; set; }
}

// Config:
e.HasOne(sc => sc.Student)
    .WithMany(s => s.StudentCourses)
    .HasForeignKey(sc => sc.StudentId);

When the join table has extra data (payload), create an explicit entity. Define the two HasOne().WithMany() relationships with the FKs.

// Install: Microsoft.EntityFrameworkCore.Proxies
options.UseLazyLoadingProxies();

// Properties must be virtual:
public class Client
{
    public virtual List<Order> Orders { get; set; }
}

// Automatic access:
var client = await context.Clients.FindAsync(1);
var orders = client.Orders; // automatic query

The Lazy Loading loads relationships automatically on access. It requires UseLazyLoadingProxies() and virtual properties. Beware of N+1.

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int? PaiId { get; set; }
    public Category Pai { get; set; }
    public List<Category> Filhos { get; set; } = new();
}

// Config:
e.HasOne(c => c.Pai)
    .WithMany(c => c.Filhos)
    .HasForeignKey(c => c.PaiId);

Self-referencing relationships link an entity to itself (trees, hierarchies). The FK is nullable for the root. Use HasOne().WithMany() on the same class.

dotnet tool install --global dotnet-ef
dotnet tool update --global dotnet-ef

# Verify:
dotnet ef --version

The dotnet-ef tools are global. Install them with dotnet tool install --global. Needed to create and apply migrations via the CLI.

dotnet ef migrations script

# From a specific migration:
dotnet ef migrations script Mig1 Mig2

# Idempotent (for production):
dotnet ef migrations script --idempotent -o deploy.sql

The migrations script generates SQL for manual execution in production. The --idempotent checks what has already been applied. The -o saves to a file.

protected override void Up(MigrationBuilder mb)
{
    mb.Sql(@"
        UPDATE Clients
        SET FullName = Name + ' ' + Surname
        WHERE FullName IS NULL
    ");

    mb.Sql("CREATE INDEX IX_Custom ON Clients(City)");
}

The mb.Sql() runs arbitrary SQL in the migration. Useful for data transformations, custom indexes or operations not supported by the builder.

dotnet ef migrations add InitialCreate
dotnet ef migrations add AdicionarCampoEmail
dotnet ef migrations add CriarTabelaPedidos

# With a separate project:
dotnet ef migrations add Name --project Core --startup-project Web

The migrations add compares the model with the snapshot and generates migration files. Each migration has an Up() and a Down().

modelBuilder.Entity<Country>().HasData(
    new Country { Id = 1, Name = "Portugal" },
    new Country { Id = 2, Name = "Brasil" },
    new Country { Id = 3, Name = "Angola" }
);

The HasData() inserts initial data via migration. It requires explicit IDs. The data is applied in the Up() and removed in the Down().

// Check in code:
var pending = await context.Database
    .GetPendingMigrationsAsync();

var applied = await context.Database
    .GetAppliedMigrationsAsync();

bool exists = await context.Database
    .CanConnectAsync();

The GetPendingMigrationsAsync() lists migrations to be applied. The GetAppliedMigrationsAsync() shows the applied ones. Useful for health checks and deploy.

dotnet ef database update
dotnet ef database update MigrationName

// In code (startup):
await context.Database.MigrupTosync();

The database update applies pending migrations. With a name, it applies up to a specific one. The MigrupTosync() applies them programmatically (useful in deploy).

public partial class CriarClientes : Migration
{
    protected override void Up(MigrationBuilder mb)
    {
        mb.CreateTable("Clients", table => new
        {
            Id = table.Column<int>(nullable: false)
                .Annotation("SqlServer:Identity", "1, 1"),
            Name = table.Column<string>(maxLength: 100),
            Email = table.Column<string>(nullable: true)
        });
    }

    protected override void Down(MigrationBuilder mb)
    {
        mb.DropTable("Clients");
    }
}

Each migration has Up() (apply) and Down() (revert). The MigrationBuilder offers CreateTable(), AddColumn(), DropTable(), etc.

// Delete the whole DB:
await context.Database.EnsureDeletedAsync();

// Recreate without migrations (model only):
await context.Database.EnsureCreatedAsync();

// With migrations:
await context.Database.MigrupTosync();

The EnsureDeleted() + MigrupTosync() recreates everything. The EnsureCreated() creates without migrations (not recommended in production). Use it in dev/tests.

# Revert DB to a previous migration:
dotnet ef database update NomeAnterior

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

# Revert everything:
dotnet ef database update 0

The database update NomeAnterior runs the Down(). The migrations remove deletes the file of the last unapplied migration. The update 0 reverts everything.

mb.AddColumn<string>("Clients", "Phone",
    maxLength: 20, nullable: true);

mb.DropColumn("Clients", "CampoAntigo");

mb.RenameColumn("Clients", "Name", "FullName");

mb.AlterColumn<decimal>("Products", "Price",
    precision: 18, scale: 2);

mb.CreateIndex("IX_Products_Name", "Products", "Name");

Available operations: AddColumn(), DropColumn(), RenameColumn(), AlterColumn(), CreateIndex(), AddForeignKey().

# If another dev's migration conflicts:
dotnet ef migrations remove
# Fix the model and recreate:
dotnet ef migrations add NomeCorrigido

# Never edit migrations already applied in production
# Always create a new migration

In a team, never edit migrations already applied. If it conflicts, remove it with migrations remove and recreate. The ModelSnapshot must always be in sync.

using var transaction =
    await context.Database.BeginTransactionAsync();
try
{
    context.Clients.Add(new);
    await context.SaveChangesAsync();

    context.Logs.Add(log);
    await context.SaveChangesAsync();

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

The BeginTransactionAsync() starts an explicit transaction. The CommitAsync() confirms and the RollbackAsync() reverts everything if something fails.

int afetados = await context.Database
    .ExecuteSqlRawAsync(
        "UPDATE Products SET Price = Price * 1.1 WHERE Category = {0}",
        category
    );

// Interpolated:
int n = await context.Database
    .ExecuteSql($"DELETE FROM Logs WHERE Data < {cutoff}");

The ExecuteSqlRawAsync() runs SQL without mapping entities. It returns the number of affected rows. Ideal for bulk updates and direct operations.

private static readonly Func<AppDbContext, int, Task<Client?>>
    _getCliente = EF.CompileAsyncQuery(
        (AppDbContext ctx, int id) =>
            ctx.Clients.FirstOrDefault(c => c.Id == id));

// Usage:
var client = await _getCliente(context, 1);

The Compiled Queries pre-compile the LINQ expression. They eliminate the parsing overhead in repeated queries. Useful in hot paths with thousands of executions.

bool exists = await context.Database.CanConnectAsync();
string name = context.Database.GetDbConnection().Database;

// Create/delete:
await context.Database.EnsureCreatedAsync();
await context.Database.EnsureDeletedAsync();

// Transaction:
await context.Database.BeginTransactionAsync();

The Database facade gives access to DB operations: CanConnectAsync(), EnsureCreatedAsync(), BeginTransactionAsync(), ExecuteSqlRawAsync().

using var scope = new TransactionScope(
    TransactionScopeAsyncFlowOption.Enabled);

await context1.SaveChangesAsync();
await context2.SaveChangesAsync();

scope.Complete();

The TransactionScope coordinates transactions across multiple contexts or resources. The Complete() commits. Without it, it rolls back automatically.

// Config (soft delete):
modelBuilder.Entity<Post>()
    .HasQueryFilter(p => !p.Eliminado);

// Multi-tenant:
modelBuilder.Entity<Product>()
    .HasQueryFilter(p => p.TenantId == _tenantId);

// Ignore the filter:
var all = context.Posts
    .IgnoreQueryFilters()
    .ToList();

The Global Query Filters apply automatic conditions to all queries. Ideal for soft delete and multi-tenancy. The IgnoreQueryFilters() disables it.

// Config:
e.ToTable("Clients", b => b.IsTemporal());

// Historical query:
var data = await context.Clients
    .TemporalAsOf(DateTime.Now.AddDays(-7))
    .Where(c => c.Id == 1)
    .ToListAsync();

// All versions:
var history = await context.Clients
    .TemporalAll()
    .Where(c => c.Id == 1)
    .ToListAsync();

The Temporal Tables (SQL Server) keep automatic history. The IsTemporal() enables it and TemporalAsOf()/TemporalAll() query past versions.

// Custom execution strategy:
options.UseSqlServer(conn, sqlOptions =>
{
    sqlOptions.ExecutionStrategy(d =>
        new SqlServerRetryingExecutionStrategy(
            d, maxRetryCount: 5,
            maxRetryDelay: TimeSpan.FromSeconds(30),
            errorNumbersToAdd: new List<int> { 4060 }
        ));
});

The ExecutionStrategy customizes resilience. The SqlServerRetryingExecutionStrategy retries on transient errors. Add custom error codes.

var clients = await context.Clients
    .FromSqlRaw(
        "SELECT * FROM Clients WHERE City = {0}",
        city
    ).ToListAsync();

// Interpolated (safer):
var result = await context.Clients
    .FromSql($"SELECT * FROM Clients WHERE Id = {id}")
    .ToListAsync();

The FromSqlRaw() uses {0} placeholders for parameters (prevents SQL injection). The interpolated FromSql() is more readable and equally safe.

try
{
    await context.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var dbValues = await entry.GetDatabaseValuesAsync();

    // Resolve the conflict:
    entry.OriginalValues.SetValues(dbValues);
    await context.SaveChangesAsync(); // retry
}

The optimistic concurrency detects conflicts via RowVersion. The catch of DbUpdateConcurrencyException lets you resolve: keep values, use the DB ones or merge.

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

public class DetalheJson
{
    public string Color { get; set; }
    public List<string> Tags { get; set; }
}

// Config:
e.OwnsOne(p => p.Detail, d => d.ToJson());

// Query:
var results = await context.Products
    .Where(p => p.Detail.Color == "blue")
    .ToListAsync();

The ToJson() (EF Core 7+) maps owned types the a JSON column. It allows queries inside the JSON. Supported in SQL Server and PostgreSQL.

var results = await context.Clients
    .FromSqlRaw("EXEC sp_GetClientes @p0", city)
    .ToListAsync();

// With return:
var total = await context.Database
    .ExecuteSqlRawAsync("EXEC sp_AtualizarStock @p0", produtoId);

The FromSqlRaw() calls stored procedures with EXEC. The ExecuteSqlRawAsync() runs without returning entities (INSERT/UPDATE/DELETE).

public class AuditInterceptor : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>>
        SavingChangesAsync(
            DbContextEventData data,
            InterceptionResult<int> result,
            CancellationToken ct = default)
    {
        var entries = data.Context!.ChangeTracker.Entries()
            .Where(e => e.State == EntityState.Modified);
        // record audit
        return base.SavingChangesAsync(data, result, ct);
    }
}

// Register:
options.AddInterceptors(new AuditInterceptor());

The Interceptors intercept EF events (save, query, connection). The SaveChangesInterceptor allows auditing, logging or validation before saving.

public override async Task<int> SaveChangesAsync(
    CancellationToken ct = default)
{
    foreach (var entry in ChangeTracker.Entries())
    {
        if (entry.State == EntityState.Added)
            entry.Property("CreatedAt").CurrentValue = DateTime.Now;
        if (entry.State == EntityState.Modified)
            entry.Property("ModifiedAt").CurrentValue = DateTime.Now;
    }
    return await base.SaveChangesAsync(ct);
}

Override SaveChangesAsync() for automatic auditing. Check the EntityState (Added/Modified) and set timestamps. A common pattern in production.

// BAD (N+1):
var orders = await context.Orders.ToListAsync();
foreach (var p in orders)
    Console.WriteLine(p.Client.Name); // query per order!

// GOOD:
var orders = await context.Orders
    .Include(p => p.Client)
    .ToListAsync();

The N+1 problem happens when you access navigations without Include. It generates 1 query + N queries. Solve it with Include() or a Select() projection.

// Single query (huge JOIN):
var data = await context.Clients
    .Include(c => c.Orders).ThenInclude(p => p.Items)
    .ToListAsync();

// Split query (multiple queries):
var data = await context.Clients
    .Include(c => c.Orders).ThenInclude(p => p.Items)
    .AsSplitQuery()
    .ToListAsync();

The AsSplitQuery() splits into multiple SQL queries. It avoids cartesian explosion (row duplication in multiple JOINs). Better with large collections.

var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseSqlite(connection)
    .Options;

using var context = new AppDbContext(options);
await context.Database.EnsureCreatedAsync();

// Test with real SQL (supports more features)

The SQLite in-memory is more faithful than InMemory (supports FKs, real SQL). Keep the connection open during the test. The EnsureCreatedAsync() creates the schema.

// Create indexes for frequent queries:
e.HasIndex(p => p.Email).IsUnique();
e.HasIndex(p => new { p.Category, p.Active });
e.HasIndex(p => p.CreatedAt).IsDescending();

// Check slow queries with:
options.LogTo(Console.WriteLine, LogLevel.Information);

Create indexes for columns used in WHERE, JOIN and ORDER BY. Composite ones for multiple filters. Monitor with LogTo() and optimize with Include/Select.

// BAD (loads everything):
var clients = await context.Clients.ToListAsync();
var names = clients.Select(c => c.Name);

// GOOD (only what is needed):
var names = await context.Clients
    .Select(c => c.Name)
    .ToListAsync();

Project with Select() to bring only the necessary columns. It reduces memory and network traffic. The generated SQL uses SELECT with specific fields.

public interface IRepository<T> where T : class
{
    Task<T?> GetByIdAsync(int id);
    Task<List<T>> GetAllAsync();
    Task AddAsync(T entity);
    void Remove(T entity);
    Task<int> SaveAsync();
}

public class Repository<T> : IRepository<T> where T : class
{
    private readonly AppDbContext _context;
    public Repository(AppDbContext context) => _context = context;

    public async Task<T?> GetByIdAsync(int id)
        => await _context.Set<T>().FindAsync(id);
}

The Repository pattern abstracts data access. It eases testing with mocks and decouples business logic from EF. The Set<T>() gives generic access.

// Never expose entities in the API:
public record ClienteDto(int Id, string Name, string Email);

// Map in the query:
var dtos = await context.Clients
    .Select(c => new ClienteDto(c.Id, c.Name, c.Email))
    .ToListAsync();

return Ok(dtos);

Never expose entities directly in the API (cycles, sensitive data). Use DTOs or records with Select(). Prevents over-posting and circular serialization.

// ✓ AsNoTracking() for reading
// ✓ Select() for projection (don't load everything)
// ✓ Include() to avoid N+1
// ✓ AsSplitQuery() with multiple Includes
// ✓ ExecuteUpdate/Delete for bulk (EF 7+)
// ✓ Async on all operations
// ✓ DTOs in the API (never entities)
// ✓ Migrations in source control
// ✓ Indexes for frequent queries
// ✓ DbContext with a short scope

Summary of the essential practices: AsNoTracking(), projection, Include(), async, DTOs, versioned migrations and indexes. Follow this for robust EF Core code.

// Reads that don't need an update:
var products = await context.Products
    .AsNoTracking()
    .Where(p => p.Active)
    .ToListAsync();

// Or per context:
context.ChangeTracker.QueryTrackingBehavior =
    QueryTrackingBehavior.NoTracking;

The AsNoTracking() removes the Change Tracker overhead in read queries. Less memory, faster. Use it whenever you won't modify the data.

public interface IUnitOfWork
{
    IRepository<Client> Clients { get; }
    IRepository<Product> Products { get; }
    Task<int> SaveAsync();
}

public class UnitOfWork : IUnitOfWork
{
    private readonly AppDbContext _context;
    public IRepository<Client> Clients { get; }
    public IRepository<Product> Products { get; }

    public async Task<int> SaveAsync()
        => await _context.SaveChangesAsync();
}

The Unit of Work coordinates multiple repositories in a single transaction. The SaveAsync() applies all changes atomically. It complements the Repository.

// ALWAYS async:
var clients = await context.Clients.ToListAsync();
var client = await context.Clients.FindAsync(1);
await context.SaveChangesAsync();

// NEVER .Result or .Wait():
// var c = context.Clients.First(); // blocks the thread!

Always use the Async methods (ToListAsync(), FindAsync(), SaveChangesAsync()). Never block with .Result or .Wait() — it causes deadlocks.

// BAD (loads + removes one by one):
var logs = await context.Logs
    .Where(l => l.Data < cutoff).ToListAsync();
context.Logs.RemoveRange(logs);
await context.SaveChangesAsync();

// GOOD (EF Core 7+):
await context.Logs
    .Where(l => l.Data < cutoff)
    .ExecuteDeleteAsync();

The ExecuteDeleteAsync()/ExecuteUpdupTosync() (EF Core 7+) operate in bulk without loading. A single SQL instead of thousands of operations.

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseInMemoryDatabase("TestDb_" + Guid.NewGuid())
    .Options;

using var context = new AppDbContext(options);
context.Clients.Add(new Client { Name = "Test" });
await context.SaveChangesAsync();

var client = await context.Clients.FirstAsync();
Assert.Equal("Test", client.Name);

The InMemory provider allows testing without a real DB. Each test uses a unique name for isolation. It doesn't support all features (e.g. transactions).

// Correct: scope per request (default DI)
builder.Services.AddDbContext<AppDbContext>(...);

// Do NOT use the Singleton:
// builder.Services.AddSingleton<AppDbContext>(); // ERROR!

// For background jobs, create a scope:
using var scope = app.Services.CreateScope();
var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

The DbContext is not thread-safe — use a scope per request. Never the a Singleton. In background jobs, create a manual scope with CreateScope().