使用EF的Code First模式創建模型
Entity Framework Core
Entity Framework (EF) Core 是輕量化、可擴展、開源和跨平台版的常用 Entity Framework 數據訪問技術。
EF Core 可用作對象關係映射程式 (O/RM)，這可以實現以下兩點：
使 .NET 開發人員能夠使用 .NET 對象處理資料庫。
無需再像通常那樣編寫大部分數據訪問程式碼。
EF 支援以下模型開發方法：
- Database First:先有資料庫，後有實體模型。從現有資料庫生成模型。對模型手動編碼，使其符合資料庫。
- Code First：創建模型後，使用 EF 遷移從模型創建資料庫。 模型發生變化時，遷移可讓資料庫不斷演進。先有實體模型，後生成資料庫
創建模型
通用模型
數據表，都會有主鍵欄位。為了滿足此需求，所以我們建立一個通用的泛型模型BaseEntity
/// <summary>
/// 通用基本模型
/// </summary>
/// <typeparam name="K">主鍵類型</typeparam>
public class BaseEntity<K>
{
/// <summary>
/// Id,主鍵
/// </summary>
public K Id { get; set; }
}
/// <summary>
/// 通用模型
/// </summary>
public class BaseEntity : BaseEntity<string>
{
}
為了讓系統知道我們的Id欄位是主鍵，我們增加了對通用模型的配置
EF可以有兩種方法來配置實體，一種是使用數據批註(Attribute)
還有一種是Fluent API，就是通過程式碼來對模型配置
本示例使用Fluent API來配置
因為有些特殊情況是不能使用數據批註(Attribute)來滿足要求
比如多主鍵，多外鍵，關聯等情況。
/// <summary>
/// 默認實體配置
/// OnModelCreating
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="K"></typeparam>
public class BaseEntityTypeConfig<T, K> : IEntityTypeConfiguration<T>
where T : BaseEntity<K>
{
public virtual void Configure(EntityTypeBuilder<T> builder)
{
#region 主外鍵關係
builder.HasKey(k => k.Id);//設置主鍵
#endregion
#region 欄位屬性:最大長度,是否必需,默認值
#endregion
#region 備註
builder.Property(p => p.Id).HasComment("主鍵");//設置備註
#endregion
}
}
public class BaseEntityTypeConfig<T> : BaseEntityTypeConfig<T, string>, IEntityTypeConfiguration<T>
where T : BaseEntity
{
public override void Configure(EntityTypeBuilder<T> builder)
{
base.Configure(builder);
#region 主外鍵關係
#endregion
#region 欄位屬性:最大長度,是否必需,默認值
builder.Property(p => p.Id).HasMaxLength(50);//設置主鍵最大長度50
#endregion
#region 備註
#endregion
}
}
對於業務實體，一般我們又會有些其它通過欄位，比如創建人，創建時間，修改人，修改時間，是否刪除等公共欄位
所以我們創建了BusEntity通用業務模型
/// <summary>
/// 業務實體基類
/// </summary>
/// <typeparam name="K">主鍵類型</typeparam>
public class BusEntity<K> : BaseEntity<K>
{
/// <summary>
/// 是否刪除
/// </summary>
public bool Deleted { get; set; }
/// <summary>
/// 創建人
/// </summary>
public K CreateUserId { get; set; }
/// <summary>
/// 創建時間
/// </summary>
public DateTime CreateTime { get; set; }
/// <summary>
/// 修改人
/// </summary>
public K ModifyUserId { get; set; }
/// <summary>
/// 修改時間
/// </summary>
public DateTime ModifyTime { get; set; }
}
/// <summary>
/// 業務實體基類
/// </summary>
public class BusEntity : BusEntity<string>
{ }
對於基本業務實體基類用以下配置
/// <summary>
/// 默認實體配置
/// OnModelCreating
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="K"></typeparam>
public class BusEntityTypeConfig<T, K> : BaseEntityTypeConfig<T, K>, IEntityTypeConfiguration<T>
where T : BusEntity<K>
{
public override void Configure(EntityTypeBuilder<T> builder)
{
base.Configure(builder);
builder.HasQueryFilter(q => q.Deleted == false);//查詢自動過濾已經刪除的記錄
#region 主外鍵關係
#endregion
#region 欄位屬性:最大長度,是否必需,默認值
builder.Property(p => p.Deleted).HasDefaultValue(false);//把是否刪除設置為默認False
builder.Property(p => p.CreateUserId).HasMaxLength(50);//把創建人設置為默認值
builder.Property(p => p.ModifyUserId).HasMaxLength(50);//把修改人設置為默認值
builder.Property(p => p.CreateTime).HasDefaultValueSql("getdate()").ValueGeneratedOnAdd();//把創建時間設置默認值並在增加的時候更新值
builder.Property(p => p.ModifyTime).HasDefaultValueSql("getdate()").ValueGeneratedOnAddOrUpdate();//把修改時間設置默認值並在增加和修改的時候更新值
#endregion
#region 備註
builder.Property(p => p.Deleted).HasComment("是否刪除");
builder.Property(p => p.CreateUserId).HasComment("創建人");
builder.Property(p => p.CreateTime).HasComment("創建時間");
builder.Property(p => p.ModifyUserId).HasComment("修改人");
builder.Property(p => p.ModifyTime).HasComment("修改時間");
#endregion
}
}
public class BusEntityTypeConfig<T> : BusEntityTypeConfig<T, string>, IEntityTypeConfiguration<T>
where T : BusEntity
{
public override void Configure(EntityTypeBuilder<T> builder)
{
base.Configure(builder);
#region 主外鍵關係
#endregion
#region 欄位屬性:最大長度,是否必需,默認值
builder.Property(p => p.Id).HasMaxLength(50);
#endregion
#region 備註
#endregion
}
}
業務模型
接下來我們有了通用模型基類。那麼我們就可以來創建具體的業務模型
比如說我們的組織架構模型
我們使用兩個局部類來定義，
第一個局部類定義基本屬性
第二個局部類定義關聯關係
然後對模型進行配置
/// <summary>
/// 組織架構
/// </summary>
public partial class Sys_Org : BusEntity
{
/// <summary>
/// 上級組織
/// </summary>
public string ParentId { get; set; }
/// <summary>
/// 名稱
/// </summary>
public string Name { get; set; }
}
public partial class Sys_Org : BusEntity
{
/// <summary>
/// 上級組織
/// </summary>
public Sys_Org Parent { get; set; }
/// <summary>
/// 下級組織
/// </summary>
public List<Sys_Org> Childs { get; set; }
}
/// <summary>
/// 實體配置
/// OnModelCreating
/// </summary>
public class Sys_OrgTypeConfig : BusEntityTypeConfig<Sys_Org>, IEntityTypeConfiguration<Sys_Org>
{
public override void Configure(EntityTypeBuilder<Sys_Org> builder)
{
base.Configure(builder);
#region 主外鍵關係
builder.HasOne(p => p.Parent).WithMany(p => p.Childs).HasForeignKey(p => p.ParentId);
#endregion
#region 欄位屬性:最大長度,是否必需,默認值
builder.Property(p => p.Name).HasMaxLength(50).IsRequired();
#endregion
#region 備註
builder.Property(p => p.ParentId).HasComment("上級組織");
builder.Property(p => p.Name).HasComment("名稱");
#endregion
}
}
/// <summary>
/// 系統用戶
/// </summary>
public partial class Sys_User : BusEntity
{
/// <summary>
/// 工號、編碼
/// </summary>
public string Code { get; set; }
/// <summary>
/// 名稱
/// </summary>
public string Name { get; set; }
/// <summary>
/// 用戶名
/// </summary>
public string UserName { get; set; }
/// <summary>
/// 密碼
/// </summary>
public string Password { get; set; }
/// <summary>
/// 狀態
/// </summary>
public string Status { get; set; }
/// <summary>
/// 所屬組織
/// </summary>
public string OrgId { get; set; }
/// <summary>
/// 性別
/// </summary>
public string Sex { get; set; }
}
public partial class Sys_User : BusEntity
{
/// <summary>
/// 所屬組織
/// </summary>
public Sys_Org Org { get; set; }
}
/// <summary>
/// 實體配置
/// OnModelCreating
/// </summary>
public class Sys_UserTypeConfig : BusEntityTypeConfig<Sys_User>, IEntityTypeConfiguration<Sys_User>
{
public override void Configure(EntityTypeBuilder<Sys_User> builder)
{
base.Configure(builder);
#region 主外鍵關係
builder.HasOne(p => p.Org).WithMany().HasForeignKey(p => p.OrgId);
#endregion
#region 欄位屬性:最大長度,是否必需,默認值
builder.Property(p => p.Code).HasMaxLength(50);
builder.Property(p => p.Name).HasMaxLength(50).IsRequired();
builder.Property(p => p.UserName).HasMaxLength(50).IsRequired();
builder.Property(p => p.Password).HasMaxLength(100).IsRequired();
builder.Property(p => p.Status).HasMaxLength(50).IsRequired();
builder.Property(p => p.OrgId).HasMaxLength(50);
builder.Property(p => p.Sex).HasMaxLength(50);
#endregion
#region 備註
builder.Property(p => p.Code).HasComment("編碼");
builder.Property(p => p.Name).HasComment("名稱");
builder.Property(p => p.UserName).HasComment("用戶名");
builder.Property(p => p.Password).HasComment("密碼");
builder.Property(p => p.Status).HasComment("狀態");
builder.Property(p => p.OrgId).HasComment("所屬組織");
builder.Property(p => p.Sex).HasComment("性別");
#endregion
}
}
創建資料庫上下文Context
有了數據模型，接下來我們創建資料庫上下文Context
OnConfiguring用來配置數據連接字元串
OnModelCreating是創建模型是對模型的配置
因為上面我們對每個模型都做了配置(實現了IEntityTypeConfiguration)
所以在這裡我們只要把具體的配置配置應用到Context就行了
我們使用modelBuilder.ApplyConfigurationsFromAssembly
就可以把所有實現了IEntityTypeConfiguration的模型配置全部應用到資料庫上下文
public class GDbContext : DbContext
{
/// <summary>
/// Context配置
/// </summary>
/// <param name="optionsBuilder"></param>
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseSqlServer("Data Source=x.x.x.x;Initial Catalog=資料庫名稱;User Id=用戶名;Password=密碼;APP=系統名稱;Pooling=true;");
}
/// <summary>
/// 模型創建
/// </summary>
/// <param name="modelBuilder"></param>
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.ApplyConfigurationsFromAssembly(this.GetType().Assembly);
}
/// <summary>
/// 組織架構
/// </summary>
public DbSet<Sys_Org> Sys_Org { get; set; }
/// <summary>
/// 用戶
/// </summary>
public DbSet<Sys_User> Sys_User { get; set; }
}
所有程式碼結構如下
遷移資料庫
專業名稱叫遷移，通用解釋就是把實體模型生成為數據表
我們在OnConfiguring中已經配置了使用SqlServer資料庫（當然也支援其它所有類型資料庫：如MySql,Oracle,Sqlite等其它資料庫）
使用遷移，必需安裝Microsoft.EntityFrameworkCore.Tools工具集
接下來我們在「程式包管理控制台」輸入Add-Migration FirstInit (FirstInit:自定義名稱)
系統會自動幫我們生成遷移程式碼
public partial class FirstInit : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "Sys_Org",
columns: table => new
{
Id = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false, comment: "主鍵"),
ParentId = table.Column<string>(type: "nvarchar(50)", nullable: true, comment: "上級組織"),
Name = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false, comment: "名稱"),
Deleted = table.Column<bool>(type: "bit", nullable: false, defaultValue: false, comment: "是否刪除"),
CreateUserId = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true, comment: "創建人"),
CreateTime = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "getdate()", comment: "創建時間"),
ModifyUserId = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true, comment: "修改人"),
ModifyTime = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "getdate()", comment: "修改時間")
},
constraints: table =>
{
table.PrimaryKey("PK_Sys_Org", x => x.Id);
table.ForeignKey(
name: "FK_Sys_Org_Sys_Org_ParentId",
column: x => x.ParentId,
principalTable: "Sys_Org",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
});
migrationBuilder.CreateTable(
name: "Sys_User",
columns: table => new
{
Id = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false, comment: "主鍵"),
Code = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true, comment: "編碼"),
Name = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false, comment: "名稱"),
UserName = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false, comment: "用戶名"),
Password = table.Column<string>(type: "nvarchar(100)", maxLength: 100, nullable: false, comment: "密碼"),
Status = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: false, comment: "狀態"),
OrgId = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true, comment: "所屬組織"),
Sex = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true, comment: "性別"),
Deleted = table.Column<bool>(type: "bit", nullable: false, defaultValue: false, comment: "是否刪除"),
CreateUserId = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true, comment: "創建人"),
CreateTime = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "getdate()", comment: "創建時間"),
ModifyUserId = table.Column<string>(type: "nvarchar(50)", maxLength: 50, nullable: true, comment: "修改人"),
ModifyTime = table.Column<DateTime>(type: "datetime2", nullable: false, defaultValueSql: "getdate()", comment: "修改時間")
},
constraints: table =>
{
table.PrimaryKey("PK_Sys_User", x => x.Id);
table.ForeignKey(
name: "FK_Sys_User_Sys_Org_OrgId",
column: x => x.OrgId,
principalTable: "Sys_Org",
principalColumn: "Id",
onDelete: ReferentialAction.Restrict);
});
migrationBuilder.CreateIndex(
name: "IX_Sys_Org_ParentId",
table: "Sys_Org",
column: "ParentId");
migrationBuilder.CreateIndex(
name: "IX_Sys_User_OrgId",
table: "Sys_User",
column: "OrgId");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Sys_User");
migrationBuilder.DropTable(
name: "Sys_Org");
}
}
更新到資料庫
我們通過Update-Database命令更新到資料庫
生成SQL腳本更新到生產資料庫
有時候我們系統已經在運行了。開始也不可能直接連接生產資料庫
那麼我們修改了模型，可以通過生成SQL腳本，來更新資料庫
#起始遷移點(沒有寫0)
#結束遷移點
Script-Migration -From 0 -To 20210225022927_FirstInit
IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL
BEGIN
CREATE TABLE [__EFMigrationsHistory] (
[MigrationId] nvarchar(150) NOT NULL,
[ProductVersion] nvarchar(32) NOT NULL,
CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId])
);
END;
GO
BEGIN TRANSACTION;
GO
CREATE TABLE [Sys_Org] (
[Id] nvarchar(50) NOT NULL,
[ParentId] nvarchar(50) NULL,
[Name] nvarchar(50) NOT NULL,
[Deleted] bit NOT NULL DEFAULT CAST(0 AS bit),
[CreateUserId] nvarchar(50) NULL,
[CreateTime] datetime2 NOT NULL DEFAULT (getdate()),
[ModifyUserId] nvarchar(50) NULL,
[ModifyTime] datetime2 NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_Sys_Org] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Sys_Org_Sys_Org_ParentId] FOREIGN KEY ([ParentId]) REFERENCES [Sys_Org] ([Id]) ON DELETE NO ACTION
);
DECLARE @defaultSchema AS sysname;
SET @defaultSchema = SCHEMA_NAME();
DECLARE @description AS sql_variant;
SET @description = N'主鍵';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'Id';
SET @description = N'上級組織';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'ParentId';
SET @description = N'名稱';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'Name';
SET @description = N'是否刪除';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'Deleted';
SET @description = N'創建人';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'CreateUserId';
SET @description = N'創建時間';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'CreateTime';
SET @description = N'修改人';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'ModifyUserId';
SET @description = N'修改時間';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_Org', 'COLUMN', N'ModifyTime';
GO
CREATE TABLE [Sys_User] (
[Id] nvarchar(50) NOT NULL,
[Code] nvarchar(50) NULL,
[Name] nvarchar(50) NOT NULL,
[UserName] nvarchar(50) NOT NULL,
[Password] nvarchar(100) NOT NULL,
[Status] nvarchar(50) NOT NULL,
[OrgId] nvarchar(50) NULL,
[Sex] nvarchar(50) NULL,
[Deleted] bit NOT NULL DEFAULT CAST(0 AS bit),
[CreateUserId] nvarchar(50) NULL,
[CreateTime] datetime2 NOT NULL DEFAULT (getdate()),
[ModifyUserId] nvarchar(50) NULL,
[ModifyTime] datetime2 NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_Sys_User] PRIMARY KEY ([Id]),
CONSTRAINT [FK_Sys_User_Sys_Org_OrgId] FOREIGN KEY ([OrgId]) REFERENCES [Sys_Org] ([Id]) ON DELETE NO ACTION
);
DECLARE @defaultSchema AS sysname;
SET @defaultSchema = SCHEMA_NAME();
DECLARE @description AS sql_variant;
SET @description = N'主鍵';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'Id';
SET @description = N'編碼';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'Code';
SET @description = N'名稱';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'Name';
SET @description = N'用戶名';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'UserName';
SET @description = N'密碼';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'Password';
SET @description = N'狀態';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'Status';
SET @description = N'所屬組織';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'OrgId';
SET @description = N'性別';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'Sex';
SET @description = N'是否刪除';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'Deleted';
SET @description = N'創建人';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'CreateUserId';
SET @description = N'創建時間';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'CreateTime';
SET @description = N'修改人';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'ModifyUserId';
SET @description = N'修改時間';
EXEC sp_addextendedproperty 'MS_Description', @description, 'SCHEMA', @defaultSchema, 'TABLE', N'Sys_User', 'COLUMN', N'ModifyTime';
GO
CREATE INDEX [IX_Sys_Org_ParentId] ON [Sys_Org] ([ParentId]);
GO
CREATE INDEX [IX_Sys_User_OrgId] ON [Sys_User] ([OrgId]);
GO
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20210225022927_FirstInit', N'5.0.3');
GO
COMMIT;
GO
這樣的話。就只要把這個腳本放到生產環境去運行，
那麼得到的結果也是和Update-Database結果是一樣的
