Efcore: 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

```c#

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:

```sql
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:
C# 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:
C# 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.