為產生的屬性設定明確值Setting Explicit Values for Generated Properties

產生的屬性係指其值會在新增和/或更新實體時產生 (由 EF 或資料庫產生) 的屬性。A generated property is a property whose value is generated (either by EF or the database) when the entity is added and/or updated. 如需詳細資訊,請參閱產生的屬性See Generated Properties for more information.

在一些情況下,您可能會想要為產生的屬性設定明確值,而不是使用產生的值。There may be situations where you want to set an explicit value for a generated property, rather than having one generated.

提示

您可以在 GitHub 上檢視此文章的範例 (英文)。You can view this article's sample on GitHub.

模型The model

本文中使用的模型包含單一 Employee 實體。The model used in this article contains a single Employee entity.

public class Employee
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public DateTime EmploymentStarted { get; set; }
    public int Salary { get; set; }
    public DateTime? LastPayRaise { get; set; }
}

在新增期間儲存明確值Saving an explicit value during add

Employee.EmploymentStarted 屬性已設定為採用資料庫為新實體產生的值 (使用預設值)。The Employee.EmploymentStarted property is configured to have values generated by the database for new entities (using a default value).

modelBuilder.Entity<Employee>()
    .Property(b => b.EmploymentStarted)
    .HasDefaultValueSql("CONVERT(date, GETDATE())");

下列程式碼會將兩個員工插入到資料庫中。The following code inserts two employees into the database.

  • 針對第一個員工,由於未指派任何值給 Employee.EmploymentStarted 屬性,因此它會維持設定為 DateTime 的 CLR 預設值。For the first, no value is assigned to Employee.EmploymentStarted property, so it remains set to the CLR default value for DateTime.
  • 針對第二個員工,我們已設定明確值 1-Jan-2000For the second, we have set an explicit value of 1-Jan-2000.
using (var context = new EmployeeContext())
{
    context.Employees.Add(new Employee { Name = "John Doe" });
    context.Employees.Add(new Employee { Name = "Jane Doe", EmploymentStarted = new DateTime(2000, 1, 1) });
    context.SaveChanges();

    foreach (var employee in context.Employees)
    {
        Console.WriteLine(employee.EmployeeId + ": " + employee.Name + ", " + employee.EmploymentStarted);
    }
}

輸出顯示資料庫為第一個員工產生了一個值,而我們的明確值則用於第二個員工。Output shows that the database generated a value for the first employee and our explicit value was used for the second.

1: John Doe, 1/26/2017 12:00:00 AM
2: Jane Doe, 1/1/2000 12:00:00 AM

將明確值插入到 SQL Server IDENTITY 資料行中Explicit values into SQL Server IDENTITY columns

依照慣例,Employee.EmployeeId 屬性是一個存放區產生的 IDENTITY 資料行。By convention the Employee.EmployeeId property is a store generated IDENTITY column.

就大多數情況而言,上面所示的方法對索引鍵屬性都適用。For most situations, the approach shown above will work for key properties. 不過,若要將明確值插入到 SQL Server IDENTITY 資料行中,您將必須在呼叫 SaveChanges() 之前,先手動啟用 IDENTITY_INSERTHowever, to insert explicit values into a SQL Server IDENTITY column, you need to manually enable IDENTITY_INSERT before calling SaveChanges().

注意

在我們的待辦項目上有一個在 SQL Server 提供者內自動進行此操作的功能要求We have a feature request on our backlog to do this automatically within the SQL Server provider.

using (var context = new EmployeeContext())
{
    context.Employees.Add(new Employee { EmployeeId = 100, Name = "John Doe" });
    context.Employees.Add(new Employee { EmployeeId = 101, Name = "Jane Doe" });

    context.Database.OpenConnection();
    try
    {
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employees ON");
        context.SaveChanges();
        context.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employees OFF");
    }
    finally
    {
        context.Database.CloseConnection();
    }


    foreach (var employee in context.Employees)
    {
        Console.WriteLine(employee.EmployeeId + ": " + employee.Name);
    }
}

