如何使用EF Code-First插入/更新可更新视图 [英] How to insert/update an updatable view using EF Code-First

问题描述

我有一个场景，其中我创建了一个由几个使用INSTEAD OF触发器的表组成的可更新视图。我已经验证可以使用SSMS手动插入此视图，而不会出现问题。但是，由于我的应用程序使用EF Code-First表示视图，因此在尝试插入记录时，由于EF在insert语句后的后续SELECT中使用SCOPE_IDENTITY（），我最终遇到了问题。我尝试在触发器的末尾添加一个语句，以强制将scope_identity（）包含在内，但没有成功。我也尝试过在插入语句中包括OUTPUT选项，但也没有运气。我开始怀疑我的方案是否太复杂以至于EF无法处理？

I have a scenario in which I have created an updatable view composed of several tables that uses INSTEAD OF triggers. I have verified that I can manually insert into this view using SSMS without an issue. However, as my application is using EF Code-First to represent the view, when attempting to insert a record I end up with a problem because of the EF's use of SCOPE_IDENTITY() in the subsequent SELECT after the insert statement. I have tried including a statement at the end of my trigger to "force" the scope_identity()'s inclusion, but without success. I have also tried including the OUTPUT option on my insert statement with no luck either. I'm beginning to wonder if my scenario is too complex for EF to handle?

基于反馈，我将这种方案大大简化为易于所有人复制的方案：

Based upon feedback, I am greatly simplifying this scenario into something that's easy to reproduce for everyone:

相关的表和视图定义：

IF NOT EXISTS(SELECT * FROM [sysobjects] WHERE [type] = 'U' AND [name] = 'Parent')
BEGIN
    CREATE TABLE [dbo].[Parent]
    (
        [ent_pk] INT IDENTITY(1,1) NOT NULL,
        [ent_id] VARCHAR(25) DEFAULT('') NOT NULL,
        CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED ([ent_pk])
    )
END
GO
IF NOT EXISTS(SELECT * FROM [sysobjects] WHERE [type] = 'U' AND [name] = 'Child1')
BEGIN
    CREATE TABLE [dbo].[Child1]
    (
        [c1_entfk] INT NOT NULL,
        [c1_field1] VARCHAR(30) DEFAULT('') NOT NULL,
        [c1_fees] DECIMAL(7,2) DEFAULT(0) NOT NULL,
        CONSTRAINT [PK_Child1] PRIMARY KEY CLUSTERED ([c1_entfk])
    )
END
GO
IF EXISTS(SELECT * FROM sys.views WHERE [object_id] = OBJECT_ID(N'[dbo].[vwView]'))
    DROP VIEW [dbo].[vwView]
GO
CREATE VIEW [dbo].[vwView] AS
SELECT [ent_pk],
    [ent_id],
    [c1_field1],
    [c1_fees]
    FROM [Parent]
        LEFT JOIN [Child1] ON [c1_entfk] = [ent_pk]
GO

触发代码如下：

IF EXISTS(SELECT * FROM sysobjects WHERE [type] = 'TR' AND [name] = 'trg_vwView_i')
    DROP TRIGGER [trg_vwView_i]
GO
CREATE TRIGGER [trg_vwView_i] ON [dbo].[vwView] INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO [Parent] ([ent_id])
        SELECT [ent_id]
        FROM [Inserted] [I]

    INSERT INTO [Child1] ([c1_entfk], [c1_field1], [c1_fees])
        SELECT [E].[ent_pk], [c1_field1], [c1_fees]
        FROM [Inserted] [I]
            INNER JOIN [Parent] [E] ON [E].[ent_id] = [I].[ent_id]
END
GO

对于上表和视图定义中未包含的外键约束，必须先插入父记录，然后再将记录插入其他表中，以保持参照完整性。

Because of Foreign Key constraints not included in the above table and view definitions, the Parent record must be inserted first before inserting the records in the other tables in order to maintain referential integrity.

POCO类定义为：

[Table("vwView")]
public class SimpleViewTest
{

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int ent_pk { get; set; }

