Efcore: Cannot Add Entity with Identity Primary Key when Entity has One to Zero or One relationship via Composite Foreign Key that includes its Primary Key

[MacKinley]

We have a Parent Child relationship where the Parent has many Children as well as One or Zero Special Children. The Child also has a One to Many relationship with a ChildSpecification Entity.

The Primary Key for the Parent is an Identity Column int name ParentId, the Primary Key for the ChildSpecification is also an Identity Column int name ChildSpecificationId, and the Primary Key for the Child is a Composite Key consisting of the ParentId and the ChildSpecificationId where they are both respective Foreign Keys. The SpecialChild relationship is formed via a Nullable SpecialChildSpecificationId int on the Parent so that the Foreign Key is its Primary Key and the SpecialChildSpecificationId.

I have created all the entities, setup all the specification using the Model Builder fluent api, created the migrations and applied them to a DB successfully. The issue is when I attempt to create a Parent EF Core attempts to explicitly set the ParentId which causes an error since it is a Identity Column.

Also sorry about the long title.

Steps to reproduce

Parent.cs:
``` C#
public class Parent
{
public int ParentId { get; set; }

public string Name { get; set; }

public int? SpecialChildId { get; set; }

public Child SpecialChild { get; set; }

public List Children { get; set; } = new List();
}

Child.cs:
``` C#
public class Child
{
  public int ParentId { get; set; }

  public int ChildSpecificationId { get; set; }

  public string Name { get; set; } 

  public Parent Parent { get; set; }

  public Parent ParentWhoThinksImSpecial { get; set; }

  public ChildSpecification ChildSpecification { get; set; }
}

ChildSpecification.cs:
``` C#
public class ChildSpecification
{
public int ChildSpecificationId { get; set; }

public string Specification { get; set; }

public List ChildrenWithSpecification { get; set; } = new List();
}

MyDbContext.cs
``` C#
public class MyDbContext : DbContext
{
  public DbSet<Parent> Parents { get; set; }
  public DbSet<Child> Children { get; set; }
  public DbSet<ChildSpecification> ChildSpecifications { get; set; }

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

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
    modelBuilder.Entity<Parent>()
      .HasKey(parent => parent.ParentId);
    modelBuilder.Entity<Parent>()
      .Property(parent => parent.ParentId)
      .UseIdentityColumn();

    modelBuilder.Entity<Parent>()
      .HasOne(parent => parent.SpecialChild)
      .WithOne(specialChild => specialChild.ParentWhoThinksImSpecial)
      .HasForeignKey<Parent>(parent => new { parent.ParentId, parent.SpecialChildId })
      .HasPrincipalKey<Child>(specialChild => new { specialChild.ParentId, specialChild.ChildSpecificationId })
      .OnDelete(DeleteBehavior.Restrict);


    modelBuilder.Entity<Child>()
      .HasKey(child => new { child.ParentId, child.ChildSpecificationId });

    modelBuilder.Entity<Child>()
      .HasOne(child => child.Parent)
      .WithMany(parent => parent.Children)
      .HasForeignKey(child => child.ParentId)
      .HasPrincipalKey(parent => parent.ParentId)
      .OnDelete(DeleteBehavior.Restrict);

    modelBuilder.Entity<Child>()
      .HasOne(child => child.ChildSpecification)
      .WithMany(childSpecification => childSpecification.ChildrenWithSpecification)
      .HasForeignKey(child => child.ChildSpecificationId)
      .HasPrincipalKey(childspecification => childspecification.ChildSpecificationId)
      .OnDelete(DeleteBehavior.Restrict);


    modelBuilder.Entity<ChildSpecification>()
      .HasKey(childSpecification => childSpecification.ChildSpecificationId);
    modelBuilder.Entity<ChildSpecification>()
      .Property(childSpecification => childSpecification.ChildSpecificationId)
      .UseIdentityColumn();
  }
}

