163 - EF Core Database Project · dotnetmadeeasy.com

No API this time — just you, a schema with real relationships, and LINQ queries that have to earn their keep.

The last project used EF Core to back an API, but the data model stayed simple — one relationship, two entities. Real domains are messier: things relate to each other in more than one way, and a "many-to-many" relationship shows up constantly — a book has many authors, and an author writes many books; a member borrows many books over time, and a book gets borrowed by many members. This project strips away the API layer entirely and puts the spotlight where it belongs for once: on modeling relationships correctly and querying them well.

You'll build the data layer for a small Library Lending System — Book, Author, Member, and Loan — as a console app that seeds a SQLite database and then runs a series of increasingly interesting LINQ queries against it. No HTTP, no minimal APIs, no request/response shapes to design. Just a schema, migrations, and queries — the deepest look at EF Core and LINQ working together that this tier has to offer.

Project Brief

Model and query a small library's lending records as a console application.

Requirements

A Book can have multiple Authors, and an author can write multiple books (many-to-many).
A Member can borrow many books over time, and a book can be borrowed by many different members — but each individual borrowing event is its own record, tracked as a Loan (a book loaned to a member on a given date, due back by a given date, with an optional return date).
Seed the database with a handful of books, authors, members, and a mix of returned and still-outstanding loans.
Query: every book by a given author, alphabetically.
Query: every currently overdue loan (due date has passed, no return date yet), with the member's name and how many days overdue.
Query: a given member's full loan history, most recent first.
Query: the most-borrowed book of all time, and its total loan count.
Query: every author who has never had a book loaned out (an author with zero loans across all their books).

Designing the Schema

Four entities, two different kinds of relationship. Book ↔ Author is many-to-many — modeled with an explicit join entity, BookAuthor, rather than letting EF Core generate a hidden join table. Member and Book each relate to Loan as one-to-many — a loan belongs to exactly one member and references exactly one book, but each can have many loans over time.

SCHEMA, VISUALIZED

Author

Id, Name

* ── *
via BookAuthor

Book

Id, Title

Member

Id, Name

1 ── *

Loan

BookId, MemberId, LoanDate, DueDate, ReturnedDate?

Loan also has a one-to-many relationship to Book (not shown separately above) — it's the entity that ties a specific book to a specific member for a specific stretch of time.

public class Author
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<BookAuthor> BookAuthors { get; set; } = [];
}

public class Book
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public List<BookAuthor> BookAuthors { get; set; } = [];
    public List<Loan> Loans { get; set; } = [];
}

// Explicit join entity for the many-to-many relationship
public class BookAuthor
{
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public class Member
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Loan> Loans { get; set; } = [];
}

public class Loan
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
    public int MemberId { get; set; }
    public Member Member { get; set; } = null!;
    public DateOnly LoanDate { get; set; }
    public DateOnly DueDate { get; set; }
    public DateOnly? ReturnedDate { get; set; }
}

Why an explicit join entity instead of EF Core's automatic many-to-many? EF Core can wire up Book.Authors and Author.Books as direct navigation lists with no join entity in your code at all, hiding the join table entirely. That's convenient for a pure many-to-many — but the moment you might ever want to attach data to the relationship itself (an "order in which authors are credited," a "role" like editor vs. writer), you need an explicit join entity like BookAuthor anyway. Modeling it explicitly from the start, even when you don't yet need the extra data, keeps the schema honest about what's really a three-entity relationship and avoids a painful migration later.

Building It Step by Step

Step 1 — Project setup

dotnet new console -n LibraryDb
cd LibraryDb
dotnet add package Microsoft.EntityFrameworkCore.Sqlite
dotnet add package Microsoft.EntityFrameworkCore.Design

A plain console app this time — no ASP.NET Core at all. EF Core doesn't need a web host to run; it's a data-access library first, and a web API is just one of many things you can build on top of it.

Step 2 — The `DbContext`, with the composite key spelled out

using Microsoft.EntityFrameworkCore;

public class LibraryDbContext(DbContextOptions<LibraryDbContext> options) : DbContext(options)
{
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Member> Members => Set<Member>();
    public DbSet<Loan> Loans => Set<Loan>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // BookAuthor's primary key is the combination of both foreign keys —
        // a composite key, which EF Core cannot infer by convention.
        modelBuilder.Entity<BookAuthor>()
            .HasKey(ba => new { ba.BookId, ba.AuthorId });

