C# Code-first Approach

Introduction

The Code-first approach is a database development methodology that allows developers to define their database structure using C# classes rather than directly creating database tables. It's part of Entity Framework, Microsoft's object-relational mapping (ORM) framework, and it's particularly powerful because it lets you:

Define your database schema using familiar C# classes
Automatically generate the database from your code model
Track changes to your model over time with migrations
Focus on your domain model rather than database specifics

This approach is highly beneficial for developers who prefer working with code over database tools, and it works particularly well in agile environments where models evolve frequently.

Prerequisites

Before diving into the Code-first approach, make sure you have:

Visual Studio installed
Basic knowledge of C# and object-oriented programming
Entity Framework installed (we'll use Entity Framework Core in this tutorial)

Getting Started with Code-first

Step 1: Set Up Your Project

First, let's create a new console application and install the required packages:

csharp
// Open Visual Studio and create a new Console Application
// Then install Entity Framework Core packages via NuGet:

// Using Package Manager Console:
// Install-Package Microsoft.EntityFrameworkCore.SqlServer
// Install-Package Microsoft.EntityFrameworkCore.Tools

Step 2: Define Your Domain Models

Code-first starts with creating C# classes that represent your database tables:

csharp
// Create a new class file named Book.cs
public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public int PublicationYear { get; set; }
    public decimal Price { get; set; }
    public bool IsAvailable { get; set; }
}

// Create another class file named Category.cs
public class Category
{
    public int CategoryId { get; set; }
    public string Name { get; set; }
    
    // Navigation property - one category can have many books
    public List<Book> Books { get; set; }
}

// Update Book.cs to include the relationship
public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public string Author { get; set; }
    public int PublicationYear { get; set; }
    public decimal Price { get; set; }
    public bool IsAvailable { get; set; }
    
    // Foreign key
    public int CategoryId { get; set; }
    
    // Navigation property
    public Category Category { get; set; }
}

Step 3: Create a Database Context

The DbContext class represents a session with the database and provides APIs for communication:

csharp
// Create a new class file named BookStoreContext.cs
using Microsoft.EntityFrameworkCore;

public class BookStoreContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Category> Categories { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=BookStoreDb;Trusted_Connection=True;");
    }
    
    // Optional: Configure your model using Fluent API
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure the Book entity
        modelBuilder.Entity<Book>()
            .Property(b => b.Title)
            .IsRequired()
            .HasMaxLength(200);
            
        modelBuilder.Entity<Book>()
            .Property(b => b.Price)
            .HasColumnType("decimal(18,2)");
            
        // Configure the relationship
        modelBuilder.Entity<Book>()
            .HasOne(b => b.Category)
            .WithMany(c => c.Books)
            .HasForeignKey(b => b.CategoryId);
    }
}

Adding Data Annotations

You can use data annotations to configure your model properties:

csharp
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class Book
{
    [Key]
    public int BookId { get; set; }
    
    [Required]
    [StringLength(200)]
    public string Title { get; set; }
    
    [Required]
    [StringLength(100)]
    public string Author { get; set; }
    
    [Range(1000, 2030)]
    public int PublicationYear { get; set; }
    
    [Column(TypeName = "decimal(18,2)")]
    public decimal Price { get; set; }
    
    public bool IsAvailable { get; set; }
    
    public int CategoryId { get; set; }
    
    [ForeignKey("CategoryId")]
    public Category Category { get; set; }
}

Creating the Database with Migrations

Migrations are a way to keep your database schema in sync with your EF Core model:

Step 1: Enable Migrations

csharp
// In Package Manager Console:
// Add-Migration InitialCreate

This command creates a Migration class that contains code to update the database schema to match your current model.

Step 2: Apply the Migration

csharp
// In Package Manager Console:
// Update-Database

This command applies the migration to create or modify the database according to your model.

Working with Data

Now that our database is set up, let's perform some CRUD operations:

csharp
using System;
using System.Linq;

