Migrations

Entity Framework Core with SQLite Migration Limitations

This is part of what has turned into a series on Entity Framework Core with SQLite. The other parts can be found below.

Entity Framework Core with SQLite
Entity Framework Core Errors Using Add-Migration
Entity Framework Core with SQLite Scaffolding

The starting point of the code for this post can be found here.

Migration Limitations when using SQLite

SQLite’s ALTER TABLE is limited which in turn limits what Entity Framework Core can do via a migration. The official docs on the subject can be found here. These limitations are on the Entity Framework Team’s list of issues as an open enhancement and can be tracked here.

As long as you are just adding new tables or columns you would never notice the limitation, but if you have spelling problems like I do then the need to rename a column can be important. Thankfully things like ReSpeller (link is to the pro page, but a free version is available in ReSharpers extension manager) help with my spelling issues.

Unsupported example with a column rename

As an example of how to handle a migration that isn’t supported, we are going to rename the State property of the Contact class to Subregion.

Rename property on the model

Open the Contact class which can be found in the Models directory and make the following change.

Before:
public string State { get; set; }

After:
public string Subregion { get; set; }
Add a migration

With the property name change using the following command in the Package Manager Console to create a new migration.

Add-Migration RenameContactStateToSubregion -c ContactsDbContext

Which produces the following migration class.

public partial class RenameContactStateToSubregion : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "State",
            table: "Contacts",
            newName: "Subregion");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "Subregion",
            table: "Contacts",
            newName: "State");
    }
}
Error trying to apply the migration

As expected when an attempt to apply the above migration results in the following exception.

System.NotSupportedException: SQLite does not support this migration operation (‘RenameColumnOperation’). For more information, see http://go.microsoft.com/fwlink/?LinkId=723262.

Modify migration to manually rename the column

Searching for how to rename a column in SQLite will turn up a lot of results including this from the official docs and answers like this on StackOverflow. The gist of the how to do a rename is to create a new table with the desired schema, copy the data from the original table, drop the old table, and finally rename the new table to match the original name.

Now knowing the process the migration above can be modified to apply SQL directly instead of using Entity Framework Core to generate the SQL. This can be done by using the Sql function of the MigrationBuilder class. The following is the resulting migration.

public partial class RenameContactStateToSubregion : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            @"PRAGMA foreign_keys = 0;

              CREATE TABLE Contacts_temp AS SELECT *
                                            FROM Contacts;
              
              DROP TABLE Contacts;
              
              CREATE TABLE Contacts (
                  Id         INTEGER NOT NULL
                                     CONSTRAINT PK_Contacts PRIMARY KEY AUTOINCREMENT,
                  Address    TEXT,
                  City       TEXT,
                  Email      TEXT,
                  Name       TEXT,
                  Phone      TEXT,
                  PostalCode TEXT,
                  Subregion  TEXT
              );
              
              INSERT INTO Contacts 
              (
                  Id,
                  Address,
                  City,
                  Email,
                  Name,
                  Phone,
                  PostalCode,
                  Subregion
              )
              SELECT Id,
                     Address,
                     City,
                     Email,
                     Name,
                     Phone,
                     PostalCode,
                     State
              FROM Contacts_temp;
              
              DROP TABLE Contacts_temp;
              
              PRAGMA foreign_keys = 1;");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
    }
}

You will notice that I didn’t bother doing the Down function, but the same idea would apply when trying to undo a migration. SQLiteStudio or similar tools can be used to generate the SQL above if SQL isn’t something you want to deal with.

Fix other references to the renamed field

This isn’t really the topic of this post, but I wanted to throw in a reminder that after a rename like this there are places that will need to be updated that the tooling may not have picked up. For example, make sure all your views are using the new column as well as any bind statements in your controllers.

Wrapping up

The first time I hit the need to rename a column and it resulted in an exception it was extremely frustrating. Over time as I learned what the tooling around SQLite provides it has become less of an issue. I look forward to seeing what the Entity Framework team does in the future around this issue. The finished code can be found here.

Entity Framework Core with SQLite Scaffolding

This is the third in what is turning into a series of post about using SQLite with Entity Framework Core. This post is going to cover adding a migration, scaffolding a controller and related views, and a few things that are harder to do using SQLite. The following are the first two post.

Entity Framework Core with SQLite
Entity Framework Core Errors Using Add-Migration