        modelBuilder.Entity<BookAuthor>()
            .HasOne(ba => ba.Book)
            .WithMany(b => b.BookAuthors)
            .HasForeignKey(ba => ba.BookId);

        modelBuilder.Entity<BookAuthor>()
            .HasOne(ba => ba.Author)
            .WithMany(a => a.BookAuthors)
            .HasForeignKey(ba => ba.AuthorId);

        modelBuilder.Entity<Loan>()
            .HasOne(l => l.Book)
            .WithMany(b => b.Loans)
            .HasForeignKey(l => l.BookId)
            .OnDelete(DeleteBehavior.Restrict);

        modelBuilder.Entity<Loan>()
            .HasOne(l => l.Member)
            .WithMany(m => m.Loans)
            .HasForeignKey(l => l.MemberId)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

HasKey(ba => new { ba.BookId, ba.AuthorId }) is the one genuinely new idea here: a composite key, made of two columns together rather than one. It makes sense for BookAuthor — the same book/author pairing should never appear twice, but neither BookId nor AuthorId alone is unique on its own. Both DeleteBehavior.Restrict settings on Loan mean you can never delete a book or member that still has loan history attached — a lending system should never lose the historical record of who borrowed what.

Step 3 — Migrate and seed

dotnet ef migrations add InitialCreate
dotnet ef database update

static async Task SeedAsync(LibraryDbContext db)
{
    if (await db.Books.AnyAsync()) return; // already seeded

    var tolkien = new Author { Name = "J.R.R. Tolkien" };
    var martin = new Author { Name = "George R.R. Martin" };

    var hobbit = new Book { Title = "The Hobbit" };
    var fellowship = new Book { Title = "The Fellowship of the Ring" };
    var thrones = new Book { Title = "A Game of Thrones" };

    hobbit.BookAuthors.Add(new BookAuthor { Book = hobbit, Author = tolkien });
    fellowship.BookAuthors.Add(new BookAuthor { Book = fellowship, Author = tolkien });
    thrones.BookAuthors.Add(new BookAuthor { Book = thrones, Author = martin });

    var alice = new Member { Name = "Alice Chen" };
    var ben = new Member { Name = "Ben Osei" };

    db.AddRange(hobbit, fellowship, thrones, alice, ben);
    await db.SaveChangesAsync();

    var today = DateOnly.FromDateTime(DateTime.UtcNow);
    db.Loans.AddRange(
        new Loan { Book = hobbit, Member = alice, LoanDate = today.AddDays(-20), DueDate = today.AddDays(-6), ReturnedDate = today.AddDays(-5) },
        new Loan { Book = hobbit, Member = ben, LoanDate = today.AddDays(-10), DueDate = today.AddDays(4) },
        new Loan { Book = fellowship, Member = alice, LoanDate = today.AddDays(-30), DueDate = today.AddDays(-16) }, // overdue, never returned
        new Loan { Book = thrones, Member = ben, LoanDate = today.AddDays(-3), DueDate = today.AddDays(11) });

    await db.SaveChangesAsync();
}

Adding a BookAuthor straight into hobbit.BookAuthors — rather than creating it separately and setting both foreign keys by hand — lets EF Core's change tracker resolve every ID automatically once everything is saved. One loan is deliberately left with no ReturnedDate and a DueDate in the past, so there's real overdue data for the queries to find.

Step 4 — The queries, one at a time

Every book by a given author, alphabetically — this needs to traverse Author → BookAuthor → Book, which is exactly what .Include().ThenInclude() is for:

static async Task<List<string>> GetBooksByAuthorAsync(LibraryDbContext db, string authorName, CancellationToken ct)
{
    var author = await db.Authors
        .Include(a => a.BookAuthors)
            .ThenInclude(ba => ba.Book)
        .FirstOrDefaultAsync(a => a.Name == authorName, ct);

    return author?.BookAuthors
        .Select(ba => ba.Book.Title)
        .OrderBy(title => title)
        .ToList() ?? [];
}

Overdue loans — filter in the database, compute "days overdue" after the data comes back:

static async Task<List<(string MemberName, string BookTitle, int DaysOverdue)>> GetOverdueLoansAsync(
    LibraryDbContext db, CancellationToken ct)
{
    var today = DateOnly.FromDateTime(DateTime.UtcNow);

    var overdue = await db.Loans
        .Where(l => l.ReturnedDate == null && l.DueDate < today)
        .Include(l => l.Member)
        .Include(l => l.Book)
        .ToListAsync(ct);

    return overdue
        .Select(l => (l.Member.Name, l.Book.Title, DaysOverdue: today.DayNumber - l.DueDate.DayNumber))
        .ToList();
}

Notice the Where runs before ToListAsync — it becomes part of the SQL sent to SQLite, so only actually-overdue rows travel over the wire. The days-overdue calculation happens afterward, on plain in-memory C# tuples, because date arithmetic like this is easiest to reason about (and to test) once the rows are already loaded.

A member's loan history, most recent first:

static async Task<List<Loan>> GetLoanHistoryAsync(LibraryDbContext db, int memberId, CancellationToken ct) =>
    await db.Loans
        .Where(l => l.MemberId == memberId)
        .Include(l => l.Book)
        .OrderByDescending(l => l.LoanDate)
        .ToListAsync(ct);

Most-borrowed book — a group, ordered by count, with only the top result taken:

static async Task<(string Title, int LoanCount)?> GetMostBorrowedBookAsync(LibraryDbContext db, CancellationToken ct)
{
    var result = await db.Loans
        .GroupBy(l => l.BookId)
        .Select(g => new { g.Key, Count = g.Count() })
        .OrderByDescending(g => g.Count)
        .FirstOrDefaultAsync(ct);

    if (result is null) return null;

    var book = await db.Books.FirstAsync(b => b.Id == result.Key, ct);
    return (book.Title, result.Count);
}

Authors with zero loans across any of their books — this is where LINQ's All and negation earn their place, expressing "an author such that none of their books have any loans":

static async Task<List<string>> GetNeverBorrowedAuthorsAsync(LibraryDbContext db, CancellationToken ct) =>
    await db.Authors
        .Where(a => a.BookAuthors.All(ba => ba.Book.Loans.Count == 0))
        .Select(a => a.Name)
        .ToListAsync(ct);

This single LINQ expression compiles down to a correlated SQL subquery — EF Core translates the whole three-level traversal (author → their books → each book's loans) into one round trip to the database. That's the real power on display in this project: you write a readable, declarative sentence in C#, and EF Core's query provider is responsible for turning it into efficient SQL.

Complete Solution

The whole program, wired together in Program.cs — entities and DbContext shown alongside for readability:

using Microsoft.EntityFrameworkCore;

var options = new DbContextOptionsBuilder<LibraryDbContext>()
    .UseSqlite("Data Source=library.db")
    .Options;

await using var db = new LibraryDbContext(options);
await db.Database.MigrateAsync();
await SeedAsync(db);

var cts = new CancellationTokenSource();

Console.WriteLine("── Books by J.R.R. Tolkien ──");
foreach (var title in await GetBooksByAuthorAsync(db, "J.R.R. Tolkien", cts.Token))
    Console.WriteLine($"  {title}");

Console.WriteLine("\n── Overdue loans ──");
foreach (var (member, title, days) in await GetOverdueLoansAsync(db, cts.Token))
    Console.WriteLine($"  {member} has \"{title}\" — {days} day(s) overdue");

Console.WriteLine("\n── Most borrowed book ──");
var mostBorrowed = await GetMostBorrowedBookAsync(db, cts.Token);
if (mostBorrowed is { } m)
    Console.WriteLine($"  \"{m.Title}\" — borrowed {m.LoanCount} time(s)");

Console.WriteLine("\n── Authors never borrowed ──");
foreach (var name in await GetNeverBorrowedAuthorsAsync(db, cts.Token))
    Console.WriteLine($"  {name}");

// ── Data access methods ──

static async Task SeedAsync(LibraryDbContext db)
{
    if (await db.Books.AnyAsync()) return;

    var tolkien = new Author { Name = "J.R.R. Tolkien" };
    var martin = new Author { Name = "George R.R. Martin" };

    var hobbit = new Book { Title = "The Hobbit" };
    var fellowship = new Book { Title = "The Fellowship of the Ring" };
    var thrones = new Book { Title = "A Game of Thrones" };

    hobbit.BookAuthors.Add(new BookAuthor { Book = hobbit, Author = tolkien });
    fellowship.BookAuthors.Add(new BookAuthor { Book = fellowship, Author = tolkien });
    thrones.BookAuthors.Add(new BookAuthor { Book = thrones, Author = martin });

    var alice = new Member { Name = "Alice Chen" };
    var ben = new Member { Name = "Ben Osei" };

    db.AddRange(hobbit, fellowship, thrones, alice, ben);
    await db.SaveChangesAsync();

    var today = DateOnly.FromDateTime(DateTime.UtcNow);
    db.Loans.AddRange(
        new Loan { Book = hobbit, Member = alice, LoanDate = today.AddDays(-20), DueDate = today.AddDays(-6), ReturnedDate = today.AddDays(-5) },
        new Loan { Book = hobbit, Member = ben, LoanDate = today.AddDays(-10), DueDate = today.AddDays(4) },
        new Loan { Book = fellowship, Member = alice, LoanDate = today.AddDays(-30), DueDate = today.AddDays(-16) },
        new Loan { Book = thrones, Member = ben, LoanDate = today.AddDays(-3), DueDate = today.AddDays(11) });

    await db.SaveChangesAsync();
}

static async Task<List<string>> GetBooksByAuthorAsync(LibraryDbContext db, string authorName, CancellationToken ct)
{
    var author = await db.Authors
        .Include(a => a.BookAuthors).ThenInclude(ba => ba.Book)
        .FirstOrDefaultAsync(a => a.Name == authorName, ct);

    return author?.BookAuthors.Select(ba => ba.Book.Title).OrderBy(t => t).ToList() ?? [];
}

static async Task<List<(string MemberName, string BookTitle, int DaysOverdue)>> GetOverdueLoansAsync(
    LibraryDbContext db, CancellationToken ct)
{
    var today = DateOnly.FromDateTime(DateTime.UtcNow);
    var overdue = await db.Loans
        .Where(l => l.ReturnedDate == null && l.DueDate < today)
        .Include(l => l.Member).Include(l => l.Book)
        .ToListAsync(ct);

    return overdue.Select(l => (l.Member.Name, l.Book.Title, DaysOverdue: today.DayNumber - l.DueDate.DayNumber)).ToList();
}

static async Task<(string Title, int LoanCount)?> GetMostBorrowedBookAsync(LibraryDbContext db, CancellationToken ct)
{
    var result = await db.Loans
        .GroupBy(l => l.BookId)
        .Select(g => new { g.Key, Count = g.Count() })
        .OrderByDescending(g => g.Count)
        .FirstOrDefaultAsync(ct);

    if (result is null) return null;
    var book = await db.Books.FirstAsync(b => b.Id == result.Key, ct);
    return (book.Title, result.Count);
}

static async Task<List<string>> GetNeverBorrowedAuthorsAsync(LibraryDbContext db, CancellationToken ct) =>
    await db.Authors
        .Where(a => a.BookAuthors.All(ba => ba.Book.Loans.Count == 0))
        .Select(a => a.Name)
        .ToListAsync(ct);

// ── Entities ──
public class Author
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<BookAuthor> BookAuthors { get; set; } = [];
}

public class Book
{
    public int Id { get; set; }
    public required string Title { get; set; }
    public List<BookAuthor> BookAuthors { get; set; } = [];
    public List<Loan> Loans { get; set; } = [];
}

public class BookAuthor
{
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
    public int AuthorId { get; set; }
    public Author Author { get; set; } = null!;
}

public class Member
{
    public int Id { get; set; }
    public required string Name { get; set; }
    public List<Loan> Loans { get; set; } = [];
}

public class Loan
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
    public int MemberId { get; set; }
    public Member Member { get; set; } = null!;
    public DateOnly LoanDate { get; set; }
    public DateOnly DueDate { get; set; }
    public DateOnly? ReturnedDate { get; set; }
}

