DatabaseGenerated Attribute in Entity Framework

Use DatabaseGenerated Attribute on a property whose value is automatically generated by the Database. This attribute is part of the System.ComponentModel.DataAnnotations.Schema.Namespace.

Table of Contents

DatabaseGenerated Attribute
DatabaseGeneratedOption
References

DatabaseGenerated Attribute

You may have computed fields in your database. The database updates the computed fields when you insert or update the data. For Example, database inserts the new values for Identity or Guid columns, when you insert the data. The inserted data must be updated in the model, once the saveChanges is called

DatabaseGeneratedOption

This attribute takes DatabaseGeneratedOption enumeration option, which has three options

Identity: The database generates value when we insert the row.
Computed: Database generates a value for the property when we insert or update the row.
None: Database does not generate a value for the property either in insert or in an update.

Note that how DatabaseGenerated implemented differs from the one database provider to another database provider. The Examples in this article uses the SQL Server

DatabaseGenerated.None

This prevents the database from creating the computed values. The user must provide the value.

This is useful when you want to disable the generation of identity for the integer primary key. The following domain model Customer, with the DatabaseGeneratedOption.None attribute will create the CustomerID column with identity disabled.

public class Customer
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int  CustomerID { get; set; }

  public int SrNo { get; set; }
  public string Test { get; set; }
  public string CustomerName { get; set; }
  public DateTime? Created { get; set; } = DateTime.UtcNow;

}

DatabaseGenerated.Identity

When we apply Identity attribute to a property, the entity framework expects that the database will compute its value when we insert a new row.

If we apply this attribute to the numeric property, the Entity Framework will create the identity column in the database. Remember that you can have only one identity column in the database.

public class Customer
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int  CustomerID { get; set; }

  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int SrNo { get; set; }
  public string Test { get; set; }
  public string CustomerName { get; set; }
  public DateTime? Created { get; set; } = DateTime.UtcNow;

}

The above will create the SrNo as Identity column in the database. The entity framework also retrieves the computed value from the database after the insert.

When you insert the data into the table, the EF generates the following queries. Note that the insert query does not contain SrNo field. Once inserted, the EF attempts to read the SrNo from the Database, so as to update the SrNo field.

INSERT INTO [Customer] ([CustomerID], [Created], [CustomerName], [Test])
VALUES (@p0, @p1, @p2, @p3);

SELECT [SrNo]
FROM [Customer]
WHERE @@ROWCOUNT = 1 AND [CustomerID] = @p0;

While in case of update the EF generates the following query. The SrNo is not in the Insert query, because EF knows that the database generates the value only when inserting the values.

UPDATE [Customer] SET [Created] = @p0, [CustomerName] = @p1, [Test] = @p2
WHERE [CustomerID] = @p3;
SELECT @@ROWCOUNT;

DatabaseGenerated.Computed

This is useful in scenarios where you have computed columns in your database. The Database computes the value of these fields after each insert/update operation. The entity framework will not update these columns. But it will query and return the values of these fields after an insert or update operation.

public class Customer
{
  [Key]
  [DatabaseGenerated(DatabaseGeneratedOption.None)]
  public int  CustomerID { get; set; }

  [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public int SrNo { get; set; }
  public string Test { get; set; }

  [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
  public string CustomerName { get; set; }

  [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
  public DateTime? Created { get; set; } = DateTime.UtcNow;

}

In the above model, we have two computed fields. CustomerName & Created.

Use the following code to insert data into the table. Both customerName & created fields are given value.

   EFContext ctx = new EFContext();
   ctx.ConfigureLogging(s => Console.WriteLine(s));

   var c = new Customer();
   c.Test = "Test";
   c.CustomerName = "Test";
   c.Created = Convert.ToDateTime("2018-01-01");
   ctx.Customer.Add(c);
   ctx.SaveChanges();

The FE generates the following SQL statement. As you can see the EF does not insert the values to the customerName & Created fields, but it tries to retrieve the value of these fields immediately after the insert. i.e. because EF expects that the database to compute the value of these fields.

INSERT INTO [Customer] ([CustomerID], [Test])
VALUES (@p0, @p1);

SELECT [Created], [CustomerName], [SrNo]
FROM [Customer]
WHERE @@ROWCOUNT = 1 AND [CustomerID] = @p0;

Similarly, the update query does not update the computed fields but retrieves them after the update expecting that the database may have computed and updated these fields

UPDATE [Customer] SET [Test] = @p0
WHERE [CustomerID] = @p1;

SELECT [Created], [CustomerName]
FROM [Customer]
WHERE @@ROWCOUNT = 1 AND [CustomerID] = @p1;

In both, the above examples, the database inserts the null value into the CustomerName & Created fields as we are not generating any values for them in the database. For testing purposes, you can use the following insert trigger to insert values and check the result.

CREATE TRIGGER insTrigger 
ON dbo.Customer
AFTER INSERT AS
BEGIN
   update Customer
   set customerName='Default Customer Name', created=GETDATE()
   from inserted
   where Customer.CustomerID=inserted.CustomerID;
   
END
GO