IndianTechnoEra: Chapter 11: EF Core Quick Reference Cheatsheet

Welcome to the EF Core Quick Reference Cheatsheet! This chapter serves as a comprehensive, at-a-glance reference for all the essential EF Core concepts, commands, patterns, and code snippets. Bookmark this chapter for quick lookup during your development work.

---

11.1 Installation and Setup

NuGet Packages

# Core packages
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Tools

# Additional providers
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Pomelo.EntityFrameworkCore.MySql

# Additional features
dotnet add package Microsoft.EntityFrameworkCore.Proxies        # Lazy loading
dotnet add package Microsoft.EntityFrameworkCore.Relational     # Relational features
dotnet add package Microsoft.EntityFrameworkCore.Design          # Design-time features
dotnet add package Microsoft.EntityFrameworkCore.Analyzers       # EF Core analyzers

dotnet-ef Tool Commands

Command	Description	Example
Install Tool	Install EF Core CLI tool globally	dotnet tool install --global dotnet-ef
Update Tool	Update to latest version	dotnet tool update --global dotnet-ef
Add Migration	Create a new migration	dotnet ef migrations add InitialCreate
Remove Migration	Remove last migration (not applied)	dotnet ef migrations remove
Update Database	Apply all pending migrations	dotnet ef database update
Update to Specific Migration	Rollback/forward to specific migration	dotnet ef database update MigrationName
Generate Script	Generate SQL script from migrations	dotnet ef migrations script -o script.sql
List Migrations	Show all migrations	dotnet ef migrations list
Scaffold DbContext	Reverse engineer from database	dotnet ef dbcontext scaffold "connection" Microsoft.EntityFrameworkCore.SqlServer
Drop Database	Delete the database	dotnet ef database drop --force

Package Manager Console Commands (Visual Studio)

Add-Migration InitialCreate          # Create migration
Update-Database                      # Apply migrations
Update-Database -Migration InitialCreate  # Rollback
Remove-Migration                     # Remove last migration
Scaffold-DbContext "connection" Microsoft.EntityFrameworkCore.SqlServer
Get-Migration                        # List migrations
Drop-Database                        # Drop database

---

11.2 DbContext Configuration

Basic DbContext Setup

using Microsoft.EntityFrameworkCore;

public class AppDbContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionString");
        optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
        optionsBuilder.EnableSensitiveDataLogging();      // Dev only
        optionsBuilder.EnableDetailedErrors();             // Dev only
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Fluent API configurations
        base.OnModelCreating(modelBuilder);
    }
}

Connection String Formats

// SQL Server (Windows Auth)
"Server=localhost;Database=MyDb;Trusted_Connection=True;TrustServerCertificate=True;"

// SQL Server (SQL Auth)
"Server=localhost;Database=MyDb;User Id=sa;Password=YourPassword;TrustServerCertificate=True;"

// SQL Server LocalDB
"Server=(localdb)\\mssqllocaldb;Database=MyDb;Trusted_Connection=True;"

// SQLite
"Data Source=app.db"

// PostgreSQL
"Host=localhost;Database=MyDb;Username=postgres;Password=password"

// Azure SQL (Managed Identity)
"Server=tcp:server.database.windows.net;Authentication=Active Directory Managed Identity;Database=MyDb"

Service Registration (ASP.NET Core)

// Program.cs
builder.Services.AddDbContext<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
    options.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking); // Global no-tracking
});

// With pooling for high performance
builder.Services.AddDbContextPool<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
}, poolSize: 128);

// With factory for background services
builder.Services.AddDbContextFactory<AppDbContext>(options =>
{
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));
});

---

11.3 Entity Configuration

Data Annotations (Attributes)

