Exercise 1: Entity Configuration with Fluent API

Objective

Practice configuring complex entities using Fluent API and IEntityTypeConfiguration.

Scenario

Build a library management system with the following entities:

Entities

public class Book
{
    public int Id { get; set; }
    public string ISBN { get; set; }
    public string Title { get; set; }
    public string Description { get; set; }
    public decimal Price { get; set; }
    public DateTime PublishedDate { get; set; }
    public bool IsAvailable { get; set; }

    public int AuthorId { get; set; }
    public Author Author { get; set; }

    public int PublisherId { get; set; }
    public Publisher Publisher { get; set; }

    public List<BookCategory> BookCategories { get; set; }
    public List<Loan> Loans { get; set; }
}

public class Author
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Biography { get; set; }

    public List<Book> Books { get; set; }
}

public class Publisher
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }

    public List<Book> Books { get; set; }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }

    public List<BookCategory> BookCategories { get; set; }
}

public class BookCategory
{
    public int BookId { get; set; }
    public Book Book { get; set; }

    public int CategoryId { get; set; }
    public Category Category { get; set; }
}

public class Member
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime JoinDate { get; set; }

    public List<Loan> Loans { get; set; }
}

public class Loan
{
    public int Id { get; set; }
    public DateTime LoanDate { get; set; }
    public DateTime? ReturnDate { get; set; }
    public DateTime DueDate { get; set; }

    public int BookId { get; set; }
    public Book Book { get; set; }

    public int MemberId { get; set; }
    public Member Member { get; set; }
}

Tasks

Task 1: Create Configuration Classes

Create IEntityTypeConfiguration for each entity with these requirements:

Book Configuration:

• ISBN: Required, max 13 characters, unique index
• Title: Required, max 200 characters, index
• Description: Max 2000 characters
• Price: decimal(18,2), required
• PublishedDate: Default value of GETUTCDATE()
• IsAvailable: Default value true
• Author: Required relationship
• Publisher: Required relationship

Author Configuration:

• FirstName: Required, max 50 characters
• LastName: Required, max 50 characters
• Biography: Max 5000 characters
• Composite index on (LastName, FirstName)

Publisher Configuration:

• Name: Required, max 100 characters, unique
• Country: Max 50 characters

Category Configuration:

• Name: Required, max 50 characters, unique

BookCategory Configuration:

• Composite primary key (BookId, CategoryId)
• Cascade delete from both sides

Member Configuration:

• Email: Required, max 100 characters, unique index
• FirstName: Required, max 50 characters
• LastName: Required, max 50 characters
• JoinDate: Default value GETUTCDATE()

Loan Configuration:

• LoanDate: Required
• DueDate: Required
• ReturnDate: Optional
• Index on (MemberId, LoanDate)
• Restrict delete (prevent deleting book/member with active loans)

Task 2: Implement Configurations

public class BookConfiguration : IEntityTypeConfiguration<Book>
{
    public void Configure(EntityTypeBuilder<Book> builder)
    {
        // Your implementation here
    }
}

// ... implement all other configurations

Task 3: Apply Configurations

public class LibraryContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Author> Authors { get; set; }
    public DbSet<Publisher> Publishers { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Member> Members { get; set; }
    public DbSet<Loan> Loans { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Apply all configurations from assembly
    }
}

Task 4: Add Seed Data

Seed the database with:

• 3 Authors
• 2 Publishers
• 5 Categories
• 10 Books
• 5 Members

Task 5: Create Migration

dotnet ef migrations add InitialCreate
dotnet ef database update

Validation

Verify your configuration by:

1. Checking generated SQL schema matches requirements
2. Attempting to insert invalid data (should fail)
3. Testing cascade deletes
4. Verifying indexes were created
5. Confirming seed data was inserted

Solution Hints

Click to see BookConfiguration example

public class BookConfiguration : IEntityTypeConfiguration<Book>
{
    public void Configure(EntityTypeBuilder<Book> builder)
    {
        builder.HasKey(b => b.Id);

        builder.Property(b => b.ISBN)
            .IsRequired()
            .HasMaxLength(13);

        builder.HasIndex(b => b.ISBN)
            .IsUnique();

        builder.Property(b => b.Title)
            .IsRequired()
            .HasMaxLength(200);

        builder.HasIndex(b => b.Title);

        builder.Property(b => b.Description)
            .HasMaxLength(2000);

        builder.Property(b => b.Price)
            .HasColumnType("decimal(18,2)")
            .IsRequired();

        builder.Property(b => b.PublishedDate)
            .HasDefaultValueSql("GETUTCDATE()");

        builder.Property(b => b.IsAvailable)
            .HasDefaultValue(true);

        builder.HasOne(b => b.Author)
            .WithMany(a => a.Books)
            .HasForeignKey(b => b.AuthorId)
            .OnDelete(DeleteBehavior.Restrict);

        builder.HasOne(b => b.Publisher)
            .WithMany(p => p.Books)
            .HasForeignKey(b => b.PublisherId)
            .OnDelete(DeleteBehavior.Restrict);
    }
}

Bonus Challenges

1. Add a computed column for FullName in Author (FirstName + LastName)
2. Implement table splitting for Book and BookDetails
3. Add value conversion to store PublishedDate as Unix timestamp
4. Create a filtered index on Books where IsAvailable = true
5. Add alternate key on Book.ISBN for foreign key references