Linq To Sql 和 identity_insert

问题描述

我正在尝试在主键是 Identity 字段的表上插入记录.

I am trying to do record inserts on a table where the Primary Key is an Identity field.

我试过打电话
mycontext.ExecuteCommand("SET identity_insert myTable ON")
但这没有任何好处.

I have tried calling
mycontext.ExecuteCommand("SET identity_insert myTable ON")
but this doesn't do any good.

我在提交更改时收到错误消息，说 IDENTITY_INSERT 是 OFF.

I get an error saying IDENTITY_INSERT is OFF when I submit changes.

如何在提交更改之前从 C# 代码中打开它ON?

How can I turn it ON from the C# code before I submit changes?

我了解到这是因为 ExecuteCommand 的代码在不同的会话中执行.

I have read that this is because ExecuteCommand's code gets executed in a different session.

有什么办法可以执行一些 DDL 从我的 C# 代码中删除身份规范，进行插入，然后重新打开身份规范?

Is there any way I can execute some DDL to remove the Identity Specification from my C# code, do the inserts, and then turn Identity Specification back on?

推荐答案

您需要在单个 T-SQL 代码块中完成所有步骤 - 如果您想打开它，这将非常困难，如果不是不可能的话，然后执行您的 LINQ-to-SQL 查询，然后将其关闭 :(

You need to do all the steps in a single T-SQL code block - which is going to be really hard if not impossible if you want to turn it on, then execute your LINQ-to-SQL query, and then turn it back off :(

我看到的唯一真正的解决方案是将整个 SQL 打包成一条 SQL 语句并执行:

The only real solution I see is to package up the entire SQL into a SQL statement and execute that:

SET IDENTITY_INSERT MyTable ON

(do your update here)

SET IDENTITY_INSERT MyTable OFF

并使用 .ExecuteContext()

马克

PS:对于您的 EDIT#2:不，不幸的是，没有(简单)方法可以从列中删除标识，然后重新打开它.基本上，您必须创建一个没有 IDENTITY 的新列，复制值，删除 IDENTITY 列，然后在完成后向后执行相同的操作 - 抱歉！:-(

PS: for your EDIT#2 : no, unfortunately, there's no (easy) way to remove the identity from a column, and turn it back on. Basicall you'd have to create a new column without the IDENTITY, copy the values over, drop the IDENTITY column and then do the same backwards when you're done - sorry! :-(

PS #2:这真的引出了一个问题:到底要做什么需要做一个身份插入"?定期，从应用程序?当然 - 您可能偶尔会遇到这种需求，但我总是在 SQL Mgmt Studio 中单独执行此操作 - 当然不是在我的应用程序中......(只是好奇你的用例/动机是什么).

PS #2: this really begs the question: what on earth to do need to do an "identity insert" for? On a regular basis, from an app? Granted - you might run into this need once in a while, but I'd always do this separately, in SQL Mgmt Studio - certainly not in my app..... (just curious what your use case / motivation is).