Using SQL Identity Insert with Entity Framework 6 – Making it work

When you have a primary key field (say ID) with IDENTITY switched on it works beautifully with EF when doing inserts, etc. Everything you would expect it to, except when you need to turn off IDENTITY insert temporarily for, say data seeding or in my case some complex data conversion where I wanted to keep the original ID values to reference back to the old system. You would think there would be an option hidden somewhere within EF to do this but there isn’t, or at least after a number of searches I could not locate one. Well I know that there is SQL I can run that would temporarily allow me to insert IDENTITY values and then to revert back i.e.

SET IDENTITY_INSERT myTablename ON
...Insert data ....
SET IDENTITY_INSERT myTablename OFF

Well there is a way in EF via the Database class to execute arbitrary SQL.

myDatabaseContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT myTablename");

Unfortunately this seems to execute but then when running SaveChanges the identity value is ignored and a new value is used i.e. the database generates it. Looking at the SQL sent by EF to the database I can see that the ID field is never passed. This is strange, I’ve set IDENTITY insert ON and not supplied an ID field at all and the SQL works. Something strange is going on here.

Well first we need to solve the ID field not being passed in the SQL generated from EF. Looking at the entity class and the database context class I find nothing specifying that the ID field is a key field and that is has IDENTITY turned on. Well, it turns out that if there is only one key field with IDENTITY specified then this is the EF default, so no attributes or code is needed to specify it. So how do I change this. Well, if my key field was not an IDENTITY field there would be an attribute stating so i.e.

[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int ID { get; set; }

But how do we do this without altering the entity class, after all, we only need this temporarily. The solution is to create a new Database Context class and override the OnModelCreating. In the override function we can set the attribute on the entity i.e.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  modelBuilder.Entity<myTableName>()
    .Property(e => e.ID)
    .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
  base.OnModelCreating(modelBuilder);
}

So that is the entity class fixed. So now I run my code again, turning IDENTITY INSERT on, saving the data and then reverting.

myDatabaseContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT myTablename ON");
myDatabaseContext.SaveChanges();
myDatabaseContext.Database.ExecuteSqlCommand("SET IDENTITY_INSERT myTablename OFF");

And I get a SQL exception that we have tried to insert an ID value for an IDENTITY field. What is going on here, the SQL looks good and is running against the same context but it does not work. Well, it turns out that when you run ExecuteSqlCommand EF creates a separate session for this, runs the command and then closes the session. So the IDENTITY INSERT is lost as it only lasts for the duration of the session. Now we have a major problem, how can I get SQL to run in the same sessions as the ‘SaveChanges’ action. Well, after some digging, experimenting and hijacking of other code I have a workable solution. In EF there is the ability to intercept the commands prior/post execution for logging purposes by defining a class based on the IDbCommandInterceptor interface and then adding it during the initialization phase of the database context. Now I’ve been using this in my code to log the details of the SQL generated to make sure it is not doing anything horrific and also to get better information out of EF when a failure occurs i.e. my LogIfError method looks like this

private void LogIfError<TResult>(DbCommand command,
  DbCommandInterceptionContext<TResult> interceptionContext)
  {
    if (interceptionContext.Exception != null)
    {
    if (interceptionContext.Exception is SqlException)
    {
      var sqlException = (SqlException) interceptionContext.Exception;
      log.Info("SQLException '{0}/{1}/{2}'", sqlException.ErrorCode, sqlException.Number, sqlException.Message);
    }
    log.Error("Command {0} failed with exception {1}", command.CommandText,   interceptionContext.Exception);
  }
}

This works nicely but there is also another method called NonQueryExecuting which is called prior to executing the SQL command and it passes in the SQL command. So I now have the raw SQL, what can I do to inject the IDENTITY INSERT sql prior to running the command. Surprisingly easily, it the answer. Just inject the IDENTITY SQL into the SQL command text string, right at the front. So what does this look like in practice. Starting with the interceptor class we have

public class EFLogCommandInterceptor : IDbCommandInterceptor
{
  public void NonQueryExecuting(DbCommand command, 
    DbCommandInterceptionContext interceptionContext)
  {
    if (!string.IsNullOrWhiteSpace(SQLInjectText ))
    {
      command.CommandText = SQLInjectText + " " + 
        command.CommandText;
    }
    LogIfNonAsync(command, interceptionContext);
  }

  public string SQLInjectText { get; set; }

Next we need to add it into the database context, shown below is a simplified version of my database context. Note the addition of the interceptor class during initialization and then in my save changes I pass the name of the IDENTITY table (only one at a time is allowed by SQL) to pass through the the intercept class

public class IdentityContext : myDatabaseContext
{
  public IdentityContext ()
  {
    commandInterceptor = new EFLogCommandInterceptor();
    DbInterception.Add(commandInterceptor)
  }

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
  {
    modelBuilder.Entity<mytablename>()
      .Property(e => e.ID)
      .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
      base.OnModelCreating(modelBuilder);
  }

  public int SaveChanges(string identityTable = null)
  {
    if (identityTable != null)
    {
      commandInterceptor.SQLInjectText = 
        string.Format("SET IDENTITY_INSERT {0} ON", identityTable);
    }
    return SaveChanges();
  }

  private static EFLogCommandInterceptor commandInterceptor;
}

So now we run this code and it works, I get IDENTITY value insert when I need it without causing any problems with my original database context or entity classes. I wish there was an easier way but I have yet to find it.

Advertisements

About Colin Blakey

Way to many years (25+) working in the Financial software world.
This entry was posted in Entity Framework. Bookmark the permalink.

2 Responses to Using SQL Identity Insert with Entity Framework 6 – Making it work

  1. rino147 says:

    this dont working!!!

    • Colin Blakey says:

      This was under EF 6.0. It is quite possible if you are using a different version i.e. EF Core that it will not work. Microsoft reworked the entire code for EF Core. I’ve updated the title to make it clear which version of EF this was for.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s