ASP.NET Core RC2 with Entity Framework code first connected to PostgreSQL database deployed on a Ubuntu 14.04 Linux server

[EDIT: this was my first blog post, and it is pretty long, it is also not formatted the best.  I worked to fix these issues by splitting this post into two, Post 1: Setting up Ubuntu 14.04 for ASP.NET Core RC2 with PostgreSQL and Post 2: Visual Studio development of ASP.NET Core RC2 Web App with PostgreSQL and Entity Framework code first deployed to Ubuntu  I would recommend reading those two posts, instead of looking at this one.]

This blog entry will go through setting up a completely newly installed Ubuntu 14.04 server, and creating an example ASP.NET core web application in visual studio on windows, and deploying to the Linux server.

Okay here goes something new for me... Blogging about software development.

Microsoft has released ASP.NET Core RC2 a week ago, which is an open-source cross platform version of the c# web framework.  And what's even cooler, is that there is an open source database connector for entity framework and postgresql!

My setup is a macbook pro running two virtual machines.  I run windows with visual studio in one, and ubuntu on the other one.

Download and install Virtual Box: https://www.virtualbox.org/wiki/Downloads

Download Ubuntu 14 Server 64bit:  http://releases.ubuntu.com/14.04/ubuntu-14.04.4-server-amd64.iso

Install Ubuntu 14.04 using Virtual Box.

Change network settings inside virtual box for ubuntu virtual machine use the 'Attached To' drop down to select the 'Bridged Adapter' option.  This will allow us to easily get an ip address for our virtual machine and access it via ssh, ftp, and http.

Once ubuntu is setup, we begin by installing ssh.  Ssh will allow connecting to the ubuntu server via a more user friendly remote terminal.  The following commands will install the ssh server, and show the IP address information.

sudo apt-get update
sudo apt-get install openssh-server 
ifconfig

Take note of IP address, it will be needed. Minimize the ubuntu virtual machine, open terminal inside of mac, and login to virtual machine via ssh using the following command:

ssh *ubuntu-setup-username*@*ubuntu-ipaddress*

There will then be a prompt for the ubuntu setup username's password, enter it and login.

Now to install postgresql9.5(the latest version as of this blog... skipping this step we would install 9.3) we need to add the repository location, get the cpg key and update our repository, and finally install postgresql 9.5, using the following commands:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -

sudo apt-get update

sudo apt-get install postgresql postgresql-contrib

Once this completes postgresql will be installed.  Now we need a database user for our application to use to access the postgresql database.  To create a new user, execute the following commands:

sudo su postgres

(become the 'postgres' user)

psql

(start the psql shell)

Inside the psql shell postgresql statments can be executed. But for now, let's log out and create a user. 

\q

(quit the psql shell)

createuser --interactive

This will create a prompt for a role (user) name and if they should be a super user. I always select 'y' for yes they should be a super user.  Then a password will need to be created for the user.  This is done by using the psql shell.  Type the following commands:

psql

ALTER USER *username* WITH PASSWORD '*password*';

\q

su *ubuntu-setup-user*

(switch back to the ubuntu setup user)

There are two configuration files we need to modify in order to open postgresql to outside connections.  The first one can be opened and modifed using the following command:

sudo nano /etc/postgresql/9.5/main/pg_hba.conf

This file controls the ability to connect to the postgres server from various ip addresses.  For our purposes, I just open the database to any ip address.  This is again not something you would want in a real production server, this is just for getting started.  We will add two lines to open to all ipv4 and ipv6 ip addresses.  

host     all     all     0.0.0.0/0     md5

host     all     all     ::0/0         md5

Once that file is saved there is one more configuration file for enabling external connections.  Type the following command to access it:

sudo nano /etc/postgresql/9.5/main/postgresql.conf

This file has a line:

#localhost='localhost' 

this needs to be changed to:

localhost = '*'

Make sure to remove the comment mark '#' at the beginning of the line.  (that darn commenting pound sign got me spinning my tires for atleast an hour).  

Now just restart the postgresql server to use the new configuration.  Type the following command to do restart it:

sudo service postgresql restart

Now you can connect to the database from a remote host. Hurray! (You can test this with pg-admin on the windows development machine, which is a handly visual tool for windows to access the postgresql database.  It's basically the equivalent of ms-sql server managment tools for postgresql).  If we can connect using this application on the windows development machine, then we know visual studio along with entity framework will have connectivity.  Download pgAdmin here: https://www.pgadmin.org/download/windows.php . Let's finish setting up ubuntu)

To install and configure ftp we need to do a few steps.  First we need to install the service vsftpd.  Use the following command to do so:

sudo apt-get install vsftpd

Now we need to modify the configuration file

sudo nano /etc/vsftpd.conf 

we must remove the '#' sign in the configuration file so we can upload the files.  Uncomment the line: 

write_enable=YES

Save the configuration, and restart the vsftpd server:

sudo service vsftpd restart

Now the ftp server is setup to allow files to be copied to the home directory of the ubuntu user.  

We need to install and configure nginx 

sudo apt-get install nginx

sudo rm /etc/nginx/sites-enabled/default

sudo nano /etc/nginx/sites-enabled/default

type the following into empty nginx configuration file:

 server {

     listen 80;

     location / {

         proxy_pass http://localhost:5000;

         proxy_http_version 1.1;

         proxy_set_header Upgrade $http_upgrade;

         proxy_set_header Connection keep-alive;

         proxy_set_header Host $host;

         proxy_cache_bypass $http_upgrade;

     }

 }