Adding Model, DbContext, Controller, and Views

If you have any experience with Entity Framework Core or have read any of my past entries on the subject this section is going to repeat some of the same information, but I am including it so someone who is looking for a full example will have it.

Model

In the Models folder add a Contact class similar the following.

public class Contact
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string PostalCode { get; set; }
    public string Phone { get; set; }
    public string Email { get; set; }
}
DbContext

In the Data folder add a ContactsDbContext that inherits from DbContext. The following is an example that auto applies migrations to a database, if you don’t need that functionality it can be dropped out.

public sealed class ContactsDbContext : DbContext
{
    private static bool _created;

    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext(DbContextOptions<ContactsDbContext> options)
        : base(options)
    {
        if (_created) return;
        Database.Migrate();
        _created = true;
    }
}

Now that the application has a model and a related DbContext the following can be used to add a migration that will create a Contacts in the SQLite database. Run from the Package Manager console.

Add-Migration AddContacts -Context ContactsDbContext

Add-Migration is a Powershell command to add a migration (surprise!), AddContacts is the name of the migration and -Context ContactsDbContext is an argument that lets the command know which DbConext to use. The Context is only needed if your application has more than one DbContext.

Controller and Views

With the above complete Visual Studio provides some tooling that makes it very fast to create a controller with views for listing, adding, editing, and deleting items. To begin right-click on the Controllers folder and select Add > New Scaffolded Item.

Select the MVC Controller with views, using Entity Framework option and click Add.

On the next dialog use the drop downs to select a model class and a data context class. Then verify the controller name and click add.

When the process completes the following items will have been added to your project.

Controllers
 - ContactsController.cs
Views
 - Contacts
   - Create.cshtml
   - Delete.cshtml
   - Details.cshtml
   - Edit.cshtml
   - Index.cshtml
Add to nav bar

To add a link to the new section of the app to the nav bar open the _Layout.cshtml in the Views/Shared/ directory. The following is the section of the file that needs to be changed to add an item to the nav bar.

<ul class="nav navbar-nav">
    <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>
    <li><a asp-area="" asp-controller="Contacts" asp-action="Index">Contacts</a></li>
    <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>
    <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>
</ul>

Specifically, the following line was added to provide access to the contact list page.

<li><a asp-area="" asp-controller="Contacts" asp-action="Index">Contacts</a></li>

Wrapping up

With the above, the application will be runnable. The code for this post can be found here. The next post in this series will cover the limitations of migrations when using SQLite with Entity Framework Core.

 

Entity Framework Core Errors Using Add-Migration

I started off trying to expand my sample from last week’s post and hit some issues when trying to add a migration for a new DbContext.

The Setup

I added the following DbContext that only has one DbSet and auto applies migrations in the constructor.

using EfSqlite.Models;
using Microsoft.EntityFrameworkCore;

public sealed class ContactsDbContext : DbContext
{
    private static bool _created;

    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext(DbContextOptions<ContactsDbContext> options)
        : base(options)
    {
        if (_created) return;
        Database.Migrate();
        _created = true;
    }
}

The command

Using Visual Studio’s Package Manager Console I ran the following command.

Add-Migration AddContacts -Context ContactsDbContext
Error 1 – No parameterless constructor

The above command resulted in the following error.

No parameterless constructor was found on ‘ContactsDbContext’. Either add a parameterless constructor to ‘ContactsDbContext’ or add an implementation of ‘IDbContextFactory<ContactsDbContext>’ in the same assembly as ‘ContactsDbContext’.

I read the first sentence and added a parameterless constructor to ContactsDbContext. I did think it was strange that a parameterless constructor wasn’t required the other contexts I had written in the past, but the error said to add a parameterless constructor so that is what I did.

Error 2 – System.InvalidOperationException: No database provider has been configured for this DbContext

Now having a parameterless constructor I ran the Add-Migration command again and was greeted with the following error.

System.InvalidOperationException: No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider. If AddDbContext is used, then also ensure that your DbContext type accepts a DbContextOptions<TContext> object in its constructor and passes it to the base constructor for DbContext.

The second error forced me to step back and think more about what the problem was as it didn’t have an action I could take as the first sentence, which is, of course, my fault for not fully digesting what the error was saying.

The fix

The bit I was missing was the fact that I hadn’t added the following to the ConfigureServices function of the project’s Startup class.

