CodeFirstDbGenerator 1.1.1

CodeFirstDbGenerator

Motivation: allow to create migrations and update database without installing Entity Framework, for libraries like Dapper.

Installation

Db Supported:

• Sql Server

Install packages : CodeFirstDbGenerator, CodeFirstDbGenerator.SqlServer, CodeFirstDbGenerator.Tools

install-package CodeFirstDbGenerator
install-package CodeFirstDbGenerator.SqlServer
dotnet tool install --global CodeFirstDbGenerator.Tools

Recommendation : Create a class Library .NET 5 or .NET 6 for Migrations

Create a migration

Use the tool to generate migrations

dotnet cf add-migration InitialCreate -p path/to/project

Define the entites to create/update the database. CF will discover columns, primary keys and foreign keys with Data Annotations

[Migration("637727087400479557_InitialCreate")]
public class InitialCreate : Migration
{
    protected override MigrationOptions GetOptions()
    {
        return new SqlServerMigrationOptions();
    }

    protected override MigrationRunner GetMigrationRunner()
    {
        var runner = new SqlServerMigrationRunner("Server=(localdb)\\mssqllocaldb;Database=TestMG;Trusted_Connection=True;MultipleActiveResultSets=true");
        runner.DropDatabase = true; // allows to drop existing database
        return runner;
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable<Company>();
        migrationBuilder.CreateTable<Employee>();
        // a stored procedure
        migrationBuilder.Sql(@"
            CREATE PROC usp_GetCompany
                @CompanyId int
            AS
            BEGIN
                SELECT *
                FROM Companies
                WHERE CompanyId = @CompanyId
            END
            GO
        ");
    }

}

// [Table("tbl_Companies")] allows to define the name of the table
public class Company
{
    [Key]
    public int CompanyId { get; set; } // key

    [StringLength(100)]
    public string Name { get; set; } //required

    [Required]
    //[Column("MyPostalCode")] allows to rename the column
    public string PostalCode { get; set; } // required with data annotations

    public string? Address { get; set; }

    [MaxLength(50)] // or [StringLength(50)]
    public string? City { get; set; } // not required

    [Timestamp]
    public byte[] RowVersion { get; set; }

    public List<Employee> Employees { get; set; } = new(); // ignored
}

public class Employee
{
    [Key]
    public int EmployeeId { get; set; } // recommendation: make Key unique, dont use names like "Id" for all primary keys

    public string FirstName { get; set; }

    public string LastName { get; set; }

    // [ForeignKey("Companies")] use foreign key attribute if column name does not match with principal column name
    public int CompanyId { get; set; }

    public Company Company { get; set; } // ignored

    [NotMapped] // ignored
    public string FullName
    {
        get { return $"{FirstName} {LastName}"; }
    }
}

DataAnnotations Attributes:

• Key: for primary key (identity if int, short or long)
• Table: to define table name
• Column: to define column name
• DatabaseGenerated + identity option: for a column identity
• StringLength ou MaxLength: to define string length (exeample "navarchar(100)")
• ForeignKey: to specify the principal table name

Its possible to define columns (default, value, unique, column type, etc.), multipe primary keys or foreign keys with fluent api. Example:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable<Company>();
    migrationBuilder
        .CreateTable<Employee>()
        .Column(x => x.FirstName, defaultValue: "X")
        .ForeignKey(column: x => x.CompanyId,
                principalTable: "Companies",
                principalColumn: "CompanyId",
                onUpdate: ReferentialAction.Cascade,
                onDelete: ReferentialAction.Cascade);

    migrationBuilder.CreateTable<Student>().PrimaryKey(columns: x => new { x.StudentKey, x.AdmissionNum });
}

Migrations History:

By default a table "__CFMigrationsHistory" is created. Its possible to create a custom MigrationsHistoryManager. Just implement IMigrationsHistoryManager and change the manager provided

protected override MigrationRunner GetMigrationRunner()
{
    var runner = new SqlServerMigrationRunner("Server=(localdb)\\mssqllocaldb;Database=TestMG;Trusted_Connection=True;MultipleActiveResultSets=true"
        , new MyMigrationsHistoryManager());
    return runner;
}

Update database

With tool

dotnet cf update-database -p path/to/library.dll

Or in code

MigrationRunner.UpdateDatabase();
// or with path
MigrationRunner.UpdateDatabase("path/to/library.dll");