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

Question

I am moving the legacy database to the new database that we need for access and "management" (as oxymoronic, as it may sound), primarily through the Entity Framework Code-First.

We are using MS SQL Server 2014.

• The inherited database contained several tables with columns with calculation . Typical GUID and DateTime properties.

• From a technical point of view, these columns did not have a computed column specification, but rather, when the default value is set with NEWID() and GETDATE()

We all know that it is very easy to configure DbContext to solve these 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 instructed the Entity Framework to ignore sending any setpoints for such properties during INSERTs and UPDATEs .

• But now we need to enable the import of obsolete records and support OLD values , including the PRIMARY KEY , which is marked as IDENTITY

  • This means that we would have to set the Id , Guid and DTS properties in DatabaseGeneratedOption.None when inserting these records.

  • In the case of Id we need to somehow execute SET IDENTITY_INSERT ... ON/OFF in the communication session.

  • And we want to do this import through Code-First.

• If I change the model and "temporarily" and set these properties to DatabaseGeneratedOption.None after creating the database, we get the typical one:

  The context-supporting model has changed since the database was created. Consider using First First Migrations to upgrade your database.

• I understand that we could generate an empty encoded migration using -IgnoreChanges to “install” this latest version of the context, but this would not be an acceptable strategy, since we would need to run empty migrations back and forth exclusively for this purpose.

---

Half the answer:

We considered the possibility of assigning these properties to types with a zero value, i.e.

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

Holding the defaults in the 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); 

---

Question:

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

At least how could we enable / disable DatabaseGeneratedOption.Identity at runtime?

Answer 1

A specific part of the context configuration will always depend on the runtime — for example, generating and checking a proxy server. So the configuration of the Entity Framework DbContext is something that I use quite heavily.

Although I have never used this approach to switch context configuration in each case, I see no reason why this will not work.

In its simplest form, this can be achieved through a set of EntityTypeConfiguration classes for each environment. Each configuration set is then connected to the DbContext for each environment. Again, in its simplest form, this can be achieved using the DbContext type for the environment. In your case it will be in case of use.

Less naive I usually encapsulate a context configuration in a unit of work related to the environment . For example, the unit of work for Asp.Net has a basic DbContext designed to delegate verification to web frameworks, as well as to disable proxy generation to prevent serialization problems. I suggest that this approach will have similar utility with 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 obviously can be matched - we usually use a combination of factory and strategy templates to encapsulate the creation of a specific runtime context. In combination with the DI container, this allows you to correctly configure configuration classes for each environment-based environment.

Usage example:

 [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 }