Save the configuration file, and then restart the nginx server:

sudo service nginx restart

Finally for setting up ubuntu we install dotnet core RC2 we need to execute the following commands: 

sudo sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/dotnet/ trusty main" > /etc/apt/sources.list.d/dotnetdev.list'

sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893

sudo apt-get update

sudo apt-get install dotnet-dev-1.0.0-preview1-002702

Once the linux virtual machine is all setup, it's time to get the windows virtual machine's development environment setup.  Once windows is installed, you'll need to install visual studio, and the web tools for RC2.

Install Visual Studio 2015 Community (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 

For the next part of this entry I go over the steps inside of visual studio to setup a basic CRUD app.  The source code that should result from following steps proceeding can be found at https://github.com/TotalTechware/unape

Open up a new project in visual studio name it 'unape', which stands for ubuntu, nginx, asp.net core, postgresql, entity framework.

Select .NetCore -> ASP.NET Core Web Application 

Select the web application template, and leave the authentication method as Indvidual User Accounts

At this point, the packages have to restore which will also add folders and stuff that aren't immediately available until all the packages have restored... so basically go check your email or get a cup of coffee cause this will take about 30-60 seconds... i'm guessing this is also dependent on your internet speed, so results may vary.  The restoration process is indicated by an icon next to the references in the solution explorer.

In order to connect entity framework to postgresql we use a package called npgsql.  It's an open source project.  Inside Visual Studio open up the Package manager console and run the following command:

Install-Package Npgsql.EntityFrameworkCore.PostgreSQL -Pre

This will cause visual studio to download and restore the references for npgsql for RC2, which again the progress of this downloading process will be indicated by the icon next to the references in the solution explorer.  

Change the default connection information inside the appsettings.json file.  Change the line from:

  

"DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=aspnet-unape-1a240cd2-788a-41a2-a6b9-ef2e02774e98;Trusted_Connection=True;MultipleActiveResultSets=true"

to:

 "DefaultConnection": "User ID=*pgsql-user*;Password=*pgsql-user-pass*;Server=192.168.1.131;Port=5432;Database=unapeDB;"

Use the name and password of the postgresql user we created previously. 

Now we will create a plain old object in the models folder.  For this example purpose, this simple Item object will work:

namespace unape.Models

{

    public class Item

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public float Price { get; set; }

    }

}

Inside the Data folder we will create a new database context.  The only difference between the traditional method of EF code first in the Database context file is the addition of the additional constructor which takes in options and passes them down to the base constructor.  Here is the way my file looked:

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; }

    }

}

Alright, now we must modify the configuration to use postgres instead of the default ms-sql.  This is done inside the Status.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 authentication database context to use the postgres database we have setup, we also need to add a line to this method to allow our unapeDbContext to be configured.  That is done pretty intuitively like this:

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

Build the solution to make sure we don't have any typos or anything unexpected.  Now let's create a migration for our unapeDbContext, and after we have created a migration, we will update the database with both the unapeDbContext as well as the ApplicationDbContext.  Open Cmd.Exe on the windows machine, and navigate to the directory of the unape project's src folder.  Then Type the following commands into the cmd.exe command prompt:

dotnet ef migrations add firstMig -c unapeDbContext

(this will create a folder named migrations inside the project along with two migration files)

dotnet ef database update -c unapeDbContext

(this will cause the migrations to be exectued on the postgres database, which will cause the database unapeDb to be created and have the Item table with the columns we gave to the Item object)

dotnet ef database update -c ApplicationDbContext

(this will cause the migrations which are for user name, password, and roles authentication to execute and corresponding tables to be created in the postgres database)

Alrighty, at this point we should have the database ready to roll, now we just need to add the controller and views for the CRUD operations on the Item object.  This can be done easily using scaffolding.  Because we picked the individual account as our authentication the appropriate package has been added to allow us to do "right-click Add new controller" inside visual studio.  So at this point it is the same scaffolding stuff as traditional entity framework.

Right click inside of the controllers folder and select to add a new controller. Select MVC Controller with views using entity framework.  Using the drop downs, or typing select the Model class:  Item (unape.Models), and Data context class: unapeDbContext (unape.Data).  Leave all the defaults selected, and the controller name should have auto populated with ItemsController.  Click Add.  This will generate 5 views and a controller with the corresponding action methods to access and populate those views.  

In order to make it easier to access the 'Items' screens, let's add a link to our Items index page inside the _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, run the app inside of visual studio.  The browser will open with our app, with the ability to navigate to the Items controller from the navbar link, and also create, view, update, and delete all the items.  Everything should work just fine.  At this point, we are ready to deploy our basic example app into Linux.  To do this we will copy over our the folders that are created during the publish process.  

Stop the app running in visual studio.  Right click the project name in the solution explorer, and select publish.  Select the file system as the publish target, and give it some profile name.  The default target location is inside the bin\release\publishoutput\ folder.  this is fine.... click publish.

Now we will ftp into our linux box via windows explorer, inside the windows explorer toolbar type in ftp://'ipaddress of server'  for me that 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 with a drag and drop.

Now once the files have copied over to the ubuntu server.  We will hop back onto the terminal to navigate to the publish output folder that was copied over via ftp, and run the following command:

dotnet unape.dll

This will launch the app, and it will be listening on localhost:5000, we configured the nginx reverse proxy to take requests coming in from the outside on port 80 to be routed to localhost:5000, so now we can open up a web browser and type in the http://'ip-address of ubuntu server',   Bam baby!  We are now running the asp.net core with entity framework connected to postgresql database all on ubuntu linux!