entity-framework – 实体框架6：如何覆盖SQL生成器？

我想在生成数据库模式(DDL)时修改由EF：CF生成的sql,如 suggested by the Entity Framework team.

如何才能做到这一点？

我无法通过Google找到合适的内容.

解决方法

您可以通过调用DbMigrationsConfiguration类的构造函数中的 DbMigrationsConfiguration.SetSqlGenerator()方法,传递数据库提供程序名称(例如,sql Server的“System.Data.sqlClient”)以及要用于的MigrationsqlGenerator实例来覆盖Entity Framework使用的 MigrationSqlGenerator.该数据库提供者.

考虑您链接到的the work item中的示例：

public class MyEntity
{
    public int Id { get; set; }

    [required]
    [MinLength(5)]
    public string Name { get; set; }
}

假设已生成MyEntity表,并使用Add-Migration命令添加Name字段.

默认情况下,scaffolded迁移是：

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生成任何内容.

要使EF传达最小长度要求,您可以使用specify an attribute-to-column annotation convention.如该文档页面所述,默认sql 生成器会忽略任何AnnotationValues.

在DbContext的OnModelCreating()覆盖中,添加以下内容：

modelBuilder.Conventions.Add(new AttributeToColumnAnnotationConvention<MinLengthAttribute,Int32>("minLength",(property,attributes) => attributes.Single().Length));

添加之后,您可以通过运行Add-Migration -Force AddMyEntity_Name重新生成scaffolded迁移.现在脚手架迁移是：

public partial class AddMyEntity_Name : DbMigration
{
    public override void Up()
    {
        AddColumn("dbo.MyEntity",c => c.String(nullable: false,annotations: new Dictionary<string,AnnotationValues>
            {
                { 
                    "minLength",new AnnotationValues(oldValue: null,newValue: "5")
                },}));
    }

    public override void Down()
    {
        DropColumn("dbo.MyEntity",removedAnnotations: new Dictionary<string,object>
            {
                { "minLength","5" },});
    }
}

假设,如在链接的工作项中,您要生成约束以检查修剪的Name值是否大于minLength(在本例中为5).

您可以首先创建一个自定义MigrationsqlGenerator,它扩展sqlServerMigrationsqlGenerator并调用SetsqlGenerator()来安装自定义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)方法,但只需调用基本实现.

如果你看一下the documentation of AddColumnOperation,你会看到两个重要的属性,Column和Table.列是由up()中的lambda创建的ColumnModel,c => c.String(nullable：false,annotations：…).

在Generate()方法中,您可以通过ColumnModel的Annotations属性访问自定义AnnotationValues.

要生成添加约束的DDL,您需要生成 sql并调用Statement()方法.例如：

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生成的异常：

minLength 5 specified for dbo.MyEntity.Name,'',does not satisfy this requirement.

要解决此问题,请在Up()方法中指定一个长度超过最小长度的defaultValue(例如“unknown”)：

public override void Up()
    {
        AddColumn("dbo.MyEntity",defaultValue: "unknown",}));
    }

现在,如果重新运行Update-Database -Verbose,您将看到添加列的ALTER TABLE语句和添加约束的ALTER TABLE语句：

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: Writing Your Own Code First Migration Operations,其中显示了如何实现自定义迁移操作.

entity-framework – 实体框架6：如何覆盖SQL生成器？

解决方法

猜你在找的MsSQL相关文章