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: http://stackoverflow.com/questions/507515/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.