EntityFramework Core筆記：表結構及資料基本操作（2）

1. 表結構操作

1.1 表名

　　Data Annotations：

using System.ComponentModel.DataAnnotations.Schema;

[Table("Role")]
public class Role
{
   // ...
}

　　FluentAPI：

using Microsoft.EntityFrameworkCore;

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Role>().ToTable("Role");
}

1.2 欄位

　　Data Annotations：

using System;
using System.Collections.Generic;
using System.Text;

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Libing.App.Models.Entities
{
    [Table("Role")]
    public class Role
    {
        [Column("RoleID")]
        public int RoleID { get; set; }

        [Required]
        [Column("RoleName",TypeName = "varchar(200)")]
        public string RoleName { get; set; }
    }
}

　　FluentAPI：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Role>().ToTable("Role");
    modelBuilder.Entity<Role>()
        .Property(t => t.RoleID)
        .HasColumnName("RoleID");
    modelBuilder.Entity<Role>()
        .Property(t => t.RoleName)
        .HasColumnName("RoleName")
        .HasColumnType("varchar(200)")
        //.HasMaxLength(200)
        .IsRequired();
}

1.3 主鍵

　　Data Annotations：

[Table("Role")]
public class Role
{
    [Key]
    public int RoleID { get; set; }
}

　　FluentAPI：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Role>()
        .HasKey(t => t.RoleID);

    // 複合主鍵
    //modelBuilder.Entity<Role>()
    //    .HasKey(t => new { t.RoleID, t.RoleName });
}

1.4 計算列

　　FluentAPI：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<User>()
        .Property(t => t.DisplayName)
        .HasComputedColumnSql("[LastName] + ',' + [FirstName]");
}

CREATE TABLE [Users] (
    [UserID] int NOT NULL IDENTITY,
    [DisplayName] AS [LastName] + ',' + [FirstName],
    [FirstName] nvarchar(max) NULL,
    [LastName] nvarchar(max) NULL,
    CONSTRAINT [PK_Users] PRIMARY KEY ([UserID])
);

1.5 生成值

　　主鍵屬性如果是整數或Guid型別，該屬性將會被EntityFramework Core設定為自動生成。

　　Data Annotations：

　　（1）沒有生成值

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int RoleID { get; set; }

　　（2）新增操作生成值

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int RoleID { get; set; }

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public DateTime ModifiedDate { get; set; }

　　（3）新增或修改操作生成值

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public DateTime ModifiedDate { get; set; }

　　FluentAPI：

modelBuilder.Entity<Role>()
    .Property(t => t.RoleID)
    .ValueGeneratedOnAdd();

modelBuilder.Entity<Role>()
    .Property(t => t.ModifiedDate)
    .ValueGeneratedNever();

modelBuilder.Entity<Role>()
    .Property(t => t.ModifiedDate)
    .ValueGeneratedOnAdd();

modelBuilder.Entity<Role>()
    .Property(t => t.ModifiedDate)
    .ValueGeneratedOnAddOrUpdate();

注：按照約定，非複合主鍵的型別 short、 int、 long、 或 Guid 將安裝程式能夠生成上新增的值。

　　所有其他屬性將與不值生成的安裝程式。

1.6 預設值

modelBuilder.Entity<Role>()
    .Property(t => t.RoleName)
    .HasDefaultValue(String.Empty);

modelBuilder.Entity<Role>()
    .Property(t => t.ModifiedDate)
    .HasDefaultValueSql("GETDATE()");

CREATE TABLE [Role] (
    [RoleID] int NOT NULL IDENTITY,
    [ModifiedDate] datetime2 NOT NULL DEFAULT (GETDATE()),
    [RoleName] varchar(200) NOT NULL DEFAULT N'',
    CONSTRAINT [PK_Role] PRIMARY KEY ([RoleID])
);

1.7 序列

modelBuilder.HasSequence<int>("RoleSequence", "dbo")
    .StartsAt(1)
    .IncrementsBy(1);

modelBuilder.Entity<Role>()
    .Property(t => t.RoleID)
    .HasDefaultValueSql("NEXT VALUE FOR dbo.RoleSequence");

CREATE SEQUENCE [dbo].[RoleSequence] AS int START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;

CREATE TABLE [dbo].[Role] (
    [RoleID] int NOT NULL DEFAULT (NEXT VALUE FOR dbo.RoleSequence),
    [ModifiedDate] datetime2 NOT NULL,
    [RoleName] varchar(200) NOT NULL,
    CONSTRAINT [PK_Role] PRIMARY KEY ([RoleID])
);

1.8 索引

modelBuilder.Entity<Role>()
    .HasIndex(t => t.RoleName)
    .HasName("IX_RoleName");

CREATE INDEX [IX_RoleName] ON [dbo].[Role] ([RoleName]);

modelBuilder.Entity<Role>()
    .HasIndex(t => t.RoleName)
    .IsUnique()
    .HasName("IX_RoleName");

CREATE UNIQUE INDEX [IX_RoleName] ON [dbo].[Role] ([RoleName]);

1.9 預設架構

modelBuilder.HasDefaultSchema("dbo");

1.10 完整實體類配置

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Role>(entity =>
    {
        // Primary Key
        entity.HasKey(t => t.RoleID);

        // Properties
        entity.Property(t => t.RoleName).HasMaxLength(100);

        // Table & Column Mappings
        entity.ToTable("Role", "dbo");
        entity.Property(t => t.RoleID).HasColumnName("RoleID");
        entity.Property(t => t.RoleName).HasColumnName("RoleName");
    });
}

　　RoleConfiguration.cs

using System;
using System.Collections.Generic;
using System.Text;

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

using Libing.App.Models.Entities;

namespace Libing.App.Models.Configurations
{
    public class RoleConfiguration : IEntityTypeConfiguration<Role>
    {
        public void Configure(EntityTypeBuilder<Role> builder)
        {
            // Primary Key
            builder.HasKey(t => t.RoleID);

            // Properties
            builder.Property(t => t.RoleName)
                .IsRequired()
                .HasMaxLength(100);

            // Table & Column Mappings
            builder.ToTable("Role", "dbo");
            builder.Property(t => t.RoleID).HasColumnName("RoleID");
            builder.Property(t => t.RoleName).HasColumnName("RoleName");
        }
    }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.ApplyConfiguration(new RoleConfiguration());
}

2. 表資料操作