MyMigration.cs
``` C#
public partial class MyMigration : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "ChildSpecifications",
columns: table => new
{
ChildSpecificationId = table.Column(nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Specification = table.Column(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_ChildSpecifications", x => x.ChildSpecificationId);
});

    migrationBuilder.CreateTable(
        name: "Parents",
        columns: table => new
        {
            ParentId = table.Column<int>(nullable: false)
                .Annotation("SqlServer:Identity", "1, 1"),
            Name = table.Column<string>(nullable: true),
            SpecialChildId = table.Column<int>(nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Parents", x => x.ParentId);
        });

    migrationBuilder.CreateTable(
        name: "Children",
        columns: table => new
        {
            ParentId = table.Column<int>(nullable: false),
            ChildSpecificationId = table.Column<int>(nullable: false),
            Name = table.Column<string>(nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Children", x => new { x.ParentId, x.ChildSpecificationId });
            table.ForeignKey(
                name: "FK_Children_ChildSpecifications_ChildSpecificationId",
                column: x => x.ChildSpecificationId,
                principalTable: "ChildSpecifications",
                principalColumn: "ChildSpecificationId",
                onDelete: ReferentialAction.Restrict);
            table.ForeignKey(
                name: "FK_Children_Parents_ParentId",
                column: x => x.ParentId,
                principalTable: "Parents",
                principalColumn: "ParentId",
                onDelete: ReferentialAction.Restrict);
        });

    migrationBuilder.CreateIndex(
        name: "IX_Children_ChildSpecificationId",
        table: "Children",
        column: "ChildSpecificationId");

    migrationBuilder.CreateIndex(
        name: "IX_Parents_ParentId_SpecialChildId",
        table: "Parents",
        columns: new[] { "ParentId", "SpecialChildId" },
        unique: true,
        filter: "[SpecialChildId] IS NOT NULL");

    migrationBuilder.AddForeignKey(
        name: "FK_Parents_Children_ParentId_SpecialChildId",
        table: "Parents",
        columns: new[] { "ParentId", "SpecialChildId" },
        principalTable: "Children",
        principalColumns: new[] { "ParentId", "ChildSpecificationId" },
        onDelete: ReferentialAction.Restrict);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
        name: "FK_Children_ChildSpecifications_ChildSpecificationId",
        table: "Children");

    migrationBuilder.DropForeignKey(
        name: "FK_Children_Parents_ParentId",
        table: "Children");

    migrationBuilder.DropTable(
        name: "ChildSpecifications");

    migrationBuilder.DropTable(
        name: "Parents");

    migrationBuilder.DropTable(
        name: "Children");
}

}

Program.cs
``` C#
class Program
{
  static void Main(string[] args)
  {
    var dbOptions = new DbContextOptionsBuilder<MyDbContext>()
      .UseSqlServer("MyConnectionString")
      .Options;
    using (var db = new MyDbContext(dbOptions))
    {
      var parent = new Parent
      {
          Name = "Bob"
      };

      db.Add(parent);
      db.SaveChanges(); // <- This is where error occurs
    }
  }
}

Exception being thrown: (Cannot insert explicit value for identity column in table 'Parents' when IDENTITY_INSERT is set to OFF.)

Microsoft.EntityFrameworkCore.DbUpdateException
  HResult=0x80131500
  Message=An error occurred while updating the entries. See the inner exception for details.
  Source=Microsoft.EntityFrameworkCore.Relational
  StackTrace:
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at SmsService.DataLayerMigrator.Program.Main(String[] args) in Program.cs:line 15

Inner Exception 1:
SqlException: Cannot insert explicit value for identity column in table 'Parents' when IDENTITY_INSERT is set to OFF.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1586
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1169
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1719
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData() in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 1123
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData() in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlDataReader.cs:line 257
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 4457
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 4276
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 3973
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) in E:\agent1\_work\34\s\src\Microsoft.Data.SqlClient\netcore\src\Microsoft\Data\SqlClient\SqlCommand.cs:line 1710
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

After enabling logging I confirmed that EF was trying to insert the element while explicitly setting the ParentId column.

Logs:

dbug: Microsoft.EntityFrameworkCore.Database.Command[20103]
      Creating DbCommand for 'ExecuteReader'.
dbug: Microsoft.EntityFrameworkCore.Database.Command[20104]
      Created DbCommand for 'ExecuteReader' (16ms).
