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)

Answer 1

This works for me:

modelBuilder.Entity<Assignee>().Property(e => e.AssigneeId).UseIdentityColumn();

So UseIdentityColumn() is the key.

I'm using Microsoft.EntityFrameworkCore.SqlServer v3.1.8.

Answer 2

Short version

We are getting and experiencing different results here one can reproduce the issue, others can not. My experience it depends on if the Id property's value is 0 or not.

Detailed version

My experience, that the default behavior (based on name convention) is definitely working, so in case you are naming your db entity's attribute (C# property) to Id or EntityNameId it should work. No C# entity class attributes neither OnModelCreating config is necessary. The same time if the issue is there neither No C# entity class attributes neither OnModelCreating config will fix it.

...because if the Id property's value is not 0, the generated SQL will contain the explicit field name and value, so we got the error. This is clearly and issue in EF core, but workaround is easy..

Answer 3

 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

Answer 4

I've tried to reproduce this issue based on your example but it appears to work just fine. I did not use Scaffold though, just coded class and I tried the model creating code you had and it hasn't had an issue. I suspect there has to be more to this though because with just the "Assignee" class, EF convention is expecting an "Assignees" table, so I suspect there is more mapping being set up.

Tested with EF Core 2.0.3 and 2.2.4

DB: used the OP's script.

Entity:

[Table("Assignee")]
public class Assignee
{
    public int AssigneeId { get; set; }
}

I had to use the Table attribute to map to the table name.

Context:

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

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

as-per OP comment.

Test:

   [Test]
    public void TestIncrement()
    {
        using (var context = new TestDbContext())
        {
            var newItem = new Assignee();
            context.Assignees.Add(newItem);
            context.SaveChanges();
        }
    }

Works as expected.

However, what I'd normally have for the entity:

[Table("Assignee")]
public class Assignee
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity), Column("AssigneeID")]
    public int AssigneeId { get; set; }
}

And then nothing for this column needed in the context OnModelCreating override.

I suspect that there is some additional configuration lurking somewhere given there is no mention of the table name issue, either manually added or via scaffold that is goofing up EF. I was full-on expecting EF to fail without the Key/DbGenerated attributes, but it seemed to work just fine.

Edit: Also tried this with scafolding running Scaffold-DbContext across the existing schema. Again, worked without an issue. For comparison against your tests:

Generated DbContext: (Unaltered save removing the warning and connection string details.)

public partial class AssigneeContext : DbContext
{
    public AssigneeContext()
    {
    }

    public AssigneeContext(DbContextOptions<AssigneeContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Assignee> Assignee { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("Data Source=machine\\DEV;Initial Catalog=Spikes;uid=user;pwd=password;MultipleActiveResultSets=True");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasAnnotation("ProductVersion", "2.2.4-servicing-10062");

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

Generated Entity: (Unaltered)

public partial class Assignee
{
    public int AssigneeId { get; set; }
}

I did figure out why my table annotation was needed. EF Core (Not sure if applies to EF6 as well) was basing the convention for the table name on the DbSet variable name in the DbContext. I couldn't see any config difference with the scaffold generated context and my own, except the DbSet name. I renamed my original DbContext's DbSet name to "Assignee" and it worked without the Table attribute.

That said, based on the information present your code should work. Something is lurking in the details because this example does work so you will need to provide more detail about an example that definitely doesn't work in your case.

Answer 5

For DB first try adding [key] as a data annotation

With Data annotation

[Key]
public int AssigneeId { get; set; }

fluent API

modelBuilder.Entity<Assignee>()
        .HasKey(o => o.AssigneeId);

See here or here if you want to use fluent API