    [MaxLength(25)]
    public string ent_id { get; set; }

    [MaxLength(30)]
    public string c1_field1 { get; set; }

    public decimal c1_fees { get; set; }

}

EF为插入生成的SQL示例：

A sample of the SQL generated by EF for the insert:

exec sp_executesql N'INSERT [dbo].[vwView]([ent_id], [c1_field1], [c1_fees])
VALUES (@0, @1, @2)
SELECT [ent_pk]
FROM [dbo].[vwView]
WHERE @@ROWCOUNT > 0 AND [ent_pk] = scope_identity()',N'@0 nvarchar(25),@1 nvarchar(30),@2 decimal(7,2) ',@0=N'AK FED CU',@1=N'Alaska Federal Credit Union',@2=10.00

现在，在SSMS中执行上述SQL语句正确地将记录插入到各个表中。但是，由于SCOPE_IDENTITY（）返回NULL，因此它无法返回任何内容。

Now, executing the above SQL statement in SSMS correctly inserts the records into the various tables. But, it fails to return anything in the select because SCOPE_IDENTITY() returns NULL.

因此，我不确定如何获取SCOPE_IDENTITY（）以返回值插入[Parent]表中，或者甚至可以插入。有谁知道这是可以做的事情，还是我需要探索定义存储过程来处理这种情况？还是有人有其他建议？

So, I'm not sure how to get the SCOPE_IDENTITY() to return the value for the insert into the [Parent] table or if it's even possible. Does anyone know if this is something that can be done, or if I need to explore defining stored procedures to handle this sort of scenario? Or are there any other suggestions people have?

注意：我想在应用程序中将实体表示为扁平对象，而不是由多个对象组成的对象。

NOTE: I want to represent the entity in my application as a "flat" object, not one composed of multiple objects...

推荐答案

好。在深入研究EF文档之后，我遇到了以下问题： https://msdn.microsoft.com/en-us/library/dn469464（v = vs.113）.aspx 。此外，我发现了以下有关实现IDbCommandInterceptor的BLOG帖子，其中提到了能够在执行前修改EF生成的SQL：> https://www.skylinetechnologies.com/Blog/Skyline-Blog/December-2013/Entity-Framework-6 -Intercepting-SQL产生的。

Ok. After digging around a lot more into the EF documentation, I ran across this: https://msdn.microsoft.com/en-us/library/dn469464(v=vs.113).aspx. Furthermore, I found the following BLOG post regarding implementing the IDbCommandInterceptor where it makes reference to being able to modify the SQL generated by EF before it gets executed: https://www.skylinetechnologies.com/Blog/Skyline-Blog/December-2013/Entity-Framework-6-Intercepting-SQL-produced.

因此，我的解决方案是实现自己的IDbCommandInterceptor并重写ReaderExecuiting方法，如下所示：

So, my solution was to implement my own IDbCommandInterceptor and override the ReaderExecuiting method as follows:

public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
{
    string sqlToIntercept = "INSERT [dbo].[vwView]";

    if (command.CommandText.Contains(sqlToIntercept))
    {
        command.CommandText = command.CommandText.Replace("WHERE @@ROWCOUNT > 0 AND [ent_pk] = scope_identity()", "WHERE @@ROWCOUNT > 0 AND [ent_id] = @1");
    }
}

现在，虽然并不完全适合这个问题，在我的情况下，它的确很好用，因为由于业务规则的原因，Parent表中的ent_id字段必须唯一。

Now, while not perfectly suited to every possible variation of this problem, in my case it does work quite nicely as the ent_id field in the Parent table must be unique thanks to business rules.

希望，即使这种情况是'与您可能遇到的相似的事物完全相同，此解决方案可以提供其他指导，使您可以解决自己的特定情况。

Hopefully, even if this scenario isn't exactly the same as a similar one you may encounter, this solution may provide additional direction allowing you to solve your specific scenario.

感谢所有花时间阅读的人这个和评论。一切顺利。

Thanks to everyone who took the time to read this and comment. All the best.

这篇关于如何使用EF Code-First插入/更新可更新视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！