search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.63k stars 3.15k forks source link

SQL Server update does not get values updated by trigger. #6474

Closed casperOne closed 7 years ago

casperOne commented 8 years ago

I'm on 1.0.0 of Microsoft.EntityFramework.Core and Microsoft.EntityFramework.Core.SqlServer.

I have a table with two columns that are updated by an AFTER trigger (not an INSTEAD OF):

__lastModified (datetimeoffset) __version (int)

There's also a __rowVersion column which is of type rowversion.

When I call SaveChanges, I don't get the values after the trigger fires, I get the values before the trigger is fired.

I logged the SQL, and this is what I get:

DECLARE @inserted0 TABLE ([__lastModified] datetimeoffset, [__rowVersion] varbinary(8), [__version] int);
UPDATE [stats].[EventSeries] SET [description] = @p0, [twitterScreenName] = @p1, [__lastModifiedByUserId] = @p2
OUTPUT INSERTED.[__lastModified], INSERTED.[__rowVersion], INSERTED.[__version]
INTO @inserted0
WHERE [eventSeriesId] = @p3 AND [__rowVersion] = @p4;
SELECT [__lastModified], [__rowVersion], [__version] FROM @inserted0;

I notice the OUTPUT statement in there (note, I've also taken a look at issue #1441 but that is an issue with EF not generating an OUTPUT INTO).

The issue is in the documentation for the OUTPUT statement, specifically the section on the INSERTED keyword (emphasis mine):

INSERTED is a column prefix that specifies the value added by the insert or update operation. Columns prefixed with INSERTED reflect the value after the UPDATE, INSERT, or MERGE statement is completed but before triggers are executed.

I've configured the __lastModified and __version columns with ValueGeneratedOnAddOrUpdate so the expectation is that upon return of an UPDATE/INSERT, those values would reflect what is in the database when the operation is complete.

As it stands now, the values that come back do not reflect what is in the database and the DbContext now stores the incorrect values.

The DbContext (and by extension, my application) is now inconsistent, and this is a HUGE problem.

There's a simple workaround, which is to refetch the entity, and that's what I'm doing now. But given that I'm wasting network bytes bringing something back from the database on a call to SaveAsync, why not bring back the correct values?

Also disturbing is that the __rowVersion column isn't brought back correctly; I now have a rowversion which doesn't exist in the database so if I wanted to try to modify the entity, I can't, because the rowversion for consistency checking will never match.

It seems the simple workaround is to start a transaction and just select out the values, like so:

BEGIN TRANSACTION;
UPDATE [stats].[EventSeries] SET [description] = @p0, [twitterScreenName] = @p1, [__lastModifiedByUserId] = @p2
WHERE [eventSeriesId] = @p3 AND [__rowVersion] = @p4;
SELECT [__lastModified], [__rowVersion], [__version] FROM [stats].[EventSeries] where [eventSeriesId] = @p3;
COMMIT TRANSACTION;

And only select out the values that have been indicated as being refreshed in the database.

casperOne commented 7 years ago

@AndriySvyryd @divega Would you make sure that the case where multiple new child entities are generated using a MERGE statement is covered?

For example, if I have a hierarchy of tables:

Event -> Tournament -> Phase

And I add 1 Event, 1 Tournament, 2 Phases at the same time, the following SQL is generated:

SET NOCOUNT ON;

INSERT INTO [stats].[Event] ([description], [end], [eventSeriesId], [locationId], [start], [_challongeId], [_eslId], [_evo2012Id], [_evo2013Id], [_evo2014Id], [_evo2015Id], [_evo2016Id], [_maxoplataId], [_smashggId], [__lastModifiedByUserId])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14);
SELECT [eventId], [__lastModified], [__rowVersion], [__version]
FROM [stats].[Event]

SET NOCOUNT ON;
INSERT INTO [stats].[Tournament] ([ban], [description], [eventId], [exhibition], [gameId], [Invitational], [notes], [ratio], [_challongeId], [_eslId], [_evo2012Id], [_evo2013Id], [_evo2014Id], [_evo2015Id], [_evo2016Id], [_maxoplataId], [_smashggId], [__lastModifiedByUserId])
VALUES (@p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32);
SELECT [tournamentId], [__lastModified], [__rowVersion], [__version]
FROM [stats].[Tournament]
WHERE @@ROWCOUNT = 1 AND [tournamentId] = scope_identity();

SET NOCOUNT ON;
DECLARE @toInsert1 TABLE ([bracketTypeId] int, [description] nvarchar(100), [losersAdvance] int, [ordinal] int, [tournamentId] int, [winnersAdvance] int, [_challongeId] int, [_eslId] int, [_evo2012Id] int, [_evo2013Id] int, [_evo2014Id] int, [_evo2015Id] int, [_evo2016Id] int, [_maxoplataId] int, [_smashggId] int, [__lastModifiedByUserId] int, [_Position] [int]);
INSERT INTO @toInsert1
VALUES (@p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48, 0),
(@p49, @p50, @p51, @p52, @p53, @p54, @p55, @p56, @p57, @p58, @p59, @p60, @p61, @p62, @p63, @p64, 1);