Attribute	Purpose	Example
[Key]	Primary key	[Key] public int BookId { get; set; }
[Required]	NOT NULL column	[Required] public string Title { get; set; }
[MaxLength(n)]	Maximum string length	[MaxLength(200)] public string Title { get; set; }
[StringLength(n)]	String length with min	[StringLength(200, MinimumLength = 3)]
[Column(name, TypeName)]	Column mapping	[Column("BookTitle", TypeName = "varchar(200)")]
[Table(name, Schema)]	Table mapping	[Table("Books", Schema = "dbo")]
[ForeignKey(name)]	Foreign key property	[ForeignKey("AuthorId")] public Author Author { get; set; }
[InverseProperty(name)]	Specify inverse navigation	[InverseProperty("WrittenBooks")]
[NotMapped]	Exclude from database	[NotMapped] public string DisplayName => $"{FirstName} {LastName}";
[DatabaseGenerated]	Value generation	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[Timestamp]	Row version (concurrency)	[Timestamp] public byte[] RowVersion { get; set; }
[Index]	Create index (EF Core 5+)	[Index(nameof(ISBN), IsUnique = true)]
[EnumDataType]	Store enum as string	[EnumDataType(typeof(BookStatus))] public BookStatus Status { get; set; }

Fluent API Common Configurations

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Table
    modelBuilder.Entity<Book>()
        .ToTable("Books", "dbo");
    
    // Primary Key
    modelBuilder.Entity<Book>()
        .HasKey(b => b.Id);
    
    // Composite Primary Key
    modelBuilder.Entity<BookCategory>()
        .HasKey(bc => new { bc.BookId, bc.CategoryId });
    
    // Properties
    modelBuilder.Entity<Book>(b =>
    {
        b.Property(b => b.Title)
            .IsRequired()
            .HasMaxLength(200)
            .HasColumnName("BookTitle")
            .HasColumnType("nvarchar(200)")
            .HasDefaultValue("Untitled");
        
        b.Property(b => b.Price)
            .HasColumnType("decimal(10,2)")
            .HasDefaultValue(0);
        
        b.Property(b => b.CreatedAt)
            .HasDefaultValueSql("GETUTCDATE()");
        
        b.Property(b => b.IsDeleted)
            .HasDefaultValue(false);
        
        // Ignore property
        b.Ignore(b => b.TemporaryProperty);
        
        // Value conversion
        b.Property(b => b.Status)
            .HasConversion<string>();
        
        // Computed column
        b.Property(b => b.FullName)
            .HasComputedColumnSql("[FirstName] + ' ' + [LastName]");
    });
    
    // Indexes
    modelBuilder.Entity<Book>()
        .HasIndex(b => b.Title)
        .HasDatabaseName("IX_Books_Title");
    
    modelBuilder.Entity<Book>()
        .HasIndex(b => b.ISBN)
        .IsUnique();
    
    modelBuilder.Entity<Book>()
        .HasIndex(b => new { b.Title, b.Price })
        .HasFilter("[Price] > 0");
    
    // Shadow properties
    modelBuilder.Entity<Book>()
        .Property<DateTime>("LastUpdated")
        .HasDefaultValueSql("GETUTCDATE()");
    
    // Global query filters
    modelBuilder.Entity<Book>()
        .HasQueryFilter(b => !b.IsDeleted);
    
    modelBuilder.Entity<Book>()
        .HasQueryFilter(b => b.TenantId == _tenantId);
    
    // Concurrency token
    modelBuilder.Entity<Book>()
        .Property(b => b.RowVersion)
        .IsRowVersion();
}

---

11.4 Relationships Configuration

One-to-Many

// Fluent API
modelBuilder.Entity<Book>()
    .HasOne(b => b.Author)
    .WithMany(a => a.Books)
    .HasForeignKey(b => b.AuthorId)
    .OnDelete(DeleteBehavior.Cascade);

// With optional relationship (nullable FK)
modelBuilder.Entity<Book>()
    .HasOne(b => b.Author)
    .WithMany(a => a.Books)
    .HasForeignKey(b => b.AuthorId)
    .IsRequired(false)
    .OnDelete(DeleteBehavior.SetNull);

One-to-One

