Are you sure you’re using EF Core right? Most teams ship with three silent bugs on day one: N+1 queries, wrong DbContext lifetime, and accidental full‑table updates. Let’s fix that. In this practical intro I’ll show you how to set up EF Core the sane way, avoid the traps I’ve seen in real projects, and get to clean, fast data access without memorizing every API.
What EF Core Actually Is (and Isn’t)
Entity Framework Core (EF Core) is an object–relational mapper for .NET that lets you write strongly typed C# instead of hand‑rolling SQL for every operation. Under the hood it translates LINQ into SQL for your provider (SQL Server, PostgreSQL, MySQL/MariaDB, SQLite, Cosmos DB, etc.), tracks entity changes, and coordinates transactions.
It is not a magic box that eliminates the need to understand SQL, indexes, or transactions. Think of EF Core as a power drill: it speeds you up, but you still aim.
Why pick EF Core over other ORMs
- First‑class .NET integration. Works naturally with ASP.NET Core DI, logging, configuration, and minimal APIs.
- Cross‑platform. Same code on Windows, Linux, macOS and in containers.
- Performance when used correctly. Compiled queries, batching, and no‑tracking queries can be very fast.
- Migrations. Evolve your schema in source control instead of “run-this-sql-please.txt”.
- Providers. Swap SQL Server for PostgreSQL or SQLite with minimal code changes.
Rule of thumb: if your queries are simple-to-moderate and your domain is relational, EF Core is a great default. If you live on hand‑tuned window functions every day, mix raw SQL where it makes sense – EF Core plays well with that too.
Architecture at a Glance
Here’s the moving parts you’ll touch daily:
┌────────────┐ LINQ ┌───────────────┐ SQL ┌───────────────┐
│ Code ├────────────────────▶│ EF Core ├────────────────▶│ Database │
│ │ │ │ │ (Provider) │
└─────┬──────┘ └───────┬───────┘ └────────┬──────┘
│ │ │
│ Change Tracking Transactions/Migrations
▼
DbContext ── DbSet<TEntity> ── Model (conventions + Data Annotations + Fluent API)
DbContext: your unit of work
public sealed class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
public DbSet<Student> Students => Set<Student>();
public DbSet<Course> Courses => Set<Course>();
protected override void OnModelCreating(ModelBuilder model)
{
model.Entity<Student>(e =>
{
e.HasKey(x => x.Id);
e.Property(x => x.Name).IsRequired().HasMaxLength(100);
e.OwnsOne(x => x.Profile, owned =>
{
owned.Property(p => p.Email).HasMaxLength(200);
owned.Property(p => p.BirthDate);
});
e.HasQueryFilter(s => !s.IsDeleted); // global soft-delete
});
model.Entity<Course>(e =>
{
e.HasKey(x => x.Id);
e.Property(x => x.Title).IsRequired().HasMaxLength(120);
});
model.Entity<StudentCourse>().HasKey(sc => new { sc.StudentId, sc.CourseId });
}
}Lifetime: In ASP.NET Core register DbContext as scoped (one per web request). Transient leads to too many connections; singleton is a memory/time bomb.
DbSet<TEntity>: your table gateway
DbSet is the entry point for querying and persisting entities.
// Query
var active = await db.Students.Where(s => s.IsActive).OrderBy(s => s.Name).ToListAsync();
// Create
db.Students.Add(new Student { Name = "Ava", IsActive = true });
await db.SaveChangesAsync();Entities & the Model: annotations vs Fluent API
public sealed class Student
{
public int Id { get; init; }
[Required, MaxLength(100)]
public string Name { get; set; } = string.Empty;
public bool IsActive { get; set; }
public bool IsDeleted { get; set; }
public StudentProfile Profile { get; set; } = new(); // owned value object
public List<StudentCourse> Courses { get; set; } = new();
}
public sealed class StudentProfile // owned type (no separate table by default)
{
public string? Email { get; set; }
public DateOnly? BirthDate { get; set; }
}
public sealed class Course
{
public int Id { get; init; }
public string Title { get; set; } = string.Empty;
public List<StudentCourse> Students { get; set; } = new();
}
public sealed class StudentCourse
{
public int StudentId { get; set; }
public int CourseId { get; set; }
public DateTime EnrolledAtUtc { get; set; }
}Tip: Prefer Fluent API for “shape of the database” (keys, indexes, relationships), and data annotations for simple field‑level rules.
Query pipeline: LINQ → SQL
LINQ queries are deferred until you enumerate (ToList/FirstOrDefault/Any). EF Core tries to translate everything to SQL; if it can’t, it may throw or (older versions) attempt client evaluation.
var top10 = await db.Students
.Where(s => s.IsActive)
.OrderByDescending(s => s.Id)
.Select(s => new { s.Id, s.Name }) // project only what you need
.Take(10)
.ToListAsync();Change tracking: states & performance
Every tracked entity has a state (Added/Unchanged/Modified/Deleted/Detached). Tracking costs memory/CPU; skip it for read‑only operations.
// Best for read-heavy endpoints
var list = await db.Students.AsNoTracking().ToListAsync();
// Update without reloading
db.Attach(student); // state = Unchanged
student.Name = "Renamed"; // EF marks the property Modified
await db.SaveChangesAsync();Migrations: evolve the schema safely
CLI (cross‑platform):
# install once
dotnet tool install --global dotnet-ef
# add/update migrations
dotnet ef migrations add InitialCreate
dotnet ef database update
# generate idempotent script for CI/CD
dotnet ef migrations script --idempotent -o migrate.sqlData seeding lives in OnModelCreating and is applied during migrations:
model.Entity<Student>().HasData(new Student { Id = 1, Name = "Admin", IsActive = true });Providers: know your quirks
- SQL Server:
DateOnly/TimeOnlyneed value converters pre‑.NET 9, collations are case‑insensitive by default. - PostgreSQL (Npgsql): rich JSON (via
jsonb), arrays, case‑sensitive identifiers unless quoted. - SQLite: great for tests; limited type system, watch out for concurrency tokens.
Hands‑On: Build a Mini CRUD in 10 Minutes
We’ll wire a minimal API with EF Core and show create/read/update/delete plus a relationship.
// Program.cs (ASP.NET Core minimal API)
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<AppDbContext>(opt =>
opt.UseSqlite(builder.Configuration.GetConnectionString("db") ?? "Data Source=app.db"));
var app = builder.Build();
app.MapPost("/students", async (AppDbContext db, Student s) =>
{
db.Students.Add(s);
await db.SaveChangesAsync();
return Results.Created($"/students/{s.Id}", s);
});
app.MapGet("/students", async (AppDbContext db) =>
await db.Students.AsNoTracking().OrderBy(s => s.Name).ToListAsync());
app.MapPut("/students/{id:int}", async (int id, AppDbContext db, Student input) =>
{
var s = await db.Students.FindAsync(id);
if (s is null) return Results.NotFound();
s.Name = input.Name; s.IsActive = input.IsActive; s.Profile = input.Profile;
await db.SaveChangesAsync();
return Results.NoContent();
});
app.MapDelete("/students/{id:int}", async (int id, AppDbContext db) =>
{
var s = await db.Students.FindAsync(id);
if (s is null) return Results.NotFound();
s.IsDeleted = true; // soft delete thanks to global filter
await db.SaveChangesAsync();
return Results.NoContent();
});
app.Run();Querying with relationships
var studentWithCourses = await db.Students
.Include(s => s.Courses)
.ThenInclude(sc => sc.Course)
.SingleAsync(s => s.Id == id);Prevent the N+1 problem
Prefer explicit Include/ThenInclude or targeted projections:
var roster = await db.Courses
.Where(c => c.Id == id)
.Select(c => new {
c.Title,
Students = c.Students.Select(sc => new { sc.StudentId, sc.Student!.Name })
})
.SingleAsync();Best Practices (Battle‑Tested)
- Context lifetime: Scoped per request in web apps; create short‑lived contexts in workers.
- No‑tracking by default for reads. Enable tracking only when you plan to update entities.
- Project what you need. Use
.Selectto avoid materializing heavy graphs. - Split vs single queries. For large includes, try
AsSplitQuery()to avoid giant JOINs. - Compiled queries for hot paths. Cache the expression and remove per‑request overhead.
- Handle concurrency. Add a
RowVersion/xmincolumn and catchDbUpdateConcurrencyException. - Transactions: Use
await using var tx = await db.Database.BeginTransactionAsync();for multi‑step operations. - Logging: Hook
ILoggerFactoryorLogToto see generated SQL during development. - Validation: Validate DTOs with FluentValidation and keep EF entities lean.
- Migrations discipline: One migration per feature; script idempotently for prod.
Performance Quick Wins
Compiled Queries
static readonly Func<AppDbContext, bool, IAsyncEnumerable<Student>> GetStudentsCompiled =
EF.CompileAsyncQuery((AppDbContext db, bool onlyActive) =>
db.Students.Where(s => onlyActive ? s.IsActive : true).OrderBy(s => s.Name));
await foreach (var s in GetStudentsCompiled(db, onlyActive: true))
{
// stream results
}No‑tracking with identity resolution
var list = await db.Students
.AsNoTracking()
.AsNoTrackingWithIdentityResolution() // avoids duplicates across includes
.Include(s => s.Courses)
.ToListAsync();Interceptors (e.g., timing queries)
public sealed class TimingInterceptor : DbCommandInterceptor
{
public override InterceptionResult<DbDataReader> ReaderExecuting(
DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
{
var sw = Stopwatch.StartNew();
var r = base.ReaderExecuting(command, eventData, result);
sw.Stop();
Console.WriteLine($"SQL took {sw.ElapsedMilliseconds} ms\n{command.CommandText}");
return r;
}
}Register via options.AddInterceptors(new TimingInterceptor()) while debugging hot paths.
Testing EF Core Without Pain
Use SQLite in‑memory (recommended)
var builder = new DbContextOptionsBuilder<AppDbContext>()
.UseSqlite("Filename=:memory:")
.EnableSensitiveDataLogging();
await using var conn = new SqliteConnection("Filename=:memory:");
await conn.OpenAsync();
using var db = new AppDbContext(builder.Options);
await db.Database.EnsureCreatedAsync();Pros: real SQL, constraints, and query translation.
EF InMemory provider (use sparingly)
Great for unit tests focused on business logic, but it does not behave like a relational database (no constraints/joins). Prefer SQLite for integration‑style tests.
Common Pitfalls & How to Avoid Them
- Accidental full‑table update. Calling
db.Update(dto)marks all properties as modified. PreferAttach+ set specific properties or useEntry(entity).Property(x => x.Name).IsModified = true. - N+1 queries. Always profile endpoints; use
Includeor projections. ConsiderAsSplitQuery()for monster graphs. - Leaking contexts. Don’t store
DbContextin singletons. Dispose early and often. - Client‑side evaluation surprises. Keep expressions translatable; push filtering into SQL.
- Concurrency conflicts. Add a
byte[] RowVersion([Timestamp]), catchDbUpdateConcurrencyException, and merge. - Time zones. Persist UTC (
DateTime.UtcNow) and convert at the edges. For PostgreSQL usetimestamptz.
FAQ: EF Core in Real Projects
Yes – FromSqlInterpolated, ExecuteSqlRaw, and database views/functions map nicely. Use for reporting or complex SQL you don’t want to express in LINQ.
Yes. Use compiled queries, no‑tracking reads, and proper indexing. Many high‑traffic services run happily on it.
Add IsDeleted and a global filter. For hard deletes in admin tools, temporarily disable the filter via IgnoreQueryFilters().
DbContext?Usually no. DbContext + DbSet is already a repository/unit‑of‑work. Add repositories only when you need a very stable boundary or to share query logic.
Use ExecutionStrategy (EnableRetryOnFailure) and wrap multi‑command flows in an explicit transaction.
Absolutely. EF Core generates SQL, but you should understand indexes, execution plans, and cardinality.
Keep seed data in migrations, or write idempotent IHostedService seeders that check for existing rows.
Yes, with LINQ projections. Avoid materializing full entities when you just need a view.
Conclusion: A Faster, Safer Way to Ship Data Access
EF Core gives you a clean, productive path to relational data without hiding the fundamentals. Keep contexts short‑lived, read with no‑tracking, project narrowly, and migrate with discipline. Do that, and you’ll avoid the classic foot‑guns and keep your app fast under load.
Your turn: which EF Core trap bit you recently – N+1, context lifetime, or something spicier? Drop a comment and let’s debug it together.
