DatabaseGenerated Attribute in Entity Framework Core

DatabaseGenerated Attribute in Entity Framework Core

In this article, I will discuss DatabaseGenerated Data Annotation Attribute in Entity Framework Core (EF Core) with Examples. Please read our previous article, discussing MaxLength and MinLength Attribute in Entity Framework Core with Examples. In Entity Framework Core, the DatabaseGenerated attribute indicates how the database generates or manages a property’s value when performing insert or update operations. This attribute can be applied to properties of our entity classes to control how their values are handled.

DatabaseGenerated Attribute in EF Core

In Entity Framework Core (EF Core), the DatabaseGenerated attribute is used to specify how values are generated by the database for a particular property in an entity class. This is particularly useful for handling properties whose values are computed or set by the database, such as primary keys, timestamps, or computed columns.

As we already discussed, by default, Entity Framework Core creates an IDENTITY column in the database for the Key Property of the entity (It will not generate an identity column for the composite key or composite primary key). In the case of Primary Key with a single column, SQL Server creates an integer IDENTITY column with identity seed and increment values as 1. So, while inserting a record into the database table, the identity column value will be automatically generated by SQL Server and inserted into the identity column or primary key column.

EF Core provides the DatabaseGenerated Data Annotation Attribute, configuring how a property’s value will be generated. The DatabaseGenerated Attribute belongs to the System.ComponentModel.DataAnnotations.Schema namespace. If you go to the definition of DatabaseGenerated Attribute class, you will see the following signature. This class has one constructor and one property.

DatabaseGenerated Attribute in Entity Framework Core

As you can see in the above image, the constructor takes one parameter of type DatabaseGeneratedOption. That means the DatabaseGenerated attribute accepts a DatabaseGeneratedOption enum as its parameter. Now, if you go to the definition of DatabaseGeneratedOption, you will see that it is an enum with three values, as shown in the image below.

DatabaseGeneratedOption Enum

There are different values you can set for the DatabaseGenerated Attribute in EF Core:

  1. DatabaseGeneratedOption.None: This is the default option and indicates that the database does not generate the property value. It’s typically used for properties you want to set in your application manually. Use this option if you do not want the database to generate a value.
  2. DatabaseGeneratedOption.Identity: This option indicates that the property is an identity column in the database, and the database automatically generates its value during an insert operation. This is often used for primary key columns. If you want the database to generate a value for a property when a row is inserted, then you need to use this option.
  3. DatabaseGeneratedOption.Computed: This option indicates that the database computes the property value during insert or update operations. This can be used for properties whose values are based on calculations or expressions involving other columns. If you want the database to generate a value for a property when a row is inserted or updated, then you need to use this option. 

Let us understand each of the above options with examples.

DatabaseGeneratedOption.None in EF Core

This DatabaseGeneratedOption.None is the default option, indicating that the database does not generate the property value. It’s typically used for properties you want to set in your application manually. Use this option if you do not want the database to generate a value.

This option will be useful to override the default convention for the id properties. If you remember, for the Id Property or <Entity Name>+Id Property, Entity Framework Makes that column an Identity Column and automatically generates the value for that column.

For example, we want to provide our own values to Id Property or <Entity Name>+Id Property instead of autogenerated database values. Then, in this case, we need to use the DatabaseGeneratedOption.None option. For a better understanding, please modify the Student Entity class as follows. As you can see, we have decorated the StudentId property with the DatabaseGenerated Attribute with DatabaseGeneratedOption.None value.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
    public class Student
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }
    }
}

In this case, the Entity Framework Core will mark the StudentId column in the database as the Primary Key Column but will not mark this column as an IDENTITY column. So, we need to specify values for this column while inserting a new record. Next, modify the context class as shown below. As you can see, we have registered the Student model within the context class using DbSet.

using Microsoft.EntityFrameworkCore;
namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

        public DbSet<Student> Students { get; set; }
    }
}

Note: Before Proceeding further, let us delete the EFCoreDB database using SSMS and Migration folder from our project.

With the above changes, open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving EFCoreDBMigration1. The name that you are giving it should not be given earlier.

DatabaseGenerated Attribute in EF Core with Examples

Now, if you check the Students database table Create SQL SQL Script, you will see it created the primary key column without Identity, as shown in the image below.

DatabaseGenerated Attribute in EF Core

Modifying the Main Method:

To prove this, modify the Program class’s Main method as follows. Here, you can see we are explicitly providing values for the StudentId. If we provide duplicate value or if we don’t provide any value for the StudentId, then it will throw a Runtime Exception.

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using EFCoreDbContext context = new EFCoreDbContext();

                //We need to provide the unique StudentId value
                var student1 = new Student() { StudentId = 101, FirstName = "Pranaya", LastName = "Kumar" };
                context.Students.Add(student1);

                //We need to provide the unique StudentId value
                var student2 = new Student() { StudentId = 102, FirstName = "Hina", LastName = "Sharma" };
                context.Students.Add(student2);

                //The following Entity will throw an exception as StudentId has duplicate value
                //var student3 = new Student() { StudentId = 102, FirstName = "Preety", LastName = "Tiwari" };
                //context.Students.Add(student3);

                //The following Entity will throw an exception as we have not supplied value for StudentId
                //var student4 = new Student() { FirstName = "Preety", LastName = "Tiwari" };
                //context.Students.Add(student4);

                context.SaveChanges();
                Console.WriteLine("Students Added");
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Once you execute the above code, please check the database table, and you should see the above two students as shown in the below image.