modelBuilder.Entity<Book>()
    .HasOne(b => b.BookDetail)
    .WithOne(bd => bd.Book)
    .HasForeignKey<BookDetail>(bd => bd.BookId);

Many-to-Many (EF Core 5+ - No Join Entity)

modelBuilder.Entity<Book>()
    .HasMany(b => b.Categories)
    .WithMany(c => c.Books)
    .UsingEntity<Dictionary<string, object>>(
        "BookCategories",
        j => j.HasOne<Category>().WithMany().HasForeignKey("CategoryId"),
        j => j.HasOne<Book>().WithMany().HasForeignKey("BookId"),
        j => j.HasKey("BookId", "CategoryId"));

Many-to-Many (With Join Entity)

public class BookCategory
{
    public int BookId { get; set; }
    public int CategoryId { get; set; }
    public DateTime AssignedDate { get; set; }
    public Book Book { get; set; }
    public Category Category { get; set; }
}

modelBuilder.Entity<BookCategory>(bc =>
{
    bc.HasKey(bc => new { bc.BookId, bc.CategoryId });
    
    bc.HasOne(bc => bc.Book)
        .WithMany(b => b.BookCategories)
        .HasForeignKey(bc => bc.BookId);
    
    bc.HasOne(bc => bc.Category)
        .WithMany(c => c.BookCategories)
        .HasForeignKey(bc => bc.CategoryId);
});

Self-Referencing

modelBuilder.Entity<Category>()
    .HasOne(c => c.ParentCategory)
    .WithMany(c => c.SubCategories)
    .HasForeignKey(c => c.ParentCategoryId)
    .OnDelete(DeleteBehavior.Restrict);

Delete Behaviors

Behavior	Description
Cascade	Dependent entities are also deleted
Restrict	No automatic action, delete fails if dependents exist
SetNull	Foreign key set to NULL (requires nullable FK)
ClientCascade	Cascade only for tracked entities
ClientSetNull	Set null only for tracked entities
NoAction	Same as Restrict

---

11.5 Querying Patterns

Basic Queries

// Get all
var allBooks = await db.Books.ToListAsync();

// Get by ID
var book = await db.Books.FindAsync(1);
var book2 = await db.Books.FirstOrDefaultAsync(b => b.Id == 1);

// Filtering
var cheapBooks = await db.Books
    .Where(b => b.Price < 20)
    .ToListAsync();

// Multiple conditions
var fantasyBooks = await db.Books
    .Where(b => b.Genre == "Fantasy" && b.Price > 15)
    .ToListAsync();

// OR condition
var books = await db.Books
    .Where(b => b.Genre == "Fantasy" || b.Genre == "SciFi")
    .ToListAsync();

// Contains (IN clause)
var genres = new[] { "Fantasy", "SciFi" };
var genreBooks = await db.Books
    .Where(b => genres.Contains(b.Genre))
    .ToListAsync();

// String operations
var startsWith = await db.Books
    .Where(b => b.Title.StartsWith("The"))
    .ToListAsync();

var contains = await db.Books
    .Where(b => b.Title.Contains("Harry"))
    .ToListAsync();

// Null checks
var booksWithReviews = await db.Books
    .Where(b => b.Reviews.Any())
    .ToListAsync();

Sorting and Pagination

// Sorting
var sorted = await db.Books
    .OrderBy(b => b.Title)
    .ThenByDescending(b => b.Price)
    .ToListAsync();

