Unable to modify a row in table 'MyTable' because its key column 'Id' is null

[Liero]

File a bug

After I upgraded from .NET 6 and EF Core 6 to .NET 7 and EF Core 7.0.1 (SQL SERVER), I'm getting following exception when I try to add and save new entity:

Unable to modify a row in table 'MyTable' because its key column 'Id' is null**

The Id column is configured as IDENTITY, and it is not a PK.

Include your code

public void Configure(EntityTypeBuilder<Material> entity)
{
    entity.HasKey(e => e.MaterialCode);
    entity.HasAlternateKey(e => e.Id);

    entity.HasIndex(e => e.Id, "UQ_Material_ID").IsUnique();
    
    entity.Property(e => e.Id).HasColumnName("ID").UseIdentityColumn(1, 1);

    entity.Property(e => e.MaterialCode).HasMaxLength(25);
}

public void Configure(EntityTypeBuilder<MaterialInfo2> entity)
{
     entity.ToTable("Material"); //same table as Material entity
     entity.HasKey(e => e.MaterialCode);        
     entity.HasAlternateKey(e => e.Id);    
     
     entity.Property(e => e.MaterialCode).HasMaxLength(25);

     entity.HasOne<Material>()
         .WithOne()
         .IsRequired(false)
         .HasForeignKey<MaterialInfo2>(e => e.MaterialCode)
         .HasPrincipalKey<Material>(e => e.MaterialCode)
         .OnDelete(DeleteBehavior.Cascade);
}


public partial class Material : IMaterial
{
    public int Id { get; set; } //Not a PK but IdentityColumn used as a principal key in navigation properties

    public string MaterialCode { get; set; } //PK

   ....
}

public partial class MaterialInfo2 : IMaterial
{
    public int Id { get; set; } 

    public string MaterialCode { get; set; } 
}

using var context = new MyDbContext();
context.Material.Add(new Material{ KódMateriálu = "ABC", Popis = "Product 1" });
try
{
    context.SaveChanges();
}

EntityEntry state before adding:

{Material {MaterialCode: 10100345} Added AK {Id: -2147482647}}}

Entry metadata:

EntityType: Material
  Properties: 
    MaterialCode (string) Required PK AfterSave:Throw MaxLength(25)
      Annotations: 
        MaxLength: 25
        Relational:ColumnName: MaterialCode
        SqlServer:ValueGenerationStrategy: None
  
    Id (int) Required AlternateKey Index AfterSave:Throw ValueGenerated.OnAdd
      Annotations: 
        Relational:ColumnName: ID
        SqlServer:ValueGenerationStrategy: IdentityColumn
  Keys: 
    Id
    MaterialCode PK
  Foreign keys: 
  Indexes: 
    Id UQ_Material_ID Unique
  Annotations: 
    DiscriminatorProperty: 
    Relational:FunctionName: 
    Relational:Schema: 
    Relational:SqlQuery: 
    Relational:TableName: Material
    Relational:ViewName: 
    Relational:ViewSchema: 

Include stack traces

Error: System.InvalidOperationException: Unable to modify a row in table 'Material' because its key column 'Id' is null.
   at Microsoft.EntityFrameworkCore.Update.Internal.SimpleRowKeyValueFactory`1.CreateKeyValue(IReadOnlyModificationCommand command, Boolean fromOriginalValues)
   at Microsoft.EntityFrameworkCore.Update.Internal.RowForeignKeyValueFactory`2.CreatePrincipalEquatableKeyValue(IReadOnlyModificationCommand command, Boolean fromOriginalValues)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.AddForeignKeyEdges(Multigraph`2 commandGraph)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.TopologicalSort(IEnumerable`1 commands)
   at Microsoft.EntityFrameworkCore.Update.Internal.CommandBatchPreparer.BatchCommands(IList`1 entries, IUpdateAdapter updateAdapter)+MoveNext()
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable`1 commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList`1 entriesToSave, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(StateManager stateManager, Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func`4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(Boolean acceptAllChangesOnSuccess, CancellationToken cancellationToken)

Reproducible demo

https://github.com/Liero/EfCore.KeyIsNull

Include provider and version information

EF Core version: 7.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework:  .NET 7.0
Operating system: Windows 10
IDE:  Visual Studio 2022 17.4

[ajcvickers]

@Liero Both ČíselníkMateriál and MaterialInfo2 are mapped to the same table, but in ČíselníkMateriál, the Id property is mapped to the column "ID", where as in MaterialInfo2 it retains its default mapping of "Id". EF column names are case-sensitive, so these are treated as different columns. Either map both to "ID" or both to "Id".

[Liero]

Yes, that solved it. Also, both columns should be configured with .UseIdentityColumn(1, 1);.

This could be a warning when building model, couldn't it?

[ajcvickers]

Note from triage: warn in this case on SQL Server. (Warning will need to be disabled if this is intentional and master database is created with a collation that is not case-sensitive.)