CodeFirstDbGenerator.MySql 6.0.0

CodeFirstDbGenerator 6

A Lightweight Library to generate quickly Databases from entities.

Concepts:

• 0 Migration. No "MigrationsHistory" table. Avoids a directory with hundred of migrations.
• Scaffold command to generate/synchronize entities, DbModel and Snapshot with Database
• Generate script or update database command to update the database

Db Supported: SQL Server, SQLite, MySQL|MariaDB, PostgreSQL

Installation

1. Library: SQL Server, SQLite, MySQL, PostgreSQL (CodeFirstDbGenerator is installed as dependency)

Sample:

install-package CodeFirstDbGenerator.SqlServer

2. Tool

For the NuGet Package Manager Console in Visual Studio (CodeFirstDbGenerator.Tools)

install-package CodeFirstDbGenerator.Tools

OR the .NET CLI tool (dotnet-cf)

dotnet tool install --global dotnet-cf

Tip: uninstall a previous version or list the tools

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

Or package references

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

Note: it's possible to invoke the commands from code (or create a GUI tool). Use:

• ScaffoldDbModelService
• GenerateScriptService
• UpdateDatabaseService

Scaffold DbModel Command

To generate/synchronize entities, DbModel and Snapshot with Database

Sample:

Scaffold-DbModel "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True;" CodeFirstDbGenerator.SqlServer -OutputDir Model

It's possible to create a custom provider that implements IDesignTimeService to configure TypeMapping or services.

Sample with dotnet-cf

dotnet-cf scaffold dbmodel -a "C:\Samples\Sample\bin\Release\net5.0-windows\Sample.dll" -c "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True;" -p "CodeFirstDbGenerator.SqlServer" -o "Model" -t Categories -t Employees

Generate Script Command

Allows to generate a SQL script

Samples:

Only for changes

Generate-Script

Initial Create Database Script

Generate-Script -DbModel NorthwindModel -Create

Sample with dotnet-cf

dotnet-cf generate script -a "C:\Samples\Sample\bin\Release\net5.0-windows\Sample.dll" -d NorthwindModel --create

Update Database Command

Allows to update the database

Sample:

Update-Database -Drop -Verbose

Sample with dotnet-cf

dotnet-cf database update -a "C:\Samples\Sample\bin\Release\net5.0-windows\Sample.dll" -d NorthwindModel --drop --verbose

Create a DbModel

• With ServiceCollection extensions
• Or override OnConfiguring DbModel method

With OnConfiguring

public class BlogModel : 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");
    }
}

OR With ServiceCollection Extensions

public class BlogModel : DbModel
{
    // 1. Add a constructor with Generic DbModelOptions
    public BlogModel(DbModelOptions<BlogModel> options)
        : base(options)
    { }

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

// 2. Registering the DbModel and the connection string to use
services.AddDbModel<BlogModel>(options =>options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Example for Sqlite

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

Data Annotations and / OR Fluent API

Two way to define columns and constraints:

• By Data Annotations attributes
• By Fluent Api

Conventions

Conventions are used to discover Data Annotations attributes and constraints. It's possible to change the conventions:

public class SampleModel : DbModel
{
    // etc.

    public override void ConfigureConventions(ConventionSet conventions)
    {
        // sample remove a convention
        conventions.ModelFinalizingConventions.Remove<NavigationDiscoveryConvention>();
    }
}

Data Annotations Attributes

• Key: for primary key (identity if int, short or long). For composite primary key use fluent api.
• Required attribute
• 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)")
• Timestamp attribute
• ForeignKey: to specify the property name

🚀 nullable enable detected

// [Table("MyCompanies", Schema = "MySchema")] 
public class Company
{
    // [Key] // or not identity [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CompanyId { get; set; } // key

    [StringLength(100)]
    public string Name { get; set; } = null!;

    //[Column("MyPostalCode")] allows to rename the column
    public string PostalCode { get; set; } = null!; 

    [Column(TypeName = "text")] // 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; } = null!;

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

public class Employee
{
    public int EmployeeId { get; set; } 
    public string FirstName { get; set; } = null!;
    public string LastName { get; set; } = null!;

