E.F. 4.1 And Those Pesky Identity Columns

I am using the Entity Framework v 4.1 and for certain cases, when I insert data into the database using my DBContext/Code First approach, I need to be able to bypass the Identity creation of my primary key. The desired behavior is that we don't always want to do this. So, after a lot of experimentation and deciphering of other people's posts/questions/blogs, I think I figured it out.

Since I am not using the Model editor, I use the fluent APIs to explicitly set my mappings, fk's, relationships, etc... (Why am I using the APIs rather than the model editor is a different discussion). Anyways,

When configuring a mapping to a table that has an identity column, the first thing we need to tell the is that we will not abby by the default HasDatabaseGeneratedOption setting which is implicitly set to DatabaseGeneratedOption.Identity. We must set it to None. But that's not all, in addition, we must use the ExecuteStoreCommand to execute the raw SQL SET IDENTITY_INSERT command, e.g.:
DbModelBuilder

private

{



    SetIdentityCommandText, tableName, enable ?
} const string SetIdentityCommandText = "SET IDENTITY_INSERT {0} {1}";private int ToggleIdentityInsertFor<T>(bool enable)  var tableName = SourceTable.GetFullName<T>();  return DataContext.ObjectContext.ExecuteStoreCommand(string.Format("ON" : "OFF"));
There is still one more thing we need to worry about. One must be aware that SET IDENTITY_INSERT is a session-dependent command, what that means is that if another connection is established, the identity behavior will be the default for that new connection/session.

Having said that, since by its nature we now need to define a transaction scope so that we can do at least 3 things:
1. Disable the identity
2. Insert your entity to the table
3. Enable back the identity
(in my case I do more complex operations)
A transaction scope is required for all of this to work, otherwise your #2 operation will reset the DB connection and therefor your #1 item is lost.

And the last catch, you must explicitly open the connection right after you instantiate the transaction scope, otherwise all of this hard work will still be a waste. For that I created a wrapper to the Transaction Scope so that my client libraries are completely shielded from this:

using System;using System.Data;using
namespace X
{/// <summary>/// Wraps the System.Transactions API for TransactionScope/// </summary>public class TransactionCoordinator : IDisposable{




{
_dataContext = (
_scope =
_dataContext.ObjectContext.Connection.Open();
}
~TransactionCoordinator()
{
private XY _dataContext;private TransactionScope _scope;private bool disposed = false;public TransactionCoordinator(IDataContext dataContext)XY)dataContext;new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted });// Simply call Dispose(false).Dispose(
}

{
_scope.Complete();

{
_dataContext.ObjectContext.Connection.Close();
}
}

{
Dispose(

}

{

{

{

{
_scope.Dispose();
}
}
disposed =
}
}
}
}false);public void Complete()if (_dataContext.ObjectContext.Connection.State == ConnectionState.Open)public void Dispose()true);GC.SuppressFinalize(this);protected virtual void Dispose(bool disposing)if (!disposed)if (disposing)if (_scope != null)true;
Now I am happily overriding the identity. Now for the second part of my problem, I need to toggle this behavior ON and OFF at run-time. Time to delve deeper into the DBModelBuilder

I hope this saves you lots of time.
System.Transactions;