[EF Core 3] 如何使用 Code First 定義資料庫結構

EF Core 的 Code First 設定跟以往有些差異,多了些不錯的功能,也有功能被拔掉了,紀錄一下使用的方式

EF Core 3 Migration 的用法請參考
https://dotblogs.com.tw/yc421206/2019/11/28/ef_core_migration
 

DbContext

DbContext EF / EF Core 最主要的一個物件,他提供跟資料庫溝通的功能有

  1. Database Connections
  2. Data operations such as querying and persistance
  3. Change Tracking
  4. Model building
  5. Data Mapping
  6. Object caching
  7. Transaction management
public class LabEmployeeContext : DbContext
{
    private static readonly bool[] s_migrated = {false};

    public LabEmployeeContext()
    {
    }

    public LabEmployeeContext(DbContextOptions<LabEmployeeContext> options) : base(options)
    {
        if (options == null)
        {
            options = DbOptionsFactory.DbContextOptions;
        }

        if (!s_migrated[0])
        {
            lock (s_migrated)
            {
                if (!s_migrated[0])
                {
                    //this.Database.Migrate();
                    this.Database.EnsureCreated();
                    s_migrated[0] = true;
                }
            }
        }
    }
}

 

DbSet

DbSet 用來映射資料表,通過它來執行 CRUD 的命令,他是 DbContext 的屬性。

DbSet 越少,DbContext 的負荷越低,在大型的系統裡面,你可以為不同的功能定義不同的 DbContext。

public class LabEmployeeContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
}

 

POCO

映射資料表的定義,他跟我們常用的 DTO 定義很像,只有屬性成員,不過 EF Core 有提供 Object Tracking,這要實作 INotifyPropertyChanged,這在未來文章會演示這一段

public class Employee
{
   public Guid Id { get; set; }
   public string Name { get; set; }
   public int? Age { get; set; }
   public long SequenceId { get; set; }
   public string Remark { get; set; }
}

 

Configuration

public class DbOptionsFactory
{
    public static DbContextOptions<LabEmployeeContext> DbContextOptions { get; }

    public static string ConnectionString { get; }

    static DbOptionsFactory()
    {
        var configuration = new ConfigurationBuilder().AddJsonFile("appsettings.json").Build();
        ConnectionString = configuration.GetConnectionString("DefaultConnection");
        DbContextOptions = new DbContextOptionsBuilder<LabEmployeeContext>().UseSqlServer(ConnectionString).Options;
    }
}

 

appsettings.json 內容如下

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=Lab.DAL.UnitTest;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

 

這樣就能動態的長出資料庫

[TestClass]
public class UnitTest1
{
    [TestMethod]
    public void TestMethod1()
    {
        using (var dbContext = new LabEmployeeContext(DbOptionsFactory.DbContextOptions))
        {
        }
    }
}

 

Config POCO

有了基本的認識之後,就要進入這一篇的主題,設定 POCO,EF Core 跟 EF 6 一樣,可以用 Fluent API、Attribute 兩種方式來定義,Fluent API 擁有完整的功能,Attribute 只有部分的功能,一開始學的時候,可以利用 Database First 來輔助,調用 Scaffold-DbContext,詳細作法可以參考以下這篇

https://dotblogs.com.tw/yc421206/2019/11/27/install_efcore3

 

Fluent API、Attribute 通用的用法

Fluent API

複寫 DbContext.OnModelCreating 方法

protected override void OnModelCreating(ModelBuilder modelBuilder) 
{
   ...
}

 

通 過 modelBuilder.Entity<Employee> 方法設定資料結構,他有兩個多載方法

 

多欄位:匿名型別,代碼如下

modelBuilder.Entity<Employee>().HasKey(p => new { p.Id, p.Age });

單一欄位:具名屬性

modelBuilder.Entity<Employee>().HasKey(p => p.Id);

 

Key Attribute

寫在 POCO 的屬性,代碼如下

public class Employee {     [Key]     public Guid Id { get; set; } }

在 EF6 / EF Core 可以用的 Attribute 命名空間有以下

System.ComponentModel.DataAnnotations:資料檢查

System.ComponentModel.DataAnnotations.Schema:資料表結構

 

單一主鍵

  • Fluent API
modelBuilder.Entity<Employee>().HasKey(p => p.Id);

 

  • Attribute
public class Employee
{
    [Key]
    public Guid Id { get; set; }
}

 

複合主鍵

  • Fluent API
modelBuilder.Entity<Employee>().HasKey(p => new {p.Id,p.Name});
  • Attribute 不支援

 

Index

  • EF Core 的 Attribute 不支援

 

Cluster Index

SQL Server PK 預設就是 Cluster Index,假使 PK 資料的資料不連續,索引會破碎,導致查詢效能低落,這時候就可以把 Cluster Index 移到一個連續的 Sequence 欄位,可以使用 Identity

