为什么这个带有 IDENTITY_INSERT 的 EF 插入不起作用? [英] Why does this EF insert with IDENTITY_INSERT not work?

问题描述

这是查询:

using (var db = new AppDbContext())
{
    var item = new IdentityItem {Id = 418, Name = "Abrahadabra" };
    db.IdentityItems.Add(item);
    db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT Test.Items ON;");
    db.SaveChanges();
}

执行时，新表上插入记录的Id仍为1.

When executed, the Id of the inserted record, on a new table, is still 1.

新:当我使用交易或 TGlatzer 的答案时，我得到了异常:

NEW: When I use either the transaction, or TGlatzer's answer, I get the exception:

必须为表项目"中的标识列指定显式值当 IDENTITY_INSERT 设置为 ON 或复制用户插入 NOT FOR REPLICATION 标识列.

Explicit value must be specified for identity column in table 'Items' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

推荐答案

这个绝对不能在生产代码中使用，它只是为了好玩
我看到我的仍然是公认的答案，再次，不要使用这个(解决这个问题)，检查下面的其他答案

我不建议这样做，因为它是一个疯狂的黑客，但无论如何.

I do not suggest this because it is a crazy hack but anyway.

我想我们可以通过截取SQL命令并更改命令文本来实现
(可以从 DbCommandInterceptor 继承并覆盖 ReaderExecuting)

I think we can achieve it by intercepting the SQL command and changing the command text
(you can inherit from DbCommandInterceptor and override ReaderExecuting)

目前我没有一个可行的例子，我必须去，但我认为这是可行的

I don't have a working example at the moment and I have to go but I think it is doable

示例代码

    public class MyDbInterceptor : DbCommandInterceptor
    {
        public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
        {

            if (is your table)
            {
                command.CommandText = "Set Identity off ,update insert into ,Set Identity off"
                return;
            }
            base.ReaderExecuting(command, interceptionContext);

        }

    }

ORM 是一个很好的抽象，我真的很喜欢它们，但我认为尝试破解"它们以支持较低(更接近 db)级别的​​操作是没有意义的.
我尽量避免存储过程，但我认为在这种情况下(正如你所说的例外)我认为你应该使用一个

ORMs are a nice abstraction and I really like them but I don't think it makes sense to try to "hack" them to support lower(closer to the db) level operations.
I try to avoid stored procs but I think in this (as you said exceptional) case I think you should use one

这篇关于为什么这个带有 IDENTITY_INSERT 的 EF 插入不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！