c# - 插入触发器中断 EF 添加

Question

我添加了一个 SQL Server 表 ON INSERT 触发器，它破坏了我的 API 的 Entity Framework 插入代码。它抛出一个错误:

InnerException = "Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded.

我尝试设置 EntityState.Added； Ben 建议here ，但这没有用。

我尝试了 chamara 的建议here .

添加了以下 SQL Server 触发器:

ALTER TRIGGER [dbo].[tr_TargetUserDel] 
ON [dbo].[TargetUser]
AFTER  INSERT
AS
BEGIN
    DECLARE @UserId AS INT
    DECLARE @TargetId AS INT
    DECLARE @TargetUserId AS INT

    SELECT @UserId = i.[UserId], @TargetId = i.[TargetId] 
    FROM inserted i

    INSERT INTO [dbo].[TargetUser] ([UserId], [TargetId])
    VALUES (@UserId, @TargetId)

    SELECT @TargetUserId = CAST(@@Identity AS INTEGER)

    DELETE [dbo].[TargetUser] 
    WHERE TargetUserId <> @TargetUserId 
      AND TargetId = @TargetId
END

这导致以下 EF 插入失败:

targetuser.UserId = interfaceUser.UserId;
targetuser.TargetId = interfaceUserDTO.TargetId;

db.InterfaceTargetUsers.Add(targetuser);                    
db.SaveChanges();      // <<Error thrown

模型对象目标用户:

[Table("TargetUser")]
public partial class InterfaceTargetUser
{
    [Key]
    [Column(Order = 0)]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int TargetUserId { get; set; }
    public int UserId { get; set; }
    public int TargetId { get; set; }
}

EF 在插入时遇到问题，但如果我直接在 SQL Server 中运行插入，它运行良好:

INSERT INTO targetuser (TargetId, UserId) 
VALUES (5276, 572)

按照 chamara 的建议修改触发器here有效，但我正在寻找 EF 解决方案。

chamara的解决方法是将其添加到触发器的末尾:

SELECT * FROM deleted UNION ALL
SELECT * FROM inserted;

这有效但没有赞成票。为什么这行得通？似乎应该有一个 EF 解决方案？

Answer 1

你那里有很多问题:

select  @UserId = i.[UserId], @TargetId=i.[TargetId] from inserted i

仅当伪 INSERTED 只有 1 行时才有效。

也为行

SELECT @TargetUserId =CAST(@@Identity AS INTEGER)

只会返回最后生成的标识值，而不是全部。

改变你的触发器

ALTER TRIGGER [dbo].[tr_TargetUserDel] ON [dbo].[TargetUser]
AFTER  INSERT
AS
BEGIN
  SET NOCOUNT ON;       
  INSERT INTO [dbo].[TargetUser]([UserId], [TargetId])
  SELECT UserId,
         TargetId
  FROM INSERTED;

  DELETE T
  FROM [dbo].[TargetUser] T INNER JOIN INSERTED TT
  ON T.TargetId = TT.TargetId 
     AND
     T.TargetUserId <> TT.TargetUserId;
  SET NOCOUNT OFF; 
END