// Pagination
int pageNumber = 2;
int pageSize = 20;
var paged = await db.Books
    .OrderBy(b => b.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToListAsync();

// Keyset pagination (for large datasets)
var lastId = 100;
var nextPage = await db.Books
    .Where(b => b.Id > lastId)
    .OrderBy(b => b.Id)
    .Take(pageSize)
    .ToListAsync();

Projection

// Select specific columns
var titles = await db.Books
    .Select(b => b.Title)
    .ToListAsync();

// Anonymous object
var bookInfos = await db.Books
    .Select(b => new
    {
        b.Title,
        b.Price,
        AuthorName = b.Author.FirstName + " " + b.Author.LastName
    })
    .ToListAsync();

// DTO
var dtos = await db.Books
    .Select(b => new BookDto
    {
        Id = b.Id,
        Title = b.Title,
        Price = b.Price
    })
    .ToListAsync();

Aggregations

// Count
int count = await db.Books.CountAsync();
int fantasyCount = await db.Books.CountAsync(b => b.Genre == "Fantasy");

// Any (exists check)
bool hasExpensive = await db.Books.AnyAsync(b => b.Price > 100);

// Min, Max, Average
decimal minPrice = await db.Books.MinAsync(b => b.Price);
decimal maxPrice = await db.Books.MaxAsync(b => b.Price);
double avgPrice = await db.Books.AverageAsync(b => (double)b.Price);

// Sum
decimal totalValue = await db.Books.SumAsync(b => b.Price);

Grouping

var byGenre = await db.Books
    .GroupBy(b => b.Genre)
    .Select(g => new
    {
        Genre = g.Key,
        Count = g.Count(),
        AveragePrice = g.Average(b => b.Price),
        Books = g.Select(b => b.Title).ToList()
    })
    .ToListAsync();

Include Related Data

// Single include
var books = await db.Books
    .Include(b => b.Author)
    .ToListAsync();

// Multiple includes
var books2 = await db.Books
    .Include(b => b.Author)
    .Include(b => b.Categories)
    .Include(b => b.Reviews)
    .ToListAsync();

// Nested includes
var authors = await db.Authors
    .Include(a => a.Books)
        .ThenInclude(b => b.Reviews)
    .ToListAsync();

// Filtered include (EF Core 5+)
var authorsWithRecent = await db.Authors
    .Include(a => a.Books
        .Where(b => b.PublishedOn > new DateTime(2020, 1, 1))
        .OrderBy(b => b.Title)
        .Take(5))
    .ToListAsync();

// Split queries (avoid Cartesian explosion)
var booksSplit = await db.Books
    .Include(b => b.Categories)
    .Include(b => b.Reviews)
    .AsSplitQuery()
    .ToListAsync();

No-Tracking Queries

// Single query
var books = await db.Books
    .AsNoTracking()
    .ToListAsync();

// With identity resolution
var books2 = await db.Books
    .AsNoTrackingWithIdentityResolution()
    .Include(b => b.Author)
    .ToListAsync();

// Global setting
optionsBuilder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking);

Raw SQL

// FromSqlRaw (with parameters)
var books = await db.Books
    .FromSqlRaw("SELECT * FROM Books WHERE Price > {0}", 20)
    .Include(b => b.Author)
    .ToListAsync();

// FromSqlInterpolated (safe)
var minPrice = 20;
var books2 = await db.Books
    .FromSqlInterpolated($"SELECT * FROM Books WHERE Price > {minPrice}")
    .ToListAsync();

// Execute non-query
int updated = await db.Database.ExecuteSqlRawAsync(
    "UPDATE Books SET Price = Price * 1.1 WHERE Genre = 'Fantasy'");

// Execute scalar
var avgPrice = await db.Database
    .SqlQueryRaw<decimal>("SELECT AVG(Price) FROM Books")
    .FirstOrDefaultAsync();

---

11.6 CRUD Operations

Create (Insert)

// Single entity
var book = new Book { Title = "New Book", Price = 29.99m };
db.Books.Add(book);
await db.SaveChangesAsync();

// Multiple entities
var books = new List<Book>
{
    new Book { Title = "Book 1", Price = 10 },
    new Book { Title = "Book 2", Price = 20 }
};
db.Books.AddRange(books);
await db.SaveChangesAsync();

// With relationships
var author = new Author { FirstName = "John", LastName = "Doe" };
var book = new Book { Title = "New Book", Author = author };
db.Books.Add(book);
await db.SaveChangesAsync();

Read (Select)

// By ID (uses cache)
var book = await db.Books.FindAsync(1);

