blocks|key|1418767|text|您需要在单个T-SQL代码块中完成所有步骤-如果您想打开它，然后执行LINQ-+to+-SQL查询，然后再关闭它，这将是非常困难的，如果不是不可能的话:(|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418768|我看到的唯一真正的解决方案是将整个SQL打包到一个SQL语句中并执行该语句：|1418769|SET+IDENTITY_INSERT+MyTable+ON

(do+your+update+here)

SET+IDENTITY_INSERT+MyTable+OFF|code-block|syntax|javascript|1418770|并使用.ExecuteContext()将其作为单个代码块执行|offset|length|style|CODE|1418771|Marc|1418772|PS:对于你的EDIT#2+:不，不幸的是，没有(简单的)方法可以从一列中删除身份，并将其重新打开。基本情况下，您必须创建一个没有标识的新列，复制值，删除标识列，然后在完成后反向执行相同的操作-对不起！:-(|1418773|PS+#2:这确实回避了一个问题:到底要做什么才能做“身份插入”？在常规的基础上，从应用程序？诚然-你可能偶尔会遇到这种需要，但我总是在SQL+Mgmt+Studio中单独做这件事-当然不是在我的应用程序中.(只是好奇你的用例/动机是什么)。|1418774|entityMap^0|0|0|0|3|H|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|Z|8|@$K|10|L|11|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|13|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

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:

<pre><code>SET IDENTITY_INSERT MyTable ON

(do your update here)

SET IDENTITY_INSERT MyTable OFF
</code></pre>

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

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

blocks|key|1572228|text|另一种选择是将所有Linq2Sql调用包装在一个TransactionScope()中。这应该会强制它们都在同一个连接中运行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1572229|using+System.Transactions;+//+Be+sure+to+add+a+reference+to+System.Transactions.dll+to+your+project.

+++++++//+...+in+a+method+somewhere+...
+++++++using+(System.Transaction.TransactionScope+trans+=+new+TransactionScope())
+++++++{
++++++++++using(YourDataContext+context+=+new+YourDataContext())
++++++++++{
+++++++++++++context.ExecuteCommand("SET+IDENTITY_INSERT+MyTable+ON");

+++++++++++++context.ExecuteCommand("yourInsertCommand");

+++++++++++++context.ExecuteCommand("SET+IDENTITY_INSERT+MyTable+OFF");
++++++++++}
++++++++++trans.Complete();
+++++++}
+++++++//+...|code-block|syntax|javascript|1572230|尽管如此，如果您正在尝试执行以下操作：|1572231|context.ExecuteCommand("SET+IDENTITY_INSERT+MyTable+ON");
context.MyTable.InsertOnSubmit(myTableObject)
context.SubmitChanges()
context.ExecuteCommand("SET+IDENTITY_INSERT+MyTable+OFF");|1572232|您可能会遇到其他问题，特别是当identity列的IsDbGenerated属性设置为true时。由Linq2Sql生成的SQL命令将不知道是否包含标识列和值。|1572233|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Another option is to wrap all your Linq2Sql calls in a TransactionScope(). This should force them all to run in the same connection.

<pre><code>using System.Transactions; // Be sure to add a reference to System.Transactions.dll to your project.

 // ... in a method somewhere ...
 using (System.Transaction.TransactionScope trans = new TransactionScope())
 {
 using(YourDataContext context = new YourDataContext())
 {
 context.ExecuteCommand("SET IDENTITY_INSERT MyTable ON");

 context.ExecuteCommand("yourInsertCommand");

 context.ExecuteCommand("SET IDENTITY_INSERT MyTable OFF");
 }
 trans.Complete();
 }
 // ...
</code></pre>

Although, if you are trying to do something like:

<pre><code>context.ExecuteCommand("SET IDENTITY_INSERT MyTable ON");
context.MyTable.InsertOnSubmit(myTableObject)
context.SubmitChanges()
context.ExecuteCommand("SET IDENTITY_INSERT MyTable OFF");
</code></pre>

you will probably run into other issues, especially if the identity column has the IsDbGenerated attribute set to true. The SQL command generated by Linq2Sql will not know to include the identity column and value.

blocks|key|1418839|text|我所做的是这样的(Nbuider用于创建实体)。我通常会创建除identity+insert+row之外的所有行；这在最后完成。这是测试数据创建，因此不需要事务。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418840|using+(var+entitiesEfContext+=+new+ContextABC())
{

++++var+platforms+=+Builder<Platform>
++++++++++++++++++++++++++++++++.CreateListOfSize(4)
++++++++++++++++++++++++++++++++.TheFirst(1)
++++++++++++++++++++++++++++++++.With(x+=>+x.Description+=+"Desc1")
++++++++++++++++++++++++++++++++.With(x+=>+x.IsDeleted+=+false)
++++++++++++++++++++++++++++++++.TheNext(1)
++++++++++++++++++++++++++++++++.With(x+=>+x.Description+=+"Desc2")
++++++++++++++++++++++++++++++++.With(x+=>+x.IsDeleted+=+false)
++++++++++++++++++++++++++++++++.TheNext(1)
++++++++++++++++++++++++++++++++.With(x+=>+x.Description+=+"Desc3")
++++++++++++++++++++++++++++++++.With(x+=>+x.IsDeleted+=+false)
++++++++++++++++++++++++++++++++.TheNext(1)
++++++++++++++++++++++++++++++++.With(x+=>+x.Description+=+"Desc4")
++++++++++++++++++++++++++++++++.With(x+=>+x.IsDeleted+=+false)
++++++++++++++++++++++++++++++++.Build();
++++++++++++++++foreach+(var+platform+in+platforms)
++++++++++++++++{
++++++++++++++++++++entitiesEfContext.Platform.AddObject(platform);
++++++++++++++++}
++++++++++++++++entitiesEfContext.SaveChanges();

++++++++++++++//+the+identity+insert+row+(o+as+id+in+my+case)

+++++++++++++++entitiesEfContext.ExecuteStoreCommand("SET+IDENTITY_INSERT+Platform+ON;+INSERT+INTO+[Platform](Platformid,[Description],[IsDeleted],[Created],[Updated])+VALUES+(0,'Desc0'+,0+,getutcdate(),getutcdate());SET+IDENTITY_INSERT+Platform+Off");


++++++++++++++}|code-block|syntax|javascript|1418841|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

What I did is something like this(Nbuider is used to create entities). I create all rows normally except the identity insert row; which is done in the end. This is test data creation hence transaction was not needed. 

<pre><code>using (var entitiesEfContext = new ContextABC())
{

 var platforms = Builder&lt;Platform&gt;
 .CreateListOfSize(4)
 .TheFirst(1)
 .With(x =&gt; x.Description = "Desc1")
 .With(x =&gt; x.IsDeleted = false)
 .TheNext(1)
 .With(x =&gt; x.Description = "Desc2")
 .With(x =&gt; x.IsDeleted = false)
 .TheNext(1)
 .With(x =&gt; x.Description = "Desc3")
 .With(x =&gt; x.IsDeleted = false)
 .TheNext(1)
 .With(x =&gt; x.Description = "Desc4")
 .With(x =&gt; x.IsDeleted = false)
 .Build();
 foreach (var platform in platforms)
 {
 entitiesEfContext.Platform.AddObject(platform);
 }
 entitiesEfContext.SaveChanges();

 // the identity insert row (o as id in my case)

 entitiesEfContext.ExecuteStoreCommand("SET IDENTITY_INSERT Platform ON; INSERT INTO [Platform](Platformid,[Description],[IsDeleted],[Created],[Updated]) VALUES (0,'Desc0' ,0 ,getutcdate(),getutcdate());SET IDENTITY_INSERT Platform Off");


 }
</code></pre>

blocks|key|1418850|text|在执行命令之前手动打开连接应该就足够了。这会使命令在同一会话中运行：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1418851|context.Connection.Open();
context.ExecuteCommand("SET+IDENTITY_INSERT+MyTable+ON");
//+make+changes
//+...
context.SubmitChanges();|code-block|syntax|javascript|1418852|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

It should be enough to open the connection manually before executing commands. This makes the commands run in the same session:

<pre><code>context.Connection.Open();
context.ExecuteCommand("SET IDENTITY_INSERT MyTable ON");
// make changes
// ...
context.SubmitChanges();
</code></pre>

blocks|key|273108|text|我也有同样的错误信息。我通过更改表中主键的属性来解决这个问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|273109|MyTable.MyId+int+IDENTITY(1,1)+NOT+NULL|code-block|syntax|javascript|273110|属性设置MyId+(在dbml中)|offset|length|style|BOLD|273111|273112|自动生成的值：True；|unordered-list-item|273113|自动同步：OnInsert；|273114|主键:int数据类型：int+NOT+NULL标识；|273115|类型：int;|273116|273117|entityMap^0|0|0|B|4|0|0|7|4|0|5|8|0|B|E|0|3|3|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|10|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|11|8|@$I|12|J|13|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|14|8|@]|9|@]|A|$]]|$1|N|3|O|5|P|7|15|8|@$I|16|J|17|K|L]]|9|@]|A|$]]|$1|Q|3|R|5|P|7|18|8|@$I|19|J|1A|K|L]]|9|@]|A|$]]|$1|S|3|T|5|P|7|1B|8|@$I|1C|J|1D|K|L]]|9|@]|A|$]]|$1|U|3|V|5|P|7|1E|8|@$I|1F|J|1G|K|L]]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1H|8|@]|9|@]|A|$]]|$1|X|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|Y|$]]