// ── DbContext ──
public class LibraryDbContext(DbContextOptions<LibraryDbContext> options) : DbContext(options)
{
    public DbSet<Book> Books => Set<Book>();
    public DbSet<Author> Authors => Set<Author>();
    public DbSet<Member> Members => Set<Member>();
    public DbSet<Loan> Loans => Set<Loan>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BookAuthor>().HasKey(ba => new { ba.BookId, ba.AuthorId });
        modelBuilder.Entity<BookAuthor>().HasOne(ba => ba.Book).WithMany(b => b.BookAuthors).HasForeignKey(ba => ba.BookId);
        modelBuilder.Entity<BookAuthor>().HasOne(ba => ba.Author).WithMany(a => a.BookAuthors).HasForeignKey(ba => ba.AuthorId);
        modelBuilder.Entity<Loan>().HasOne(l => l.Book).WithMany(b => b.Loans).HasForeignKey(l => l.BookId).OnDelete(DeleteBehavior.Restrict);
        modelBuilder.Entity<Loan>().HasOne(l => l.Member).WithMany(m => m.Loans).HasForeignKey(l => l.MemberId).OnDelete(DeleteBehavior.Restrict);
    }
}

Run dotnet ef database update, then dotnet run. You should see Tolkien's two books listed alphabetically, one overdue loan (Alice's copy of "The Fellowship of the Ring"), "The Hobbit" as the most-borrowed book with 2 loans, and an empty "never borrowed" list — every author here has at least one loan on one of their books.

