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
License
—
Deps
4
Install Size
—
Vulns
✓ 0
Published
Jul 30, 2022
$ dotnet add package CodeFirstDbGeneratorMotivation: allow to create migrations and update database without installing Entity Framework, for libraries like Dapper.
Db Supported:
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
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:
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 });
}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.
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>();
}
}