DatabaseGenerated Data Annotation Attribute in EF Core

DatabaseGeneratedOption.Identity in EF Core

It is also possible to mark the non-key properties as DB-generated properties in Entity Framework Core using the DatabaseGeneratedOption.Identity option. This option specifies that the property’s value will be generated automatically by the database on the INSERT SQL statement. This Identity property cannot be updated.

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

Let us modify the Student Entity as follows. As you can see here, we have applied the DatabaseGeneratedOption.Identity option with DatabaseGenerated Attribute on SequenceNumber property. This will mark the SequenceNumber as the Identity Column in the database. One more point that you need to remember is in a database table, only one column can be marked as Identity. So, in this case, StudentId will be the Primary Key column without Identity, and SequenceNumber will be the Identity column.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
    public class Student
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }

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

With the above changes, open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving EFCoreDBMigration2. The name that you are giving it should not be given earlier.

DatabaseGenerated Data Annotation Attribute in EF Core

Now, if you check the Students database table Create SQL Script, then you will see it created the primary key column without Identity and SequenceNumber with Identity column as shown in the below image.

DatabaseGenerated Data Annotation Attribute in EF Core with Examples

Before proceeding further, let us truncate the Students database table by executing the following Truncate table statement.

Truncate table Students;

Modifying the Main Method:

Next, we need to modify the Main method of the Program class as follows. Here, you can see we are explicitly providing values for the StudentId but not for SequenceNumber. If we provide duplicate value or if we don’t provide any value for the StudentId, then it will throw a Runtime Exception.

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using EFCoreDbContext context = new EFCoreDbContext();

                //We need to provide unique StudentId value as StudentId is Primary Key
                //SequenceNumber is auto generated, so need to provide explicit value
                var student1 = new Student() { StudentId = 101, FirstName = "Pranaya", LastName = "Kumar" };
                context.Students.Add(student1);

                //We need to provide the unique StudentId value as StudentId is Primary Key
                //SequenceNumber is auto generated, so need to provide explicit value
                var student2 = new Student() { StudentId = 102, FirstName = "Hina", LastName = "Sharma" };
                context.Students.Add(student2);

                context.SaveChanges();
                Console.WriteLine("Students Added");
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}

Once you execute the above code, please check the database table, and you should see the above two students as shown in the below image.

DatabaseGenerated Data Annotation Attribute in EF Core with Examples

DatabaseGeneratedOption.Compute in EF Core

The DatabaseGeneratedOption.Compute option specifies that the property’s value will be generated by the database when the value is first saved and subsequently regenerated every time the value is updated. The practical effect is that Entity Framework will not include the property in INSERT or UPDATE statements but will obtain the computed value from the database on retrieval.

Similar to 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.

Database providers differ in the way that values are automatically generated. Some will generate values for the Selected Data Types, such as Identity, RowVersion, and GUID. Others may require manual configuration, such as setting default values or triggers or configuring the column as Computed.

Before proceeding further, let us truncate the Students database table by executing the following Truncate table statement.

Truncate table Students;

Let us modify the Student Entity as follows. As you can see here, we have applied the DatabaseGeneratedOption.Computed option with DatabaseGenerated Attribute on CreatedDate and FullName properties. This will mark the CreatedDate and FullName columns as the Computed Column in the database, and for the Computed column, we don’t need to pass any values.

using System.ComponentModel.DataAnnotations.Schema;
namespace EFCoreCodeFirstDemo.Entities
{
    public class Student
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public int StudentId { get; set; }
        public string? FirstName { get; set; }
        public string? LastName { get; set; }

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

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

        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public string? FullName { get; set; }
    }
}
Modifying the Context Class:

We also need to provide some logic so that the values for this column are calculated and stored in the database automatically. For this, in EF Core, we need to use Fluent API. So, modify the context class as follows. As you can see inside the OnModelCreating Method, we use Fluent API to set the computed column values. Here, we have set the GetUtcDate as the value for the CreatedDate property and First Name + Last Name as the value for the Full Name Property.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace EFCoreCodeFirstDemo.Entities
{
    public class EFCoreDbContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            //Configuring the Connection String
            optionsBuilder.UseSqlServer(@"Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV;Database=EFCoreDB;Trusted_Connection=True;TrustServerCertificate=True;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //We will discuss OnModelCreating Method and Fluent API In Details in our upcoming articles
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<Student>()
                .Property(u => u.FullName)
                .HasComputedColumnSql("[FirstName] + ' ' + [LastName]");

            modelBuilder.Entity<Student>()
                .Property(p => p.CreatedDate)
                .HasComputedColumnSql("GetUtcDate()");
        }

        public DbSet<Student> Students { get; set; }
    }
}

