c# - Entity Framework Core does not respect Identity columns

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)

标签： c#entity-frameworktsqlentity-framework-coreauto-increment

Answer 1

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

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

    }

尝试这样做。Ef 核心依赖：Microsoft.EntityFrameworkCore.SqlServer