I recently built a complete Entity Framework Core Code-First demo project from scratch using Visual Studio Code — no Visual Studio, no scaffolding wizards, just C# and the terminal. The goal was to cover the full lifecycle: define models in code, generate the database, perform CRUD operations with explicit transactions, and set up real debugging workflows. Didn’t include any logging for you yet. But that you can add as sidecar. Also you can add testing and have fun with it.
Along the way, I ran into several issues that aren’t covered in most tutorials but absolutely come up in practice. This post walks through the entire build and documents every problem I hit with the solutions that fixed them.
The full source code is available at: github.com/hoquem1/EF_Project
What We’re Building
A person-employment tracking system with three tables: a parent Person table, a child Employment table (companies and job titles), and a PersonEmployment junction table that links them with a StartDate and a nullable EndDate — where null means “still employed.”
┌──────────────┐ ┌─────────────────────┐ ┌──────────────────┐
│ Person │ │ PersonEmployment │ │ Employment │
├──────────────┤ ├─────────────────────┤ ├──────────────────┤
│ PersonId (PK)│──┐ │ PersonEmploymentId │ ┌──│ EmploymentId(PK) │
│ FirstName │ └───>│ PersonId (FK) │ │ │ CompanyName │
│ LastName │ │ EmploymentId (FK) │<───┘ │ JobTitle │
│ Email (UQ) │ │ StartDate │ └──────────────────┘
│ CreatedDate │ │ EndDate (nullable) │
└──────────────┘ └─────────────────────┘
This is a real-world pattern. The junction table isn’t just a many-to-many link — it carries its own payload (the date range), which makes it more interesting than what most EF Core tutorials cover.
Step 1: Define the Models
Code-First means the C# classes are the schema. No SQL scripts, no designers — EF Core reads these classes and generates the database structure.
The parent entity is straightforward:
public class Person
{
public int PersonId { get; set; }
public string FirstName { get; set; } = string.Empty;
public string LastName { get; set; } = string.Empty;
public string Email { get; set; } = string.Empty;
public DateTime CreatedDate { get; set; } = DateTime.UtcNow;
// Navigation: one Person -> many PersonEmployments
public ICollection<PersonEmployment> PersonEmployments { get; set; } = new List<PersonEmployment>();
}
The junction table is where it gets interesting. It has its own primary key, foreign keys to both parent tables, and the date payload:
public class PersonEmployment
{
public int PersonEmploymentId { get; set; }
// Foreign keys
public int PersonId { get; set; }
public int EmploymentId { get; set; }
// Payload columns
public DateTime StartDate { get; set; }
public DateTime? EndDate { get; set; } // nullable = still employed
// Navigation properties
public Person Person { get; set; } = null!;
public Employment Employment { get; set; } = null!;
}
The null! pattern on navigation properties tells the compiler “I know this looks nullable, but EF Core will populate it.” It avoids nullable warnings without making the property actually optional.
Step 2: Configure with Fluent API
The DbContext is where you define relationships, constraints, indexes, and delete behaviors. I prefer Fluent API over data annotations because it keeps the models clean and puts all the database concerns in one place.
Here’s the junction table configuration — the most important part:
modelBuilder.Entity<PersonEmployment>(entity =>
{
entity.HasKey(pe => pe.PersonEmploymentId);
// Cascade: deleting a Person removes their employment records
entity.HasOne(pe => pe.Person)
.WithMany(p => p.PersonEmployments)
.HasForeignKey(pe => pe.PersonId)
.OnDelete(DeleteBehavior.Cascade);
// Restrict: can't delete an Employment that's still referenced
entity.HasOne(pe => pe.Employment)
.WithMany(e => e.PersonEmployments)
.HasForeignKey(pe => pe.EmploymentId)
.OnDelete(DeleteBehavior.Restrict);
// Composite unique index for query performance
entity.HasIndex(pe => new { pe.PersonId, pe.EmploymentId, pe.StartDate })
.IsUnique();
});
The asymmetric delete behavior is intentional. If a person leaves the system, their history goes with them (cascade). But you can’t delete a company record while people are still linked to it (restrict). This is a pattern that comes up constantly in enterprise systems.
Step 3: Transactions That Actually Span Multiple Operations
Most tutorials show a single SaveChangesAsync() call. In practice, you often need to insert a parent, get its generated ID, then use that ID to insert children — all atomically.
await using var transaction = await _db.Database.BeginTransactionAsync();
try
{
// Step 1: Insert Person — we need the generated PersonId
var person = new Person { FirstName = firstName, LastName = lastName, Email = email };
_db.Persons.Add(person);
await _db.SaveChangesAsync(); // person.PersonId is now populated
// Step 2: Find or create the Employment
var employment = await _db.Employments
.FirstOrDefaultAsync(e => e.CompanyName == companyName && e.JobTitle == jobTitle);
if (employment is null)
{
employment = new Employment { CompanyName = companyName, JobTitle = jobTitle };
_db.Employments.Add(employment);
await _db.SaveChangesAsync();
}
// Step 3: Link them
var personEmployment = new PersonEmployment
{
PersonId = person.PersonId,
EmploymentId = employment.EmploymentId,
StartDate = startDate,
EndDate = null
};
_db.PersonEmployments.Add(personEmployment);
await _db.SaveChangesAsync();
// All succeeded — commit
await transaction.CommitAsync();
}
catch (Exception ex)
{
// Any failure rolls back ALL three inserts
await transaction.RollbackAsync();
throw;
}
The key insight: BeginTransactionAsync() wraps multiple SaveChangesAsync() calls into a single atomic unit. Without it, each SaveChanges auto-commits, and a failure at step 3 would leave orphaned records from steps 1 and 2.
Step 4: Debugging — See What EF Core Is Actually Doing
This is the part most developers skip and then regret when a query is slow in production. Three techniques that I wired into the project:
Console SQL Logging — configured once in DbContext, logs every generated query:
options
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging() // shows actual parameter values
.EnableDetailedErrors();
ToQueryString() — shows the SQL without executing it, great for debugging LINQ:
var query = db.PersonEmployments
.Where(pe => pe.EndDate == null)
.Include(pe => pe.Person)
.Include(pe => pe.Employment);
Console.WriteLine(query.ToQueryString());
Change Tracker inspection — see exactly what EF thinks has changed before it hits the database:
foreach (var entry in db.ChangeTracker.Entries())
{
Console.WriteLine($"{entry.Entity.GetType().Name} → {entry.State}");
if (entry.State == EntityState.Modified)
{
foreach (var prop in entry.Properties.Where(p => p.IsModified))
{
Console.WriteLine($" {prop.Metadata.Name}: '{prop.OriginalValue}' → '{prop.CurrentValue}'");
}
}
}
This last one is invaluable when an update isn’t persisting — you can see whether EF even detected the change and which properties it flagged as modified.
The Gotchas: What Actually Went Wrong
Here’s where tutorials and real life diverge. I hit four distinct issues getting this project running, each teaching a different lesson about EF Core’s behavior.
Gotcha 1: Multiple Project Files in One Directory
The error:
MSBUILD : error MSB1011: Specify which project or solution file to use because
this folder contains more than one project or solution file.
What happened: My working directory had more than one .csproj file. The dotnet restore and dotnet run commands didn’t know which one to target.
The fix: Either specify the project explicitly (dotnet run --project EfCoreDemo.csproj) or isolate each project in its own subfolder. The second approach is cleaner and avoids the issue permanently.
Gotcha 2: SQLite Doesn’t Support Idempotent Migration Scripts
The error:
System.NotSupportedException: Generating idempotent scripts for migrations
is not currently supported for SQLite.
What happened: I ran dotnet ef migrations script --idempotent to generate a safe-to-rerun SQL script. SQLite can’t do this because it doesn’t support the conditional execution blocks (IF NOT EXISTS wrappers) that idempotent scripts require.
The fix — two options:
Drop the --idempotent flag if staying on SQLite: dotnet ef migrations script -o migration.sql. This works fine for development.
Or switch to SQL Server LocalDB for the full production-grade experience. This requires changing two files — the NuGet package in .csproj from EntityFrameworkCore.Sqlite to EntityFrameworkCore.SqlServer, and the provider call in AppDbContext.cs from .UseSqlite(...) to .UseSqlServer(...).
Gotcha 3: EnsureCreated and Migrations Don’t Mix
The error:
SQLite Error 1: 'table "Employments" already exists'.
What happened: This was the most instructive failure. The Program.cs demo called EnsureCreatedAsync(), which builds all the tables directly from the model. Then I ran dotnet ef database update to apply migrations. The migration tried to CREATE TABLE — but those tables already existed.
The root cause is that EnsureCreated and Migrations are mutually exclusive strategies. EnsureCreated creates the schema but does not write anything to the __EFMigrationsHistory table. So migrations have no idea the tables are already there.
| Approach | Use Case | Tracks History? |
|---|---|---|
EnsureCreated() | Quick prototyping, demos | No |
Migrations | Production, team environments | Yes |
The fix: Delete the database file, remove the old migration, and regenerate cleanly. The rule going forward: pick one approach per database and stick with it.
Gotcha 4: Swapped Files Not Taking Effect
The symptom: After switching from SQLite to SQL Server, the logs still showed SQLite-specific SQL (sqlite_master, AUTOINCREMENT).
What happened: The updated .csproj and AppDbContext.cs files weren’t actually copied into the working directory. The project was still using the original SQLite configuration.
The lesson: After any provider swap, always verify:
Select-String "Sqlite|SqlServer" .\EfCoreDemo.csproj
If it still shows the old provider, the files weren’t replaced. Simple, but easy to overlook when you’re iterating fast.
Recovery Checklist
When EF Core migrations get into a broken state — and they will, especially during development — this sequence resets everything:
# 1. Delete the database
Remove-Item .\efcore_demo.db -ErrorAction SilentlyContinue # SQLite
# dotnet ef database drop --force # SQL Server
# 2. Remove all migrations
Remove-Item .\Migrations -Recurse -ErrorAction SilentlyContinue
# 3. Restore packages
dotnet restore
# 4. Fresh migration
dotnet ef migrations add InitialCreate
# 5. Apply
dotnet ef database update
# 6. Run
dotnet run
I’ve used this sequence multiple times already. Worth keeping in a sticky note.
Key Patterns in the Project
For quick reference, here’s what’s demonstrated in the codebase and where to find it:
- Code-First schema definition — Models + Fluent API in
AppDbContext.cs - Explicit transactions —
AddPersonWithEmploymentAsyncwraps threeSaveChangescalls atomically - Transaction rollback — duplicate email insert triggers rollback, no partial data saved
- Eager loading —
.Include().ThenInclude()to avoid N+1 queries - Projection queries —
.Select()to fetch only the columns the UI needs - AsNoTracking — applied on read-only queries for better performance
- Cascade vs. Restrict delete — asymmetric delete behavior on the junction table
- Seed data — repeatable test data defined in
OnModelCreating - Change Tracker debugging — inspect entity states and modified properties before save
- SQL logging —
LogTo,EnableSensitiveDataLogging, andToQueryString()
Wrapping Up
EF Core’s Code-First approach is powerful once you understand its boundaries. The models-to-database pipeline works well, transactions give you proper atomicity, and the debugging tools are genuinely useful.
But the rough edges are real: the EnsureCreated vs. Migrations conflict isn’t obvious until it breaks, provider-specific limitations like SQLite’s idempotent script gap aren’t documented prominently, and multi-project directories silently confuse the CLI. These are the kinds of things you learn by building, not by reading.
The full project is on GitHub: github.com/hoquem1/EF_Project
Clone it, run it, break it, fix it. That’s how this stuff sticks.