I had the same error message. And I resolve it by changing the properties of the Primary key in the table. 

<pre><code>MyTable.MyId int IDENTITY(1,1) NOT NULL
</code></pre>

The property settings MyId (in the dbml)

<ul>
<li>Auto Generated Value: True; </li>
<li>Auto-Sync: OnInsert; </li>
<li>Primary Key: True;</li>
<li>Server Data Type: int NOT NULL IDENTITY; </li>
<li>Type: int;</li>
</ul>

blocks|key|1418917|text|只需在每次添加新记录时使用简单的reseed+identity+key+(带有自定义id)，例如：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1418918|++++using+(var+desDb+=+new+DesDbContext())
++++{
++++++++//+del-table-all+--------------------------------------------------------
++++++++desDb.Database.ExecuteSqlCommand("DELETE+FROM+[Products]");

++++++++foreach+(var+desItem+in+desList)+//desList+is+List+of+Product
++++++++{
++++++++++++//+reseed+identity+key
++++++++++++desDb.Database.ExecuteSqlCommand("DBCC+CHECKIDENT('Products',+RESEED,"+
++++++++++++++++%2B+(desItem.ProductID+-+1)+%2B+");");

++++++++++++//+and+record
++++++++++++desDb.Products.Add(desItem);++++++++++++++++++++++++

++++++++++++//+save-db++++++++++++++++++++
++++++++++++desDb.SaveChanges();

++++++++}
++++}|code-block|syntax|javascript|1418919|entityMap^0|G|J|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

