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
... 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:
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";
}
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
var runner = new SqlServerRunner();
runner.ConnectionString = "Server=(localdb)\\mssqllocaldb;Database=SampleDb;Trusted_Connection=True;MultipleActiveResultSets=true";
runner.UpdateDatabase();
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'
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
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