輸出顯示所提供的識別碼已儲存至資料庫。Output shows that the supplied ids were saved to the database.

100: John Doe
101: Jane Doe

在更新期間設定明確值Setting an explicit value during update

Employee.LastPayRaise 屬性已設定為採用資料庫在更新期間產生的值。The Employee.LastPayRaise property is configured to have values generated by the database during updates.

modelBuilder.Entity<Employee>()
    .Property(b => b.LastPayRaise)
    .ValueGeneratedOnAddOrUpdate();

modelBuilder.Entity<Employee>()
    .Property(b => b.LastPayRaise)
    .Metadata.AfterSaveBehavior = PropertySaveBehavior.Ignore;

注意

根據預設,如果您嘗試針對已設定為要在更新期間產生的屬性儲存明確值,EF Core 將會擲回例外狀況。By default, EF Core will throw an exception if you try to save an explicit value for a property that is configured to be generated during update. 若要避免此問題,您必須降到較低層級的中繼資料 API 並設定 AfterSaveBehavior (如以上所示)。To avoid this, you need to drop down to the lower level metadata API and set the AfterSaveBehavior (as shown above).

注意

EF Core 2.0 中的變更: 在舊版中,是透過 IsReadOnlyAfterSave 旗標來控制儲存後的行為。Changes in EF Core 2.0: In previous releases the after-save behavior was controlled through the IsReadOnlyAfterSave flag. 此旗標已淘汰並被 AfterSaveBehavior 取代。This flag has been obsoleted and replaced by AfterSaveBehavior.

資料庫中也有一個可在 UPDATE 作業期間為 LastPayRaise 資料行產生值的觸發程序。There is also a trigger in the database to generate values for the LastPayRaise column during UPDATE operations.

CREATE TRIGGER [dbo].[Employees_UPDATE] ON [dbo].[Employees]
    AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
                  
    IF ((SELECT TRIGGER_NESTLEVEL()) > 1) RETURN;
                  
    IF UPDATE(Salary) AND NOT Update(LastPayRaise)
    BEGIN
        DECLARE @Id INT
        DECLARE @OldSalary INT
        DECLARE @NewSalary INT
          
        SELECT @Id = INSERTED.EmployeeId, @NewSalary = Salary        
        FROM INSERTED
          
        SELECT @OldSalary = Salary        
        FROM deleted
          
        IF @NewSalary > @OldSalary
        BEGIN
            UPDATE dbo.Employees
            SET LastPayRaise = CONVERT(date, GETDATE())
            WHERE EmployeeId = @Id
        END
    END
END

下列程式碼會為資料庫中的兩個員工加薪。The following code increases the salary of two employees in the database.

  • 針對第一個員工,由於未指派任何值給 Employee.LastPayRaise 屬性,因此它會維持設定為 Null。For the first, no value is assigned to Employee.LastPayRaise property, so it remains set to null.
  • 針對第二個員工,我們已將明確值設定為一週前 (回溯加薪日期)。For the second, we have set an explicit value of one week ago (back dating the pay raise).
using (var context = new EmployeeContext())
{
    var john = context.Employees.Single(e => e.Name == "John Doe");
    john.Salary = 200;

    var jane = context.Employees.Single(e => e.Name == "Jane Doe");
    jane.Salary = 200;
    jane.LastPayRaise = DateTime.Today.AddDays(-7);

    context.SaveChanges();

    foreach (var employee in context.Employees)
    {
        Console.WriteLine(employee.EmployeeId + ": " + employee.Name + ", " + employee.LastPayRaise);
    }
}

輸出顯示資料庫為第一個員工產生了一個值,而我們的明確值則用於第二個員工。Output shows that the database generated a value for the first employee and our explicit value was used for the second.

1: John Doe, 1/26/2017 12:00:00 AM
2: Jane Doe, 1/19/2017 12:00:00 AM