efcore: Updating fails when using an identity column that is not a key

I define an entity like this:

public class Entity
{
    public Guid ID { get; set; }
    public int ClusteredId { get; set; }
    public string SomeText { get; set; }
}

and a context like this:

public class SomeContext : DbContext
{
    public DbSet<Entity> Entities { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.Entity<Entity>(b => {
            b.HasKey(x => x.ID).ForSqlServerIsClustered(false);

            // With the following two lines the update FAILS
            b.Property(x => x.ClusteredId).UseSqlServerIdentityColumn();
            b.HasIndex(x => x.ClusteredId).IsUnique().ForSqlServerIsClustered();

            //// With the following two lines the update works
            //b.Property(x => x.ClusteredId).UseSqlServerIdentityColumn().ValueGeneratedOnAddOrUpdate();
            //b.HasIndex(x => x.ClusteredId).IsUnique().ForSqlServerIsClustered();

            //// With the following two lines the update works
            //b.Property(x => x.ClusteredId).UseSqlServerIdentityColumn();
            //b.HasAlternateKey(x => x.ClusteredId).ForSqlServerIsClustered();
        });
    }
}

If i use this context in the following way, it will fail on the second SaveChanges(), because EF will generate SQL to update the ClusteredId column.

Entity entity = new Entity { SomeText = "Some Text" };
context.Entities.Add(entity);
context.SaveChanges();
context.Entities.Update(entity);
context.SaveChanges();
exec sp_executesql N'SET NOCOUNT ON;
UPDATE [Entities] SET [ClusteredId] = @p0, [SomeText] = @p1
WHERE [ID] = @p2;
SELECT @@ROWCOUNT;
',N'@p2 uniqueidentifier,@p0 int,@p1 nvarchar(4000)',@p2='...',@p0=1,@p1=N'Some Text'

If, when defining the model, I either specify ValueGeneratedOnAddOrUpdate() for the ClusteredId property, or use an alternate key instead of a unique index on the same property, then the code will work, but in my oppinion either one of those two changes should not be neccessary.

Further technical details

EF Core version: 1.1.0 Database Provider: Microsoft.EntityFrameworkCore.SqlServer Operating system: Windows 10 IDE: Visual Studio 2015

About this issue

  • Original URL
  • State: closed
  • Created 10 years ago
  • Comments: 15 (10 by maintainers)

Most upvoted comments

It is really good to update EF Core framework time by time rather updating too late.

I am using EF Core 2.1, I tried this.

builder.Property(e => e.ColumnName).Metadata.AfterSaveBehavior = PropertySaveBehavior.Ignore; It worked for me.

EF Core 2.1 is stable, and enough updates to cater most of the problems.