Try It Yourself — Extension Challenges

Challenge 1 — Return a bookEasy

Write ReturnBookAsync(db, loanId, ct) that sets a loan's ReturnedDate to today, only if it hasn't already been returned.

Hint

Load the loan with FindAsync, check ReturnedDate is null before setting it (returning an already-returned loan should be a no-op or an error, not silently overwritten), then SaveChangesAsync.

Challenge 2 — Prevent double-borrowingMedium

Before creating a new loan, check that the book doesn't already have an outstanding (unreturned) loan, and throw a clear exception if it does.

Hint

await db.Loans.AnyAsync(l => l.BookId == bookId && l.ReturnedDate == null, ct) — if true, the copy is already out. This is the same "check before you act" guard-clause pattern from the exception-handling lessons, just applied against the database instead of an in-memory collection.

Challenge 3 — Add a second migrationMedium

Add a Genre property to Book, generate a new migration for it (don't touch InitialCreate), and apply it without losing existing data.

Hint

dotnet ef migrations add AddBookGenre then dotnet ef database update. Look at the generated migration file — it should contain only an AddColumn operation, proof that EF Core diffed the new model against the old one rather than regenerating the whole schema from scratch.

Challenge 4 — Co-authored books reportHard

Write a query that returns every book with more than one author, listing all of that book's author names together.

Hint

db.Books.Include(b => b.BookAuthors).ThenInclude(ba => ba.Author).Where(b => b.BookAuthors.Count > 1), then project each matching book's BookAuthors.Select(ba => ba.Author.Name) into a joined string with string.Join(", ", ...) after the query materializes.

Challenge 5 — Member with the longest average loan durationHard

Among members with at least one returned loan, find the one whose average number of days between LoanDate and ReturnedDate is highest.

Hint

Filter to l.ReturnedDate != null first, group by MemberId, and average (l.ReturnedDate!.Value.DayNumber - l.LoanDate.DayNumber) inside the group. Date arithmetic like this can be awkward to translate directly to SQL depending on the provider — if you hit a translation error, materialize the filtered loans with ToListAsync first and do the grouping and averaging in memory with LINQ-to-Objects instead.

Key Takeaway

Many-to-many relationships are best modeled with an explicit join entity the moment there's any chance you'll need data on the relationship itself — and it needs a composite key EF Core can't infer on its own.
Include/ThenInclude traverse multi-level navigation chains; whether to filter before or after materializing a query is a real design decision, not an afterthought — filter in the database when you can, finish in memory when the logic gets awkward to translate.
EF Core is a data-access library, not a web framework — it works exactly the same in a console app as it does behind a REST API.
Migrations are incremental and diffed — adding one column produces a small, focused migration, not a full schema rewrite.
Complex LINQ — grouping, aggregation, nested All/Any — reads like a sentence in C# and compiles down to real, often surprisingly efficient, SQL.

You've modeled a genuinely relational schema and queried it with real depth — many-to-many relationships, composite keys, multi-level includes, and grouped aggregation, all through LINQ. Next: putting delegates and events to work in a real system.

dotnetmadeeasy.com — Learn C# and .NET, the right way.

Project Brief

Requirements

Designing the Schema

Building It Step by Step

Step 1 — Project setup

Step 2 — The DbContext, with the composite key spelled out

Step 3 — Migrate and seed

Step 4 — The queries, one at a time

Complete Solution

Try It Yourself — Extension Challenges

Key Takeaway

Step 2 — The `DbContext`, with the composite key spelled out