Explicit values for generated properties

Note

This documentation is for EF Core. For EF6.x, see Entity Framework 6.

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.

Tip

You can view this article's sample on GitHub.

The model

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

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.

  • For the first, no value is assigned to Employee.EmploymentStarted property, so it remains set to the CLR default value for DateTime.
  • For the second, we have set an explicit value of 1-Jan-2000.
using (var db = new EmployeeContext())
{
    db.Employees.Add(new Employee { Name = "John Doe" });
    db.Employees.Add(new Employee { Name = "Jane Doe", EmploymentStarted = new DateTime(2000, 1, 1) });
    db.SaveChanges();

    foreach (var employee in db.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

Explicit values into SQL Server IDENTITY columns

By convention the Employee.EmployeeId property is a store generated IDENTITY column.

For most situations, the approach shown above will work for key properties. However, to insert explicit values into a SQL Server IDENTITY column, you need to manually enable IDENTITY_INSERT before calling SaveChanges().

Note

We have a feature request on our backlog to do this automatically within the SQL Server provider.

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

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


    foreach (var employee in db.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

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.IsReadOnlyAfterSave = false;
Note

By default, EF Core will throw the following exception if you try to save an explicit value for a property that is configured to be generated during update. To avoid this, you need to drop down to the lower level metadata API and set the IsReadOnlyAfterSave flag (as shown above).

System.InvalidOperationException The property 'LastPayRaise' on entity type 'Employee' is defined to be read-only after it has been saved, but its value has been modified or marked as modified.

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.

  • 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 db = new EmployeeContext())
{
    var john = db.Employees.Single(e => e.Name == "John Doe");
    john.Salary = 200;

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

    db.SaveChanges();

    foreach (var employee in db.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