如何在运行时切换DatabaseGeneratedOption.Identity，Computed和None，而不必生成空的DbMigrations [英] How to switch between DatabaseGeneratedOption.Identity, Computed and None at runtime without having to generate empty DbMigrations

问题描述

我正在将一个遗留数据库迁移到一个新的数据库，我们需要通过实体框架代码优先访问和管理（如同它的声音一样）。

我们正在使用MS SQL Server 2014。

1. 遗留数据库包含一些表，其中已计算列。 典型的GUID和DateTime内容




2. 从技术上讲，这些列没有计算列规范，价值与 NEWID（）和 GETDATE（）

我们都知道，配置 DbContext 可以很容易地处理这些属性，如下所示：

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

上述将指示实体框架忽略在<$ c $期间提交此类属性的任何提供的值c> INSERTs 和更新。

1. 但现在，我们需要允许导入旧记录并维护OLD值，包括标记为 IDENTITY 的 PRIMARY KEY code>

   
   

   1. 这意味着我们必须设置 Id ， Guid 和 DTS 属性为 DatabaseGeneratedOption.None

   
   

   2. 对于 Id 的情况，我们必须以某种方式执行 SET IDENTITY_INSERT ... ON / OFF 在连接会话中。

   
   

   3. 我们要做这个
      通过Code-First导入过程。

   
   




2. 修改模型和临时并将这些属性设置为数据库创建后的$ code> DatabaseGeneratedOption.None 我们将获得典型的：

   
   
   

   模型支持自创建数据库以来，上下文已经更改。考虑使用代码优先迁移来更新数据库。




3. 我知道我们可以使用 -IgnoreChanges ，以便建立这个最新版本的上下文，但是这并不是一个可以接受的策略，因为我们必须为此而独立运行空的迁移。

---

一半答案：

我们已经考虑给这些属性可空类型，即

  public class Foo 
 {
 ... 
 public Guid？指导组; } 
} 
 
 public class Bar 
 {
 ... 
 public DateTime？ DTS {get;组; } 
} 
  

在初始化中关注默认值时， 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） ; 
  

---

问题：

但问题仍然存在：有没有办法在 DatabaseGeneratedOption.Identity 之间切换， DatabaseGeneratedOption.Computed 和 DatabaseGeneratedOption.None 在运行时？

至少，我们怎么可以 DatabaseGeneratedOption.Identity 运行时的on / off？

解决方案

一定量的配置上下文总是依赖于运行时环境 - 例如代理生成和验证。因此，实体框架 DbContext 的运行时配置是我充分利用的东西。

尽管我从未使用过这种方法在每个用例的基础上切换上下文的配置，但是我看不出为什么这不行。 p>

以最简单的形式，可以通过为每个环境设置一组 EntityTypeConfiguration 类来实现。然后，每个配置集在每个环境的基础上连接到 DbContext 。再次，以最简单的形式，这可以通过在每个环境中具有 DbContext 类型来实现。在您的情况下，这将是每个用例。

不太天真，我通常将环境的配置封装在环境特定的工作单元即可。例如，Asp.Net环境的工作单位有一个底层的 DbContext 配置为将验证委托给Web框架，以及关闭代理生成以防止序列化的问题。我想象这种方法对你的问题有类似的用处。

例如（使用暴力代码）：

  //强制计算列的Foo配置
 public class FooConfiguration：EntityTypeConfiguration&Foo> 
 {
 public FooConfiguration（）
 {
属性（p => p.DateTime）.HasDatabaseGeneratedOption（DatabaseGeneratedOption.Computed）; 
属性（p => p.Guid）.HasDatabaseGeneratedOption（DatabaseGeneratedOption.Computed）; 
} 
} 
 
 //允许计算列被覆盖的Foo配置
 public class FooConfiguration2：EntityTypeConfiguration&Foo> 
 {
 public FooConfiguration2（）
 {
属性（p => p.DateTime）.HasDatabaseGeneratedOption（DatabaseGeneratedOption.None）; 
属性（p => p.Guid）.HasDatabaseGeneratedOption（DatabaseGeneratedOption.None）; 
} 
} 
 
 //强制执行计算列的DbContext 
 public class MyContext：DbContext 
 {
 protected override void OnModelCreating（DbModelBuilder modelBuilder ）
 {
 modelBuilder.Configurations.Add（new FooConfiguration（））; 
} 
} 
 
 //允许计算列被覆盖的DbContext 
 public class MyContext2：DbContext 
 {
 protected override void OnModelCreating （DbModelBuilder modelBuilder）
 {
 modelBuilder.Configurations.Add（new FooConfiguration2（））; 
} 
} 
  

这显然可以整理 - 我们通常使用工厂和策略模式的组合来封装创建运行时特定上下文。结合DI容器，这允许在每个环境基础上注入正确的设置配置类。

示例用法：

  [事实] 
 public void CanConfigureContextAtRuntime（）
 {
 //强制计算列
使用（var context = new EfContext（））
 {
 var foo1 = new Foo（）; 
 context.Foos.Add（foo1）; 
 context.SaveChanges（）; 
} 
 
 //允许重写的计算列
使用（var context = new EfContext2（））
 {
 var foo2 = new Foo {DateTime = DateTime.Now.AddYears（-3）}; 
 context.Foos.Add（foo2）; 
 context.SaveChanges（）; 
} 
 
 // etc 
} 
  

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?

解决方案

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
}

这篇关于如何在运行时切换DatabaseGeneratedOption.Identity，Computed和None，而不必生成空的DbMigrations的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！