DECLARE @inserted1 TABLE ([phaseId] int, [bracketTypeId] int, [description] nvarchar(100), [losersAdvance] int, [ordinal] int, [tournamentId] int, [winnersAdvance] int, [_challongeId] int, [_eslId] int, [_evo2012Id] int, [_evo2013Id] int, [_evo2014Id] int, [_evo2015Id] int, [_evo2016Id] int, [_maxoplataId] int, [_smashggId] int, [__lastModified] datetimeoffset, [__lastModifiedByUserId] int, [__rowVersion] varbinary(8), [__version] int, [_Position] [int]);
MERGE [stats].[Phase] USING @toInsert1 AS i ON 1=0
WHEN NOT MATCHED THEN
INSERT ([bracketTypeId], [description], [losersAdvance], [ordinal], [tournamentId], [winnersAdvance], [_challongeId], [_eslId], [_evo2012Id], [_evo2013Id], [_evo2014Id], [_evo2015Id], [_evo2016Id], [_maxoplataId], [_smashggId], [__lastModifiedByUserId])
VALUES (i.[bracketTypeId], i.[description], i.[losersAdvance], i.[ordinal], i.[tournamentId], i.[winnersAdvance], i.[_challongeId], i.[_eslId], i.[_evo2012Id], i.[_evo2013Id], i.[_evo2014Id], i.[_evo2015Id], i.[_evo2016Id], i.[_maxoplataId], i.[_smashggId], i.[__lastModifiedByUserId])
OUTPUT INSERTED.[phaseId], INSERTED.[bracketTypeId], INSERTED.[description], INSERTED.[losersAdvance], INSERTED.[ordinal], INSERTED.[tournamentId], INSERTED.[winnersAdvance], INSERTED.[_challongeId], INSERTED.[_eslId], INSERTED.[_evo2012Id], INSERTED.[_evo2013Id], INSERTED.[_evo2014Id], INSERTED.[_evo2015Id], INSERTED.[_evo2016Id], INSERTED.[_maxoplataId], INSERTED.[_smashggId], INSERTED.[__lastModified], INSERTED.[__lastModifiedByUserId], INSERTED.[__rowVersion], INSERTED.[__version], i._Position
INTO @inserted1;

SELECT [phaseId], [__lastModified], [__rowVersion], [__version] FROM @inserted1
ORDER BY _Position;

Note the insertion for the two Phase instances at the end; a MERGE is used and exhibits the same behavior as above.

When there's a single Phase child instance, then an insert is used (not sure why it isn't used when there are two child inserts, since that case can be detected by EF before the call is executed) and works as expected using the following SQL:

SET NOCOUNT ON;
INSERT INTO [stats].[Event] ([description], [end], [eventSeriesId], [locationId], [start], [_challongeId], [_eslId], [_evo2012Id], [_evo2013Id], [_evo2014Id], [_evo2015Id], [_evo2016Id], [_maxoplataId], [_smashggId], [__lastModifiedByUserId])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14);
SELECT [eventId], [__lastModified], [__rowVersion], [__version]
FROM [stats].[Event]
WHERE @@ROWCOUNT = 1 AND [eventId] = scope_identity();
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (43ms) [Parameters=[@p15='?', @p16='?' (Size = 500), @p17='?', @p18='?', @p19='?', @p20='?', @p21='?' (Size = 500), @p22='?', @p23='?', @p24='?', @p25='?', @p26='?', @p27='?', @p28='?', @p29='?', @p30='?', @p31='?', @p32='?'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [stats].[Tournament] ([ban], [description], [eventId], [exhibition], [gameId], [Invitational], [notes], [ratio], [_challongeId], [_eslId], [_evo2012Id], [_evo2013Id], [_evo2014Id], [_evo2015Id], [_evo2016Id], [_maxoplataId], [_smashggId], [__lastModifiedByUserId])
VALUES (@p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32);
SELECT [tournamentId], [__lastModified], [__rowVersion], [__version]
FROM [stats].[Tournament]
WHERE @@ROWCOUNT = 1 AND [tournamentId] = scope_identity();
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (56ms) [Parameters=[@p33='?', @p34='?' (Size = 100), @p35='?', @p36='?', @p37='?', @p38='?', @p39='?', @p40='?', @p41='?', @p42='?', @p43='?', @p44='?', @p45='?', @p46='?', @p47='?', @p48='?'], CommandType='Text', CommandTimeout='30']
SET NOCOUNT ON;
INSERT INTO [stats].[Phase] ([bracketTypeId], [description], [losersAdvance], [ordinal], [tournamentId], [winnersAdvance], [_challongeId], [_eslId], [_evo2012Id], [_evo2013Id], [_evo2014Id], [_evo2015Id], [_evo2016Id], [_maxoplataId], [_smashggId], [__lastModifiedByUserId])
VALUES (@p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45, @p46, @p47, @p48);
SELECT [phaseId], [__lastModified], [__rowVersion], [__version]
FROM [stats].[Phase]
WHERE @@ROWCOUNT = 1 AND [phaseId] = scope_identity();