CodeFirstDbGenerator.Sqlite 5.8.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|MariaDB (CodeFirstDbGenerator.MySql)
• PostgreSql (CodeFirstDbGenerator.Npgsql)
• Or write your own library

Languages supported:

• C#

Installation

SqlServer, Sqlite, MySql, PostgreSql

install-package CodeFirstDbGenerator.SqlServer

CodeFirstDbGenerator is installed as dependency

install-package CodeFirstDbGenerator

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.SqlServer" Version="5.8.0" />
<PackageReference Include="CodeFirstDbGenerator.Tools" Version="5.2.1">
    <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
</PackageReference>

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

Generate entities from existing/ updated database with Entity Generator (dotnet tool)

• dotnet-eg

Create a DbModel

With On Configuring

public class ApplicationDbModel : DbModel
{
    public override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Category>();
        modelBuilder.Entity<Author>();
        modelBuilder.Entity<Post>();
    }

    public override void OnConfiguring(DbModelOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=Sample;Trusted_Connection=True;MultipleActiveResultSets=true");
    }
}

With ServiceCollection Extensions

public class ApplicationDbModel : DbModel
{
    public ApplicationDbModel(DbModelOptions<ApplicationDbModel> options)
        : base(options)
    { }

    public override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Category>();
        modelBuilder.Entity<Author>();
        modelBuilder.Entity<Post>();
    }
}

CF autodetects data annotations, primary keys, foreign keys and relations by default. Also Fluent Api can be used.

Data Annotations 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

// [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}"; }
    }
}

Primary Key

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

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

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

Fluent Api

Allows to define composite key for example

modelBuilder.Entity<Category>().HasKey(x => new { x.CategoryId1, x.CategoryId2 });

Methods

• ToTable : allows to set the table name
• HasKey: to define the primary key
• HasForeignKey: to add a foreign key
• HasIndex: to add index
• HasColumnName: to change the column name
• HasColumnType: to change the column type
• IsRequired
• IsUnique
• HasDefaultValue
• etc.

Samples :

modelBuilder.Entity<Author>().ToTable("MyAuthors");
modelBuilder.Entity<Category>().Property(x => x.Name).HasColumnName("CategoryName");
modelBuilder.Entity<Post>().Property(x => x.Content).HasColumnType("text");
modelBuilder.Entity<Post>().HasForeignKey(x => x.TheAuthorId, "Authors", "AuthorId", onDelete: ReferentialStrategy.Cascade);
modelBuilder.Entity<Post>().HasIndex(x => x.Title);

Configuration

• With ServiceCollection extensions
• Or override OnConfiguring DbModel method

ServiceCollection. Example for SqlServer

services.AddDbModel<ApplicationDbModel>(options =>options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Add a constructor with DbModelOptions like this

public class ApplicationDbModel : DbModel
{
    public ApplicationDbModel(DbModelOptions<ApplicationDbModel> options)
        : base(options)
    { }
}

Example for Sqlite

services.AddDbModel<SqliteDbModel>(options => options.UseSqlite(@"Data Source=C:\Db\Sample.db; Cache = Shared")); // dont use relative path

OnConfiguring

public class ApplicationDbModel : DbModel
{
    public override void OnConfiguring(DbModelOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=Sample;Trusted_Connection=True;MultipleActiveResultSets=true");
    }

    // etc.
}

Add Migration

3 choices :

• Package Manager Console (require CodeFirstDbGenerator.Tools)
• dotnet cf (require dotnet-cf tool)
• Code (not recommended) : do not forget Migration and DbModel attributes

Package Manager Console . First select the project with the DbModel in the Package Manager Console then

Add-Migration InitialCreate

Add-Migration InitialCreate -StartupProject SampleWeb -DbModel ApplicationDbModel

A migration (20220119222431_InitialCreate for example) is generated in a directory Migrations.

Note: It's possible to remove last migrations.

... Or dotnet cf tool

dotnet cf migrations add InitialCreate -a path/to/assembly.dll

Update Database

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

Package Manager Console

Update-Database

Update-Database -DbModel ApplicationDbModel -Verbose

Sample to rollback to a migration

Update-Database -Migration 20220119222431_InitialCreate

... Or dotnet cf tool

dotnet cf database update -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 -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 -a "C:\Samples\Sample1\bin\Debug\net5.0\Sample1.dll"
echo '> Sample2'
dotnet cf database update -a "C:\Samples\Sample2\bin\Debug\net6.0\Sample2.dll"

Custom Migrations

It's possible to generate an empty migration.

CodeFirstDbGenerator cannot currently detect renamed properties and entites. So, to not lose data, use RenameTable and RenameColumn migration operations. (note: Sqlite and MySql use temp tables).

RenameTable

public partial class RenameTableToMyCategories : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameTable("Categories", "MyCategories");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameTable("MyCategories", "Categories");
    }
}

RenameColumn: to not lose data (Sql Server)

public partial class RenameColumnToCategoryName : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn("Categories", "Name", "CategoryName");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn("Categories", "CategoryName", "Name");
    }
}

Sql: for stored procedure for example

public partial class AddGetCompanySP : Migration
{
    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(@"
            CREATE PROC usp_GetCompany
                @CompanyId int
            AS
            BEGIN
                SELECT *
                FROM Companies
                WHERE CompanyId = @CompanyId
            END
            GO
        ");
    }
}

Custom Migration Operation