    public int CompanyId { get; set; } // foreign key auto detected
    public Company Company { get; set; } = null!;

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

Or configure properties with Fluent Api. Samples:

modelBuilder.Entity<Category>()
    .Property(x => x.Name)
    .HasColumnName("CategoryName")
    .NotNull()
    .HasDefaultValue("My default value");

modelBuilder.Entity<Category>()
    .Property(x => x.Id)
    .Identity(5,5);

modelBuilder.Entity<Post>()
    .Property(x => x.CreatedAt)
    .HasDefaultValueSql("CURRENT_TIMESTAMP");

Primary Key

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

public class Author
{
    public int AuthorId { get; set; }
    // or
    // public int Id { get; set; }
}

Use Fluent API for a composite Key

modelBuilder.Entity<Book>().HasKey(x => new { x.Id1, x.Id2 });

HasOne (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; } 
    public Author Author { get; set; } // navigation required
}

With Foreign key Attribute on Navigation

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

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

... or on properties

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

With Fluent Api

modelBuilder.Entity<Post>().HasOne(x => x.Author).WithForeignKey(x => x.AuthorId).WithName("FK_Posts_Authors");

HasMany

When a Many to Many relation is detected, a relation table ("PostCategory" for example) 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; }
    // etc.

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

With Fluent Api

modelBuilder.Entity<Post>().HasMany(x => x.Categories);
modelBuilder.Entity<Category>().HasMany(x => x.Posts);

Samples

Create and use a Sequence with SQL Server

public class SampleModel : DbModel
{
    public override void OnModelCreating(ModelBuilder modelBuilder)
    {
            modelBuilder.HasSql(@"
IF OBJECT_ID('dbo.MySequence') IS NULL 
BEGIN
CREATE SEQUENCE [MySequence] AS [int] 
START WITH 1000 
INCREMENT BY 5;
END", SqlPosition.BeforeAllQueries);

        modelBuilder.Entity<Category>()
            .Property(x => x.Id)
            .HasDefaultValueSql("(NEXT VALUE FOR [MySequence])");

        modelBuilder.Entity<Category>().HasKey(x => x.Id);
    }

    public override void OnConfiguring(DbModelOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=SampleDb;Integrated Security=True;");
    }
}

public class Category
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Configure a WPF App to use Microsoft.Extensions.DependencyInjection

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

Create a custom Provider for Scaffolding

public class CustomDesignTimeService : IDesignTimeService
{
    // services used by Scaffold command
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services
            .AddTransient<IDatabaseModelFactory, SqlServerDatabaseModelFactory>()
            .AddTransient<ITypeMappingSource, CustomSqlServerTypeMappingSource>() // use custom type mapping source
            .AddTransient<IEntityNamer, EntityNamer>()
            .AddTransient<IPropertyNamer, PropertyNamer>()
            .AddTransient<IModelMapper, ModelMapper>()
            .AddTransient<IMigrationOperationsMapper, MigrationOperationsMapper>()
            .AddTransient<IEntityCodeGenerator, CSharpEntityCodeGenerator>()
            .AddTransient<IDbModelCodeGenerator, CSharpDbModelCodeGenerator>()
            .AddTransient<IDatabaseModelSnapshotCodeGenerator, CSharpDatabaseModelSnapshotCodeGenerator>();
    }
}

public class CustomSqlServerTypeMappingSource : SqlServerTypeMappingSource
{
    public CustomSqlServerTypeMappingSource()
    {
        // custom Mapping with JArray (Json.Net) for example
        AddOrReplaceTypeMapping(typeof(JArray), ClrTypeMapping.Default(typeof(JArray), "text"));
        AddOrReplaceColumnMapping(new ColumnMapping("dbo", "MyTable", "MyColumn", typeof(JArray), "text"));
    }
}

Replace the Provider by the name of the project with the custom DesignTimeService

Scaffold-DbModel "Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=SampleDb;Integrated Security=True;" MyProject -OutputDir Model

Seed database

Use HasSql method. Sample with SQL Server:

modelBuilder.HasSql(@"
IF((SELECT COUNT(*) FROM [Categories])=0)
BEGIN
INSERT INTO [Categories]([Name]) VALUES('Category A'),('Category B'),('Category C');
END", SqlPosition.AfterAllQueries);

OR use a Library like SDE or Dapper and override OnMigrated or OnMigrationCompleted methods of the DbModel.