实体框架6:如何覆盖SQL生成器? [英] Entity Framework 6: How to override SQL generator?
问题描述
我想在生成数据库架构(DDL)时修改由EF:CF生成的SQL，如.
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.
这怎么办?
我无法通过Google找到任何合适的内容.
I couldn't find anything appropriate via Google.
推荐答案
您可以覆盖DbMigrationsConfiguration类的构造函数中的migrations.dbmigrationsconfiguration.setsqlgenerator.aspx> DbMigrationsConfiguration.SetSqlGenerator()方法，传递数据库提供程序名称(例如，SQL Server为"System.Data.SqlClient")和MigrationSqlGenerator实例用于该数据库提供程序.
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.
请考虑您链接到的工作项中的示例:
public class MyEntity
{
public int Id { get; set; }
[Required]
[MinLength(5)]
public string Name { get; set; }
}
假设已经生成了MyEntity的表，并使用Add-Migration命令添加了Name字段.
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");
}
}
请注意，脚手架没有为MinLengthAttribute生成任何东西.
Notice that the scaffolder did not generate anything for the MinLengthAttribute.
要让EF传达最小长度要求，您可以指定属性到列的注释约定.如该文档页面所述，任何 AnnotationValues 会被默认的SQL生成器忽略.
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.
在DbContext的OnModelCreating()覆盖范围内，添加以下内容:
Within your DbContext's OnModelCreating() override, add the following:
modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<MinLengthAttribute, Int32>("minLength", (property, attributes) => attributes.Single().Length));
添加后，您可以通过运行Add-Migration -Force AddMyEntity_Name重新生成支架式迁移.现在，脚手架的迁移是:
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" },
});
}
}
假设像链接的工作项中一样，您想要生成一个约束以检查修剪后的Name值是否大于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).
您可以先创建一个扩展SqlServerMigrationSqlGenerator的自定义MigrationSqlGenerator，然后调用SetSqlGenerator()来安装该自定义MigrationSqlGenerator:
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)
{
//...
}
}
现在，此CustomSqlServerMigrationSqlGenerator覆盖了Generate(AddColumnOperation)方法，但只是调用了基本实现.
Right now, this CustomSqlServerMigrationSqlGenerator overrides the Generate(AddColumnOperation) method, but simply calls the base implementation.
如果您查看 AddColumnOperation的文档，/a>，您将看到两个重要的属性，Column和Table. Column是 ColumnModel 由lambda在Up()，c => c.String(nullable: false, annotations: ...)中创建.
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: ...).
在Generate()方法中，您可以通过ColumnModel的Annotations属性访问自定义AnnotationValues.
In the Generate() method, you can access the custom AnnotationValues via the Annotations property of the ColumnModel.
要生成添加约束的DDL，您需要生成SQL并调用Statement()方法.例如:
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());
}
}
}
}
}
}
如果运行Update-Database -Verbose，则会看到CustomSqlServerMigrationSqlGenerator生成的异常:
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.
要解决此问题，请在Up()方法中指定一个默认值，该值大于最小长度(例如"unknown"):
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")
},
}));
}
现在，如果您重新运行Update-Database -Verbose，将看到添加列的ALTER TABLE语句和添加约束的ALTER TABLE语句:
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)
另请参见: EF6:首先编写您自己的代码迁移操作，其中显示了如何实施自定义迁移操作.
See also: EF6: Writing Your Own Code First Migration Operations, which shows how to implement a custom migration operation.
这篇关于实体框架6:如何覆盖SQL生成器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
