Efcore: SQL Server: Switch IDENTITY_INSERT on (and then off) when explicit values are specified for an identity column

[ajcvickers]

When using generated values the state manager will not generate a temporary value if a specific value was already set. The update pipeline should then handle inserting the specific value or throw an exception. This isn't currently implemented for Identity columns in SQL Server. (It is also an option that the update pipeline could somehow special case this situation using metadata information and still generate a new value, just as it did in the old stack, but this would be a break from the expected behavior for value generation.)

[divega]

I personally think it is fine to let the database server throw. At least the users will know that they are doing something wrong instead of having their specified values being ignored.

[rowanmiller]

With the implementation we landed on you will get an exception if you try to specify explicit values... but it is possible to make it work with a bit of extra code.

We could be smart about this in our provider and actually switch IDENTITY_INESRT on then off when we know that we're going to try and insert and explicit value.

[ajcvickers]

We should look at what was done for seed data and see if this can be generalized. However, even then it is not clear that the behavior should be switched on by default.

[ajcvickers]

See #11586 for an API proposal.

[AndriySvyryd]

Consider how this would interact with https://github.com/aspnet/EntityFrameworkCore/issues/13575

[davisnw]

I found this issue linked off of https://docs.microsoft.com/en-us/ef/core/saving/explicit-values-generated-properties#explicit-values-into-sql-server-identity-columns.
This issue description also mentions that "state manager will not generate a temporary value".

Not having a full grasp of what "temporary value" means in the larger context, I'll just address automatically setting IDENTITY_INSERT ON/OFF.

While I can definitely see this being useful for seed data scenarios in testing (since Sql Server only allows IDENTITY_INSERT ON for one table at a time on the transaction/connection), I think that identity insert should not automatically be switched on/off by default.

In most application code I see (outside of seed data), explicitly assigning a value to a field that is mapped to an identity should be considered an application bug, and I would prefer Entity Framework to raise an exception if an attempt to set an explicit identity value is encountered.

In some cases, I see the identity field being used to obtain an ordering of when the application inserted records, e.g. because it is immune to system clock "jumps" when time synchronization occurs (understanding that "identity order" is not necessarily "transaction commit order")

For me it would be more useful to have explicit control over identity insert on a case-by-case basis, rather than a global configuration switch e.g. what was proposed in #13575 in that regard:

using (context.ChangeTracker.EnableExplicitKeys())
{
    context.Add(new Blog { Id = 77 });
}

Also there appears to be a bit of overlap with HasData, but that is a different use case. Notably, based on my limited testing in EfCore 2.2, it appears that HasData bypasses the normal db context hooks (that is I don't see e.g. SaveChangesAsync being called in the derived override), I would expect entities added under e.g. EnableExplicitKeys to go through all the normal DbContext hooks when SaveChanges / SaveChangesAsync is called.

[IanKemp]

The fact that this hasn't been implemented in over half a decade is mind-boggling.

[DawidIzydor]

Is there any chance this will be implemented in any forseeable future (ie. in the next 5 years)?

[AndriySvyryd]

The age of an issue has no impact on its priority, all currently open issues are expected to be implemented in the forseeable future.