在linq-to-sql中指定标识值 [英] specify identity value in linq-to-sql

问题描述

我试图在将新记录插入数据库之前设置标识字段的值，当我知道目标表已经为空时，我试图省去不必要地重新映射所有FK字段的工作.

I am trying to set an identity field's value before inserting the new record into the database, I am trying to save having to needlessly re-map all of the FK fields when I know the destination tables are empty already.

我希望这个问题: 如何手动在LINQ-To-SQL(IDENTITY INSERT)中设置一个Identity字段 会回答我的问题，但是看了看，被接受的答案似乎仍然无法提供直接的答案.

I had hoped that this question: How do I MANUALLY set an Identity field in LINQ-To-SQL (IDENTITY INSERT) would answer my problem however having looked it, the accepted answer still doesn't seem to provide a straight forward answer.

字段定义为:

[Column(Storage = "_ID", AutoSync = AutoSync.OnInsert, DbType = "BigInt NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
    public long ID
    {
        get {}
        set {}
    }

但是，所有这些操作将忽略任何设置值，而仅使用数据库中的下一个值.我发现将IsDbGenerated更改为false仅成功使应用程序引发当IDENTITY_INSERT设置为OFF时，无法在表'Project'中为身份列插入显式值".

All this does however is ignore any set value and just use the next value in the database. I have found that changing the IsDbGenerated to false only succeeds in having the application thrown an "Cannot insert explicit value for identity column in table 'Project' when IDENTITY_INSERT is set to OFF." exception.

即使使用以下内容也无济于事:

Even using the following doesn't help:

newDb.ExecuteCommand("SET IDENTITY_INSERT Contact ON");
newDb.SubmitChanges();
newDb.ExecuteCommand("SET IDENTITY_INSERT Contact OFF");

推荐答案

解决方案是使用SqlConnection并调用.Open()，然后再使用该SqlConnection创建MappingSource.

The solution was to use a SqlConnection and calling .Open() before creating the MappingSource using that SqlConnection.

然后您需要做的就是在字段定义中将IsDbGenerated设置为false.

Then all that you need to do is set the IsDbGenerated to false in the field definition.

所以发件人:

MyDatabaseDataContext newDb = new MyDatabaseDataContext();
var query = from u in newDb .Users
    where u.email == txtEmail.Text
    select u;

成为:

SqlConnection con = new SqlConnection();
con.Open();
MyDatabaseDataContext newDb = new MyDatabaseDataContext(con);
var query = from u in newDb.Users
    where u.email == txtEmail.Text
    select u;

否则，它将使用它自己的内部SQL连接池，并且您的IDENTITY_INSERT无法正常工作.但是请记住，完成后请关闭SQL连接.另外，更改字段定义会使您在正常插入时不会更新ID字段.

Otherwise it will use it's own internal SQL connection pooling and your IDENTITY_INSERT doesn't work. But remember to close the SQL Connection when you're finished. Also, changing the field definition is going to make it not update the ID field when you do inserts normally.

这篇关于在linq-to-sql中指定标识值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！