Visual Studio development of ASP.NET Core RC2 Web App with PostgreSQL and Entity Framework code first deployed to Ubuntu

Getting started with Visual Studio development for ASP.NET Core RC2 connected to a PostgreSQL database using Entity Framework code first all deployed to a Ubuntu Linux server*

*While being developed in a Windows environment using Visual Studio 

Wow!  That's a mouthful of a heading!

Alright, so Microsoft just released ASP.NET Core RC2 a week ago.  ASP.NET Core RC2 is a cross platform web framework which allows a traditional c# web developer who is used to using tools like Visual Studio to develop applications for Windows servers can now target much cheaper Linux servers.  Hurray!  That's great.  But what about databases?  Well, Microsoft has announced they will be releasing MS-SQL Server 2016 on Linux, which is pretty incredible.  But it's still going to cost money.  PostgreSQL is a free open source SQL database which there is an open source package written (Npgsql) which provides Entity Framework support to PostgreSQL.

Basically what this means, is that you can develop with Visual Studio using the traditional ASP.NET MVC/webAPI with Entity Framework and Controller and View generation goodness you are used to, and now deploy to a free server with a free database.  Oh and did I mention, Visual Studio Community 2015 is free as well!  It's never been a better time to be a c# developer.  And in fact, any one can now use this toolset, so I expect it will grow in popularity.

Getting started with ASP.NET Core RC2 from a Windows and Visual Studio perspective

This is part 2 of a 2 part blog series for getting started with ASP.NET RC2 with Entity Framework connected to PostgreSQL deployed to an Ubuntu Linux server (In part 1 of this series the Ubuntu Linux server is created, setup and configured).  This blog entry will go over getting started on the Windows development experience, and that will include creating a basic app, and deploying to the linux server.

You will either need to run Windows 7+ in a virtual machine (which is what I do on my MacBook using Virtual Box running Windows 7) or you will need to be running it natively. 

Let's get started with download and install Visual Studio Community (It's free): https://www.visualstudio.com/en-us/products/visual-studio-community-vs.aspx

Install Microsoft ASP.NET and Web Tools Preview 1 tooling for Microsoft .NET Core 1.0.0.0 RC2: https://visualstudiogallery.msdn.microsoft.com/c94a02e9-f2e9-4bad-a952-a63a967e3935

Creating our example ASP.NET Core RC2 Application in Visual Studio

Open up visual studio, and select to create a new project.  For this example I will be naming the project unape.  I'm choosing this name as an acronym for Ubnutu nginx ASP.NET Core PostgreSQL Entity Framework.  This is what I'm calling the unape stack. Select .NetCore and ASP.NET Core Web Application.  Select the web application template, and leave the authentication method as Individual User Accounts.  This setting will populate the base application with a database context, controller methods, and views for user's to login with a password and change their password, along with role values.

As soon as you hit ok the app will be created and the packages will need to be restored.  This will add folders and stuff that aren't immediately available until all the packages have restored.   This takes about 15-45 seconds or so.  Although it may be dependent on internet connection speed.  The restoration process is indicated by an icon next to the references in the solution explorer. 

Add PostgreSQL EntityFramework data provider Npgsql

In order to connect Entity Framework to PostgreSQL a package named Npgsql must be added to the project.  It's an open source project, check it out.  Inside Visual Studio open up the Package manager console and run the following command:  

Install-Package Npgsql.EntityFrameworkCore.PostgreSQL -Pre

This command will cause Visual Studio to add the appropriate references to the project.json file and subsequently restore (download) the references for Npgsql.  The -Pre is for RC2.  The process of this restoration will again be indicated by the references icon in the solution explorer.  

Change database connection string to point to PostgreSQL

Next, the default connection information inside the appsettings.json file needs to be changed.  Inside the ConnectionStrings json object change the value for the DefaultConnection property.  Change the default connection from:

"Server=(localdb)\\mssqllocaldb;Database=aspnet-POengine-bb847c65-bea9-4d5e-9bd6-627efd36bfc7;Trusted_Connection=True;MultipleActiveResultSets=true"

Which will target a local ms-sql server database.  Replace this value with one that will target our PostgreSQL database:

"User ID=*pgsql-user*;Password=*pgsql-user-pass*;Server=*Ubuntu-IP-Address*;Port=5432;Database=unapeDbContext;"

Replace the values for *pgsql-user* and *pgsql-user-pass* with the user name and password combination that was created for the PostgreSQL database in Step 3 of the previous blog post when we were configuring the server. The value *Ubuntu-IP-Address* should also be changed to the appropriate IP address for the Ubuntu server.  This was also acquired in the previous blog post.

Create a simple example Model class named 'Item'

Now create a plan old object in the models folder, call it 'Item.cs'.  This will be our simple model for this example app.  To anyone who has done Entity Framework code first, this should look very familiar, as it is the exact same for type of model file.  Type or copy the code below into Item.cs:

namespace unape.Models

{

