How to deploy ASP.NET MVC site with existing database to Azure Web Apps?

Question

Question structure: First I give structure and configs of my ASP-MVC-4 project, then describe how I deploy my site to Azure Web Apps and finally present my error and question. My problem is related to conflicts with EF Code-First Migrations during Azure Deploy.

---

My ASP MVC 4 Project and configs:

I'm using Visual Studio 2015-U3. I've ASP.NET MVC 4 website. Target framework: .NET Framework 4.5. The site have its own database and works locally and interacts with database ((LocalDb)\v11.0). I was using Entity Framework Code-First approach to create and fill data.

My DB connection string in Web.config:

 <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;Initial Catalog=OnlineAuctionDb;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\OnlineAuctionDb.mdf" providerName="System.Data.SqlClient"/>

(it is replaced on publish, but just in case)

My DbContext class:

    public partial class AuctionDataContext : DbContext
    {       
        public AuctionDataContext() : base("DefaultConnection") 
        {    }

        public DbSet<Auction> Auctions { get; set; }
        public DbSet<Category> Categories { get; set; }
        public DbSet<User> Users { get; set; }
        public DbSet<Order> Orders { get; set; }
        public DbSet<Bid> Bids { get; set; }
        public DbSet<ItemImage> Images { get; set; }
        public DbSet<Detail> Details { get; set; }
        public DbSet<DetailValue> DetailValues { get; set; }
   }

I have DataContextInitializer class where I initialize all needed data.

 public class DataContextInitializer : DropCreateDatabaseAlways<AuctionDataContext>
    {
        protected override void Seed(AuctionDataContext context)
        { 
           ***All my initializations go here 
        }
    }

Since I'm not using plain SQL and instead use Entity Framework Code First I enabled migrations. In Configuration class and Configuration.cs file I wrote:

 public Configuration()
        {
            AutomaticMigrationsEnabled = true;
        }

---

Deployment to Azure

Now I switch to deployment to Azure Web Apps. I deployed the site to Azure Web Sites through VS using Publish button.

Step-by-step:

I selected Microsoft Azure Web Apps:

---

Then I selected my created site auctionWeb (everything here was set up automatically):

---

At the next step I only selected Execute Code First Migrations. Connection string (Default Connection) was set up automatically. It was set up to:

Data Source=tcp:auction-tserver.database.windows.net,1433;Initial Catalog=OnlineAuctionDb;User Id=auctionAdmin@auction-tserver.database.windows.net;Password=mypassword

---

In final step I hit Publish button:

---

Output in browser:

I received the error:

Server Error in '/' Application. Invalid column name 'FirstName'. Invalid column name 'LastName'. Invalid column name 'Email'. Invalid column name 'Country'. Invalid column name 'State'. Invalid column name 'City'. Invalid column name 'Address'. Invalid column name 'PostalCode'. Invalid column name 'Phone'. Invalid column name 'Password'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack trace:

[SqlException (0x80131904): Invalid column name 'FirstName'.
Invalid column name 'LastName'.
Invalid column name 'Email'.
Invalid column name 'Country'.
Invalid column name 'State'.
Invalid column name 'City'.
Invalid column name 'Address'.
Invalid column name 'PostalCode'.
Invalid column name 'Phone'.
Invalid column name 'Password'.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2442634
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5766568
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4162
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption) +255
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2031
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +911
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +359
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +286
   WebMatrix.Data.Database.Execute(String commandText, Object[] args) +111
   WebMatrix.WebData.DatabaseWrapper.Execute(String commandText, Object[] parameters) +14
   WebMatrix.WebData.SimpleMembershipProvider.CreateUserRow(IDatabase db, String userName, IDictionary`2 values) +695
   WebMatrix.WebData.SimpleMembershipProvider.CreateUserAndAccount(String userName, String password, Boolean requireConfirmation, IDictionary`2 values) +65
   WebMatrix.WebData.WebSecurity.CreateUserAndAccount(String userName, String password, Object propertyValues, Boolean requireConfirmationToken) +70
   OnlineAuction.MvcApplication.SetSecurityOptions() +322
   OnlineAuction.MvcApplication.Application_Start() +51

