代码优先迁移-Update-database -script命令生成的SQL脚本不起作用 [英] Code First Migrations - Update-database -script command generated SQL script not working

问题描述

我必须通过Entity Framework 5使用以下模型创建数据库：

I have to created a database through Entity Framework 5 with the following model:

public class Post
{
        public int PostId { get; set; }
        [MaxLength(200)]
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public Blog Blog { get; set; }
}

然后我在 Post

public string Abstract { get; set; }

然后我已经跑步

Add-Migration AddPostAbstract

这在我的<$中创建了以下类c $ c> Migrations 文件夹，之后我通过添加另一个SQL语句

which created the following class in my Migrations folder, after that I have modified this file by adding one more SQL statement

//201308300714477_AddPostAbstract.cs
public override void Up()
{
                AddColumn("dbo.Posts", "Abstract", c => c.String());
                Sql("UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL");
}

public override void Down()
{
    DropColumn("dbo.Posts", "Abstract");
}

之后，我执行了此命令

Update-Database –Verbose

其中更新了我的数据库并返回了此SQL查询：

which updated my database and also returned this SQL query:

ALTER TABLE [dbo].[Posts] ADD [Abstract] [nvarchar](max)
UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL

现在，我从表中删除了 Abstract 列，并尝试在SQL中手动执行上述查询，当时它向我显示以下错误。

Now I have deleted Abstract column from table and tried to execute the above query manually in SQL at that time it show me following error.

消息207，级别16，状态1，第2行

无效的列名摘要。

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Abstract'.

我的问题是，即使通过迁移生成此查询，为什么也不在SQL中执行此查询？

My question is why this query is not executed in SQL even this query is generated through migration?

还是在那里通过迁移生成的脚本来运行此类多重查询的任何方式。

Or is there any way to run such multiple query through migration generated script.

推荐答案

-详细输出仅显示语句摘要。如果在SQL Server Management Studio中手动运行命令，则在两个语句之间需要 GO ：

The -Verbose output just shows a summary of statements. If you run your command manually in SQL Server Management Studio then you need a GO between the two statements:

ALTER TABLE [dbo].[Posts] ADD [Abstract] [nvarchar](max)
GO
UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL

快速解决方案是执行以下操作：

The quick fix is to do something like:

• 更新数据库-script


• 然后使用Sql Server Management Studio进行以下搜索并替换生成的脚本：
  
  
  
  
  • 查找内容： ^ {：b *} {{INSERT | UPDATE | SELECT | DELETE}。+} （这会找到任何CRUD语句）
  
  
  • 替换为： \1GO\n\1\2\n （保留缩进，并在任何CRUD语句之前添加 GO ）
  
  
  • 查找选项：使用正则表达式
  
  
  • update-database -script
  • then use Sql Server management Studio to do the following search and replace in the generated script:
    • find what: ^{:b*}{{INSERT|UPDATE|SELECT|DELETE}.+} (this finds any CRUD statements)
    • replace with: \1GO\n\1\2\n (keep the indents, and add GO before any CRUD statements)
    • Find options: Use regular expressions

    但是请注意，-详细没有提供您想要的输出，您需要输出从-脚本，否则您将丢失 __ MigrationHistory 历史记录表的插入数据，这可能会导致应用程序抛出运行时出现错误（有关详细信息，请参见下文）。

    But, note that -Verbose doesn't give you the output you want, you need the output from -Script, or you will be missing the inserted data for the __MigrationHistory history table which could cause your application to throw an error when it runs (see below for details).

    详细信息

    您对以下 MSDN代码优先迁移页面上的信息的评论很有趣。该页面实际上声明了（在获取SQL脚本 下）

    Your comment below about the information on the MSDN Code First Migrations page is interesting. The page actually states (under the section "Getting a SQL Script")

    
    

    运行Update-Database命令但这一次指定–Script标志

    Run the Update-Database command but this time specify the –Script flag

    如果执行此操作，您将看到类似以下内容的

    If you do this you will see something like:

    ALTER TABLE [dbo].[Posts] ADD [Abstract] [nvarchar](max)
    UPDATE dbo.Posts SET Abstract = LEFT(Content, 100) WHERE Abstract IS NULL
    INSERT INTO [__MigrationHistory] ([MigrationId], [Model], [ProductVersion]) VALUES ( ...
    

    INSERT 很重要-这就是您的应用程序中的EF如何知道它正在使用最新的数据库版本（因此运行，而不是向您显示错误。）但是，它仍然缺少GO命令。因此，SQL Server尝试将3行作为一个批处理进行编译，并失败。

    The INSERT is important - this is how your EF in your application will know it is using the latest db version (and will therefore run instead of showing you an error). But, it is still missing that GO command. SQL server therefore tries to compile the 3 lines as a single batch and fails.

    添加所需的 GO 语句后，您仍然可以通过将其包围来在单个事务中运行它h：

    After adding the GO statements you need, you can still run this in a single transaction by surrounding it with:

    BEGIN TRANSACTION;
    BEGIN TRY
       --Your migration code, with GO statements
    END TRY
    BEGIN CATCH
      SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
    
      IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    END CATCH;
    
    IF @@TRANCOUNT > 0
      COMMIT TRANSACTION;
    GO
    

    如果由于生成大型脚本而感到沮丧，则将<$ c在 ALTER TABLE 行末尾的$ c> GO 对于SSMS中的replace来说是微不足道的，这类似于顶部的这个答案

    If you are frustrated because you are generating large scripts, putting a GO at the end of any ALTER TABLE line is trivial with replace in SSMS, which would be something like the one at the top of this answer

    这篇关于代码优先迁移-Update-database -script命令生成的SQL脚本不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！