CodeFirstDbGenerator.SqlServer 2.0.8

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

Tips:

To uninstall a previous version of the tool

dotnet tool uninstall -g codefirstdbgenerator.tools 

List the tools

dotnet tool list -g

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

Create a migration

Use the tool to generate migrations

dotnet cf migrations add 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
{
    public override MigrationOptions GetOptions()
    {
       return new MigrationOptions
       {
           DropDatabase = true
       };
    }

    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

    [Column(TypeName ="ntext")] // allows to change the type
    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.

To use JsonMigrationsHistory:

var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";
runner.History = new JsonMigrationsHistory("path/to/file.json");
runner.UpdateDatabase();

Its possible to create a custom MigrationsHistory. Just implement IMigrationsHistory and change the history.

Update database

With tool

dotnet cf database update -c "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true" -a path/to/assembly.dll

With a library that contains migrations and a startup assembly (Application Web Asp.Net Core) for example :

dotnet cf database update -c "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true" -a path/to/assembly.dll -s path/to/startup-assembly.dll

Note: add "-m json" to use JsonMigrationsHistory

Or in code

var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";

// with entry assembly
runner.UpdateDatabase();

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

Tip : auto register migrations with with 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 != null && 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());
}

Inject the runner to update the database

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

    // etc.
}

Its possible to set the service provider. Example

runner.ServiceProvider = app.ApplicationServices;

Tip: create a bash file to execute multiple commands. Example test.sh and use GIT Bash 'sh test.sh'

echo '> Sample1'
dotnet cf database update -c "Server=(localdb)\mssqllocaldb;Database=Sample1Db;Trusted_Connection=True;MultipleActiveResultSets=true" -a "C:\Samples\Sample1\bin\Debug\net5.0\Sample1.dll"
echo '> Sample2'
dotnet cf database update -c "Server=(localdb)\mssqllocaldb;Database=Sample2Db;Trusted_Connection=True;MultipleActiveResultSets=true" -a "C:\Samples\Sample2\bin\Debug\net6.0\Sample2.dll"

Tip: inject services with a Wpf app in migrations. Provide a CreateHostBuilder function

Install packages :

<ItemGroup>
	<PackageReference Include="Microsoft.Extensions.Hosting" Version="6.0.0" />
</ItemGroup>

using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Sample.Data;
using System.Windows;

namespace WpfDi
{
    public partial class App : Application
    {
        private IHost host;

        public App()
        {
            host = CreateHostBuilder().Build();
        }

        private void Application_Startup(object sender, StartupEventArgs e)
        {
            host.Start();

            var shell = host.Services.GetRequiredService<MainWindow>();
            shell.Show();
        }

        public static IHostBuilder CreateHostBuilder() =>
            Host.CreateDefaultBuilder()
            .ConfigureServices((context, services) =>
            {
                services.AddScoped<IMyService,MyService>();
                services.AutoRegisterMigrations(typeof(SampleMigration).Assembly);
                services.AddScoped<MainWindow>();
            })
            .ConfigureAppConfiguration((context, configurationBuilder) =>
            {
                //configurationBuilder.SetBasePath(context.HostingEnvironment.ContentRootPath);
                //configurationBuilder.AddJsonFile("appsettings.json", optional: false);
            });
    }
}

Sample migration with a dependency

[Migration("SampleMigration")]
public class SampleMigration : Migration
{
    public A_Migration(IMyService myService)
    {
        MyService = myService;
    }

    public IMyService MyService { get; }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
           
    }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable<Company>();       
    }
}