PK 不設定 Cluster Index
modelBuilder.Entity<Employee>(p =>
                              {
                                  p.HasKey(e => e.Id)
                                   .HasName("PK_Employee")
                                   .IsClustered(false);
                              });

 

SequenceId 設定 Cluster Index
modelBuilder.Entity<Employee>(p =>
                              {
                                  p.HasIndex(e => e.SequenceId).HasName("CLIX_Employee_SequenceId")
                                   .IsUnique().IsClustered();
                              });

 

對應結果如下圖:

 

Noncluster Index
modelBuilder.Entity<Employee>(entity =>
                              {
                                  entity.HasIndex(p => new {p.Id, p.Name})
                                        .HasName("IX_Employee");
                              });

 

Noncluster Unique Index
modelBuilder.Entity<Employee>(entity =>
                              {
                                  entity.HasIndex(p => new {p.Id, p.Name}).HasName("IX_Employee")
                                        .IsUnique(true);
                              });

 

Included Columns
modelBuilder.Entity<Employee>(entity =>
                              {
                                  entity.HasIndex(p => new {p.Id, p.Name}).HasName("IX_Employee")
                                        .IncludeProperties(p => p.Remark);
                              });

 

對應結果如下圖:

 

Unique Constraint (UK)

UK 也是 Unique Index

EF Core /EF 6 的 FK 不支援關聯 UK

modelBuilder.Entity<Employee>(p => p.HasAlternateKey(e => e.Name));

 

對應結果如下圖:

 

Default Vaule

可以調用 SQL Server 的 function 或是一般的值

modelBuilder.Entity<Employee>(p =>
                              {
                                  p.Property(e => e.Id).HasDefaultValueSql("NEWSEQUENTIALID()");
                                  p.Property(e => e.Name).HasDefaultValue("99");
                              });

 

對應結果如下圖:

 

ValueGeneratedOnAdd

EF Core 新增時,不需要給值,會使用 SQL Server 的預設值,若 EF Core 更新此欄位,則拋出例外

modelBuilder.Entity<Employee>(p =>
                              {
                                  p.Property(e => e.SequenceId)
                                   .ValueGeneratedOnAdd();
                              });

 

對應結果如下圖:

在 EF 6 等效功能為 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

 

ValueGeneratedNever

EF Core 設定該欄位,不使用資料庫的預設值

modelBuilder.Entity<Employee>(p =>
                              {
                                  p.Property(e => e.SequenceId)
                                   .ValueGeneratedNever();
                              });

 

在 EF 6 等效功能為 [DatabaseGenerated(DatabaseGeneratedOption.None)]

 

ValueGeneratedOnAddOrUpdate

EF Core 不會產生該欄位的 SQL 語法

modelBuilder.Entity<Employee>(p =>
                              {
                                  p.Property(e => e.SequenceId).ValueGeneratedOnAddOrUpdate();
                              });

 

在 EF 6 等效功能為 [DatabaseGenerated(DatabaseGeneratedOption.Computed)]

 

關聯

定義物件,Employee 有 Identity 屬性,Identity 有 Employ 屬性,雙方都看的到彼此

Has/With pattern,使用這兩個字的開頭來定義關聯

FK 的屬性命名是 PK Table + 欄位,例如要關聯到 Employee Table 的 Id 欄位,FK 則命名為 EmployeeId

 

一對一關聯
public class Employee
{
    [Key]
    public Guid Id { get; set; }

    public string Name { get; set; }

    public int? Age { get; set; }

    public long SequenceId { get; set; }

    public string Remark { get; set; }

    public Identity Identity { get; set; }
}

 

Identity 的 FK,欄位是EmployeeId,FK 正好也是 PK

public class Identity
{
    [Key]
    public Guid EmployeeId { get; set; }

    public string Account { get; set; }

    public string Password { get; set; }

    public long SequenceId { get; set; }

    public string Remark { get; set; }

    public Employee Employee { get; set; }
}

 

EF Core 翻成  SQL 的時候,會把這個欄位設為必填,Delete Rule 設為 CASCADE

 

我想要設定 Identity 關連到 Employee

modelBuilder.Entity<Employee>(p =>
                              {
                                  p.HasOne(e => e.Identity).WithOne(e => e.Employee)
                                   .HasConstraintName("FK_Identity_to_Employee");
                              });

 

對應結果如下圖:

 

反之,Employee to Identity 寫法如下

modelBuilder.Entity<Identity>(p =>
                              {
                                  p.HasOne(e => e.Employee).WithOne(e => e.Identity)
                                   .HasConstraintName("FK_Employee_to_Identity");
                              });

 

一對多關聯
public class Employee
{
    [Key]
    public Guid Id { get; set; }

