Caution

This documentation is for EF7 onwards. For EF6.x and earlier release see http://msdn.com/data/ef.

Setting explicit values for generated properties

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.

In this article:

Tip

You can view this article’s sample on GitHub.

The model

The model used in this article contains a single Employee entity.

1
2
3
4
5
6
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public DateTime EmploymentStarted { get; set; }
    }
The context is setup to target SQL Server:
  • By convention the Employee.EmployeeId property will be a store generated IDENTITY column
  • The Employee.EmploymentStarted property has also been setup to have values generated by the database for new entities
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
    public class EmployeeContext : DbContext
    {
        public DbSet<Employee> Employees { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFSaving.ExplicitValuesGenerateProperties;Trusted_Connection=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Employee>()
                .Property(b => b.EmploymentStarted)
                .HasDefaultValueSql("CONVERT(date, GETDATE())");
        }
    }

Saving an explicit value during add

In the following code, two employees are being inserted 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.
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
            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}");
                }
            }

The code results in the following output, showing that the database generated a value for the first employee and our explicit value was used for the second:

1: John Doe, 1/28/2016 12:00:00 AM
2: Jane Doe, 1/1/2000 12:00:00 AM

Explicit values into SQL Server IDENTITY columns

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.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
            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.Employee ON");
                    db.SaveChanges();
                    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Employee OFF");
                }
                finally
                {
                    db.Database.CloseConnection();
                }


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

Setting an explicit values during update

Caution

Due to various bugs, this scenario is not properly supported in the current pre-release of EF Core. See documentation issue #122 for more details.