Previous
Next

Data Annotations - DatabaseGenerated Attribute in EF 6 & EF Core

As you know, EF creates an IDENTITY column in the database for all the id (key) properties of the entity, by default. So, the underlying database generates a value for this column on each insert command, e.g., SQL Server creates an integer IDENTITY column with identity seed and increment to 1.

EF 6 and EF Core provide the DatabaseGenerated data annotation attribute to configure how the value of a property will be generated. The DatabaseGenerated attribute takes one out of the following three DatabaseGeneratedOption enum values:

  1. DatabaseGeneratedOption.None
  2. DatabaseGeneratedOption.Identity
  3. DatabaseGeneratedOption.Computed

DatabaseGeneratedOption.None

DatabaseGeneratedOption.None option specifies that the value of a property will not be generated by the underlying database. This will be useful to override the default convention for the id properties.

For example, if you want to provide your own values to id properties instead of database generated values, use the None option, as shown below. 

public class Course
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int CourseId { get; set; }
    public string CourseName { get; set; }
}

In the above example, EF will create the CourseId column in the database and will not mark it as an IDENTITY column. So, each time you will have to provide the value of the CourseId property before calling the SaveChanges() method. 

using (var context = new SchoolContext())
{
    // you must provide the unique CourseId value
    var maths = new Course(){ CourseId=1,  CourseName="Maths"};
    context.Courses.Add(maths);

    // you must provide the unique CourseId value
    var eng = new Course(){ CourseId=2,  CourseName="English"};
    context.Courses.Add(eng);

    // the following will throw an exception as CourseId has duplicate value
    //var sci = new Course(){ CourseId=2,  CourseName="sci"};

    context.SaveChanges();
}

Note: EF will throw an exception if you do not provide unique values each time because CourseId is a primary key property.

Use the ValueGeneratedNever() method of Fluent API to specify an Identity property in EF Core, as shown below. 

modelBuilder.Entity<Course>()
    .Property(p => p.CourseId)
    .ValueGeneratedNever();

DatabaseGeneratedOption.Identity

You can mark the non-key (non-id) properties as DB-generated properties by using the DatabaseGeneratedOption.Identity option. This specifies that the value of the property will be generated by the database on the INSERT statement. This Identity property cannot be updated.

Please note that the way the value of the Identity property will be generated by the database depends on the database provider. It can be identity, rowversion or GUID. SQL Server makes an identity column for an integer property. 

public class Course
{
    public int CourseId { get; set; }
    public string CourseName { get; set; }

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

In the above example, the RecordNum property will be an identity property. This means that EF will create an IDENTITY column in the SQL Server database for this property.

Use the ValueGeneratedOnAdd() method of Fluent API to specify an Identity property in EF Core, as shown below. 

modelBuilder.Entity<Course>()
    .Property(p => p.RecordNum)
    .ValueGeneratedOnAdd();

DatabaseGeneratedOption.Compute

DatabaseGeneratedOption.Compute specifies that the value of the property will be generated by the underlying database on insert and then, on each subsequent update.

Same as Identity, the way the database generates the value depends on the database provider. You may configure a default value or use a trigger for this computed column.

Consider the following example. 

public class Student
{
    public int StudentID { get; set; }
    public string StudentName { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public decimal Height { get; set; }
    public float Weight { get; set; }

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

In the above example, the CreatedDate property is marked with the DatabaseGeneratedOption.Computed option. This tells EF that values are generated for this column in the database. However, EF does not guarantee that it will setup the actual mechanism to generate values. Here, we will specify date function of SQL Server which will generate current date-time value on INSERT command, as shown below. 

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
            
    modelBuilder.Entity<Student>()
            .Property(s => s.CreatedDate)
            .HasDefaultValueSql("GETDATE()");
}

The above code set the SQL Server function GETDATE() as a default value SQL which will insert the current date and time on each INSERT command.

Note: EF does not include DatabaseGeneratedOption.Computed columns in INSERT or UPDATE statements.

Previous
Next