Note: In our upcoming articles, we will discuss the OnModelCreating Method and Fluent API In Detail.

With the above changes, open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give any name to your migration. Here, I am giving EFCoreDBMigration3. The name that you are giving it should not be given earlier.

DatabaseGenerated Data Annotation Attribute in Entity Framework Core with Examples

Now, if you check the Students database table Create SQL Script, then you will see it created the primary key column without Identity and SequenceNumber with Identity column, Fullname, and CreatedDate as Computed Columns, as shown in the below image.

DatabaseGenerated Data Annotation Attribute in Entity Framework Core with Examples

Next, modify the Main method of the Program class as follows:

using EFCoreCodeFirstDemo.Entities;
namespace EFCoreCodeFirstDemo
{
    internal class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using EFCoreDbContext context = new EFCoreDbContext();

                //We need to provide unique StudentId value as StudentId is Primary Key
                //SequenceNumber is auto generated, so need to provide explicit value
                //CreatedDate is Computed, so need to provide the value
                var student1 = new Student() { StudentId = 101, FirstName = "Pranaya", LastName = "Kumar" };
                context.Students.Add(student1);

                context.SaveChanges();

                Console.WriteLine("Student Details:");
                var student = context.Students.Find(101);
                Console.WriteLine($"Id: {student?.StudentId}, FirsName: {student?.FirstName}, FirsName: {student?.LastName}");
                Console.WriteLine($"FullName: {student?.FullName}, SequenceNumber: {student?.SequenceNumber}, CreatedDate: {student?.CreatedDate}");
                
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}"); ;
            }
        }
    }
}
Output:

DatabaseGenerated Data Annotation Attribute in Entity Framework Core

Once you execute the above code, please check the database table, and you should see the data as expected, as shown in the below image.

DatabaseGenerated Data Annotation Attribute in Entity Framework Core

Advantages and Disadvantages of DatabaseGenerated Attribute in EF Core

The DatabaseGenerated attribute in Entity Framework Core (EF Core) allows developers to specify how values are generated by the database for specific properties of an entity. The attribute can take values like Identity, Computed, and None from the DatabaseGeneratedOption enum. Let’s understand the advantages and disadvantages of using the DatabaseGenerated attribute in EF Core:

Advantages of DatabaseGenerated Attribute in EF Core:
  • Automatic Value Generation: It simplifies scenarios where you want the database to automatically generate values (e.g., identity columns, computed columns).
  • Consistency: By offloading the generation of certain values to the database, you can ensure consistency, especially if multiple applications or services are interacting with the same database.
  • Concurrency Control: Combined with properties like RowVersion (or timestamp), DatabaseGenerated can be used for optimistic concurrency control, helping to prevent conflicts when multiple users or services try to update the same record simultaneously.
  • Optimization: Some databases optimize inserts for identity columns or have specialized functions to efficiently generate certain types of values (e.g., NEWID() or NEWSEQUENTIALID() in SQL Server for GUIDs).
  • Reduced Application Complexity: Instead of writing custom code to generate values or compute certain properties, you can delegate that responsibility to the database.
  • Data Integrity: Ensures that certain fields (like identity columns) are unique and are not accidentally overwritten or mismanaged by application logic.
Disadvantages of DatabaseGenerated Attribute in EF Core:
  • Database Dependency: Your application’s logic becomes tightly coupled with specific database behaviors, potentially making it harder to switch databases or work with multiple database providers.
  • Reduced Flexibility: In some scenarios, having the database generate values might reduce flexibility, especially if you need to enforce business rules or complex logic during value generation.
  • Potential Performance Overhead: Some database-generated operations might introduce performance overhead, especially if not properly optimized (e.g., non-sequential GUID generation causing index fragmentation).
  • Migration Concerns: Changing how values are generated (e.g., from client-side to database-generated or vice versa) can introduce complexities in database migrations.
  • Debugging and Troubleshooting: When values are generated or computed by the database, it can sometimes be harder to troubleshoot issues or bugs related to those values, as the logic is not present in the application layer.
  • Concurrency Issues with Computed Values: If not handled properly, relying on database-computed values can sometimes lead to concurrency issues, especially if multiple operations depend on the computed value.

While the DatabaseGenerated attribute offers convenient ways to manage and delegate value generation to the database, developers need to be aware of its implications. It’s essential to understand the advantages against the disadvantages based on the specific needs of the project and the characteristics of the chosen database system.

In the next article, I will discuss TimeStamp Attribute in Entity Framework Core with Examples. Here, in this article, I try to explain the DatabaseGenerated Data Annotation Attribute in Entity Framework Core with Examples. I hope you enjoyed this DatabaseGenerated Attribute in EF Core with Examples article.

Leave a Reply

Your email address will not be published. Required fields are marked *