Error while inserting record whose primary key set by AFTER INSERT trigger
For some reason few of the SQL tables in my .Net Core project has a primary key column (varchar) value set by an after insert trigger. see the trigger below.
USE [MyDb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Lookup].[MyTable_Insert] ON [Lookup].[MyTable] AFTER INSERT
AS
SET NOCOUNT ON
UPDATE Lookup.MyTable
SET ID = convert(varchar,ID_AUTO)
ID - Primary Key, varchar, not null
ID_AUTO - smallint, not null, Identity column
When Inserting a new record, I get the following error.
DbUpdateConcurrencyException: Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded
When I was using Entity Framework (Not the core), I was able to take care of the issue by adding the following line of code at the end of the AFTER INSERT trigger.
SELECT CAST(SCOPE_IDENTITY() AS varchar) AS ID
But when I converted my application to .net core (ef core), that line of code is causing another error as below.
InvalidOperationException: The database generated a null value for non-nullable property 'IdAuto' of entity type 'MyTable'. Ensure value generation configuration in the database matches the configuration in the model.
Looking at the SQL profiler, here is the SQL statement I can see.
exec sp_executesql N'SET NOCOUNT ON;
INSERT INTO [Lookup].[MyTable] ([ID], [Active])
VALUES (@p0, @p1);
SELECT [ID_AUTO]
FROM [Lookup].[MyTable]
WHERE @@ROWCOUNT = 1 AND [ID] = @p0;
',N'@p0 varchar(5),@p1 varchar(3),@p0='9999',@p1='Y'
So I think EF Core ignore the SCOPE_IDENTITY and just uses the value of ID used to insert the record, to query it back.
Any help?
UPDATE:
I submitted a ticket to the EF Core team, and looks like they have confirmed it as an issue. Here is the link to the ticket.
Ticket
I submitted a ticket to EF Core team. They said they are not supporting these feature and the best course of action will be to make the ID_AUTO column as the Primary Key. So that's what I ended up doing.
链接地址: http://www.djcxy.com/p/60290.html上一篇: C＃是否优化了字符串文字的连接？