Create a Migration Operation that implements ICustomMigrationOperation interface.

Sample, create a Check Constraint Migration Operation:

public class CheckConstraintMigrationOperation : MigrationOperation, ICustomMigrationOperation
{
    public string TableName { get; set; }
    public string ConstraintName { get; set; }
    public string Sql { get; set; }

    public string GetQuery(MigrationOperationSqlGenerator sqlGenerator)
    {
        return $"ALTER TABLE {sqlGenerator.DelimitName(TableName)} ADD CONSTRAINT {ConstraintName} CHECK({Sql})";
    }
}

Add an extension method to MigrationBuilder

public static class MigrationBuilderExtensions
{
    public static void HasCheckConstraint(this MigrationBuilder migrationBuilder, string tableName, string constraintName, string sql)
    {
        migrationBuilder.Operations.Add(new CheckConstraintMigrationOperation { TableName = tableName, ConstraintName = constraintName, Sql = sql });
    }
}

... and use it.

public partial class MyMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // ...

        migrationBuilder.HasCheckConstraint("People", "CHK_PersonAge", "Age>=18");
    }

    // ...
}

Custom Type

Sample with Json.Net, map JArray to ntext.

optionsBuilder.SetCustomType(typeof(JArray), "ntext");

Other sample with DateOnly and TimeOnly

public class ApplicationDbModel : DbModel
{
    public override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MyCustomTypes>();
    }

    public override void OnConfiguring(DbModelOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseSqlServer(Constants.ConnectionString)
            .SetCustomType(typeof(DateOnly), "datetime2")
            .SetCustomType(typeof(TimeOnly), "time");
    }
}

public class MyCustomTypes
{
    // ...

    public DateOnly MyDateOnly { get; set; }
    public TimeOnly MyTimeOnly { get; set; }
}

Migrations History

By default a table "__CFMigrationsHistory" is created.

Sample to use JsonMigrationsHistory:

services.AddDbModel<ApplicationDbModel>(options =>
{
    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))
    .WithDropDatabase()
    .UseHistory(new JsonMigrationsHistory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "CodeFirstDbGenerator\\SampleDb__MigrationsHistory.json")));
});

It's possible to create a custom MigrationsHistory. Just implement IMigrationsHistory and change the history.

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<MainWindow>();
                services.AddDbModel<MyDbModel>(options => options.UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=TestWpfDi;Trusted_Connection=True;"));
            });
    }
}

Seed Database

Sample. Use Faker.Net to create fake data and SDE System.Data.Extensions to execute commands.

Create a DbInitializer

public interface IDbInitializer
{
    void Initialize();
}

public class DbInitializer : IDbInitializer
{
    private string _connectionString;

    public DbInitializer(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    public void Initialize()
    {
        try
        {
            using (var scope = new TransactionScope())
            {
                using (var connection = new SqlConnection(_connectionString))
                {
                    if ((int)connection.ExecuteScalar("SELECT COUNT(*) FROM [Companies]") == 0)
                    {
                        // companies
                        var newIds = new List<int>();
                        for (int i = 0; i < Faker.RandomNumber.Next(5, 10); i++)
                            newIds.Add(AddCompany(connection, new Company
                            {
                                Name = Faker.Company.Name(),
                                Address = Faker.Address.StreetAddress(),
                                City = Faker.Address.City(),
                                PostalCode = Faker.Address.ZipCode()
                            }));

                        // employees
                        foreach (var id in newIds)
                        {
                            for (int x = 0; x < Faker.RandomNumber.Next(2, 20); x++)
                            {
                                AddEmployee(connection, new Employee
                                {
                                    Name = Faker.Name.FullName(),
                                    Email = Faker.Internet.Email(),
                                    Title = "Employee",
                                    CompanyId = id
                                });
                            }
                        }
                    }
                    scope.Complete();
                }
            }
        }
        catch (Exception ex)
        { }
    }

    private static void AddEmployee(SqlConnection connection, Employee employee)
    {
        connection.ExecuteNonQuery("INSERT INTO [Employees](Name,Email,Phone,Title,CompanyId)VALUES(@Name,@Email,@Phone,@Title,@CompanyId)", employee);
    }

    private static int AddCompany(SqlConnection connection, Company company)
    {
        return (int)connection.ExecuteScalar("INSERT INTO [Companies](Name,Address,City,State,PostalCode) output inserted.CompanyId VALUES(@Name,@Address,@City,@State,@PostalCode)", company);
    }
}

Call the db initializer on database update complete.

 services.AddDbModel<ApplicationDbModel>(options => options
            .UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))
            .WithDropDatabase()
            .WithDefaultDeleteStrategy(ReferentialStrategy.Cascade)
            .WithOnMigrateComplete(rollback => new DbInitializer(Configuration).Initialize())); // <=

Notes:

• WithOnPrepareMigrate invoked before all migrations
• WithOnMigrating invoked before each migration
• WithOnMigrated invoked after each migration
• WithOnMigrateComplete after all migrations completed

Create a library for a Database

Create a libray for a database is easy.

• Create a Migrator that inherits from Migrator base class
• Create a TypeTranslator. The service receives a clr type (string, int, short, etc.) and returns the sql type (varchar(255), integer, tinyint, etc.) with length. Use the column, string length and max length annotations to resolve the column type.
• Create a DatabaseCreator that inherits from DatabaseCreator base class and ensures database created
• etc.