LINQ to SQL和IDENTITY_INSERT [英] Linq To Sql and identity_insert

问题描述

我试图做一个表记录插入了主键是身份字段。

我已经打过电话
mycontext.ExecuteCommand（SET IDENTITY_INSERT为myTable ON）
但是这并没有什么好处。

我得到一个错误说 IDENTITY_INSERT 是关闭当我提交修改。

我怎样才能把它 ON 从C＃code之前，我提交更改？

修改

我看过，这是因为的ExecuteCommand的code被在不同的会话中执行。

编辑2

有没有什么办法可以执行一些DDL从我的C＃code删除标识规范，执行插入，然后将标识规范回来了？

解决方案

您需要做的，单一的T-SQL code座的所有步骤 - 这将是真的很难，如果不是不可能的，如果你想打开它，然后再执行你的LINQ到SQL查询，然后将它关闭:(

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

  SET IDENTITY_INSERT MyTable的ON

（做你的更新在这里）

SET IDENTITY_INSERT MyTable的关
 

和执行，作为使用单个code座 .ExecuteContext（）

马克·

PS：为您编辑＃2：没有，不幸的是，有没有（简单）的方法来去除列中的身份，并重新打开它。 Basicall你必须创建一个没有身份的新列，在复制的值，删除IDENTITY列，然后做向后当你做同样的 - 对不起！ ： - （

PS＃2：这确实引出了一个问题：到底该怎么做需要做一个标识插入的呢？定期，从一个应用程序？当然 - 你可能会在一段时间运行到这方面的需要一次，但我总是这样做分别，在SQL管理工作室 - 当然不是我的应用程序.....（只是好奇你的用例/动机）<。 / P>

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

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

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

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

EDIT

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

EDIT 2

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?

解决方案

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 :(

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

and execute that as a single code block using .ExecuteContext()

Marc

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: 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).

这篇关于LINQ to SQL和IDENTITY_INSERT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！