services.AddDbContext<ContactsDbContext>(options =>
    options.UseSqlite(Configuration.GetConnectionString("Sqlite")));

With the above added I removed the parameterless constructor from ContactsDbContext and was able to successfully run the add migration command again.

Wrapping up

The moral of the story is to actually read the full error message before running off and trying to fix the problem. The second error message saying “using AddDbContext on the application service provider” is what triggered me to head in the right direction.

This was also a good reminder that tools like the ones used by Add-Migration can/do compile the project they are being used on in order to have enough context to perform their tasks.

Migration from ASP.NET Core 1.0.x to 1.1

UPDATE: For a guide dealing with the conversion to csproj/Visual Studio 2017 check out this post.

On November 16th .NET Core 1.1 was released including ASP.NET Core 1.1 and Entity Framework 1.1. Each of the links contain the details of what was including in the 1.1 release. Unlike some of the previous migrations this is pretty simple.

I will be using my normal ASP.NET Basics solution for this upgrade. The examples will be out of the Contacts project. This post is coming out of order so the repo that goes with this post will contain some items not covered in posts yet. The starting point of the repo can be found here.

Installation

Make sure you already have Visual Studio 2015 Update 3 installed with .NET Core 1.0.1 tools Preview 2 installed. If not use the previous links to install the needed versions. Next head over to the download page for .NET Core and under All downloads and select Current and SDK and select the download for your OS.

downloaddotnet

Another option is to install Visual Studio 2017 RC which can be found here.

Project.json

Project.json is the file that contains all the versions of assembles used by the application. A couple of items need to edited by hand and the rest can be updated using NuGet UI or you can change them all by hand if you like.

First the by hand items. The platform version needs to be updated to 1.1.

Before:
"Microsoft.NETCore.App": {
      "version": "1.0.0",
      "type": "platform"
    }

After:
"Microsoft.NETCore.App": {
      "version": "1.1.0",
      "type": "platform"
    }

The second by hand item is the net core app version in the frameworks section.

