How to switch between DatabaseGeneratedOption.Identity, Computed and None at runtime without having to generate empty DbMigrations

Question

I am migrating a legacy database to a new database which we need to access and "manage" (as oxymoronic as it might sound) primarily through Entity Framework Code-First.

We are using MS SQL Server 2014.

1. The legacy database contained some tables with computed columns. Typical GUID and DateTime stuff.

2. Technically speaking, these columns did not have a computed column specification, but rather where given a default value with NEWID() and GETDATE()

We all know that it is very easy to configure the DbContext to deal with those properties as follows:

modelBuilder.Entity<Foo>()
            .Property(t => t.Guid)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
modelBuilder.Entity<Bar>()
            .Property(t => t.DTS)
            .HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);

The above would instruct the Entity Framework to ignore submitting any supplied values for such properties during INSERTs and UPDATEs.

1. But now we need to allow for import of legacy records and maintain the OLD values, including the PRIMARY KEY, which is marked as IDENTITY

   1. This means we would have to set the Id, Guid and DTS properties to DatabaseGeneratedOption.None while inserting those records.

   2. For the case of Id, we would have to somehow execute SET IDENTITY_INSERT ... ON/OFF within the connection session.

   3. And we want to do this importing process via Code-First as well.

2. If I modify the model and "temporarily" and set those properties to DatabaseGeneratedOption.None after the database has been created, we would get the typical:

   The model backing the context has changed since the database was created. Consider using Code First Migrations to update the database.

3. I understand that we could generate an empty coded-migration with -IgnoreChanges so as to "establish" this latest version of the context, but this wouldn't be an acceptable strategy as we would have to be run empty migrations back-and-forth solely for this purpose.

---

Half an answer:

We have considered giving these properties nullable types, i.e.

public class Foo
{
    ...
    public Guid? Guid { get; set; }
}

public class Bar
{
    ...
    public DateTime? DTS { get; set; }
}

While caring about the default values in an initial DbMigration:

CreateTable(
    "dbo.Foos",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Guid = c.Guid(nullable: false, defaultValueSql: "NEWID()"),
        })
    .PrimaryKey(t => t.Id);


CreateTable(
    "dbo.Bars",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            DTS = c.Guid(nullable: false, defaultValueSql: "GETDATE()"),
        })
    .PrimaryKey(t => t.Id);

---

The Question:

But the question remains: Is there a way to switch between DatabaseGeneratedOption.Identity, DatabaseGeneratedOption.Computed and DatabaseGeneratedOption.None at runtime?

At the very least, how could we turn DatabaseGeneratedOption.Identity on/off at runtime?

Monamonachal

BTW: for values that are only set when a new record is created, it is more efficient to use DatabaseGeneratedOption.Identity instead of DatabaseGeneratedOption.Computed. (if the option is computed, EF will read the values back from the database after each update assuming that they value MAY have changed) (see this answer #5333917) – Collapse

Answer 1

A certain amount of the configuration of the context is always going to be dependent on the runtime environment - for example, proxy generation and validation. As such, runtime configuration of the Entity Framework DbContext is something I leverage quite heavily.

Although I've never used this approach to switch the configuration of the context on a per use-case basis, I see no reason why this would not work.

In its simplest form, this can be achieved by having a set of EntityTypeConfiguration classes for each environment. Each configuration set is then wired to the DbContext on a per-environment basis. Again, in its simplest form this could be achieved by having a DbContext type per environment. In your case, this would be per use-case.

Less naively, I usually encapsulate the configuration of the context in an environment-specific unit of work. For example, the unit of work for an Asp.Net environment has an underlying DbContext configured to delegate validation to the web framework, as well as to turn off proxy generation to prevent serialisation issues. I imagine this approach would have similar usefulness to your problem.

For example (using brute force code):

// Foo Configuration which enforces computed columns
public class FooConfiguration : EntityTypeConfiguration<Foo>
{
    public FooConfiguration()
    {           
        Property(p => p.DateTime).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
        Property(p => p.Guid).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed);
    }
}

// Foo configuration that allows computed columns to be overridden
public class FooConfiguration2 : EntityTypeConfiguration<Foo>
{
    public FooConfiguration2()
    {           
        Property(p => p.DateTime).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
        Property(p => p.Guid).HasDatabaseGeneratedOption(DatabaseGeneratedOption.None);
    }
}

// DbContext that enforces computed columns
public class MyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new FooConfiguration());     
    }
}

// DbContext that allows computed columns to be overridden
public class MyContext2 : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new FooConfiguration2());     
    }
}

This can obviously be tidied up - we usually use a combination of factory and strategy patterns to encapsulate the creation of a runtime specific context. In combination with a DI container this allows the correct set up configuration classes to be injected on a per-environment basis.

Example usage:

[Fact]
public void CanConfigureContextAtRuntime()
{
    // Enforce computed columns
    using (var context = new EfContext())
    {
        var foo1 = new Foo();
        context.Foos.Add(foo1);                             
        context.SaveChanges();
    }

    // Allow overridden computed columns
    using (var context = new EfContext2())
    {              
        var foo2 = new Foo { DateTime = DateTime.Now.AddYears(-3) };
        context.Foos.Add(foo2);
        context.SaveChanges();
    }

    // etc
}

Drumbeat

One thing to note if using this solution: you'll probably need to call Database.SetInitializer<[ContextType]>(null) in the constructor of your alternative DbContext, otherwise you'll still get those "The model backing the context has changed..." errors. – Jakoba

Wouldn't the configuration on the alternative context rewrite the table schemas when OnModelCreating is invoked? And if we call Database.SetInitializer<FooConfig2>(null) how will OnModelCreating be called? – Lati