CodeFirstDbGenerator.SqlServer 1.2.0.2

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] // or not identity [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    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 JsonFileMigrationsManager());
    return runner;
}

Sample with JSON file

public class JsonFileMigrationsManager : ISqlServerMigrationsHistoryManager
{
    private const string path = "path/to/file.json";
    private List<JsonMigration> _migrations;

    public JsonFileMigrationsManager()
    {
        _migrations = LoadMigrations();
    }

    public void ClearHistory(SqlServerMigrationRunnerContext context)
    {
        _migrations.Clear();
        Save(_migrations);
    }

    public void DeleteMigration(string migrationId, SqlServerMigrationRunnerContext context)
    {
        var migration = _migrations.FirstOrDefault(x => x.MigrationId == migrationId);
        if (migration != null)
        {
            _migrations.Remove(migration);
            Save(_migrations);
        }
    }

    public void InsertMigration(string migrationId, SqlServerMigrationRunnerContext context)
    {
        if (!MigrationExists(migrationId, context))
        {
            _migrations.Add(new JsonMigration { MigrationId = migrationId });
            Save(_migrations);
        }
    }

    public bool MigrationExists(string migrationId, SqlServerMigrationRunnerContext context)
    {
        var migration = _migrations.FirstOrDefault(x => x.MigrationId == migrationId);
        return migration != null;
    }

    private List<JsonMigration> LoadMigrations()
    {
        if (File.Exists(path))
        {
            var json = File.ReadAllText(path);
            return JsonSerializer.Deserialize<List<JsonMigration>>(json);
        }
        return new List<JsonMigration>();
    }

    private void Save(List<JsonMigration> history)
    {
        string json = JsonSerializer.Serialize(history, new JsonSerializerOptions { WriteIndented = true });
        File.WriteAllText(path, json);
    }
}
public class JsonMigration
{
    public string MigrationId { get; set; }

    public string CreatedAt { get; set; } = DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss");
}

Update database

With tool

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

Or in code

var resolver = new MigrationResolver();

// with executing assembly
resolver.UpdateDatabase();

// or with an assembly
resolver.UpdateDatabase(typeof(MyMigration).Assembly);

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

With Custom Resolver. Example Microsoft.Extensions.DependencyInjection

// Example: services.AutoRegisterMigrations(typeof(InitialCreate).Assembly);

public static class IServiceCollectionExtensions
{
    public static void AutoRegisterMigrations(this IServiceCollection services, Assembly assembly)
    {
        var types = assembly.GetExportedTypes();
        foreach (var type in types)
        {
            if (type.BaseType.Name == typeof(Migration).Name && !type.IsAbstract)
            {
                if (!services.Any(x => x.ServiceType == type))
                {
                    services.AddTransient(type);
                }
            }
        }
    }

    public static void AutoRegisterMigrations(this IServiceCollection services)
        => AutoRegisterMigrations(services, Assembly.GetExecutingAssembly());
}

public class DIMigrationResolver : MigrationResolver
{
    private readonly IServiceProvider _serviceProvider;

    public DIMigrationResolver(IServiceProvider serviceProvider)
    {
        _serviceProvider = serviceProvider;
    }

    protected override Migration CreateMigration(Type migrationType)
    {
        var migration = _serviceProvider.GetRequiredService(migrationType) as Migration;
        return migration;
    }
}

Register the custom migration resolver and inject. Allows to inject options in migrations with the default connection string.

public void Configure(IApplicationBuilder app, IWebHostEnvironment env, DIMigrationResolver migrationResolver)
{
    migrationResolver.UpdateDatabase(typeof(InitialCreate).Assembly);

    // etc.
}