Skip to content
Tutorials Center
My learning

Topics

Lesson 39 of 41 ยท Projects and Practice

Project: A Library Lending System

Build a library lending system model with EF Core: entities, relationships, constraints, loan rules, queries and a concurrency-safe checkout, with read-only EF code and runnable stand-ins.

This project ties the course together. You will design the data model for a small library: books with several copies, members, and loans. You will write the EF Core mapping, the queries that a librarian needs, and the checkout operation that must never hand the same copy to two people.

EF Core code is read-only on this site because no database runs here. Each step has a runnable C# stand-in for the rules and queries, so you can check the logic. Treat the EF Core snippets as the real implementation.

Requirements

  • A book (title, ISBN) has many physical copies, each with a barcode.
  • A member can borrow up to 3 copies at once. Loans last 14 days.
  • A copy can be on loan to only one member at a time.
  • The librarian wants: books currently available, overdue loans, and a member's history.
erDiagram
    BOOK ||--|{ COPY : "has"
    MEMBER ||--o{ LOAN : "borrows"
    COPY ||--o{ LOAN : "is lent in"
    BOOK {
        int Id PK
        string Isbn
        string Title
    }
    COPY {
        int Id PK
        int BookId FK
        string Barcode
        byte[] RowVersion
    }
    MEMBER {
        int Id PK
        string Name
        string Email
    }
    LOAN {
        int Id PK
        int CopyId FK
        int MemberId FK
        date BorrowedOn
        date DueOn
        date ReturnedOn
    }

Loan connects a member and a physical copy. A null ReturnedOn means the loan is still open.

Step 1: the model

C#
public class Book
{
    public int Id { get; set; }
    public string Isbn { get; set; } = "";
    public string Title { get; set; } = "";
    public List<Copy> Copies { get; set; } = new();
}

public class Copy
{
    public int Id { get; set; }
    public int BookId { get; set; }
    public Book Book { get; set; } = null!;
    public string Barcode { get; set; } = "";
    public byte[] RowVersion { get; set; } = null!;
    public List<Loan> Loans { get; set; } = new();
}

public class Member
{
    public int Id { get; set; }
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
    public List<Loan> Loans { get; set; } = new();
}

public class Loan
{
    public int Id { get; set; }
    public int CopyId { get; set; }
    public Copy Copy { get; set; } = null!;
    public int MemberId { get; set; }
    public Member Member { get; set; } = null!;
    public DateOnly BorrowedOn { get; set; }
    public DateOnly DueOn { get; set; }
    public DateOnly? ReturnedOn { get; set; }
}

Step 2: configuration and constraints

Use the Fluent API (see data annotations and Fluent API and relationship configuration) to put the rules in the database, where they cannot be bypassed:

C#
protected override void OnModelCreating(ModelBuilder b)
{
    b.Entity<Book>(e =>
    {
        e.HasIndex(x => x.Isbn).IsUnique();
        e.Property(x => x.Title).HasMaxLength(200).IsRequired();
    });

    b.Entity<Copy>(e =>
    {
        e.HasIndex(x => x.Barcode).IsUnique();
        e.Property(x => x.RowVersion).IsRowVersion();           // optimistic concurrency token
        e.HasOne(x => x.Book).WithMany(x => x.Copies).OnDelete(DeleteBehavior.Restrict);
    });

    b.Entity<Member>().HasIndex(x => x.Email).IsUnique();

    b.Entity<Loan>(e =>
    {
        e.HasOne(x => x.Copy).WithMany(x => x.Loans).OnDelete(DeleteBehavior.Restrict);
        e.HasOne(x => x.Member).WithMany(x => x.Loans).OnDelete(DeleteBehavior.Restrict);

        // A copy can have at most ONE open loan: a filtered unique index.
        e.HasIndex(x => x.CopyId)
         .IsUnique()
         .HasFilter("[ReturnedOn] IS NULL");                      // SQL Server syntax; PostgreSQL uses "ReturnedOn" IS NULL
    });
}

The filtered unique index is the key trick: the database itself refuses a second open loan for the same copy, even under concurrency. This is stronger than any check in C#. Use Restrict delete behavior so deleting a book or member with history fails instead of erasing loans (see cascade rules).

Step 3: the checkout rules

The business rules (limit of 3, due date after 14 days) live in plain code so they can be unit tested without a database (see testing EF Core code):

C# Stand-in: checkout rules and the 'one open loan per copy' index

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Step 4: concurrent checkouts

Two librarians scan the same barcode at the same moment. Both run the "is it free?" check, both see yes, both insert. The filtered unique index guarantees that the second INSERT fails with a unique constraint violation, which you translate into a friendly message. A row version on Copy can serve as an extra guard if you also update the copy (for example a Status column):

C#
public async Task<CheckoutResult> CheckoutAsync(int memberId, string barcode, CancellationToken ct)
{
    var copy = await db.Copies.SingleOrDefaultAsync(c => c.Barcode == barcode, ct);
    if (copy is null) return CheckoutResult.UnknownCopy;

    var openLoans = await db.Loans.CountAsync(l => l.MemberId == memberId && l.ReturnedOn == null, ct);
    if (openLoans >= 3) return CheckoutResult.LimitReached;

    var today = DateOnly.FromDateTime(DateTime.UtcNow);
    db.Loans.Add(new Loan { CopyId = copy.Id, MemberId = memberId, BorrowedOn = today, DueOn = today.AddDays(14) });

    try
    {
        await db.SaveChangesAsync(ct);
        return CheckoutResult.Ok;
    }
    catch (DbUpdateException ex) when (IsUniqueViolation(ex))
    {
        return CheckoutResult.AlreadyOnLoan;       // the other librarian won the race
    }
}

IsUniqueViolation inspects the provider-specific inner exception (SQL Server error 2601 or 2627; PostgreSQL SQLSTATE 23505). See optimistic concurrency and pessimistic locking for alternatives.

Step 5: the librarian's queries

C#
// Titles with at least one available copy (no open loan)
var available = await db.Books
    .AsNoTracking()
    .Where(b => b.Copies.Any(c => !c.Loans.Any(l => l.ReturnedOn == null)))
    .Select(b => new
    {
        b.Title,
        FreeCopies = b.Copies.Count(c => !c.Loans.Any(l => l.ReturnedOn == null))
    })
    .ToListAsync();

// Overdue loans with member and title in ONE query, projecting only what the screen needs
var overdue = await db.Loans
    .AsNoTracking()
    .Where(l => l.ReturnedOn == null && l.DueOn < today)
    .OrderBy(l => l.DueOn)
    .Select(l => new { l.Member.Name, l.Member.Email, l.Copy.Book.Title, l.DueOn })
    .ToListAsync();

// A member's history, newest first, paged with keyset pagination
var history = await db.Loans
    .AsNoTracking()
    .Where(l => l.MemberId == memberId && l.Id < lastSeenId)
    .OrderByDescending(l => l.Id)
    .Take(20)
    .Select(l => new { l.Copy.Book.Title, l.BorrowedOn, l.ReturnedOn })
    .ToListAsync();

Projections with Select avoid loading full entities and the N+1 problem (see query performance and N+1). The same three queries run below over in-memory data:

C# Stand-in: availability, overdue and history queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with Console.ReadLine()), it gets the next line from this box.

Step 6: extend it yourself

  1. Add a reservation entity so a member can queue for a book with no free copy. What index prevents duplicate reservations?
  2. Add a nightly job (see IDbContextFactory) that flags overdue loans with ExecuteUpdateAsync.
  3. Add soft delete for members with a global filter and an interceptor (filters and interceptors).
  4. Write three tests: limit reached, same copy twice, return then re-borrow, against SQLite in memory.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does the model guarantee a copy has at most one open loan, even under concurrency?

    The database enforces the rule itself, so two simultaneous inserts cannot both succeed.
  2. 2. Why use DeleteBehavior.Restrict on Loan relationships?

    Loan history is valuable; Restrict forces a deliberate decision rather than a cascade.
  3. 3. Why project the overdue list with Select?

    A projection becomes one narrow SQL query, which is faster than loading entities and then navigating.
  4. 4. Where should business rules like the 3-loan limit live to be easily testable?

    Plain code is quick to unit test; constraints in the database catch what races past it.

Summary

  • Model books, copies, members and loans with explicit foreign keys and Restrict deletes.
  • Put hard guarantees (unique ISBN, one open loan per copy) in database constraints, and friendly rules in testable code.
  • Catch unique-violation exceptions to report lost races politely.
  • Use projections and AsNoTracking for read screens.

Next: Project: a multi-tenant SaaS data layer