CodeFirstDbGenerator.MySql 4.0.0

CodeFirstDbGenerator

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

Db Supported:

• Sql Server (CodeFirstDbGenerator.SqlServer)
• Sqlite (CodeFirstDbGenerator.Sqlite)
• MySql (CodeFirstDbGenerator.MySql)
• Or write your own library

Languages supported:

• C#
• VB

Installation

Install packages : CodeFirstDbGenerator (Migration base class)

install-package CodeFirstDbGenerator

SqlServer, Sqlite, MySql

install-package CodeFirstDbGenerator.SqlServer

And CodeFirstDbGenerator.Tools (Visual Studio Package Manager Console)

install-package CodeFirstDbGenerator.Tools

.. or dotnet-cf (dotnet tool)

dotnet tool install --global dotnet-cf

Tip: To uninstall a previous version of the tool and list the tools

dotnet tool uninstall -g dotnet-cf 
dotnet tool list -g

Or add package references to project

<PackageReference Include="CodeFirstDbGenerator" Version="4.0.0" />
<PackageReference Include="CodeFirstDbGenerator.SqlServer" Version="4.0.0" />
<PackageReference Include="CodeFirstDbGenerator.Tools" Version="4.0.0">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>

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

Create a migration

3 choices:

• Package Manager Console
• dotnet cf tool
• Code

Package Manager Console

Add-Migration InitialCreate

... Or dotnet cf tool

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

... Or code : create a class that inherits from Migration. Dont forget the migration attribute.

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] or auto discovered if property name equals ClassName + Id (One Key attribute per class)
    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();
}

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; }

    public int CompanyId { get; set; } // foreign key auto detected

    public Company Company { get; set; } 

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

DataAnnotations Attributes:

• Key: for primary key (identity if int, short or long). For composite primary key use fluent api.
• Table: to define table name
• Column: to define column name and type name
• DatabaseGenerated + identity option: for a column identity
• StringLength or MaxLength: to define string length (exeample "navarchar(100)")
• ForeignKey: to specify the property 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 });
}

Primary Key

Auto detected if property name equals "Class name + Id"

public class Author
{
    public int AuthorId { get; set; }
}

Else use the Key Attribute (auto incremented if the type is short, int or long). Use DatabaseGenerated attribute with option None to cancel incrementation.

public class Author
{
    [Key]
    public int TheAuthorId { get; set; }
}

Composite primary key

public class Category
{
    public int CategoryId1 { get; set; }

    public int CategoryId2 { get; set; }

    public string CategoryName { get; set; }
}

... with Fluent api

migrationBuilder.CreateTable<Category>().PrimaryKey(x => new { x.CategoryId1, x.CategoryId2 });

Relations

Foreign key. Auto detected if property equals "Principal" class name + Id

public class Author
{
    public int AuthorId { get; set; }
}

public class Post
{
    public int PostId { get; set; }

    public int AuthorId { get; set; }
}

Else use Foreign key Attribute

public class Author
{
    public int AuthorId { get; set; }
}

public class Post
{
    public int PostId { get; set; }

    [ForeignKey(nameof(TheAuthor))]
    public int TheAuthorId { get; set; }

    public Author TheAuthor { get; set; }
}

Or

public class Post
{       
    public int PostId { get; set; }

    public int TheAuthorId { get; set; }

    [ForeignKey(nameof(TheAuthorId))]
    public Author TheAuthor { get; set; }
}

Many to Many relations : a table "PostCategory" is created

public class Post
{
    public int PostId { get; set; }

    // etc.

    public List<Category> Categories { get; set; }
}

public class Category
{
    public int CategoryId { get; set; }

    public string CategoryName { get; set; }

    public List<Post> Posts { get; set; }
}

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";

var path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "CodeFirstDbGenerator\\SampleDb__MigrationsHistory.json");
runner.History = new JsonMigrationsHistory(path);
runner.UpdateDatabase();

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

Define/ Use a runner

3 choices:

• RunnerOptions
• Service Collection
• Code

RunnerOptions

public class SqlServerOptions : RunnerOptions<SqlServerRunner>
{
    public override string GetConnectionString()
        => "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Integrated Security=true";

    public override bool? GetVerbose()
        => true;

    public override ConstraintStyle? GetConstraintStyle()
        => ConstraintStyle.CreateTable;
}

// Sqlite
public class SqliteOptions : RunnerOptions<SqliteRunner>
{
    public override string GetConnectionString()
        => @"Data Source=C:\db\Sample.db;Cache=Shared"; // dont use relative path
}

// MySql
public class MySqlOptions : RunnerOptions<MySqlRunner>
{
    public override string GetConnectionString() => "server=localhost;database=SampleDb;uid=root";
}

... Or Service collection

 services.AddRunner<SqlServerRunner>(options => options.WithConnection(Configuration.GetConnectionString("DefaultConnection")));

If you want to inject dependencies in migrations or runner options ctor. You have to register the services with the Service collection (or the container used)

Tip : auto register migrations with Microsoft.Extensions.DependencyInjection

services.AutoRegisterMigrations(typeof(InitialCreate).Assembly);

.. Or code

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

Its possible to set the service provider. Example

runner.ServiceProvider = app.ApplicationServices;

Update database

3 choices :

• Package Manager Console (require CodeFirstDbGenerator.Tools)
• dotnet cf (require dotnet-cf tool)
• Code

With Package Manager Console and dotnet cf... CodeFirstDbGenerator search RunnerOptions or Runners registered in Service provider.

Package Manager Console

Update-Database

If you are not sure, you can provide the project name and the startup project name (for dependency injection). The Tab key provides intelliSense.

Update-Database -Project MyProject -StartupProject MyStartupProject

Its possible to provide a connection string

Update-Database -ConnectionString "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;"

Note: Sometimes its better to provide explicitly the startup project (when the current startup project is not the project expected or when the solution has multiple projects launched).

... Or dotnet cf 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

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"

... Or Code

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

// with entry assembly
runner.UpdateDatabase();

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

Tip configure the host builder factory for a wpf app for example

Install packages

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

Add a CreateHostBuilder function to the entry point of the application

using CodeFirstDbGenerator;
using CodeFirstDbGenerator.SqlServer;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Sample.Data;
using System;
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(Sample_Migration).Assembly);
                services.AddScoped<MainWindow>();
                services.AddRunner<SqlServerRunner>().WithConnection(() => "Server=(localdb)\\mssqllocaldb;Database=TestWpfDi;Trusted_Connection=True;");
            });
    }
}

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>();       
    }
}

Create a library for a Database

For example MySql. Create a library

• Create a runner (MySqlRunner for example) that inherits from Runner
  • Create a TypeTranslator (MySqlTypeTranslator for example). The service receives a clr type (string, int, short, etc.) and returns the sql type (varchar(255), interger, tinyint, etc.) with length
  • Create a TableHistory (MySqlTableHistory for example) that inherits from TableHistory
  • Create a SqlQueryProvider (MySqlQueryProvider for example) that inherits from SqlQueryProvider base class
  • Create a DatabaseCreator (MySqlDatabaseCreator for example) that inherits from DatabaseCreator base class