CODEWITHSUNDEEP
sql-serverentity-framework
Nicholas Hunter
Nicholas Hunter

Reputation: 11

Entity Framework DatabaseGeneratedOption SQL Server GETDATE

My table includes an identity column, a created and a created by columns.

CREATE TABLE dbo.T_CA_EMPLOYEE 
(
    ID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    FIRST_NAME VARCHAR(50),
    LAST_NAME VARCHAR(50),
    CREATED DATETIME NOT NULL DEFAULT GETDATE(),
    CREATED_BY VARCHAR(30) NOT NULL DEFAULT CURRENT_USER
); GO

Class definition (generated by code-first with existing database):

Public Class T_CA_EMPLOYEE

    Public Property ID As Integer

    <StringLength(50)>
    Public Property FIRST_NAME As String

    <StringLength(50)>
    Public Property LAST_NAME As String

    Public Property CREATED As Date

    <Required>
    <StringLength(30)>
    Public Property CREATED_BY As String

End Class

The code:

Using ctx As New Model1
    Dim newEmployee As T_CA_EMPLOYEE = New T_CA_EMPLOYEE
    newEmployee.LAST_NAME = "New Employee"
    ctx.T_CA_EMPLOYEE.Add(newEmployee)
    ctx.SaveChanges()
    logger.DebugFormat("Created new employee {0}", newEmployee.ToString)
End Using

I am not clear how to annotate the domain object properties.

When I run the code as above, I get the following

EntityValidationError: CREATED_BY: The CREATED_BY field is required

If I remove the <Required> attribute from the CREATED_BY property, and add <DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)> attributes to the CREATED and CREATED_BY properties, run the code, I get the following error:

Cannot insert explicit value for identity column in table 'T_CA_EMPLOYEE' when IDENTITY_INSERT is set to OFF.

If I add the <DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)> annotation to the ID column, everything seems to work.

Two questions:

  1. Am I using the correct DatabaseGeneratedAttribute tags for the three properties?

  2. Since ID follows the naming convention for an identity/primary key, I thought <DatabaseGenerated(DatabaseGeneratedOption.Identity)> would be unnecessary. Why do I get an error if I omit the annotation?

Thanks in advance for your help.

Upvotes: 1

Views: 178

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205759

1) Am I using the correct DatabaseGeneratedAttribute tags for the three properties?

Correct. DatabaseGeneratedOption.Identity is the only way to tell EF that the column value is generated by the database when inserting a new record.

2) Since ID follows the naming convention for an identity/primary key, I thought would be unnecessary. Why do I get an error if I omit the annotation?

It's true that normally EF convention implies identity for integer type PK. At the same time it assumes (probably wrong) that the tables have only one identity column defined, so when you explicitly mark some other column as identity, the default convention is not applied and you are supposed to explicitly mark the other identity columns, including the PK.

Upvotes: 1

Related Questions