class Program
{
    static void Main(string[] args)
    {
        // Create some data
        using (var context = new BookStoreContext())
        {
            // Clear previous test data
            context.Books.RemoveRange(context.Books);
            context.Categories.RemoveRange(context.Categories);
            context.SaveChanges();
            
            // Add categories
            var fictionCategory = new Category { Name = "Fiction" };
            var nonFictionCategory = new Category { Name = "Non-Fiction" };
            
            context.Categories.Add(fictionCategory);
            context.Categories.Add(nonFictionCategory);
            context.SaveChanges();
            
            // Add some books
            context.Books.Add(new Book 
            { 
                Title = "The Great Gatsby", 
                Author = "F. Scott Fitzgerald", 
                PublicationYear = 1925,
                Price = 12.99m, 
                IsAvailable = true,
                CategoryId = fictionCategory.CategoryId
            });
            
            context.Books.Add(new Book 
            { 
                Title = "A Brief History of Time", 
                Author = "Stephen Hawking", 
                PublicationYear = 1988,
                Price = 15.99m, 
                IsAvailable = true,
                CategoryId = nonFictionCategory.CategoryId
            });
            
            context.SaveChanges();
        }
        
        // Read and display data
        using (var context = new BookStoreContext())
        {
            Console.WriteLine("All Books:");
            foreach (var book in context.Books.Include(b => b.Category))
            {
                Console.WriteLine($"Title: {book.Title}, Author: {book.Author}, " +
                                 $"Year: {book.PublicationYear}, Category: {book.Category.Name}");
            }
            
            // Query example
            var expensiveBooks = context.Books
                .Where(b => b.Price > 15.00m)
                .OrderBy(b => b.Title)
                .ToList();
                
            Console.WriteLine("\nExpensive Books (>$15):");
            foreach (var book in expensiveBooks)
            {
                Console.WriteLine($"{book.Title} - ${book.Price}");
            }
        }
        
        // Update data
        using (var context = new BookStoreContext())
        {
            var bookToUpdate = context.Books.FirstOrDefault(b => b.Title == "The Great Gatsby");
            if (bookToUpdate != null)
            {
                bookToUpdate.Price = 14.99m;
                context.SaveChanges();
                Console.WriteLine($"\nUpdated the price of {bookToUpdate.Title} to ${bookToUpdate.Price}");
            }
        }
        
        // Delete data
        using (var context = new BookStoreContext())
        {
            var bookToDelete = context.Books.FirstOrDefault(b => b.Author == "Stephen Hawking");
            if (bookToDelete != null)
            {
                context.Books.Remove(bookToDelete);
                context.SaveChanges();
                Console.WriteLine($"\nDeleted book: {bookToDelete.Title}");
            }
            
            // Verify deletion
            var remainingBooks = context.Books.Count();
            Console.WriteLine($"Remaining books in database: {remainingBooks}");
        }
    }
}

Output:

All Books:
Title: The Great Gatsby, Author: F. Scott Fitzgerald, Year: 1925, Category: Fiction
Title: A Brief History of Time, Author: Stephen Hawking, Year: 1988, Category: Non-Fiction

Expensive Books (>$15):
A Brief History of Time - $15.99

Updated the price of The Great Gatsby to $14.99

Deleted book: A Brief History of Time
Remaining books in database: 1

Model First vs. Code First

Let's compare different approaches for Entity Framework:

Approach	Description	Best For
Code First	Create model classes in code first, then generate database	New projects, domain-driven design
Database First	Create the database first, then generate model	Existing databases, database-centric apps
Model First	Design model using visual designer, then generate database	Visual modeling preference

Real-world Application: Building a Library Management System

Let's expand our example to a more comprehensive library management system:

csharp
// Additional model classes
public class Member
{
    public int MemberId { get; set; }
    
    [Required]
    [StringLength(50)]
    public string FirstName { get; set; }
    
    [Required]
    [StringLength(50)]
    public string LastName { get; set; }
    
    [EmailAddress]
    public string Email { get; set; }
    
    public DateTime MemberSince { get; set; }
    
    // Navigation property
    public ICollection<BookLoan> BookLoans { get; set; }
}

public class BookLoan
{
    public int BookLoanId { get; set; }
    
    public int BookId { get; set; }
    public Book Book { get; set; }
    
    public int MemberId { get; set; }
    public Member Member { get; set; }
    
    public DateTime LoanDate { get; set; }
    public DateTime DueDate { get; set; }
    public DateTime? ReturnDate { get; set; }
    
    [StringLength(500)]
    public string Notes { get; set; }
}

// Extended Book class
public class Book
{
    public int BookId { get; set; }
    
    [Required]
    [StringLength(200)]
    public string Title { get; set; }
    
    [Required]
    [StringLength(100)]
    public string Author { get; set; }
    
    [StringLength(20)]
    public string ISBN { get; set; }
    
    public int PublicationYear { get; set; }
    
    [Column(TypeName = "decimal(18,2)")]
    public decimal Price { get; set; }
    
    public bool IsAvailable { get; set; }
    
    public int CategoryId { get; set; }
    public Category Category { get; set; }
    
    // Navigation property
    public ICollection<BookLoan> BookLoans { get; set; }
}

