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