SQL Server: Support INSTEAD OF INSERT triggers

[sam-wheat]

Question

Client of mine has INSTEAD OF INSERT trigger defined on one of their tables. This causes EF to fail when inserting rows:

The instance of entity type 'ThisEntity' cannot be tracked because another instance with the same key value for {'Id'} is already being tracked.
When attaching existing entities, ensure that only one entity instance with a given key value is attached.
Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.

I have seen this documentation.
The table is defined with .HasTrigger(...), which appears to have no effect. Using UseSqlOutputClause(false) also has no effect.
Have seen this issue also.
The trigger sets SET NOCOUNT ON and does not return anything.
Removing the trigger fixes the problem but the client does not want to do that.
Anything else I can try?

SQL being run:

SET NOCOUNT ON;
INSERT INTO [TableName] (C1, C2)
VALUES (@P0, @P1);
SELECT [ID] FROM [TableName] WHERE @@ROWCOUNT = 1 AND [ID] == scope_identity();

The SELECT statement returns no rows, presumably because scope_identity is lost when the trigger returns.

.net 9
EF version 9.0.1

Your code

Stack traces

Verbose output

EF Core version

9.0.1

Database provider

No response

Target framework

No response

Operating system

No response

IDE

No response

[ErikEJ]

Map Inserts to a stored procedure?

[roji]

EF expects INSERTs (and other statements) to behave in the standard way, and triggers are indeed known to sometimes mess that up. @AndriySvyryd any known workarounds here? (the SO linked to above provides some possibilities)

[AndriySvyryd]

I don't know of any straightforward workarounds for this.

EF would need to generate different SQL for different scenarios. I think we'll need to add ways to force the usage of OUTPUT INTO for single row or to use @@IDENTITY instead of scope_identity().