Migrations: InsertData() causes error "Table 'table_name' does not have the identity property. Cannot perform SET operation."

[divega]

Our implementation for SQL Server is incorrect because SET IDENTITY_INSERT table_name ON is only valid when the table does actually have an identity column. I see a few options:

1. Have a flag indicating if the statement should be generated
2. Have a separate method e.g. InsertDataOverridingIdentity 😸
3. Query for schema metadata in the SQL (might not work if we don't have enough permissions).

[divega]

cc @javiercn @bricelam

[divega]

This indicates we are also missing testing.

[bricelam]

Another option:

• Use the backing model when generating SQL to determine if SET IDENTITY_INSERT is needed. Out-of-band tables would need to manually add the statements using migraitonBuilder.Sql().

[divega]

@bricelam that option seems to avoid leaking the SQL Server-specific requirement and sets other providers to do whatever they need.

[bricelam]

I'm leaning toward the SQL:

IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [object_id] = OBJECT_ID(N'People'))
    SET IDENTITY_INSERT [People] ON;
INSERT INTO [People] ([Id], [Name])
VALUES (1, N'Brice');
IF EXISTS (SELECT * FROM [sys].[identity_columns] WHERE [object_id] = OBJECT_ID(N'People'))
    SET IDENTITY_INSERT [People] OFF;

I'll have a PR out in a few minutes if it works.

[bricelam]

It works.