dbug: Microsoft.EntityFrameworkCore.Database.Command[20100]
      Executing DbCommand [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 4000), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Parents] ([ParentId], [Name], [SpecialChildId])
      VALUES (@p0, @p1, @p2);
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (110ms) [Parameters=[@p0='?' (DbType = Int32), @p1='?' (Size = 4000), @p2='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      SET NOCOUNT ON;
      INSERT INTO [Parents] ([ParentId], [Name], [SpecialChildId])
      VALUES (@p0, @p1, @p2);

After commenting out the One to One or Zero SpecialChild relationship in DbContext.OnModelCreating(...), I was able to successfully run Program.cs and EF added the Parent.

Also, as another test, I changed all the Primary Keys to Guid and generated the Ids in the code via Guid.NewGuid() and was able to add all my entities successfully.
EditedProgram.cs:
``` C#
class Program
{
static void Main(string[] args)
{
var dbOptions = new DbContextOptionsBuilder()
.UseSqlServer("MyConnectionString")
.Options;
using (var db = new MyDbContext(dbOptions))
{
var parent = new Parent
{
ParentId = Guid.NewGuid(),
Name = "Bob"
};

  db.Add(parent);
  db.SaveChanges();

  var childSpec1 = new ChildSpecification
  {
    ChildSpecificationId = Guid.NewGuid(),
    Specification = "spec1"
  };
  var childSpec2 = new ChildSpecification
  {
    ChildSpecificationId = Guid.NewGuid(),
    Specification = "spec2"
  };

  db.Add(childSpec1);
  db.Add(childSpec2);
  db.SaveChanges();

  var child1 = new Child
  {
    Parent = parent,
    ChildSpecification = childSpec1,
    Name = "Jill"
  };
  var child2 = new Child
  {
    Parent = parent,
    ChildSpecification = childSpec2,
    Name = "Jack"
  };

  db.Add(child1);
  db.Add(child2);
  parent.SpecialChild = child1;
  db.SaveChanges();
}

}
}
```

Further technical details

EF Core version: 3.1.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 2.1
Operating system: Windows 10
IDE: Visual Studio 2019 16.1.3

[ajcvickers]

@AndriySvyryd I am able to reproduce this still on EF Core 5.0. Model is below. Parent.ParentId is created as an Identity column in the database, but the property is not marked as ValueGenerated.OnAdd.

Model: 
  EntityType: Child
    Properties: 
      ParentId (int) Required PK FK AfterSave:Throw
      ChildSpecificationId (int) Required PK FK Index AfterSave:Throw
      Name (string)
    Navigations: 
      ChildSpecification (ChildSpecification) ToPrincipal ChildSpecification Inverse: ChildrenWithSpecification
      Parent (Parent) ToPrincipal Parent Inverse: Children
      ParentWhoThinksImSpecial (Parent) ToDependent Parent Inverse: SpecialChild
    Keys: 
      ParentId, ChildSpecificationId PK
    Foreign keys: 
      Child {'ChildSpecificationId'} -> ChildSpecification {'ChildSpecificationId'} ToDependent: ChildrenWithSpecification ToPrincipal: ChildSpecification Restrict
      Child {'ParentId'} -> Parent {'ParentId'} ToDependent: Children ToPrincipal: Parent Restrict
    Indexes: 
      ChildSpecificationId
  EntityType: ChildSpecification
    Properties: 
      ChildSpecificationId (int) Required PK AfterSave:Throw ValueGenerated.OnAdd
      Specification (string)
    Navigations: 
      ChildrenWithSpecification (List<Child>) Collection ToDependent Child Inverse: ChildSpecification
    Keys: 
      ChildSpecificationId PK
  EntityType: Parent
    Properties: 
      ParentId (int) Required PK FK Index AfterSave:Throw
      Name (string)
      SpecialChildId (Nullable<int>) FK Index
    Navigations: 
      Children (List<Child>) Collection ToDependent Child Inverse: Parent
      SpecialChild (Child) ToPrincipal Child Inverse: ParentWhoThinksImSpecial
    Keys: 
      ParentId PK
    Foreign keys: 
      Parent {'ParentId', 'SpecialChildId'} -> Child {'ParentId', 'ChildSpecificationId'} Unique ToDependent: ParentWhoThinksImSpecial ToPrincipal: SpecialChild Restrict
    Indexes: 
      ParentId, SpecialChildId Unique

[eddyzhu1980]

I have same issue. Can you please advise what is the solution?

[eddyzhu1980]

Hi @ajcvickers ,

I managed to fix it by adding ValueGeneratedOnAdd(). Below code works now. It seems EC core 3.15 doesn't respect UseIdentityColumn(). I have to add ValueGeneratedOnAdd() so EC core can know OrderDetailId column is identity column. Can you please advise why? should I add ValueGeneratedOnAdd() for all the identity columns?

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

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

[ajcvickers]

@eddyzhu1980 This is a bug that has not been fixed yet. Adding .ValueGeneratedOnAdd() where you are hitting this is the correct workaround.

[MacKinley]

@ajcvickers, .ValueGeneratedOnAdd() did seem to work but I ran into another issue when writing tests with the UseInMemoryDatabase(...) call. When I tried to Add() 2 or more Parents, the context did not assign Parent.ParentId on Add nor on SaveChanges(). Instead they remained 0 and when trying to Add() the second parent, an exception occurred because they both had Parent.ParentId of 0. My workaround was to explicitly set the ParentIds in the tests. Again, I did not have an issue when my DB was setup using UseSqlServer(...) only with UseInMemoryDatabase(...).