// First or default
var book2 = await db.Books.FirstOrDefaultAsync(b => b.Id == 1);

// Single (throws if not found or multiple)
var book3 = await db.Books.SingleAsync(b => b.Id == 1);

// Existence check
bool exists = await db.Books.AnyAsync(b => b.Id == 1);

Update

// Tracked entity (loaded from same context)
var book = await db.Books.FindAsync(1);
book.Price = 39.99m;
await db.SaveChangesAsync();

// Disconnected entity
var book = new Book { Id = 1, Title = "Updated Title", Price = 39.99m };
db.Books.Update(book);  // Updates ALL properties
await db.SaveChangesAsync();

// Partial update (better)
var existing = await db.Books.FindAsync(1);
existing.Title = "Updated Title";
await db.SaveChangesAsync();

// Update specific properties
var book = new Book { Id = 1 };
db.Books.Attach(book);
book.Title = "Updated Title";
db.Entry(book).Property(b => b.Title).IsModified = true;
await db.SaveChangesAsync();

// Bulk update (EF Core 7+)
await db.Books
    .Where(b => b.Genre == "Fantasy")
    .ExecuteUpdateAsync(s => s
        .SetProperty(b => b.Price, b => b.Price * 1.1m)
        .SetProperty(b => b.LastUpdated, DateTime.UtcNow));

Delete

// Tracked entity
var book = await db.Books.FindAsync(1);
db.Books.Remove(book);
await db.SaveChangesAsync();

// Disconnected entity
var book = new Book { Id = 1 };
db.Books.Remove(book);
await db.SaveChangesAsync();

// Multiple entities
var books = await db.Books.Where(b => b.Price < 10).ToListAsync();
db.Books.RemoveRange(books);
await db.SaveChangesAsync();

// Bulk delete (EF Core 7+)
await db.Books
    .Where(b => b.Price < 10)
    .ExecuteDeleteAsync();

Upsert (Insert or Update)

// Using raw SQL MERGE
await db.Database.ExecuteSqlInterpolatedAsync($@"
    MERGE INTO Books AS Target
    USING (SELECT {book.Id} AS Id, {book.Title} AS Title, {book.Price} AS Price) AS Source
    ON Target.Id = Source.Id
    WHEN MATCHED THEN
        UPDATE SET Title = Source.Title, Price = Source.Price
    WHEN NOT MATCHED THEN
        INSERT (Title, Price) VALUES (Source.Title, Source.Price);");

---

11.7 Change Tracker

Entity States

// Check state
var state = db.Entry(book).State;

// Set state
db.Entry(book).State = EntityState.Added;
db.Entry(book).State = EntityState.Modified;
db.Entry(book).State = EntityState.Deleted;
db.Entry(book).State = EntityState.Unchanged;
db.Entry(book).State = EntityState.Detached;

// Get all tracked entities
var entries = db.ChangeTracker.Entries().ToList();

// Get modified entities
var modified = db.ChangeTracker.Entries()
    .Where(e => e.State == EntityState.Modified)
    .ToList();

// Get original values
var original = db.Entry(book).OriginalValues;

// Get current values
var current = db.Entry(book).CurrentValues;

// Get database values
var dbValues = db.Entry(book).GetDatabaseValues();

// Reload from database
await db.Entry(book).ReloadAsync();

// Detect changes manually
db.ChangeTracker.DetectChanges();

// Has changes
bool hasChanges = db.ChangeTracker.HasChanges();

// Accept all changes
db.ChangeTracker.AcceptAllChanges();

// Clear change tracker
db.ChangeTracker.Clear();

Property Change Tracking

// Check if property is modified
bool isModified = db.Entry(book).Property(b => b.Title).IsModified;

// Mark property as modified
db.Entry(book).Property(b => b.Title).IsModified = true;

// Get original value
var original = db.Entry(book).Property(b => b.Title).OriginalValue;

// Get current value
var current = db.Entry(book).Property(b => b.Title).CurrentValue;

---

11.8 Transactions

Implicit Transaction (Default)

// Each SaveChanges is a transaction
db.Books.Add(book1);
db.Books.Add(book2);
await db.SaveChangesAsync(); // One transaction for both inserts

Explicit Transaction

using var transaction = await db.Database.BeginTransactionAsync();

try
{
    db.Books.Add(book1);
    await db.SaveChangesAsync();
    
    db.Authors.Add(author);
    await db.SaveChangesAsync();
    
    await transaction.CommitAsync();
}
catch
{
    await transaction.RollbackAsync();
    throw;
}

Transaction with Isolation Level

using var transaction = await db.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted);