Before:
"frameworks": {
  "netcoreapp1.0": {
    "imports": [
      "dotnet5.6",
      "portable-net45+win8"
    ]
  }

After:
"frameworks": {
  "netcoreapp1.1": {
    "imports": [
      "dotnet5.6",
      "portable-net45+win8"
    ]
  }

Here is the resulting dependencies and tools sections.

"dependencies": {
  "Microsoft.NETCore.App": {
    "version": "1.1.0",
    "type": "platform"
  },
  "Microsoft.AspNetCore.Authentication.Cookies": "1.1.0",
  "Microsoft.AspNetCore.Diagnostics": "1.1.0",
  "Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore": "1.1.0",
  "Microsoft.AspNetCore.Identity.EntityFrameworkCore": "1.1.0",
  "Microsoft.AspNetCore.Mvc": "1.1.0",
  "Microsoft.AspNetCore.Razor.Tools": {
    "version": "1.1.0-preview4-final",
    "type": "build"
  },
  "Microsoft.AspNetCore.Server.IISIntegration": "1.1.0",
  "Microsoft.AspNetCore.Server.Kestrel": "1.1.0",
  "Microsoft.AspNetCore.StaticFiles": "1.1.0",
  "Microsoft.EntityFrameworkCore.SqlServer": "1.1.0",
  "Microsoft.EntityFrameworkCore.SqlServer.Design": {
    "version": "1.1.0",
    "type": "build"
  },
  "Microsoft.EntityFrameworkCore.Tools": {
    "version": "1.0.0-preview3-final",
    "type": "build"
  },
  "Microsoft.Extensions.Configuration.EnvironmentVariables": "1.1.0",
  "Microsoft.Extensions.Configuration.Json": "1.1.0",
  "Microsoft.Extensions.Configuration.UserSecrets": "1.1.0",
  "Microsoft.Extensions.Logging": "1.1.0",
  "Microsoft.Extensions.Logging.Console": "1.1.0",
  "Microsoft.Extensions.Logging.Debug": "1.1.0",
  "Microsoft.Extensions.Options.ConfigurationExtensions": "1.1.0",
  "Microsoft.VisualStudio.Web.BrowserLink.Loader": "14.1.0",
  "Microsoft.VisualStudio.Web.CodeGeneration.Tools": {
    "version": "1.1.0-preview4-final",
    "type": "build"
  },
  "Microsoft.VisualStudio.Web.CodeGenerators.Mvc": {
    "version": "1.1.0-preview4-final",
    "type": "build"
  },
  "BundlerMinifier.Core": "2.2.301"
},

"tools": {
  "BundlerMinifier.Core": "2.2.301",
  "Microsoft.AspNetCore.Razor.Tools": "1.1.0-preview4-final",
  "Microsoft.AspNetCore.Server.IISIntegration.Tools": "1.1.0-preview4-final",
  "Microsoft.EntityFrameworkCore.Tools": "1.1.0-preview4-final",
  "Microsoft.Extensions.SecretManager.Tools": "1.1.0-preview4-final",
  "Microsoft.VisualStudio.Web.CodeGeneration.Tools": {
    "version": "1.1.0-preview4-final",
    "imports": [
      "portable-net45+win8"
    ]
  }
}

Make note that using the NuGet UI will update the dependencies but not the tools section. For some reason the tools section doesn’t seem to have intellisense so I ended up searching the NuGet site to find the new versions. If you do end up changing the tooling version I recommend doing a dotnet restore in the project directory from the command prompt to ensure the proper versions get downloaded.

Wrapping up

As I said this was a really pain less migration. Make sure you check out the release pages ( .NET Core 1.1ASP.NET Core 1.1 and Entity Framework 1.1) for the details on what has changed. For example ASP.NET Core has gotten significant performance increases with this release as well as URL Rewriting Middleware and Response Caching Middleware.

It has been less than six months since the initial release of ASP.NET Core until the 1.1 release which a huge increase in the pace of releases compared regular ASP.NET. From what I have see this is a pace the team will continue. Check out the roadmap for a preview of things coming in 1.2.

The code in its final state can be found here.

ASP.NET Core Basics: MVC Controller with Entity Framework Core

Last week’s post covered the installation and creation of a new project in ASP.NET Core. This week I will be expanding that existing project to create a basic contact list which will use Entity Framework Core and its scaffolding capabilities to generate the needed files from a model class. This code from last week is being used as a starting point for this post.

Add a Model

To start off I need a class to represent the information for a single contact. In the default setup the proper place to store this type of class is in the Models folder. To add a new class from the Solution Explorer window right click on the Models folder and select Add > Class menu option.

AddClassMenu

This menu choice will bring up the Add New Item dialog defaulted to adding a new class. Enter the name as Contact.cs and click Add.

AddNewItemDialogContact

The following is my resulting Contact after adding the properties of a contact that this application is going to use.

namespace Contacts.Models
{
    public class Contact
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string PostalCode { get; set; }
        public string Phone { get; set; }
        public string Email { get; set; }
    }
}

Scaffolding

Now that the application knows what a Contact looks like we can take advantage of a feature of Entity Framework Core called scaffolding. Scaffolding can be sued to create a number of different setups based on a model, but in this case it I am using it to generate a MVC Controller with views, using Entity Framework which will result in a controller, DbContext and associated MVC razor views being created.

In the Solution Explorer window right click on the Controllers folder and select Add > New Scaffolded Item.

AddNewScaffoldedItemMenu

On the Add Scaffold dialog select MVC Controller with views, using Entity Framework. This is the only option that will create views. Now click the Add button.

AddScaffoldDialog

The Add Controller dialog will be shown next. First select the model that should be the base of the scaffolding operation which is the Contact class for this project. For the data context class option I used the plus (+) button to add a new one since this project doesn’t have an existing data context that I want to use.

AddControllerDialog

After clicking add and letting the process finish the Controllers folder will now contain a ContactsController and in the Models folder there will be ContactsContext. I always move the newly created context out of the Models folder and into the Data folder to match the location used for the ApplicationDbContext.

Entity Framework Migration

Now that the application has a contact model and DbContext it is time to create an entity framework migration which is the mechanism entity framework uses to create and/or migrate a database to match the changes made in the models of an application. Migrations can be added within Visual Studio via the Package Manager Console or from a command prompt using the .NET CLI. I am going to walk through both, but keep in mind you only need to use one.

Package Manager Console

To open the Package Manager Console use the View > Other Windows > Package Manager Console menu. Which show a window that looks like the following.

PackageManagerConsole

To add a migration use run the Add-Migration Init-Contacts -Context ContactsContext command. The Package Manager Console is using powershell so Add-Migration is a command that take a parameter that is the name of the migration which is Init-Contacts in this case. -Context ContactsContext is required in this application because it contains more than one DbContext and the command has to know which DbContext it is working with.

.NET CLI

Open a command prompt and navigate to your project’s directory and then run the dotnet ef migrations add Init-Contacts –context ContactsContext command. This is does exactly the same thing as the Package Manager Console above with just slightly different syntax.

Regardless of which version is used you will see a new Migrations folder added to the root of the project that contains a snapshot of the models referenced by the relevant DbContext as well as a migration file named by using a time stamp plus the migration name.

Automatic Database Migration

In the early stages of a project (or always if it meets your need) it is nice to automatically apply migrations instead of having to do so manually using the package manager console or the .NET CLI. One way to accomplish this is to add a static flag to the constructor of the DbContext in question and if that flag is false then use Database.Migrate() to apply any migrations that the database is missing. Here is an example using my ContatsContext class with the flag named _created.

public sealed class ContactsContext : DbContext
{
    private static bool _created;
    public DbSet<Contact> Contact { get; set; }
    public ContactsContext(DbContextOptions<ContactsContext> options)
        : base(options)
    {
        if (_created) return;
        Database.Migrate();
        _created = true;
    }
}

Add Navigation

At this point you could type in a URL that would take you to the index action of the new ContactsController, but it would be much more useful to add a link to the sites navigation bar to provide an easy way to access the new functionality. The code for the navigation bar can be found in _Layout.cshtml which is located in the Views > Shared folder.

The navigation bar is defined using an unordered list with a class of “nav navbar-nav”. Add a new link to the unorderd list labeled Contact List. This new link needs to point to the index action of ContactsController. ASP.NET Core provides some tag helpers to assist in building links to controller actions. Using the asp-controller and specify “Contacts” as the controller which will resolve to the ContactsController and then use asp-action set to “Index” to point to the index action.

<ul class="nav navbar-nav">
    <li><a asp-area="" asp-controller="Home" asp-action="Index">Home</a></li>
    <li><a asp-area="" asp-controller="Contacts" asp-action="Index">Contact List</a></li>
    <li><a asp-area="" asp-controller="Home" asp-action="About">About</a></li>
    <li><a asp-area="" asp-controller="Home" asp-action="Contact">Contact</a></li>
</ul>

Wrapping Up

Now when the application is run there will be a link for Contact List at the top that will trigger actions in the new controller and backing database table.

Next week I play to take this same project and add a web API end point to it and show how that end point can be verified using Postman.

The code that goes with the post can be found here.

Starting Over with Entity Framework 7

Last week as part of a migration from ASP.NET 5 beta 4 to beta 5 I deleted all the entity framework migrations for a project. This post is going to walk through the process deleting the existing database, creating new migrations and applying the new migrations.

The first step is to delete the existing database associated with the project. To do this from within Visual Studio 2015 open the SQL Server Object Explorer. Click on the Add SQL Server button (second button).

SqlServerObjectExplorer

The add button launches the Connect to Server dialog. If using the default setup with SQL Server Express the settings below should work. After all the relevant information has been entered click connect.

SqlServerObjectExplorerConnectToServer

Right click on the database for the application and click delete.

SqlServerObjectExplorerDeleteDatabase

Another option would be to change the database name in the connection string which would also trigger entity framework to create a new database.

The next step is to add migrations for each DbContext in your project. The following two commands, run from the command prompt, will add migrations for the two contexts associated with my application.

dnx . ef migration add ApplicationInit --context ApplicationDbContext
dnx . ef migration add ContactsInit --context ContactsDbContext

The following is a repeat, but it is important part of the setup since in it is how this application applies migrations. The constructors of the DbContext classes apply migrations as demonstrated in the following code.

public class ContactsDbContext : DbContext
{
    private static bool _created;
    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext()
    {
        if (!_created)
        {
            Database.AsRelational().ApplyMigrations();
            _created = true;
        }
    }
}

After the first run of the application a new database will be created and you will be ready to go with a fresh database.

ASP.NET 5 Web Site to Azure

At the point the basics of my ASP.NET 4 contacts application have been moved to the ASP.NET 5. This is never going to be a production application, but I want it run it on a remote server just to prove it works. I decided to publish to Microsoft Azure which is Microsoft’s cloud offering. The process was a lot more challenging that I had expected, but as with all the rough spots I have hit with ASP.NET 5 I am sure the path will be made smooth for the final release.

Publishing to Azure from Visual Studio 2015 RC

To get started right click on the project to be published and select the Publish.

ProjectRightClickPublishMenu

This will load the Publish Web dialog. On the Profile tab select the Microsoft Azure Web Apps option.
PublishDialogProfile

This will show the Select Existing Web App dialog. Click the New button to add a new Web App.
AzureWebAppDialog

The new button show the Create Web App on Microsoft Azure. This dialog has a bit more to it. Web App name sets the url of the app as well as the app name with Azure. For region I just chose the closest data center. My application uses a database and I don’t have an existing database server in Azure so I chose Create new server for Database server selection. Database username and password are self explanatory. With all the options filled in click Create.
AzureCreateWebApp

After the creation process, which creates all the infrastructure need for the app in Azure, is done Visual Studio returns to the Publish Web dialog. The following is the Connection tab. The fields are editable, but are auto filled from the creation process. In case changes are need use the provided Validate Connection button to verify Visual Studio is still able to communicate with Azure.
PublishDialogConnection

The Settings tab allows selection of Release or Debug configurations as well as the target DNX version.
PublishDialogSettings

The last tab on the Publish Web dialog is Preview. It is not overly useful on a first publish since all the file for the project needed to be push, but on subsequent publishes it would be useful to verify nothing unexpected is being pushed.PublishDialogPreview

Pushing the Publish button on the Publish Web dialog push the files to Azure and opens a browser with the newly published web app.

Issues

After a few seconds I was greeted with a HTTP 500 Internal Server Error instead of my web app. I spent a lot of time on the Azure Portal trying to find my issue. I created a new project and published it without issue which means it is a problem with my app and not the publish process. I spent a lot of time digging and Googling my issues, but thankfully ended up with answers.

App Configuration

My first issues were the result of not setting up the configuration options for my user secrets. As I posted last week this was the main worry I had with user secrets. You would think with my concerns that would be the first thing I checked but it actually took me awhile to get around to checking my user secrets. Just to be clear I am not against user secrets I think they are an awesome feature I am just not used to dealing with this side of configuration. The steps to make the proper configuration for user secrets follow.

From the Azure Portal click Browse everything to get a list of all resources.

AzurePortal

From All resources click on the Web App that needs configuration. If you are paying close attention you will notice the name of my web app is different than the publication settings above. This is the result of one of my tries to get the site running before discovering the problem was configuration.
AzurePortalAllResources

Selecting a web app cause the details page to load. On the details page select Settings.
AzurePortalWebAppDetail

From the settings details page click Application Settings which will load the Web app settings page.AzurePortalWebAppSettings

In Web app settings scroll down to the App settings section. In my case I was missing the Authentication:Google:ClientId and Authentication:Google:ClientSecret settings used for OAuth with Google. Also make note of the Connection strings section as  this is the section where the connection string to the database needs to be entered.
AzurePortalWebAppSettingsAuthAndConnection

With all the above changes I was finally able to get the site to load.

Database/Entity Framework

As soon as I click on the contact list section of my app I got another HTTP 500 Internal Server Error. This happens to be the first time the app hits the database. The issue this time is that the DefaultConnection string that needs set so the app can connect to the database.

First step to fix this issue is to go back to the All resources on the Azure Portal and select the database that goes with the web app, aspnetcontacts_db in this example.

AzurePortalAllResourcesDb

This will load the SQL Database detail. Under Connection string is a link for Show database connection strings. Clicking this will load a page with a list of connection strings for this database. Copy the appropriate string and replace the dummy password with the real one. Take that connection string with the password enter and put it in your web app’s DefaultConnection. With the DefaultConnection set the 500 error went away.
AzurePortalDbDetailThe next time I ran I got a stack trace with a message that migrations needed to be run. In order to get migrations to run I made a few changes to my project.

The first change was in the ConfigureServices function of Startup.  I added the setup for the ContactsDbContext using the DefaultConnection string. In the ContactsDbContext I removed the OnConfiguring function which is where the connection string for the ContactsDbContext had been set before.

// Add EF services to the services container.
services.AddEntityFramework()
   .AddSqlServer()
   .AddDbContext<ApplicationDbContext>(options =>
       options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]))
   .AddDbContext<ContactsDbContext>(options =>
       options.UseSqlServer(Configuration["Data:DefaultConnection:ConnectionString"]));

