ValueGeneratedOnAdd has no effect

c# entity-framework-core

Question

For example, I have the next table:

CREATE TABLE Person (
    id int IDENTITY(1,1) NOT NULL PK,
    firstName nvarchar(20) NOT NULL,
    lastName nvarchar(30) NOT NULL,
    birtdate datetimeoffset(7) NOT NULL,
    age int,
    height int,
)

There is a part of entity mapping:

public void Configure(EntityTypeBuilder<Person> builder)
{
    ...
    builder.HasKey(k => k.Id);

    builder.Property(k => k.Id)
        .ValueGeneratedOnAdd()
        .IsRequired();
    ...
}

I have the MS SQL server with that table on the client side and the same table on the server side. My program retrieves data from the table on the client and sends it to server where data is added to database. There are no problems during retrieving data but when I try to insert data I get the error:

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

despite of .ValueGeneratedOnAdd()

When I retrieve data I get entities with filled Id properties (1, 2, 3 etc) but I think It doesn't matter.

1
2
1/18/2019 8:43:00 AM

Accepted Answer

When I retrieve data I get entities with filled Id properties (1, 2, 3 etc) but I think It doesn't matter.

Actually it does matter and is the cause of the problem. ValueGenerated has no effect for properties which have set value to the non default value for the type (null for nullable types, 0 for numeric types etc.).

The behavior is explained in the Generated Values section of the EF Core documentation:

If you add an entity to the context that has a value assigned to the property, then EF will attempt to insert that value rather than generating a new one. A property is considered to have a value assigned if it is not assigned the CLR default value (null for string, 0 for int, Guid.Empty for Guid, etc.). For more information, see Explicit values for generated properties.

This is basically to allow the so called "identity inserts" scenarios when you upload data. For more info, see Explicit values into SQL Server IDENTITY columns.

With that being said, if you don't want that behavior, make sure all your entity Id (PK) properties are set to 0 before calling adding the entity to the db context/set.

3
1/17/2019 5:00:23 PM

Popular Answer

I solved adding UseSqlServerIdentityColumn() in the configuration of the Id property:

builder.Property(k => k.Id)
    .ValueGeneratedOnAdd()
    .UseSqlServerIdentityColumn()
    ...

Note that it's part of SqlServerPropertyBuilderExtensions inside Microsoft.EntityFrameworkCore.SqlServer package.

Note, .UseSqlServerIdentityColumn() has been deprecated and changed to .UseIdentityColumn() for "Microsoft.EntityFrameworkCore.SqlServer" versions 3.x and (3.0 and 3.1 at the time of writing this)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow