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; }
}
Then I have added new property in Post
public string Abstract { get; set; }
then I have run
Add-Migration AddPostAbstract
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");
}
After that I have executed this command
Update-Database –Verbose
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
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.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Abstract'.
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.
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:
- 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
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).
Details
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")
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 ( ...
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.
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
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