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.
}
A migration (20220119222431_InitialCreate for example) and a model snapshot (ApplicationDbModelSnapshot for example) are generated in a directory Migrations.
Note: It's possible to remove last migrations and ModelSnapshot.
... Or dotnet cf tool
dotnet cf migrations add InitialCreate -a path/to/assembly.dll
It's possible to generate a migration and define a SQL Query 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
");
}
}
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