从 C# 中的 LINQ 写入数据库
我正在尝试通过 LINQ 将一些内容写入我的数据库。目前，我收到以下错误：
当 IDENTITY_INSERT 设置为 OFF 时，无法在表“MyTable”中插入标识列的显式值。
我了解这是与主键相关的问题。我的表将身份规范设置为“是”，身份增量设置为“1”。但我已经使用如下代码成功更新了其他表：
using (DataModelDataContext context = new DataModelDataContext())
{
List<MyTable> newRecords = new List<MyTable>();
MyTable record1 = new MyTable();
record1.CreatedBy = GetUserName();
record1.Information = GetInformation();
newRecords.Add(record1);
MyTable record2 = new MyTable();
record2.CreatedBy = GetUserName();
record2.Information = GetInformation();
newRecords.Add(record2);
context.MyTables.InsertAllOnSubmit(newRecords);
context.SubmitChanges();
}
什么会导致此错误？如果我想写入新记录，有没有办法在将其发送到数据库之前设置主键？像“GetNextKey()”之类的东西？基本上，我只需要插入这些记录。我做错了什么？
谢谢！
I am trying to write some content to my database via LINQ. Currently, I'm getting the following error:
Cannot insert explicit value for identity column in table 'MyTable' when IDENTITY_INSERT is set to OFF.
I understand this is a primary key related issue. My table has the identity specification set to "Yes" with Identity Increment set to "1". But I have successfully updated other tables using code that looks like the following:
using (DataModelDataContext context = new DataModelDataContext())
{
List<MyTable> newRecords = new List<MyTable>();
MyTable record1 = new MyTable();
record1.CreatedBy = GetUserName();
record1.Information = GetInformation();
newRecords.Add(record1);
MyTable record2 = new MyTable();
record2.CreatedBy = GetUserName();
record2.Information = GetInformation();
newRecords.Add(record2);
context.MyTables.InsertAllOnSubmit(newRecords);
context.SubmitChanges();
}
What would cause this error? If I want to write new records, is there a way to set the primary key before it gets sent to the db? Something like "GetNextKey()" or something? Basically, I just need to insert these records. What am I doing wrong?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
假设这些字段都没有
identity值，您发布的代码就可以工作。您基本上需要确保在将项目添加到 Table 集合之前不会尝试设置标识值。 （实际上，在大多数情况下，当要在数据库中生成身份值时，您不应该从代码中设置身份值）。因此，在某个地方，出现错误的代码正在尝试设置标识字段的值。
The code you have posted would work, assuming neither of those fields have the
identityvalue. You basically need to assure you do not try to set the identity value before you add the item to the Table collection. (actually, you should never set the identity value from your code in most circumstances when it is to be generated in the DB).So somewhere, the code that is having an error is trying to set the identity field's value.
如果 ID 很重要（假设您正在导入数据，并且稍后插入的记录将引用这些记录），则在执行插入时暂时关闭标识。如果每条记录上有一个特定的 ID 并不重要，并且您可以让标识列选择一个（这是 99.9% 的情况），请确保在尝试保存记录之前没有为 ID 指定值。还要确保对象和数据库之间的映射指定 ID 是一个标识，以便 EF 知道不要尝试插入 ID 列。
If the ID is important (say you're importing data, and records you'll be inserting later will reference these records), then turn off the identity temporarily while you perform the insert. If having a particular ID on each record doesn't matter, and you can let the identity column pick one (which is 99.9 percent of the time), make sure you're not specifying a value for the ID before trying to save the record. Also make sure that your mapping between the object and the DB specifies that the ID is an identity, so EF knows not to try to insert the ID column.
检查以确保您的主键/标识列在 DBML 中具有以下设置：
这将确保 LINQ-To-SQL 不会尝试将键值插入到该列中，并且它会在插入发生后更新值。当您将表添加到 DBML 时，这些应该打开。
Check to make sure your primary key/identity column has the following set within the DBML:
This will make sure that LINQ-To-SQL will not try and insert the key value into that column and that it will update the value after an insert takes place. These should be on when you added the table to the DBML.