specify identity value in linq-to-sql

Question

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.

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.

The field definition is:

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

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");

Answer 1

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

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

So From:

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

Becomes:

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;

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.