EF Core database insert fails when table has a SQL INSTEAD OF trigger

[Metritutus]

I have a table in SQL Server that utilises an INSTEAD OF trigger to update a column with a unix timestamp representing when the record was updated.

When attempting to insert a record into this table from Entity Framework Core, a Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException is thrown.

The SQL scripts, non-generated application code and the scaffold command used are below.

SQL scripts

CREATE DATABASE EFCoreTriggerTest
GO

USE EFCoreTriggerTest

CREATE TABLE [TestTable]
(
    [Id]           INTEGER IDENTITY    NOT NULL    PRIMARY KEY,
    [Value]         VARCHAR(MAX)        NOT NULL,
    [DateUpdated]   INTEGER             NOT NULL
)
GO

CREATE TRIGGER trTestTable_ioiu  ON TestTable INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON

    IF EXISTS(SELECT * FROM [inserted]) AND NOT EXISTS(SELECT * FROM [deleted]) --Insert
    BEGIN
        INSERT INTO [TestTable]([Value], [DateUpdated])
        SELECT [i].[Value], DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 120), GETDATE())
        FROM [inserted] AS [i]
    END
    ELSE IF EXISTS(SELECT * FROM [inserted]) AND EXISTS(SELECT * FROM [deleted]) --Update
    BEGIN
        UPDATE [tt]
        SET [tt].[Value] = [i].[Value],
        [tt].[DateUpdated] = DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 120), GETDATE())
        FROM        [TestTable] AS [tt]
        INNER JOIN  [inserted] AS [i] ON [i].[Id] = [tt].[Id]
        WHERE [tt].[Value] <> [i].[Value]
    END
END

Application code

static void Main(string[] args)
{
    var ctx = new EFCoreTriggerTest001.Models.EFCoreTriggerTestContext();

    ctx.TestTable.Add(new Models.TestTable() { Value = "TestValue" });

    ctx.SaveChanges();
}

Stacktrace

at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagation(Int32 commandIndex, RelationalDataReader reader)
at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(DbContext _, ValueTuple`2 parameters)
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IReadOnlyList`1 entriesToSave)
at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
at EFCoreTriggerTest001.Program.Main(String[] args) in C:\<Removed>\Program.cs:line 13

Scaffold command:
Scaffold-DbContext "Server=DbServer;Database=EFCoreTriggerTest;User ID=<REMOVED>;Password=<REMOVED>;" Microsoft.EntityFrameworkCore.SqlServer -Force -OutputDir Models

Why am I getting this error? Are there other steps I need to take in order to allow this operation to succeed?

Further technical details

EF Core version: netcoreapp2.1
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2017 15.7.1

[ajcvickers]

Duplicate of #10443

[Metritutus]

Oops. I believe I did see that case earlier but was unsure if it was a duplicate because I'm not deleting any rows, however I have just noticed the latest posts there also reference upserts. Is there any workaround for this?

[ajcvickers]

@AndriySvyryd to look into workarounds

[Metritutus]

I have figured out a workaround. Following reading this, and observing that they SELECT the Id column, I wondered why that would work. Following this, I happened upon issue #7188 which helped to clarify some things.

As a result, my workaround is, in my INSTEAD OF trigger to SELECT the database-generated values.

CREATE TRIGGER trTestTable_ioiu  ON TestTable INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON

    IF EXISTS(SELECT * FROM [inserted]) AND NOT EXISTS(SELECT * FROM [deleted]) --Insert
    BEGIN
        DECLARE @t TABLE([Id] INTEGER NOT NULL)

        INSERT INTO [TestTable]([Value], [DateUpdated]) OUTPUT [inserted].[Id] INTO @t
        SELECT [i].[Value], DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 120), GETDATE())
        FROM [inserted] AS [i]

        SELECT * FROM @t
    END
    ELSE IF EXISTS(SELECT * FROM [inserted]) AND EXISTS(SELECT * FROM [deleted]) --Update
    BEGIN
        UPDATE [tt]
        SET [tt].[Value] = [i].[Value],
        [tt].[DateUpdated] = DATEDIFF(SECOND, CONVERT(DATETIME, '1970-01-01', 120), GETDATE())
        FROM        [TestTable] AS [tt]
        INNER JOIN  [inserted] AS [i] ON [i].[Id] = [tt].[Id]
        WHERE [tt].[Value] <> [i].[Value]
    END
END

[saluce65]

@Metritutus This workaround will only work for so long, because the ability for a trigger to return a result set is deprecated (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017#returning-results). Also, it may be better to run the select statement as

SELECT [Id] FROM [TestTable] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

since that is what Entity Framework is actually running when it generates the batch statement for inserts.

[Metritutus]

@saluce65 Darn, I was unaware of the impending deprecation of that piece of trigger functionality.

With regards to SCOPE_IDENTITY(), I did not use it because it won't function inside an INSTEAD OF trigger (it just returns NULL).

[saluce65]

@Metritutus scope_identity() works within an INSTEAD OF trigger, because the trigger maintains its own scope. If you attempt to use it outside the trigger (which is what EF is trying to do), then, yes, it returns NULL. So, my suggestion was to replace

SELECT * FROM @t

with

SELECT [Id] FROM [TestTable] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

since all EF cares about it getting back the last identity value inserted.

[Metritutus]

@saluce65 I stand corrected! When I was thinking back to this I thought that was the reason I didn't use SCOPE_IDENTITY() within the trigger itself.

I suspect the actual reason may simply have been because it was my understanding that Entity Framework Core does more than just get the Id of the newly-inserted record. I was under the impression that Entity Framework Core uses an OUTPUT clause on the insert to return all fields with with database-generated values. I therefore just made use of the whole insert rather than using SCOPE_IDENTITY(). Admittedly my example is modified from what I'm actually using and has no other database-generated values, so SCOPE_IDENTITY() could be used in this example as an alternative approach.

However this is all ultimately superfluous as it doesn't address the greater issue of how to get the above-mentioned values back to Entity Framework Core when utilising an INSTEAD OF trigger.

EDIT:

One approach that does come to mind though is removing the INSTEAD OF INSERT trigger entirely and just leave it as an INSTEAD OF UPDATE trigger. For the behaviour handled by the INSTEAD OF INSERT trigger in my scenario I could probably just use DEFAULT constraints instead.

[vcrobe]

@Metritutus scope_identity() works within an INSTEAD OF trigger, because the trigger maintains its own scope. If you attempt to use it outside the trigger (which is what EF is trying to do), then, yes, it returns NULL. So, my suggestion was to replace

SELECT * FROM @t

with

SELECT [Id] FROM [TestTable] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

since all EF cares about it getting back the last identity value inserted.

I've tested the suggested solution by @saluce65

SELECT [Id] FROM [TestTable] WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()

with EF version 5.04 and it works!

PD: In my case the trigger also update another two int columns.

[AndriySvyryd]

Related: #25406

[AndriySvyryd]

EF Triage: Currently this is by-design and triggers need to account for what EF is expecting, #10443 tracks adding an opt-out.