πŸ“‘ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Model Changes
  5. Migration Class Walkthrough
  6. Applying & Rolling Back the Migration
  7. Sample Code Blocks & Usage Examples
  8. UML Diagram
  9. Error Handling & Return Types
  10. Third-party Integrations
  11. Conclusion

🌟 1. Overview & Purpose

This migration file, 20230717144622_AddWorkEmailToEmployeeTable.cs, is part of the IrbyImaging.DbContext project. It introduces two new columnsβ€”UserName and WorkEmailβ€”to the existing irby.Employee table in the database schema.

  • Why? To store each employee’s login UserName and work email address in the database for use in authentication, notifications, reporting, etc.
  • When to use?
    • When the codebase or business requirements demand capturing and persisting these new pieces of information.
    • Prior to updating the data model and related service layers to leverage these new fields.

βš™οΈ 2. Dependencies & Technologies

Technology / Package Purpose
Microsoft.EntityFrameworkCore.Migrations Core EF migrations API to build and apply schema changes.
.NET 6+ (or higher) Platform for running EF Core and the DbContext.
C# 10+ Language for writing the migration class.
SQL Server (assumed) Target relational database; migrations generate T-SQL.

Note: The schema name irby is application-specific and must exist in your database.


πŸ”‘ 3. Key Functional Areas

  • Schema Evolution
    • Introduce new columns without dropping or rebuilding the entire table.
  • Safe Rollback
    • Support down‑migration to remove the columns in case of rollback.
  • Declarative API
    • Use EF Core’s fluent migrationBuilder API for changes.

πŸ—„οΈ 4. Data Model Changes

After applying this migration, the Employee entity effectively has two new properties:

public class Employee
{
    public int EmployeeId { get; set; }
    public string FullName    { get; set; }
    // ── New Fields ─────────────────────────────────────
    public string? UserName   { get; set; }    // Added
    public string? WorkEmail  { get; set; }    // Added
    // ──────────────────────────────────────────────────
}
  • UserName (nvarchar(max), nullable)
    • Typically used for login or SSO identity.
  • WorkEmail (nvarchar(max), nullable)
    • For sending internal communications, notifications, etc.

πŸ“‚ 5. Migration Class Walkthrough

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    /// <inheritdoc />
    public partial class AddWorkEmailToEmployeeTable : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "UserName",
                schema: "irby",
                table: "Employee",
                type: "nvarchar(max)",
                nullable: true);

            migrationBuilder.AddColumn<string>(
                name: "WorkEmail",
                schema: "irby",
                table: "Employee",
                type: "nvarchar(max)",
                nullable: true);
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "UserName",
                schema: "irby",
                table: "Employee");

            migrationBuilder.DropColumn(
                name: "WorkEmail",
                schema: "irby",
                table: "Employee");
        }
    }
}

5.1 Up() Method

  • Purpose: Apply schema changes (add two new columns).
  • Key API Calls:
    • migrationBuilder.AddColumn<string>(...)
      • name: Column name in SQL
      • schema: Database schema
      • table: Target table
      • type: SQL data type
      • nullable: Whether the column allows NULLs

5.2 Down() Method

  • Purpose: Reverse the Up migration.
  • Key API Calls:
    • migrationBuilder.DropColumn(...)

πŸš€ 6. Applying & Rolling Back the Migration

  1. Add the Migration
    dotnet ef migrations add AddWorkEmailToEmployeeTable
    
  2. Apply to Database
    dotnet ef database update
    
  3. Rollback (if needed)
    dotnet ef database update LastGoodMigrationName
    

πŸ’‘ Tip: Always run migrations in a transactional environment and backup critical databases before applying schema changes.


πŸ“‹ 7. Sample Code Blocks & Usage Examples

Reading & Writing the New Fields

using var context = new IrbyDbContext();

// Create a new employee
var employee = new Employee
{
    FullName  = "Jane Doe",
    UserName  = "jdoe",
    WorkEmail = "[email protected]"
};

context.Employees.Add(employee);
context.SaveChanges();

// Query by WorkEmail
var result = context.Employees
    .FirstOrDefault(e => e.WorkEmail == "[email protected]");

πŸ“ˆ 8. UML Diagram

Below is a simplified UML class diagram showing the Employee entity after this migration:

@startuml
class Employee {
    +int    EmployeeId
    +string FullName
    +string? UserName
    +string? WorkEmail
}
@enduml

Employee UML


πŸ›‘οΈ 9. Error Handling & Return Types

  • EF Core Migrations
    • Will throw MigrationException if SQL execution fails.
    • Up/Down methods return void; EF Core handles transaction management under the hood.
  • Best Practices:
    • Verify migrations in a staging environment.
    • Inspect generated SQL via dotnet ef migrations script.

πŸ”Œ 10. Third-party Integrations

  • EF Core
    • Entity Framework Core’s migration engine.
  • SQL Server (or any EF‑supported provider)
    • Executes the generated SQL DDL.

No other external integrations are introduced by this migration.


🏁 11. Conclusion

This migration is a straightforward and reversible schema update to support new business requirements for tracking employee usernames and work email addresses. By leveraging EF Core’s migration system:

  • You ensure repeatable, version-controlled database changes.
  • You maintain consistency between your code’s data model and the underlying database.

Once applied, you can immediately read/write the UserName and WorkEmail fields in your application code, enabling features like user authentication and email-based notifications.

πŸŽ‰ Happy coding!