In doing a bit of development with the Entity Framework I cam across an interesting conundrum. There were a number of alternative paths I could have taken (the “ever helpful” responses you see online to of “just don’t do it that way”), but I wanted to see if I could find a solution within some constraints (see Constraint Driven Development[1]). The idea behind having these constraints was two-fold; first it is easy to imagine a solution set where the constraint is a hard constraint and one can’t “just do it another way” and second it makes the problem much more interesting to solve.
Description of Problem
I want to pull some data from an external dataset into a database structure that I have created. The external dataset has unique identifiers that I both want to store and use in communications. Further the external dataset is both reliable and consistent so I wanted to use those unique identifiers (integers) as the primary keys in the internal database structure. I am using Entity Framework 6.2 with Database First for this solution.
Alternatives and Constraints
There were a number of alternatives considered and either discarded as I wasn’t happy with the solution or discarded as I wanted a solution that fit some “ideal” constraint in my mind.
- Use SQL Statements: the obvious and most direct solution was to just ignore the entity framework, generate some straight forward SQL statements using IDENTITY_INSERT. This would work as an alternative, but was discounted as I wanted to determine if it was possible using Entity Framework (and I already understood this alternative solution)
- Don’t use the Unique Identifiers as Primary Keys: this is a bit more of a reasonable solution, rather than using the external unique identifiers as the TableId primary key, create another column “ExternalUniqueIdentifier” and store the data there. This would work as an alternative, but was discounted due to “aesthetics”; in this case I would have ended up with the potentially confusing situation where the internal Ids went from 1-100 the external unique identifiers when from 1-100 but they didn’t line-up (and worse sometimes they did).
Initial Attempt
Were to start, well let’s try the obvious, we’ll read in the data from the external source and set the Id to that unique value and then save to the database.
var myObject = new MyObject();
myObject.myId = externalUniqueIdentifier;
myEntities.MyObjects.Add(myObject);
myEntities.SaveChanges()
Interestingly this neither works, nor generates an error message. In the database the myId column is an identity column and is set to the next identity value regardless of what is assigned in the “myObject.myId equals” part of the code.
Understanding the Problem
The initial attempt idea is failing for two main reasons (which happen to be the two items to overcome in the solution).
- IDENTITY_INSERT Not On: since IDENTITY_INSERT hasn’t been set to on for the MyObjects table going down this route will never be successful; somehow we need to have IDENTITY_INSERT on for this to work.
- StoreGeneratedPattern on Model: the myId column has the “Store Generated Pattern” set to Identity, which EF does by default for Identity columns, meaning that data isn’t sent to the database, rather EF does a select after the insert to pull this data back for further usage.
Now that the problem was understood, I just needed to sort out those two issues and I would be able to reach a conclusion.
IDENTITY_INSERT Not On
The first item to tackle is how to turn IDENTITY_INSERT On for the table. Initially there also seems like a very straight-forward solution with something like this replacing the standard call to .SaveChanges()
myEntities.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyObject] ON)
myEntities.SaveChanges()
myEntities.Database.ExecuteSqlCommand("SET IDENTITY_INSERT [dbo].[MyObject] OFF)
Unfortunately this doesn’t work quite as expected and is a well worn road[2][3]. The basic issue is when items run and in what context so that the IDENTITY_INSERT is still ON when SaveChanges occurs. There as some possible workarounds with transactions, but that then needs to exist where .SaveChanges is called.
Solution: IDENTITY_INSERT Not On
There are many potential solutions to this, but I chose to go down the path of utilising a command interceptor to turn on and off IDENTITY_INSERT when inserting data. A lot of this solution came from Colin Blakey’s blog post with a similar solution targeting code-first entity frameworks[4]
There are two main aspects to this solution, the CommandInterceptor and Connecting the CommandInterceptor to the DbContext
CommandInterceptor
The command interceptor is a way of intercepting the call to the data store and providing modification. In many instances people use this for dealing with soft deletes or similar. In this instance for the commands that could be inserts we do a little bit of manipulation, in particular we find if it is an INSERT statement, find the table that is being instered into, and then turn on IDENTITY_INSERT for that table. This is how we ensure the IDENTITY_INSERT is run in the same context as the instert statment.
public class IdentityInsertableCommandInterceptor : IDbCommandInterceptor
{
public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
// is insert statement (only time a identity_insert is required)
if (command.CommandText.StartsWith("INSERT"))
{
// regex to determine table being inserted into: INSERT [dbo].[Country]
var identityTableRegex = new Regex(@"INSERT \[dbo\]\.\[(?.+?)\]");
var identityTableMatch = identityTableRegex.Match(command.CommandText);
if (identityTableMatch.Success)
{
var identityTable = identityTableMatch.Groups["identityTable"].Value;
command.CommandText = String.Format("SET IDENTITY_INSERT {0} ON", identityTable) + Environment.NewLine +
command.CommandText + Environment.NewLine +
String.Format("SET IDENTITY_INSERT {0} OFF", identityTable);
}
}
Console.WriteLine("NonQueryExecuting: " + command.CommandText);
}
public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
//Console.WriteLine("NonQueryExecuted");
}
public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
//Console.WriteLine("ReaderExecuting: " + command.CommandText);
}
public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
//Console.WriteLine("ReaderExecuted: " + command.CommandText);
}
public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
//Console.WriteLine("ScalarExecuting");
}
public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext interceptionContext)
{
//Console.WriteLine("ScalarExecuted");
}
Connect CommandInterceptor to DbContext
In addition to the creation of the command interceptor we need to wire that command interceptor up to the DbContext. This is one of those great features / designs / implementation decisions that really shines.
public partial class MyEntitiesForExternalDataInsert
{
public static IdentityInsertableCommandInterceptor identityInsertableCommandInterceptor;
// Alternative DbContext for use when inserting identity columns.
// identityInsertOn - True: run identity insert command incerceptor; False: do not setup command interceptor
// identityInsertOn as a parameter is used to differentiate from the base constructorpublic
MyEntitiesForExternalDataInsert(bool identityInsertOn) : base("name=MyEntitiesForExternalDataInsert")
{
if (identityInsertOn)
{
identityInsertableCommandInterceptor = new IdentityInsertableCommandInterceptor();
DbInterception.Add(identityInsertableCommandInterceptor);
}
}
}
StoreGeneratedPattern on Model
The command interceptor now resolves context and having IDENTITY_INSERT on when inserting the rows, but in reviewing the data coming through the second part become clear as to what needs to occur. When looking at the insert statement one will see that the identity (myId) column is not coming through in the insert statement. This is occurring because that column is marked as identity in the model (the .edmx).
This has the easiest answer, one need but to change that column’s StoreGeneratedPattern property from “identity” to “none” and the entity framework will send it as a regular column to be updated / inserted. This could potentially cause issues depending on what other aspects one is working on with the entities (i.e. if you wanted to say in some cases not provide the identity on insert).
To solve this I tried to find some method of dynamically changing that column’s properties, but those properties are more about how to build the code from the database rather than properties on objects. I then had the realisation that I had two different use cases when inputting data into the data store and that the solution to that wasn’t dynamic changing of properties on the fly in the code or anything more spectacular than just having two DbContexts for the two use cases.
Solution: Generate Usage Specific Models
I generate two models, one just the normal standard way and one where I turn the Id columns from “identity” to “none” and attach the command interceptor. This both resolves issues seen, but also make the code clean and clear. Where there is normal operations the standard DbContext is utilsed, but where there is special inserts occurring a DbContext for that purpose “DbContextForExternalDataInsert” is used.
Conclusion
I was pretty happy with this end solution. It provided two separate methods of accessing the data store (via the two entities / contexts), with both having a defined purpose and clarity for usage. The solution also appealed to my sense of KISS[5] (keep it simple and straightforward) in that there is not a lot of hidden changes or manipulation occurring. It isn’t perfect though, as I had the hardest time with what to name the context that was to be used for Identity Inserts[6][7] ending up with naming that referenced the purpose “ForExternalDataInsert”.
References
[1] .Net Rocks #1542 Constraints Liberate with Mark Seemann
[2] How Can I Force Entity Framework to Insert Identity Columns (StackOverflow)
[3] Why does this EF insert with IDENITY_INSERT not work? (StackOverflow)
[4] Using SQL Identity Insert with Entity Framework Making it Work
[5] KISS Principle (Wikipedia)
[6] Two Hard Things
[7] Naming is Hard
Tidemark @ RisingTide.Tech 