Entity Framework Core does not respect Identity columns

auto-increment c# entity-framework entity-framework-core tsql

Question

Entity Framework is not respecting my Identity columns. It insists on trying to insert a value into an Identity (auto-increment) column in my MS SQL DB, which is obviously an error since the DB is supposed to supply the value.

System.Data.SqlClient.SqlException: 'Cannot insert explicit value for identity column in table 'Assignee' when IDENTITY_INSERT is set to OFF.'

Why is it trying to do that? I've paired it down to a schema involving one table and one column:

CREATE TABLE [dbo].[Assignee](
  [AssigneeID] INT IDENTITY(-1, 1) NOT NULL
CONSTRAINT [Assignee$PrimaryKey] PRIMARY KEY CLUSTERED 
( [AssigneeID] ASC ))

After publishing this schema to my local DB I use Scaffold-DbContext to generate entity and context classes. The generated Assignee class contains just this public property.

public int AssigneeId { get; set; }

The context only refers to Assignee here:

modelBuilder.Entity<Assignee>(entity =>
{
  entity.Property(e => e.AssigneeId).HasColumnName("AssigneeID");
});

Searching around I see people claiming that for E.F. to respect Identity columns, the context should configure the property with ValueGeneratedOnAdd(). In other words, the line in the context class should read:

entity.Property(e => e.AssigneeId).HasColumnName("AssigneeID")
  .ValueGeneratedOnAdd();

I have two problems with this:

  1. I'm starting with an existing DB and generating entity classes. If I need ValueGeneratedOnAdd() then why isn't Scaffold-DbContext generating it?
  2. Even if I manually edit the generated context class and add ValueGeneratedOnAdd() it still doesn't work with the same error.

Elsewhere I see suggestions to use UseSqlServerIdentityColumn(). That also doesn't work for me. Points 1 and 2 still apply.

Any help would be greatly appreciate. Please don't suggest that I use IDENTITY_INSERT as that defeats the entire point of using auto-increment columns.

(I am using Entity Framework Core 2.2.3 and Microsoft SQL Server 14)

1
2
4/16/2019 10:08:21 PM

Popular Answer

 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Todo>(entity =>
            {
        entity.Property(x => x.Id)
                    .HasColumnName("id")
                    .HasColumnType("int")
                    .ValueGeneratedOnAdd()
                    **.UseIdentityColumn();**

    }

Try do this. Ef Core Dependency : Microsoft.EntityFrameworkCore.SqlServer

0
1/10/2020 10:17:50 AM


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