// or
using var transaction = await db.Database.BeginTransactionAsync(
    IsolationLevel.Serializable);

Savepoints

using var transaction = await db.Database.BeginTransactionAsync();

await transaction.CreateSavepointAsync("BeforeAdd");

try
{
    db.Books.Add(book);
    await db.SaveChangesAsync();
}
catch
{
    await transaction.RollbackToSavepointAsync("BeforeAdd");
}

---

11.9 Concurrency Control

Row Version (Timestamp)

// Entity
public class Book
{
    public int Id { get; set; }
    public string Title { get; set; }
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

// Handle concurrency
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateConcurrencyException ex)
{
    var entry = ex.Entries.Single();
    var databaseValues = await entry.GetDatabaseValuesAsync();
    
    if (databaseValues == null)
    {
        // Record was deleted
    }
    else
    {
        var dbBook = (Book)databaseValues.ToObject();
        
        // Client wins
        entry.OriginalValues.SetValues(databaseValues);
        await db.SaveChangesAsync();
        
        // Or database wins
        entry.Reload();
        
        // Or merge
        // entry.CurrentValues.SetValues(databaseValues);
    }
}

Concurrency Check Attribute

public class Book
{
    public int Id { get; set; }
    
    [ConcurrencyCheck]
    public string Title { get; set; }
}

---

11.10 Value Conversions

Enum to String

modelBuilder.Entity<Book>()
    .Property(b => b.Status)
    .HasConversion<string>();

// Or explicit
modelBuilder.Entity<Book>()
    .Property(b => b.Status)
    .HasConversion(
        v => v.ToString(),
        v => (BookStatus)Enum.Parse(typeof(BookStatus), v));

List to JSON String

modelBuilder.Entity<Book>()
    .Property(b => b.Tags)
    .HasConversion(
        v => JsonSerializer.Serialize(v),
        v => JsonSerializer.Deserialize<List<string>>(v));

DateTime to UTC

modelBuilder.Entity<Book>()
    .Property(b => b.CreatedAt)
    .HasConversion(
        v => v.ToUniversalTime(),
        v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

---

11.11 Owned Types

// Value object
public class Address
{
    public string Street { get; set; }
    public string City { get; set; }
    public string ZipCode { get; set; }
}

// Configuration
modelBuilder.Entity<Customer>(c =>
{
    c.OwnsOne(c => c.ShippingAddress, a =>
    {
        a.Property(p => p.Street).HasColumnName("ShippingStreet");
        a.Property(p => p.City).HasColumnName("ShippingCity");
        a.Property(p => p.ZipCode).HasColumnName("ShippingZip");
    });
    
    c.OwnsOne(c => c.BillingAddress, a =>
    {
        a.Property(p => p.Street).HasColumnName("BillingStreet");
        a.Property(p => p.City).HasColumnName("BillingCity");
        a.Property(p => p.ZipCode).HasColumnName("BillingZip");
        a.ToTable("BillingAddresses"); // Separate table
    });
    
    c.OwnsMany(c => c.Addresses, a =>
    {
        a.ToTable("CustomerAddresses");
        a.WithOwner().HasForeignKey("CustomerId");
        a.HasKey("Id");
    });
});

---

11.12 Inheritance Mapping

TPH (Table Per Hierarchy - Default)

modelBuilder.Entity<ContentItem>()
    .HasDiscriminator<string>("ContentType")
    .HasValue<Article>("Article")
    .HasValue<Video>("Video");

TPT (Table Per Type)

modelBuilder.Entity<ContentItem>().ToTable("ContentItems");
modelBuilder.Entity<Article>().ToTable("Articles");
modelBuilder.Entity<Video>().ToTable("Videos");

TPC (Table Per Concrete Class) - EF Core 7+

modelBuilder.Entity<Article>()
    .ToTable("Articles")
    .UseTpcMappingStrategy();

modelBuilder.Entity<Video>()
    .ToTable("Videos")
    .UseTpcMappingStrategy();

---

11.13 JSON Columns (EF Core 7+)

// Owned type as JSON
modelBuilder.Entity<Blog>()
    .OwnsOne(b => b.Metadata, m =>
    {
        m.ToJson();
        m.OwnsOne(x => x.SocialLinks);
    });

// Collection as JSON
modelBuilder.Entity<Blog>()
    .Property(b => b.Tags)
    .HasColumnType("nvarchar(max)");

// Query JSON
var blogs = await db.Blogs
    .Where(b => b.Metadata.Theme == "Dark")
    .ToListAsync();

var blogsWithTag = await db.Blogs
    .Where(b => b.Tags.Contains("EF Core"))
    .ToListAsync();

---

11.14 Spatial Data

// Install: dotnet add package Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite
// Install: dotnet add package NetTopologySuite

using NetTopologySuite.Geometries;

public class Store
{
    public int Id { get; set; }
    public string Name { get; set; }
    public Point Location { get; set; }
}

// Configuration
optionsBuilder.UseSqlServer(connectionString, 
    x => x.UseNetTopologySuite());

// Queries
var myLocation = new Point(-73.9857, 40.7484) { SRID = 4326 };
var nearby = await db.Stores
    .Where(s => s.Location.IsWithinDistance(myLocation, 5000))
    .ToListAsync();

---

11.15 Performance Optimization Cheatsheet

Technique	Code	When to Use
AsNoTracking	.AsNoTracking()	Read-only queries
Projection	.Select(b => new { b.Title })	When you don't need all columns
Split Queries	.AsSplitQuery()	When including multiple collections
Compiled Queries	EF.CompileQuery() vs	Frequently executed parameterized queries vs \)+
Bulk Delete	.ExecuteDelete()	Deleting many records
Bulk Update	.ExecuteUpdate()	Updating many records
AddRange	.AddRange()	Inserting many records
Find (vs FirstOrDefault)	.Find(id)	Single entity by key (uses cache)
Any (vs Count)	.Any()	Check existence
Streaming	.AsAsyncEnumerable()	Processing large result sets

---

11.16 Common Patterns and Anti-Patterns

✅ DO: Use Async Methods

await db.Books.ToListAsync();      // Good
db.Books.ToList();                  // Bad (blocks thread)

✅ DO: Use Any() for Existence

bool exists = await db.Books.AnyAsync(b => b.Id == 1);  // Good
bool exists = await db.Books.CountAsync(b => b.Id == 1) > 0; // Bad

✅ DO: Filter at Database Level

var books = await db.Books.Where(b => b.Price > 20).ToListAsync();  // Good
var books = db.Books.ToList().Where(b => b.Price > 20).ToList();    // Bad

❌ DON'T: N+1 Queries

// Bad
var books = await db.Books.ToListAsync();
foreach (var book in books)
{
    Console.WriteLine(book.Author.Name); // Each iteration queries
}

// Good
var books = await db.Books.Include(b => b.Author).ToListAsync();

❌ DON'T: Multiple SaveChanges in Loops

// Bad
foreach (var book in books)
{
    db.Books.Add(book);
    await db.SaveChangesAsync(); // Separate transaction each time
}

// Good
db.Books.AddRange(books);
await db.SaveChangesAsync(); // One transaction

❌ DON'T: Hold DbContext Too Long

// Bad - long-running context in web app
public class MyService
{
    private readonly AppDbContext _context; // Singleton lifetime
}