In order to actually run migrations for the ContactsDbContext I added a constructor with a call to Database.AsRelational().ApplyMigrations() which will ensure anytime the DbContext is constructed that the latest migrations will be applied. The following is the full ContactsDbContext class after the changes.

public class ContactsDbContext : DbContext
{
    private static bool _created;
    public DbSet<Contact> Contacts { get; set; }

    public ContactsDbContext()
    {
        if (!_created)
        {
            Database.AsRelational().ApplyMigrations();
            _created = true;
        }
    }
}

The last project change was to the ContactsController to allow ASP.NET to inject the ContactsDbContext instead of creating the context with in the controller.

private readonly ContactsDbContext _db;

public ContactsController(ContactsDbContext dbContext)
{
    _db = dbContext;
}

The change made above to the ConfigureServices function in the Startup class is how ASP.NET knows what to inject into the ContactsController. Build in dependence injection is one of the new features of ASP.NET 5.

After all the above changes I published to Azure and tried to access the contact list again. This resulted in a different error and a stack trace. The issue this round turned out to be that the default SQL Server created by Azure does not support the way that Entity Framework 7 is auto incrementing the ID column on contacts table. Thankfully there is an updated version of SQL available and the default server just need to be upgraded.

Back on the detail page for the SQL database there is a Server version listed as V2. Click the V2.