// Updated context
public class LibraryContext : DbContext
{
    public DbSet<Book> Books { get; set; }
    public DbSet<Category> Categories { get; set; }
    public DbSet<Member> Members { get; set; }
    public DbSet<BookLoan> BookLoans { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=LibraryDb;Trusted_Connection=True;");
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Configure Book entity
        modelBuilder.Entity<Book>()
            .HasIndex(b => b.ISBN)
            .IsUnique();
            
        // Configure relationships
        modelBuilder.Entity<BookLoan>()
            .HasOne(bl => bl.Book)
            .WithMany(b => b.BookLoans)
            .HasForeignKey(bl => bl.BookId);
            
        modelBuilder.Entity<BookLoan>()
            .HasOne(bl => bl.Member)
            .WithMany(m => m.BookLoans)
            .HasForeignKey(bl => bl.MemberId);
    }
}

Library System Usage Example

csharp
static void LibrarySystemDemo()
{
    using (var context = new LibraryContext())
    {
        // Add a member
        var member = new Member
        {
            FirstName = "John",
            LastName = "Smith",
            Email = "[email protected]",
            MemberSince = DateTime.Now.AddMonths(-6)
        };
        context.Members.Add(member);
        
        // Add a fiction category
        var fictionCategory = new Category { Name = "Science Fiction" };
        context.Categories.Add(fictionCategory);
        context.SaveChanges();
        
        // Add a book
        var book = new Book
        {
            Title = "Dune",
            Author = "Frank Herbert",
            ISBN = "9780441172719",
            PublicationYear = 1965,
            Price = 18.99m,
            IsAvailable = true,
            CategoryId = fictionCategory.CategoryId
        };
        context.Books.Add(book);
        context.SaveChanges();
        
        // Create a loan
        var loan = new BookLoan
        {
            BookId = book.BookId,
            MemberId = member.MemberId,
            LoanDate = DateTime.Now,
            DueDate = DateTime.Now.AddDays(14),
            Notes = "First time borrower"
        };
        context.BookLoans.Add(loan);
        
        // Update book availability
        book.IsAvailable = false;
        context.SaveChanges();
        
        // Query to find overdue books
        var overdueLoanQuery = context.BookLoans
            .Include(bl => bl.Book)
            .Include(bl => bl.Member)
            .Where(bl => bl.DueDate < DateTime.Now && bl.ReturnDate == null)
            .Select(bl => new {
                BookTitle = bl.Book.Title,
                MemberName = bl.Member.FirstName + " " + bl.Member.LastName,
                DueDate = bl.DueDate,
                DaysLate = EF.Functions.DateDiffDay(bl.DueDate, DateTime.Now)
            });
            
        var overdueLoans = overdueLoanQuery.ToList();
        
        foreach (var overdue in overdueLoans)
        {
            Console.WriteLine($"Overdue book: {overdue.BookTitle} borrowed by {overdue.MemberName}");
            Console.WriteLine($"Due date: {overdue.DueDate:d}, Days late: {overdue.DaysLate}");
        }
    }
}

Best Practices for Code-first Development

Use Meaningful Names:
- Choose class and property names that clearly reflect your domain entities
Configure Relationships Properly:
- Define all relationships (one-to-many, many-to-many) explicitly
Use Data Annotations or Fluent API:
- Choose a consistent approach for configuring your model
Organize Migrations:
- Create focused migrations for each logical change to your model
- Give migrations descriptive names (e.g., AddBookPublisherTable)
Seed Data:
- Use the OnModelCreating method or separate migration to seed initial data
Separate DbContext from Domain Models:
- Keep your domain models clean and focused on business logic
Use Database Transactions:
- Wrap multiple operations in transactions for data integrity

Summary

The Code-first approach in Entity Framework offers developers a powerful way to define and manage database structures through C# code. In this tutorial, we've learned:

How to create model classes to represent database entities
How to define relationships between entities
How to configure entities using Data Annotations and Fluent API
How to create and apply migrations to build and update the database
How to perform CRUD operations using the context
Real-world application with a library management system

Code-first development is particularly beneficial for projects where the domain model drives design, allowing you to focus on business logic while Entity Framework handles the database interactions. It promotes clean architecture and makes database development more accessible to object-oriented developers.

Additional Resources

Exercises

Basic: Create a simple blogging system with Blog and Post entities with a one-to-many relationship.
Intermediate: Add a tagging system to the blog with a many-to-many relationship between Post and Tag.
Advanced: Implement soft delete functionality where records are marked as deleted rather than actually removed from the database.
Challenge: Create a versioning system that keeps track of all changes to a post over time.

Happy coding!

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Prerequisites​

Getting Started with Code-first​

Step 1: Set Up Your Project​

Step 2: Define Your Domain Models​

Step 3: Create a Database Context​

Adding Data Annotations​

Creating the Database with Migrations​

Step 1: Enable Migrations​

Step 2: Apply the Migration​

Working with Data​

Model First vs. Code First​

Real-world Application: Building a Library Management System​

Library System Usage Example​

Best Practices for Code-first Development​

Summary​

Additional Resources​

Exercises​