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

[rotem925]

EF adds identity on composite key where it shouldn't - On EF core 2.2 this Identity was not set when creating a composite key.

This is the exception I get when trying to add a new row:

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

Steps to reproduce

I'm adding a code that you will be able to run and reproduce it.
Pay attention for the Identity that was added to the Key column

using Microsoft.EntityFrameworkCore;

namespace EFCoreBug
{
    public class BloggingContext : DbContext
    {
        public BloggingContext()
        {
            
        }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder
                .UseSqlServer("Server=(localdb)\\mssqllocaldb;Database=Test;Connect Timeout=5");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Table1>().HasKey(a=>a.Id);
            modelBuilder.Entity<Table2>().HasKey(a => new { a.Table1Id, a.Key});
        }
    }

    public class Table1
    {
        public int Id { get; set; }

    }
    public class Table2
    {
        public int Table1Id { get; set; }
        public int Key { get; set; }
        public Table1 Table1 { get; set; }
    }
   

    public class Program
    {
        public static void Main()
        {
           

            using (var context = new BloggingContext())
            {
                context.Database.EnsureDeleted();
                context.Database.EnsureCreated();
                var table1Set = context.Set<Table1>();
                table1Set.Add(new Table1());

                var table2Set = context.Set<Table2>();
                table2Set.Add(new Table2() { Table1Id = 1, Key = 2});
                context.SaveChanges();
            }
        }
    }
}

Bellow is the schema that was generated:

CREATE TABLE [dbo].[Table1]
(
[Id] [int] NOT NULL IDENTITY(1, 1)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table1] ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED ([Id]) ON [PRIMARY]


CREATE TABLE [dbo].[Table2]
(
[Table1Id] [int] NOT NULL,
[Key] [int] NOT NULL IDENTITY(1, 1)
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] ADD CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ([Table1Id], [Key]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table2] ADD CONSTRAINT [FK_Table2_Table1_Table1Id] FOREIGN KEY ([Table1Id]) REFERENCES [dbo].[Table1] ([Id]) ON DELETE CASCADE
GO

Further technical details

EF Core version: 3.0.0-preview3.19153.1
Database Provider: (Microsoft.EntityFrameworkCore.SqlServer
Operating system:
IDE: Visual Studio 2017 15.9.7

[baoduy]

I’m also facing the same issue. What is work around solution sine the fix is tag to version 3.0.0?

[ajcvickers]

@baoduy The workaround is to explicitly specify the property as not generated. For example:

modelBuilder.Entity<Table2>().Property(a => a.Table1Id).ValueGeneratedNever();

[baoduy]

Thanks @ajcvickers , I suspected this issue also happened with owned type

[RomainAn]

Stilll have the same issue even with ValueGeneratedNever() on EF Core 3.0.0-preview3.19153.1
It's happening when i'm benchmarking using BenchmarkDotNet and inserting 1000 items using AddRange.

[ajcvickers]

@rymix I just ran the code above but with the following:

modelBuilder.Entity<Table2>().Property(e => e.Table1Id).ValueGeneratedNever();
modelBuilder.Entity<Table2>().Property(e => e.Key).ValueGeneratedNever();

It worked for me, so it looks like you may be hitting something different. Can you file a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[ajcvickers]

We discussed in triage whether to retain this change in behavior--that is, that if there is a composite key where only one of the properties is numeric and not a foreign key, then it should be made store-generated and hence use an Identity column on SQL Server. While this does make sense (although the key space is larger than it needs to be) it would be a breaking change (as reported here) and on balance the value of doing this is not worth the break.

We will keep the new behavior for owned collections.

[eddyzhu1980]

I have this issue on 3.1.5. Can you please advise the solution?

[eddyzhu1980]

@rymix I just ran the code above but with the following:

modelBuilder.Entity<Table2>().Property(e => e.Table1Id).ValueGeneratedNever();
modelBuilder.Entity<Table2>().Property(e => e.Key).ValueGeneratedNever();

It worked for me, so it looks like you may be hitting something different. Can you file a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

This work-around doesn't work to me.

` [Table ("OrderDetails")]
public class OrderDetail :ITrackable
{
public int OrderDetailId { get; set; }
public int ShopId { get; set; }
public int OrderId { get; set; }
public Order Order { get;set;}
public int? ProductId { get; set; }
}

DBContext.cs:
builder.Entity()
.HasKey(s => new { s.ShopId, s.OrderId, s.OrderDetailId });

builder.Entity()
.Property(s => s.OrderDetailId)
.UseIdentityColumn();

builder.Entity()
.Property(s => s.OrderDetailId)
.ValueGeneratedNever();`

Error message:

Failed executing DbCommand (24ms) [Parameters=[@p0='2', @p1='9268', @p2='0', @p3='2020-06-18T13:16:55' (Nullable = true), @P4='1120' (Nullable = true), @p5='False' (Nullable = true), @p6='False' (Nullable = true), @P7='2020-06-18T13:16:55' (Nullable = true), @p8='1120' (Nullable = true), @p9='http://res.cloudinary.com/dzvbuqogl/image/upload/v1576804505/qcojj1xyg2gr2iqvqrof.jpg' (Size = 4000), @p10='15.4', @p11='4086' (Nullable = true), @p12='1', @P13='Swi Calcuim 100tablets' (Size = 4000), @p14='15.4', @P15='0.35' (Nullable = true)], CommandType='Text', CommandTimeout='60']
SET NOCOUNT ON;
INSERT INTO [OrderDetails] ([ShopId], [OrderId], [OrderDetailId], [CreatedAt], [CreatedBy], [IsPostage], [IsShippingFree], [LastUpdatedAt], [LastUpdatedBy], [PhotoUrl], [Price], [ProductId], [Quantity], [Title], [TotalPrice], [UnitWeight])
VALUES (@p0, @p1, @p2, @p3, @P4, @p5, @p6, @P7, @p8, @p9, @p10, @p11, @p12, @P13, @p14, @P15);
fail: Microsoft.EntityFrameworkCore.Update[10000]
An exception occurred in the database while saving changes for context type 'DatingApp.API.Data.DataContext'.
Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot insert explicit value for identity column in table 'OrderDetails' when IDENTITY_INSERT is set to OFF.