[HttpException (0x80004005): Invalid column name 'FirstName'.
Invalid column name 'LastName'.
Invalid column name 'Email'.
Invalid column name 'Country'.
Invalid column name 'State'.
Invalid column name 'City'.
Invalid column name 'Address'.
Invalid column name 'PostalCode'.
Invalid column name 'Phone'.
Invalid column name 'Password'.]
   System.Web.HttpApplicationFactory.EnsureAppStartCalledForIntegratedMode(HttpContext context, HttpApplication app) +9982013
   System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +118
   System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +172
   System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +336
   System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +296

[HttpException (0x80004005): Invalid column name 'FirstName'.
Invalid column name 'LastName'.
Invalid column name 'Email'.
Invalid column name 'Country'.
Invalid column name 'State'.
Invalid column name 'City'.
Invalid column name 'Address'.
Invalid column name 'PostalCode'.
Invalid column name 'Phone'.
Invalid column name 'Password'.]
   System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +9963380
   System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +101
   System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +254

Question:

Why I receive the error? How to properly deploy ASP.NET MVC 4 website to Microsoft Azure Web Apps cloud when I use EF Code-first so that database and site work together. On localhost everything works.

---

Update 1:

When I go to settings of my database in Azure portal and open Database connection strings I see this string:

Server=tcp:auction-tserver.database.windows.net,1433;Initial Catalog=OnlineAuctionDb;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

But when I set this string (with correct login and password) to my Web App settings of connection strings I still receive the same error page.

---

Update 2:

In Global.asax file in Application_Start() method I had this line of code:

 System.Data.Entity.Database.SetInitializer(new OnlineAuction.Models.Entities.DataContextInitializer());

Now I changed it to:

System.Data.Entity.Database.SetInitializer(new System.Data.Entity.MigrateDatabaseToLatestVersion<AuctionDataContext, Configuration>());

I moved all my initializations from DataContextInitializer class to Configuration class (Seed() method).

So my DataContextInitializer that looked like this:

 public class DataContextInitializer : DropCreateDatabaseAlways<AuctionDataContext>
        {
            protected override void Seed(AuctionDataContext context)
            { 
               ***All my initializations go here 
            }
        }

is no longer needed. So Configuration and DataContextInitializer classes no longer conflict.

Now, all my initializations are in Configuration class:

   public class Configuration : DbMigrationsConfiguration<OnlineAuction.Models.Entities.AuctionDataContext>
    {
        public Configuration()
        {
            AutomaticMigrationsEnabled = false;
        }

        protected override void Seed(AuctionDataContext context)
        { 
            ***All my initializations go here 
        }
    }

I also set to false AutomaticMigrationsEnabled option.

But I'm not sure what to do with checkbox Execute Code First Migrations (runs on application start) during website Publish process. Do I have to switch it off now?

I'm confused with all code-first migrations conflicts. It still doesn't work.

Answer 1

According to the Stack Trace you provided, I assumed that you did not migrate the DB tables correctly. Based on the source code you linked, I could reproduce your issue after I deployed the website to Azure. Here is the DB tables on my Azure SQL.

Note: Upon the screenshot, we could see that there are only two properties in Users table.

After some trials, I could deploy my website to Azure successfully. You could follow the steps below and try to find whether it could work as expected on your side.

Modify the database initialization strategy below in Application_Start as you mentioned:

System.Data.Entity.Database.SetInitializer(new System.Data.Entity.MigrateDatabaseToLatestVersion<AuctionDataContext, Configuration>());

Move the initialization in function SetSecurityOptions to the bottom of the Seed() function within the Configuration class.

Note: Since you have enabled migrations, you need to either add the pending model changes to a code-based migration or enable automatic migration to update the database. So, you need to set DbMigrationsConfiguration.AutomaticMigrationsEnabled to true to enable automatic migration.

Result: http://bruce-chen-001.azurewebsites.net/

Additionally, please follow the tutorial provided by Russell Young for a better understanding of Code First Migrations.