实体框架 6:如何覆盖 SQL 生成器? [英] Entity Framework 6: How to override SQL generator?
问题描述
我想修改 EF:CF 在生成数据库模式 (DDL) 时生成的 SQL，如 由实体框架团队建议.
如何做到这一点?
我无法通过 Google 找到任何合适的内容.
您可以覆盖 MigrationSqlGenerator 由实体框架通过调用 DbMigrationsConfiguration.SetSqlGenerator() 方法，传递数据库提供程序名称(例如 "System.Data.SqlClient" 用于 SQL Server)，以及用于该数据库提供程序的 MigrationSqlGenerator 实例.
考虑您链接到的工作项中的示例:
public class MyEntity{公共 int ID { 获取；放;}[必需的][最小长度(5)]公共字符串名称 { 获取；放;}}假设 MyEntity 的表已经生成，并且使用 Add-Migration 命令添加了 Name 字段.
默认情况下，脚手架迁移是:
公共部分类 AddMyEntity_Name : DbMigration{公共覆盖无效Up(){AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false));}公共覆盖无效 Down(){DropColumn("dbo.MyEntity", "Name");}}请注意，脚手架没有为 MinLengthAttribute 生成任何内容.
要让 EF 传达最小长度要求，您可以指定属性到列的注释约定.如该文档页面所述，任何 AnnotationValues 被默认的 SQL 生成器忽略.
在 DbContext 的 OnModelCreating() 覆盖中，添加以下内容:
modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<MinLengthAttribute, Int32>("minLength", (property, attributes) => attributes.Single().Length));添加后，您可以通过运行 Add-Migration -Force AddMyEntity_Name 重新生成脚手架迁移.现在脚手架的迁移是:
公共部分类 AddMyEntity_Name : DbMigration{公共覆盖无效Up(){AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false,注释:新字典<字符串，AnnotationValues>{{"最小长度",新的注释值(旧值:空，新值:5")},}));}公共覆盖无效 Down(){DropColumn("dbo.MyEntity", "名称",删除注释:新字典<字符串，对象>{{ "minLength", "5" },});}}假设在链接的工作项中，您想要生成一个约束来检查修剪后的 Name 值是否大于 minLength(在本例中为 5).
您可以首先创建一个扩展 SqlServerMigrationSqlGenerator 的自定义 MigrationSqlGenerator 并调用 SetSqlGenerator() 来安装自定义 MigrationSqlGenerator:
内部类 CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator{受保护的覆盖无效生成(AddColumnOperation addColumnOperation){base.Generate(addColumnOperation);}}内部密封类配置:DbMigrationsConfiguration<DataContext>{公共配置(){AutomaticMigrationsEnabled = 假；SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());}受保护的覆盖无效种子(DataContext 上下文){//...}}现在，这个 CustomSqlServerMigrationSqlGenerator 覆盖了 Generate(AddColumnOperation) 方法，只是简单地调用了基础实现.
如果您查看 AddColumnOperation 的文档，你会看到两个重要的属性，Column和Table.Column 是 ColumnModel由 Up() 中的 lambda 创建的 ，c =>c.String(nullable: false, annotations: ...).
在 Generate() 方法中，您可以通过 ColumnModel 的 Annotations 属性访问自定义的 AnnotationValues.
要生成添加约束的 DDL，需要生成 SQL 并调用 Statement() 方法.例如:
内部类 CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator{受保护的覆盖无效生成(AddColumnOperation addColumnOperation){base.Generate(addColumnOperation);var column = addColumnOperation.Column;if (column.Type == System.Data.Entity.Core.Metadata.Edm.PrimitiveTypeKind.String){var annotations = column.Annotations;AnnotationValues minLengthValues;if (annotations.TryGetValue("minLength", out minLengthValues)){var minLength = Convert.ToInt32(minLengthValues.NewValue);如果 (minLength > 0){if (Convert.ToString(column.DefaultValue).Trim().Length < minLength){throw new ArgumentException(String.Format("为 {1}.{2} 指定了 minLength {0}，但默认值 '{3}' 不满足此要求.", minLength, addColumnOperation.Table, column.名称，列.默认值))；}使用 (var writer = new StringWriter()){writer.Write("ALTER TABLE");writer.Write(Name(addColumnOperation.Table));writer.Write("添加约束");writer.Write(Quote("ML_" + addColumnOperation.Table + "_" + column.Name));writer.Write("检查 (LEN(LTRIM(RTRIM({0}))) > {1})", Quote(column.Name), minLength);语句(writer.ToString())；}}}}}}如果你运行Update-Database -Verbose，你会看到CustomSqlServerMigrationSqlGenerator产生的异常:
要解决此问题，请在 Up() 方法中指定一个比最小长度更长的 defaultValue(例如 "unknown"):
public override void Up(){AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false, defaultValue: "unknown",注释:新字典<字符串，AnnotationValues>{{"最小长度",新的注释值(旧值:空，新值:5")},}));}现在，如果您重新运行 Update-Database -Verbose，您将看到添加列的 ALTER TABLE 语句和 ALTER TABLE 添加约束的语句:
另请参阅:EF6:首先编写自己的代码迁移操作，展示了如何实现自定义迁移操作.
I'd like to amend the SQL that's being generated by EF:CF when generating the database schema (DDL), as suggested by the Entity Framework team.
How can this be done?
I couldn't find anything appropriate via Google.
You can override the MigrationSqlGenerator that is used by Entity Framework by calling the DbMigrationsConfiguration.SetSqlGenerator() method in the constructor of your DbMigrationsConfiguration class, passing the database provider name (e.g. "System.Data.SqlClient" for SQL Server), and the MigrationSqlGenerator instance to use for that database provider.
Consider the example from the work item that you linked to:
public class MyEntity
{
public int Id { get; set; }
[Required]
[MinLength(5)]
public string Name { get; set; }
}
Suppose that the table for MyEntity had already been generated and the Add-Migration command was used to add the Name field.
By default, the scaffolded migration is:
public partial class AddMyEntity_Name : DbMigration
{
public override void Up()
{
AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false));
}
public override void Down()
{
DropColumn("dbo.MyEntity", "Name");
}
}
Notice that the scaffolder did not generate anything for the MinLengthAttribute.
To have EF convey the minimum length requirement, you can specify an attribute-to-column annotation convention. As mentioned on that documentation page, any AnnotationValues are ignored by the default SQL generators.
Within your DbContext's OnModelCreating() override, add the following:
modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<MinLengthAttribute, Int32>("minLength", (property, attributes) => attributes.Single().Length));
After adding that, you can regenerate the scaffolded migration by running Add-Migration -Force AddMyEntity_Name. Now the scaffolded migration is:
public partial class AddMyEntity_Name : DbMigration
{
public override void Up()
{
AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false,
annotations: new Dictionary<string, AnnotationValues>
{
{
"minLength",
new AnnotationValues(oldValue: null, newValue: "5")
},
}));
}
public override void Down()
{
DropColumn("dbo.MyEntity", "Name",
removedAnnotations: new Dictionary<string, object>
{
{ "minLength", "5" },
});
}
}
Suppose that, as in the linked work item, you want to generate a constraint to check that the trimmed Name value is greater than the minLength (5 in this case).
You can start by creating a custom MigrationSqlGenerator that extends SqlServerMigrationSqlGenerator and call SetSqlGenerator() to install the custom MigrationSqlGenerator:
internal class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
protected override void Generate(AddColumnOperation addColumnOperation)
{
base.Generate(addColumnOperation);
}
}
internal sealed class Configuration : DbMigrationsConfiguration<DataContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false;
SetSqlGenerator("System.Data.SqlClient", new CustomSqlServerMigrationSqlGenerator());
}
protected override void Seed(DataContext context)
{
//...
}
}
Right now, this CustomSqlServerMigrationSqlGenerator overrides the Generate(AddColumnOperation) method, but simply calls the base implementation.
If you look at the documentation of AddColumnOperation, you will see two important properties, Column and Table. Column is the ColumnModel that was created by the lambda in Up(), c => c.String(nullable: false, annotations: ...).
In the Generate() method, you can access the custom AnnotationValues via the Annotations property of the ColumnModel.
To generate the DDL that adds the constraint, you need to generate the SQL and call the Statement() method. For example:
internal class CustomSqlServerMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
protected override void Generate(AddColumnOperation addColumnOperation)
{
base.Generate(addColumnOperation);
var column = addColumnOperation.Column;
if (column.Type == System.Data.Entity.Core.Metadata.Edm.PrimitiveTypeKind.String)
{
var annotations = column.Annotations;
AnnotationValues minLengthValues;
if (annotations.TryGetValue("minLength", out minLengthValues))
{
var minLength = Convert.ToInt32(minLengthValues.NewValue);
if (minLength > 0)
{
if (Convert.ToString(column.DefaultValue).Trim().Length < minLength)
{
throw new ArgumentException(String.Format("minLength {0} specified for {1}.{2}, but the default value, '{3}', does not satisfy this requirement.", minLength, addColumnOperation.Table, column.Name, column.DefaultValue));
}
using (var writer = new StringWriter())
{
writer.Write("ALTER TABLE ");
writer.Write(Name(addColumnOperation.Table));
writer.Write(" ADD CONSTRAINT ");
writer.Write(Quote("ML_" + addColumnOperation.Table + "_" + column.Name));
writer.Write(" CHECK (LEN(LTRIM(RTRIM({0}))) > {1})", Quote(column.Name), minLength);
Statement(writer.ToString());
}
}
}
}
}
}
If you run Update-Database -Verbose, you will see an exception generated by CustomSqlServerMigrationSqlGenerator:
minLength 5 specified for dbo.MyEntity.Name, but the default value, '', does not satisfy this requirement.
To fix this issue, specify a defaultValue in the Up() method that is longer than the minimum length (e.g. "unknown"):
public override void Up()
{
AddColumn("dbo.MyEntity", "Name", c => c.String(nullable: false, defaultValue: "unknown",
annotations: new Dictionary<string, AnnotationValues>
{
{
"minLength",
new AnnotationValues(oldValue: null, newValue: "5")
},
}));
}
Now if you re-run Update-Database -Verbose, you will see the ALTER TABLE statement that adds the column and the ALTER TABLE statement that adds the constraint:
ALTER TABLE [dbo].[MyEntity] ADD [Name] [nvarchar](max) NOT NULL DEFAULT 'unknown' ALTER TABLE [dbo].[MyEntity] ADD CONSTRAINT [ML_dbo.MyEntity_Name] CHECK (LEN(LTRIM(RTRIM([Name]))) > 5)
See also: EF6: Writing Your Own Code First Migration Operations, which shows how to implement a custom migration operation.
这篇关于实体框架 6:如何覆盖 SQL 生成器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