    public string Name { get; set; }

    public int? Age { get; set; }

    public long SequenceId { get; set; }

    public string Remark { get; set; }

    public Identity Identity { get; set; }

    public ICollection<Order> Orders { get; set; }
}

 

FK 是 EmployeeId,是 Nullable<Guid> 型別

public class Order
{
    [Key]
    public Guid Id { get; set; }

    public Guid? EmployeeId { get; set; }

    public DateTime? OrderTime { get; set; }

    public string Remark { get; set; }

    public long SequenceId { get; set; }

    public Employee Employee { get; set; }
}

EF Core 翻成  SQL 的時候,會把這個欄位設為非必填,Delete Rule 設為 NoAction

 

對應結果如下圖:

 

多對多關聯

物件的關係如下代碼:

public class Company
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<CompanyEmployee> CompanyEmployees { get; set; }
}

public class Employee
{
    public int Id { get; set; }

    public string Name { get; set; }

    public ICollection<CompanyEmployee> CompanyEmployees { get; set; }
}

public class CompanyEmployee
{
    public int CompanyId { get; set; }

    public Company Company { get; set; }

    public int EmployeeId { get; set; }

    public Employee Employee { get; set; }
}

 

EF 6 是不需要 CompanyEmployee class,就能完成多對多資料表對應

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<CompanyEmployee>(builder =>
                                         {
                                             builder.HasKey(p => new {p.CompanyId, p.EmployeeId});
                                             builder.HasOne(p => p.Company).WithMany(p => p.CompanyEmployees)
                                                    .HasForeignKey(p => p.CompanyId);
                                             builder.HasOne(p => p.Employee).WithMany(p => p.CompanyEmployees)
                                                    .HasForeignKey(p => p.EmployeeId);
                                         });
}

 

對應結果如下圖:

ForeignKeyAttribute

假若 FK 不遵守命名的話,可以使用 ForeignKeyAttribute,例如 FK 欄位名稱 Employee_Id,上面掛著 [ForeignKey("Employee")]

public class Order
{
    [Key]
    public Guid Id { get; set; }

    [ForeignKey("Employee")]
    public Guid? Employee_Id { get; set; }

    public DateTime? OrderTime { get; set; }

    public string Remark { get; set; }

    public long SequenceId { get; set; }

    public Employee Employee { get; set; }
}

 

HasForeignKey

或是使用 Fluent API,HasForeignKey

modelBuilder.Entity<Employee>(p =>
                              {
                                  p.HasMany(e => e.Orders).WithOne(e => e.Employee)
                                   .HasConstraintName("FK_Order_to_Employee")
                                   .HasForeignKey(e => e.Employee_Id);
                              });

 

這兩種方法都可以讓自訂 FK 的命名

Class

對應資料表

ToTable Fluent API

modelBuilder.Entity<Employee>().ToTable("Employee","Library");

 

Table Attribute

[Table("Employee", Schema = "Library")]
public class Employee
{
    [Key]
    public Guid Id { get; set; }
}

 

對應結果如下圖:

Property

對應DB欄位/型別

Fluent API

modelBuilder.Entity<Employee>().Property(b => b.Name).HasColumnType("varchar(100)");

 

modelBuilder.Entity<Employee>().Property(b => b.Name).HasColumnName("Description");

 

Column Atturibute

上面的 Fluent API 可以用 Column Attribute

public class Employee
{
    [Key]
    public Guid Id { get; set; }

    [Column("Description", Order = 2, TypeName = "nvarchar(100)")]
    public string Name { get; set; }
}

 

對應 SQL Function

HasComputedColumnSql

Select 的時候就直接對應(主動)

modelBuilder.Entity<Employee>().Property(p => p.CreateAt).HasComputedColumnSql("GetUtcDate()");

 

DbFunction

Select 時調用該方法(被動)

[DbFunction("CHARINDEX")]
public static int? CharIndex(string toSearch, string target) => throw new Exception();

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDbFunction(typeof(LabEmployeeContext).GetMethod(nameof(LabEmployeeContext)))
                .HasTranslation(p => SqlFunctionExpression.Create("CHARINDEX", p, typeof(int?), null));
}

 

 

不對應DB欄位

NotMapped Attribute

public class Employee
{
    [Key]
    public Guid Id { get; set; }

    [NotMapped]
    public string Name { get; set; }
}

 

Ignore Fluent API

modelBuilder.Entity<Employee>().Ignore(c => c.Name);

 

必填

IsRequired Fluent API

modelBuilder.Entity<Employee>().Property(p => p.Name).IsRequired();

 

Required Attribute

public class Employee
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public string Name { get; set; }
}

 

若有謬誤,煩請告知,新手發帖請多包涵


Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2019 .NET

Image result for microsoft+mvp+logo