EF6 entity with DatabaseGeneratedOption.Identity Guid Id force insert my Id value

Question

I am trying to use EF to export/import the existing database of a DbContext. In this context, there are several entities with Guid Id properties with DatabaseGeneratedOption.Identity defined by the ModelBuilder. When I re-import the entities, I want to use the Id value from the serialized object, but it always generates a new Id value when I save the changes. Is there any way to force EF to use my Id value in this case? I know DatabaseGeneratedOption.None will allow me to do it, but then I will always be responsible for generating the Id. I know there segmentation issues of the index that occur without using sequential Guids, so I do not want to do this.

Am I out of luck or has anyone found a trick?

Update: we have decided to simply change all Guid Id from DatabaseGeneratedOption.Identity to DatabaseGenerationOption.None and provide the Id ourselves. Although this leads to index fragmentation, we do not expect this to be a problem with the smaller size of our tables.

Answer 1

You can achieve what you want by defining two contexts that derive from a base context. One context defines its keys with DatabaseGeneratedOption.Identity, the other one with DatabaseGeneratedOption.None. The first one will be your regular application's context.

This is possible by virtue of Guid primary keys not being real identity columns. They're just columns with a default constraint, so they can be inserted without a value, or with a value without having to set identity_insert on.

To demonstrate that this works I used a very simple class:

public class Planet
{
    public Guid ID { get; set; }
    public string Name { get; set; }
}

The base context:

public abstract class BaseContext : DbContext
{
    private readonly DatabaseGeneratedOption _databaseGeneratedOption;

    protected BaseContext(string conString, DatabaseGeneratedOption databaseGeneratedOption)
        : base(conString)
    {
        this._databaseGeneratedOption = databaseGeneratedOption;
    }

    public DbSet<Planet> Planets { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Planet>().HasKey(p => p.ID);
        modelBuilder.Entity<Planet>().Property(p => p.ID)
                    .HasDatabaseGeneratedOption(this._databaseGeneratedOption);
        base.OnModelCreating(modelBuilder);
    }
}

The context subclasses:

public class GenerateKeyContext : BaseContext
{
    public GenerateKeyContext(string conString)
        : base(conString, DatabaseGeneratedOption.Identity)
    { }
}

public class InsertKeyContext : BaseContext
{
    public InsertKeyContext(string conString)
        : base(conString, DatabaseGeneratedOption.None)
    { }
}

I first run some code to create and seed the source database:

var db1 = @"Server=(localDB)\MSSQLLocalDB;Integrated Security=true;Database=GuidGen";
var db2 = @"Server=(localDB)\MSSQLLocalDB;Integrated Security=true;Database=GuidInsert";

// Set initializers:
// 1. just for testing.
Database.SetInitializer(new DropCreateDatabaseAlways<GenerateKeyContext>());
// 2. prevent model check.
Database.SetInitializer<InsertKeyContext>(null);

using (var context = new GenerateKeyContext(db1))
{
    var earth = new Planet { Name = "Earth", };
    var mars = new Planet { Name = "Mars", };
    context.Planets.Add(earth);
    context.Planets.Add(mars);

    context.SaveChanges();
}

And a target database:

using (var context = new GenerateKeyContext(db2))
{
    context.Database.Initialize(true);
}

Finally this is the code that does the actual job:

var planets = new List<UserQuery.Planet>();
using (var context = new GenerateKeyContext(db1))
{
    planets = context.Planets.AsNoTracking().ToList();
}
using (var context = new InsertKeyContext(db2))
{
    context.Planets.AddRange(planets);
    context.SaveChanges();
}

Now in both databases you'll see two records with identical key values.

You might wonder: why can't I use one context class, and construct it either with or without the Identity option? That's because EF builds the EDM model only once for a context type and stores it in the AppDomain. So the option you use first would determine which model EF will use for your context class.