AzurePortalDbDetail

This will load the Latest SQL database update page. Click Upgrade This Server.

AzurePortalDbUpgrade

Currently the SQL Server created by the Visual Studio 2015 RC is in the Web tier which does not support the latest version of SQL which resulted in the warning below.
AzurePortalDbUpgradePriceWarningClick on your database name to load the Recommended pricing tier page. Since this is a new database Azure does not have enough information to recommend a tier and defaults to S0. At the very bottom of the page there is a big blue S0 link. Click it to change pricing tiers.

AzurePortalDbUpgradePriceRecommended

The Choose your pricing tier page will load. The current setting for the server is Web which shows as a retired. I chose the Basic tier but anything that is not marked as retired should support the newer version of SQL Server. After clicking on the option you want click the Select button at the bottom of the page.

AzurePortalDbUpgradePriceSelect

Now that the new pricing tier has been selected go back to the server details and click the V2 link under Server version. This round you will get a big warning. Under the warning enter the name of your server and click OK to perform the upgrade.

AzurePortalDbUpgradeTypeServerName

The upgrade process took less than 20 minutes for me. After the processes was complete the app worked as expected.

Duplicate Records from Migration Seed

I am writing a contact management application as a vehicle for my ASP.NET MVC learning using Entity Framework 6. To pre-load some test data I utilize the Seed method found in Migrations\Configuration.cs which gets added when migrations are enabled for a project.

