Database Setup & Migrations

Relevant source files

The following files were used as context for generating this wiki page:

• Data/UserDBContext.cs
• Data/UserDBContextFactory.cs
• Migrations/20250104092740_New.cs
• Migrations/20250108150417_AddResetTokenToUser.cs
• Migrations/UserDBContextModelSnapshot.cs
• Program.cs
• Configuration/UserConfiguration.cs
• Configuration/ProductConfiguration.cs
• Seeders/DatabaseSeeder.cs

This document outlines the database architecture for the WEBAPI project, which leverages Entity Framework Core (EF Core) for data access and schema management. The system is built around a central DbContext, uses a code-first approach with fluent API configurations for defining the data model, and relies on EF Core Migrations to incrementally evolve the database schema.

The core of the data layer is the UserDBContext, which serves as the bridge between the application's entity models and the SQL Server database. It includes DbSet properties for all major entities such as User, Product, Order, and Category. Configuration for each entity is encapsulated in separate IEntityTypeConfiguration classes, promoting a clean and maintainable structure within the OnModelCreating method.

Core Components

The database setup is comprised of several key components that work together to manage data persistence and schema.

UserDBContext

The UserDBContext class is the primary API for interacting with the database. It inherits from Microsoft.EntityFrameworkCore.DbContext.

• Entity Sets: It defines DbSet<T> properties for each entity, which represent the tables in the database.
  • public DbSet<User> User { get; set; }
  • public DbSet<Order> Orders { get; set; }
  • public DbSet<Product> Products { get; set; }
  • public DbSet<Admin> Admins { get; set; }
  • public DbSet<OrderItem> OrderItems { get; set; }
  • public DbSet<Category> Categories { get; set; }
• Model Creation: The OnModelCreating method is used to apply all entity configurations from separate classes and to apply data seeding.

Sources: Data/UserDBContext.cs:9-37

DbContext Registration

The UserDBContext is registered as a service in the application's dependency injection container. This allows it to be injected into other services, such as UserService. The connection string UserDataBase is retrieved from the application's configuration (appsettings.json).

// File: Program.cs

builder.Services.AddDbContext<UserDBContext>(option =>
{
    option.UseSqlServer(builder.Configuration.GetConnectionString("UserDataBase"));
});

Sources: Program.cs:25-28

Design-Time DbContext Factory

The UserDBContextFactory class implements IDesignTimeDbContextFactory<UserDBContext>. Its purpose is to enable EF Core design-time tools (e.g., for creating migrations) to create an instance of UserDBContext. It reads the UserDataBase connection string from appsettings.json to configure the DbContextOptions.

This diagram illustrates the flow for creating a DbContext instance during design time.

Entity Configuration

Entity configurations are managed using classes that implement IEntityTypeConfiguration<T>. This approach decouples the model configuration from the DbContext class itself, making the setup cleaner. These configurations are applied within UserDBContext.OnModelCreating.

// File: Data/UserDBContext.cs
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Apply all configurations
    modelBuilder.ApplyConfiguration(new UserConfiguration());
    modelBuilder.ApplyConfiguration(new OrderConfiguration());
    modelBuilder.ApplyConfiguration(new ProductConfiguration());
    modelBuilder.ApplyConfiguration(new AdminConfiguration());
    modelBuilder.ApplyConfiguration(new CategoryConfiguration());
    modelBuilder.ApplyConfiguration(new OrderItemConfiguration());
    // ...
}

Sources: Data/UserDBContext.cs:15-22

This class diagram shows how UserDBContext uses various configuration classes.

User Configuration

The User entity is configured in UserConfiguration.

Property	Type	Constraints	Description
Id	int	Primary Key	Unique identifier for the user.
Email	string	Required, MaxLength(100)	User's email address.
UserName	string	Required, MaxLength(50)	User's chosen username.
Password	string	Required, MaxLength(256)	Hashed password for the user.
Role	bool	Required	Defines user role (true for admin-like).
ResetToken	string	Nullable	Token for password reset functionality.
ResetTokenExpiration	DateTime?	Nullable	Expiration date for the reset token.

Sources: Configuration/UserConfiguration.cs, Migrations/20250108150417_AddResetTokenToUser.cs:14-23

Product Configuration

The Product entity is configured in ProductConfiguration.

Property	Type	Constraints	Description
Id	int	Primary Key	Unique identifier for the product.
Title	string	Required, MaxLength(100)	Product title.
Name	string	Required, MaxLength(200)	Product name.
Description	string	MaxLength(500)	Detailed product description.
Price	decimal	Required, decimal(18,2)	Price of the product.
CategoryId	int?	Foreign Key (to Category)	The category this product belongs to.

Sources: Configuration/ProductConfiguration.cs, Migrations/UserDBContextModelSnapshot.cs:193-238

Database Schema & Relationships

The database contains several related entities to model an e-commerce system. The relationships are defined via foreign keys and navigation properties.

The following Entity-Relationship diagram shows the overall database schema.

Migrations

The project uses EF Core Migrations to manage database schema changes in a structured way. Each migration file contains the C# code to apply (Up method) and revert (Down method) a schema change.

This flowchart shows the general workflow for applying a database migration.

Initial Schema Creation

The migration 20250104092740_New is the initial migration that sets up the database schema. Its Up method contains CreateTable calls for all the initial entities: Admins, Categories, User, Product, and Orders. It also defines primary keys, foreign keys, and constraints for each table.

Sources: Migrations/20250104092740_New.cs:11-140

Schema Evolution

Later migrations modify the existing schema. For example, the 20250108150417_AddResetTokenToUser migration adds two new nullable columns, ResetToken and ResetTokenExpiration, to the User table to support password reset functionality.

// File: Migrations/20250108150417_AddResetTokenToUser.cs
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "ResetToken",
        table: "User",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<DateTime>(
        name: "ResetTokenExpiration",
        table: "User",
        type: "datetime2",
        nullable: true);
    // ...
}

Sources: Migrations/20250108150417_AddResetTokenToUser.cs:12-28

Data Seeding

The application uses EF Core's data seeding feature to populate the database with initial data. This is handled by the DatabaseSeeder static class, which contains an extension method Seed for the ModelBuilder.

The seeder is configured to add a default user to the User table. This Seed method is called from UserDBContext.OnModelCreating.

// File: Seeders/DatabaseSeeder.cs
public static void Seed(this ModelBuilder modelBuilder)
{
    _ = modelBuilder.Entity<User>().HasData(
        new User()
        {
            Id = 1,
            Email = "chihtt123@gmail.com",
            UserName = "chi",
            Password = "123",
            Role = true
        }
    );
}

Sources: Seeders/DatabaseSeeder.cs:8-20, Data/UserDBContext.cs:29