just simple <code>reseed identity key</code> (with custom id) every time you add new record, eg:

<pre><code> using (var desDb = new DesDbContext())
 {
 // del-table-all --------------------------------------------------------
 desDb.Database.ExecuteSqlCommand("DELETE FROM [Products]");

 foreach (var desItem in desList) //desList is List of Product
 {
 // reseed identity key
 desDb.Database.ExecuteSqlCommand("DBCC CHECKIDENT('Products', RESEED," 
 + (desItem.ProductID - 1) + ");");

 // and record
 desDb.Products.Add(desItem); 

 // save-db 
 desDb.SaveChanges();

 }
 }
</code></pre>

I am trying to do record inserts on a table where the Primary Key is an <code>Identity</code> field.
I have tried calling 
<code>mycontext.ExecuteCommand(&quot;SET identity_insert myTable ON&quot;)</code> 
but this doesn't do any good.
I get an error saying <code>IDENTITY_INSERT</code> is <code>OFF</code> when I submit changes.
How can I turn it <code>ON</code> from the C# code before I submit changes?
<h2>EDIT</h2>
I have read that this is because ExecuteCommand's code gets executed in a different session.
<h2>EDIT 2</h2>
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?

Linq To Sql and identity_insert

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我正尝试在主键为Identity字段的表上插入记录。我试着给你打电话mycontext.ExecuteCommand("SET identity_insert myTable ON")但这不会有任何好处。当我提交更改时，我收到一个错误，告诉我IDENTITY_INSERT是OFF。在提交更改之前，如何从C#代码中将其转...

问Linq To Sql和identity_insert
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Linq To Sql和identity_insertEN