Azure SQL database column not updated after redeployment of ASP.NET Core MVC Website

Question

I'm developing an ASP.NET Core MVC web application using C#. I've deployed my application only once to Azure and used an Azure SQL Database to store the application's data. I made local changes to the database (by adding an extra column called LogType to a table called Logs in the database) and applied migration, and everything workes just fine, locally. I redeployed my application to Azure and it was successful with no errors. However, I keep getting an Sql exception when I browse to the website:

SqlException: Invalid column name 'LogType'.

So I'm asking how can I apply the migration and update the Azure SQL database to reflect my local changes?

in this case EF core automatic migrations can be the solution: https://stackoverflow.com/questions/39526595/entityframework-core-automatic-migrations — hujtomi, Jun 06 '18 at 21:55

score 1 · Answer 1 · answered Jun 05 '18 at 22:22

In this article: https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/migrations-and-deployment-with-the-entity-framework-in-an-asp-net-mvc-application#deploy-the-application-to-azure

In part "8. Select Update database.", it describes how can you set it up during publish:

If you don't want automatic migration here are the advanced scenarios: https://learn.microsoft.com/en-us/aspnet/mvc/overview/getting-started/getting-started-with-ef-using-mvc/migrations-and-deployment-with-the-entity-framework-in-an-asp-net-mvc-application#advanced-migrations-scenarios

If you have EF core, you can also check this: EntityFramework Core automatic migrations

I don't have the checkbox "Update database". – Mahmoud Hamad Jun 06 '18 at 16:05 — Mahmoud Hamad, Jun 06 '18 at 16:05

score 0 · Accepted Answer · answered Jun 05 '18 at 22:27

Ideally, there is a CI/CD pipeline in place to propagate changes (code, database schema, etc.) upstream the environments (e.g. development, test, staging, production, etc.), including BVT tests, to prevent breaking things.

Find below a couple of options to propagate this change:

--- Option 1 ---

One approach would be connecting to the Azure SQL server/database via SSMS (SQL Server Management Studio, version 17.2 for example) and running a T-SQL script such as the example below (change the table name and debug prints), where it is adding the LogType column to an existing table named dbo.Customer.

Moreover, this script can be executed in the Azure Portal as well by navigating to the Azure SQL database > Query editor (Preview) and running the script.

IF EXISTS(SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo.Customer') AND name='LogType')
BEGIN
    PRINT 'Column LogType already exists on table dbo.Customer and it is configured properly.'
END
ELSE
BEGIN
    PRINT 'Adding column LastName to table dbo.Customer...'

    ALTER TABLE dbo.Customer
    ADD LogType VARCHAR(100) NOT NULL
END
PRINT '--- DONE ---'
GO

--- Option 2 ---

Another option would be manually adding the LogType column to the table, which can be accomplished by connecting to the Azure SQL server/database using SSMS, right-click the table name and choosing design. Then, adding the new column.

Thanks! It works but I had to make a change to the SQL code to add a **nullable** column because otherwise, it wanted a default value since table `dbo.Logs` is populated with data. — Mahmoud Hamad, Jun 06 '18 at 16:30
Do you know how can I automate this process through EF Core when I publish the website? — Mahmoud Hamad, Jun 06 '18 at 16:31

Azure SQL database column not updated after redeployment of ASP.NET Core MVC Website

2 Answers2