    public class Item

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public float Price { get; set; }

    }

}

Create a database context and add the 'Item' Model to the context

Now we need to create a database context so we can connect our Item object model to the actual database tables.   This is the same way as traditional entity framework code first, however we have to add an additional construction to the database context file that accepts options and passes them down to a base constructor.  Create a new file inside the Data folder and name it 'unapeDbContext.cs', write this code in it:

using Microsoft.EntityFrameworkCore;

using unape.Models;

namespace unape.Data

{

    public class unapeDbContext : DbContext

    {

        public unapeDbContext(DbContextOptions<unapeDbContext> options) : base(options) { }

        public DbSet<Item> Items { get; set; }

    }

}

Update method in Startup.cs to use Npgsql and add database context

Now we must modify the configuration of the application to use PostgreSQL instead of the default MS-SQL.  This is done inside the 'Startup.cs' file.  In the ConfigureServices method, change the following line:

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

to:

services.AddDbContext<ApplicationDbContext>(options => options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));

This will modify the application's individual account authentication database context to use the PostgreSQL with the connection string we modified earlier.  We need to add an additional line right underneath this one to add the database context for the 'unapeDbContext', this is done by the following line:

services.AddDbContext<unapeDbContext>(options => options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));

Use dotnet cli for migrations and updating the datatbase

At this point the application is ready for database migrations.  In previous versions of Visual Studio the Visual Studio Package manager was used to do entity framework database migrations.  In ASP.NET Core the dotnet command line interface(cli) is used.  Open 'cmd.exe' and navigate to the directory of the unape project's src folder.  (this is done by using the command 'cd' and the directory's name you want to enter... But I expect you know how to do this, if not google will help.)  Once in the unape project's src folder, type the command:

dotnet ef migrations add firstMig -c unapeDbContext

This will create a folder named migrations inside the project, along with an initial migration file and a 'firstMig' file.  These migrations will be used to update the database with the following command:

dotnet ef database update -c unapeDbContext

The migrations will be executed on the PostgreSQL database which will cause the Item table to be created with the columns corresponding to the Item.cs classes properties.  Now we need to update the database for the individual account authentication database context, type the following command:

dotnet ef database update -c ApplicationDbContext

Now the migrations for the tables associated with user names, passwords and roles will be added to the PostgreSQL database.  As you can probably tell, the '-c' flag is for declaring the database context name.

Scaffold the controller and views for the 'Item' Model

At this point we have the database ready to roll, and now we need to add a way to create, read, update, and delete Item's.  Visual Studio has had a wonderful tool for doing this rapidly for quite a while now, it's called scaffolding.  Right click inside of the 'Controllers' folder and select Add-> New Controller.  Select MVC Controller with views, using Entity Framwork and click 'Add'.   The Model class: should be entered as 'Item', it will auto populate as you begin to type, and the Data context class should be 'unapeDbContext'.  Leave all the defaults selected, and click 'Add'.  This will a generate five views and a controller with corresponding corresponding actions.  

Add link and test run the app

In order to easier access the scaffolded Item views, let's add a link inside our 'Views/Shared/_Layout.cshtml' file.  Inside the navbar unordered list, add an additional list item:

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

At this point we are ready to test run the app inside visual studio.  Press F5 to build and launch the application, this will automatically bring up a browser and navigate to the locally running site.  Once the app is launched, clicked the 'Items' link in the navbar toward the top right of the page.  To ensure we have everything completely setup correctly, create a new item.  Everything should work correctly, and the new item will show up in the index page.  At this point we are ready to bundle everything up and publish our example app onto our Ubuntu Linux server. 

Publish the app to Ubuntu using ftp

Stop the app running in visual studio.  Right click the project name in the solution explorer, and select publish.  We are planning on using the ftp server we setup on the Ubuntu server, but there isn't a publish option with ftp.  What we will do is deploy to the file system and then copy the files over to the ftp server.  Select the file system as the publish target, and give it some profile name.  The default target location is inside the bin\release\publishoutput\ folde.  This is fine. Click Publish.

Now we will ftp into our Linux box via Windows explorer, inside the windows explorer toolbar, type in ftp://'IP-address-of-Ubuntu' for me this looks like:

ftp://192.168.1.131

With another windows explorer folder navigate to the unape's project bin\release\publishoutput\ folder and copy the contents to the ftp server.

Run the published ASP.NET RC2 web application on Ubuntu

Once the files have finished coping to the Ubuntu server, hop back onto the terminal and navigate to the publish output folder which was copied over via ftp.  The files will be in the Ubunut user's home directory.  Run the following command to start the web application:

dotnet unape.dll

Once the application launches it will begin listening on localhost port 5000.  We previously configured nginx to take external requests on port 80 and route them to localhost port 5000.  Now we can open up a web browser and type in, 'http://*ip-address-of-Ubuntu*', and bam baby!  ASP.NET Core with Entity Framework connected to a PostgreSQL database all hosted on Ubuntu Linux!  How awesome is that!?