// Good - short-lived context
public class MyService
{
    private readonly IDbContextFactory<AppDbContext> _factory;
    
    public async Task DoWork()
    {
        await using var context = await _factory.CreateDbContextAsync();
        // Use context...
    }
}

---

11.17 Logging and Debugging

// Enable logging in DbContext
optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);

// Enable sensitive data logging (dev only)
optionsBuilder.EnableSensitiveDataLogging();

// Enable detailed errors
optionsBuilder.EnableDetailedErrors();

// Get generated SQL
var query = db.Books.Where(b => b.Price > 20);
string sql = query.ToQueryString();  // EF Core 5+
Console.WriteLine(sql);

// Query tags
var books = await db.Books
    .TagWith("Get cheap books for homepage")
    .Where(b => b.Price < 15)
    .ToListAsync();

---

11.18 Testing Utilities

In-Memory Database

var options = new DbContextOptionsBuilder<AppDbContext>()
    .UseInMemoryDatabase(databaseName: Guid.NewGuid().ToString())
    .Options;

var context = new AppDbContext(options);

SQLite In-Memory

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

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

var context = new AppDbContext(options);
context.Database.EnsureCreated();

Test Database Builder

public static class TestDatabaseBuilder
{
    public static async Task<AppDbContext> CreateTestContextAsync()
    {
        var connection = new SqliteConnection("DataSource=:memory:");
        await connection.OpenAsync();
        
        var options = new DbContextOptionsBuilder<AppDbContext>()
            .UseSqlite(connection)
            .Options;
        
        var context = new AppDbContext(options);
        await context.Database.EnsureCreatedAsync();
        
        return context;
    }
}

---

11.19 Connection Resiliency

// Configure retry
optionsBuilder.UseSqlServer(connectionString, options =>
{
    options.EnableRetryOnFailure(
        maxRetryCount: 3,
        maxRetryDelay: TimeSpan.FromSeconds(30),
        errorNumbersToAdd: null);
    options.CommandTimeout(60);
});

// Custom execution strategy
var strategy = db.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async () =>
{
    using var transaction = await db.Database.BeginTransactionAsync();
    // Operations...
    await transaction.CommitAsync();
});

---

11.20 Health Checks

// Basic health check
builder.Services.AddHealthChecks()
    .AddDbContextCheck<AppDbContext>("database");

// Custom health check
public class DbHealthCheck : IHealthCheck
{
    public async Task<HealthCheckResult> CheckHealthAsync(
        HealthCheckContext context,
        CancellationToken cancellationToken)
    {
        try
        {
            await _dbContext.Database.ExecuteSqlRawAsync("SELECT 1", cancellationToken);
            return HealthCheckResult.Healthy();
        }
        catch (Exception ex)
        {
            return HealthCheckResult.Unhealthy("Database unavailable", ex);
        }
    }
}

---

Quick Command Reference

Task	Command
Create migration	dotnet ef migrations add MigrationName
Apply migrations	dotnet ef database update
Remove last migration	dotnet ef migrations remove
Generate SQL script	dotnet ef migrations script -o script.sql
List migrations	dotnet ef migrations list
Drop database	dotnet ef database drop --force
Scaffold from DB	dotnet ef dbcontext scaffold "connection" Microsoft.EntityFrameworkCore.SqlServer
Update tool	dotnet tool update --global dotnet-ef

---

Conclusion

This cheatsheet covers the essential EF Core patterns, commands, and code snippets you'll use daily. Keep it handy as a quick reference while building your applications.

Pro Tips:

• Always use await with async methods
• Use AsNoTracking() for read-only queries
• Create indexes on foreign keys and filtered columns
• Use ExecuteDelete() and ExecuteUpdate() for bulk operations
• Test migrations on staging before production
• Monitor slow queries with logging
• Configure connection resiliency for cloud deployments

---

Would you like me to:

• Add more specific sections to this cheatsheet?
• Create a downloadable PDF version?
• Provide additional examples for any of the covered topics?