When testing the contact creation process I noticed that I forgot to add a property for contact’s state. Easy enough to fix. I opened up the Contact class, added the missing state property and updated all the related views. Then I ran the app to test my change and was greeted with this error:ContextChanged

Of course the model backing my DbContext has changed since the database was created, I just added a new property to the model. Being new to entity framework workflow I often see this error when I forget to update the database before running the app to try out a change. It is easy to fix by using the package manager console to Add-Migration and then Update-Database.

After updating the database I ran the app to verify my changes. What I saw when my contact list load was that all my seed data had been duplicated. The jest of my seed function was something like this:

context.Contacts.AddOrUpdate(c => c.Id,
    new Contact
    {
        Id = 0,
        Name = "Eric",
        State = "TN"
    },
    new Contact
    {
        Id = 1,
        Name = "Tommy",
        State = "ND"
    });

To track down the duplication issue the first thing I did was to view the data in the database to make sure the table a primary key set. The Id field was an integer identity column and was the primary key. Next I viewed all the records in the table which looked like this:

Id Name State
0 Eric TN
1 Tommy ND
2 Eric TN
3 Tommy ND

The Id lookup I defined as the first parameter to AddOrUpdate seemed to be my issue. From my tests it seems that AddOrUpdate will always perform an add when trying to match on an identity type column. By changing from c => c.Id to c => c.Name no data is duplicated.