EF6 MySql：Update-Database -Script生成不带分号的SQL [英] EF6 MySql: Update-Database -Script generates SQL without semicolon

问题描述

 
 更新数据库
  

但是当我想为其他的生成SQL脚本环境（并保持源代码控制）我使用

 更新数据库-Script 
  

，生成的SQL是这样的：

  CREATE TABLE`AddressType`（`Id` NVARCHAR（10）NOT NULL，`Description` NVARCHAR（30）NOT NULL，PRIMARY KEY（`Id`））ENGINE = INNODB AUTO_INCREMENT = 0 
 CREATE TABLE`Bank `````````````````````````````````````````````b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b b）））））））））  

当我尝试在SQLyog上运行生成的脚本并运行它时，我收到一个错误，我的脚本有无效的语法。我相信这是因为EF在其末尾添加了一个分号，因为它在手动添加分号时运行。当我添加分号时，我遇到的一个问题是，如果由于某些原因，脚本失败，数据库处于不一致的状态，这意味着迁移系统将失败，因为表/列已经存在。

是否有任何设置会在每个语句后自动添加分号？有没有办法让MySql在运行我的脚本/迁移时做全部或全部？

谢谢。

解决方案

您可以通过扩展MySqlMigrationSqlGenerator来完成此操作，如下所示：

  /// <总结> 
 ///自定义MigrationSqlGenerator在
 ///所有迁移语句的末尾添加分号。 
 ///< / summary> 
 public class CustomMySqlMigrationSqlGenerator：MySqlMigrationSqlGenerator {
 public override IEnumerable< MigrationStatement>生成（IEnumerable< MigrationOperation> migrateOperations，string providerManifestToken）{
 IEnumerable< MigrationStatement> statements = base.Generate（migrationOperations，providerManifestToken）; 
 foreach（语句中的MigrationStatement语句）{
 if（！statement.Sql.EndsWith（;））{
 statement.Sql = statement.Sql.TrimEnd（）+; ; 
} 
} 
 return语句; 
} 
} 
  

并在中配置.cs ：

  public Configuration（）{
 AutomaticMigrationsEnabled = false; 
 SetSqlGenerator（MySql.Data.MySqlClient，新的CustomMySqlMigrationSqlGenerator（））; 
} 
  

I am having the following uneasy situation when using EF6 with MySql: each time I create a new migrations I apply the changes to my development environment using

     Update-Database

but when I want to generate the SQL script for my other environments (and to keep in source control) I use

     Update-Database -Script

and the generated SQL is something like this:

CREATE TABLE `AddressType` (`Id` NVARCHAR(10)  NOT NULL ,`Description` NVARCHAR(30)  NOT NULL ,PRIMARY KEY ( `Id`) ) ENGINE=INNODB AUTO_INCREMENT=0
CREATE TABLE `Bank` (`Id` INT NOT NULL ,`CNPJ` BIGINT NOT NULL ,`Name` NVARCHAR(100) ,`WebSite` NVARCHAR(500) ,PRIMARY KEY ( `Id`) ) ENGINE=INNODB AUTO_INCREMENT=0

When I try running the generated script on SQLyog and run it I get an error that my script has invalid syntax. I believe this is because EF only added one semicolon at the end of it, as it runs when I add the semicolons manually. One problem I have when I add the semicolons is that if the script fails for some reason the database is in inconsistent state, meaning the migration system will fail onwards because tables/columns will already exist.

Are there any settings that automatically add the semicolons after every statement? Is there any way I can ask MySql to do all or nothing when running my scripts / migrations?

Thanks.

解决方案

You can accomplish this by extending the MySqlMigrationSqlGenerator as follows:

/// <summary>
/// Custom MigrationSqlGenerator to add semi-colons to the end of 
/// all migration statements.
/// </summary>
public class CustomMySqlMigrationSqlGenerator : MySqlMigrationSqlGenerator {
    public override IEnumerable<MigrationStatement> Generate(IEnumerable<MigrationOperation> migrationOperations, string providerManifestToken) {
        IEnumerable<MigrationStatement> statements = base.Generate(migrationOperations, providerManifestToken);
        foreach (MigrationStatement statement in statements) {
            if (!statement.Sql.EndsWith(";")) {
                statement.Sql = statement.Sql.TrimEnd() + ";";
            }
        }
        return statements;
    }
}

And enable it in Configuration.cs:

public Configuration() {
    AutomaticMigrationsEnabled = false;
    SetSqlGenerator("MySql.Data.MySqlClient", new CustomMySqlMigrationSqlGenerator());
}

这篇关于EF6 MySql：Update-Database -Script生成不带分号的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！