Building an ASP.NET Core 1.1 Application with PostgreSQL on Ubuntu Linux
Overview
ASP.NET Core 1.1 framework is the redefined version of tried and tested ASP.NET optimized to work cross platform and be hosted on various cloud platforms. Microsoft has finally opened up to embrace Linux rather than fight it off and the ASP.NET Core framework is a big step in that direction. In order to be able to achieve a true first class experience on the Linux Platform, Microsoft has rewritten the whole .NET Platform, releasing a subset of the BCL Framework under the .NET Standard and then the ASP.NET Core framework on top of it. ASP.NET Core was built from scratch to be platform independent and cloud optimized as a result of this it has far lesser features as compared to ASP.NET MVC 5 and is slowly catching up on the missing features implementing all the new ones in an platform independent way.
The move to a platform independent ASP.NET Core has several added advantages, dependence of ASP.NET on Windows Server, specifically IIS is gone! ASP.NET Core can be hosted on any of the supporting platforms, this a huge win for small projects where building an ASP.NET Application for clients was cost prohibitive since the clients had to pay the windows server tax. Having a hard dependence on IIS made ASP.NET to be highly uncompetitive from a cost perspective for small projects, where PHP based projects are far more popular due to lower cost of hosting. Heck even Amazon has started $5 VPS Linux VPS service! Such low priced hosting solutions have been totally out of reach for ASP.NET developers, hopefully ASP.NET Core will be popular enough for the vendors to start offering it as a selectable runtime option.
The another benefit of moving cross platform is that dependence on Visual Studio goes away. Earlier, it has been unimaginable to build a professional ASP.NET application without using Visual Studio. Even though MS has released Visual Studio Community edition it has its hidden riders associated. ASP.NET Core on the other hand has been built to run via the CLI like other popular open source platforms for e.g NodeJS and any popular text editor can be used to build production sized applications. Microsoft’s open source, light-weight Visual Studio Code Editor is also one of the good contenders for building ASP.NET Core applications.
In this blog post, we will update the default scaffold ASP.NET Core 1.1 application using Entity Framework Core to connect to PostgreSQL database using Code first approach on Ubuntu Linux. We will explore some of the new features of ASP.NET Core 1.1 and how easy it is to use entity framework to connect to open source databases like PostgreSQL. We will be using the free, open source Visual Studio Code Editor and .NET CLI to build the application to understand how to build ASP.NET Core applications on Linux or other supported platforms.
Install ASP.NET Core 1.1 Framework on Ubuntu
The first step to start developing ASP.NET Core applications on Linux is to install the .NET Core runtime. Installation instructions are beyond the scope of this blog as well as constantly evolving as MS keeps updating their platform. As of now, 1.0.1 is the latest version of the .NET Core SDK which has support for the new csproj project file and other enhancements, you can install 1.0.1 version of the .NET SDK from here. I prefer Ubuntu 16.04 as the desktop environment for development. Once the .NET SDK is installed we can move forward to developing ASP.NET Core based applications.
Installing Visual Studio Code
As mentioned above, ASP.NET Core has been freed from Visual Studio and any text editor can be used to develop ASP.NET Core based applications. In this blog I choose to use Visual Studio Code which is an open source offering from Microsoft getting quickly popular and has the best open source editor support for ASP.NET Core. Its been regularly updated and its available for Windows, Mac and Linux. You can download the latest version of Visual Studio Code from its website. The downloaded *.deb file can be clicked in the File Manager and installed with the Ubuntu Software Center. There are tons of popular extensions available for VS Code. One of the core ones that we will need is the C# extension. If you have VS Code open you can go to the Extensions icon and see the popular extensions. Click on the Install button for C# to install the extension and Reload button once the extension is installed to have it enabled. We are now all set to start building our first ASP.NET Core Application.
Figure 1: Install C# extension in Visual Studio Code
Installing PostgreSQL on Ubuntu
PostreSQL is a popular open source relational database. Installation and configuration of PostgresSQL is not in the scope for this blog. There are various help guides that can guide you through the installation and configuration steps. Personally I like the DigitalOcean blog which explains the installation and Configuration process in a fairly easy to understand manner. Once you have PostgreSQL installed you should be able to get to the psql prompt to run sql queries against your database.
Starting a New ASP.NET Core project using VS Code
Now that we have the basics requirements for our first ASP.NET Core application ready we can dive straight up into creating our first ASP.NET Core application. Fire Up Visual Studio Code editor, and turn on the Integrated Terminal window from View Menu –> Integrated Terminal or using the shortcut Ctrl+`. The integrated terminal window will also us to run the Dotnet CLI commands so that we do not need an independent terminal window. Navigate to the folder where you wish to create your new application, I usually use create an Applications folder to store all my projects. Create a new folder called AlumniConnect under the Applications folder and use the DOTNET CLI command to create a new blank ASP.NET Core project. There is an alternative way to scaffold a project using Yoman Generator, but for the purpose of this blog post I want to focus on creating our web application from scratch so that we have a better understanding about the different parts of our application. Run the following commands as shown in Listing 1 below, in the Integrated Terminal in VS Code to create a new AlumniConnect folder as well as create a new ASP.NET Core application using the dotnet cli command. It is essential to give read write permissions to the current user on the newly created folder so that we can develop, build and debug the application without seeking admin permissions all the time. If you skip this step you will find battling permission issues every time you use the editor.
The new parameter to the dotnet cli command is used to create a new .NET Core project. The latest SDK of .NET CLI has improved vastly on the new project template in efforts to get it feature rich and make available the various different templates that are available under Visual Studio.
Passing the mvc parameter tells the cli to generate an ASP.NET Core MVC project file and the –auth Individual is a parameter for the MVC template indicating that we wish to use ASP.NET Identity for user authentication. The –framework netcoreapp1.1 tells the cli that we wish to use the ASP.NET Core 1.1 framework vs. the older V1.0 framework. This command generates a basic ASP.NET MVC Core template with User Identity integration.
sudo mkdir AlumniConnect sudo chmod ugo+rwx AlumniConnect cd AlumniConnect dotnet new mvc --auth Individual --framework netcoreapp1.1
Listing 1: Dotnet CLI to generate new ASP.NET Core web project
Figure 2: Creating a new ASP.NET Core project using dotnet cli commands
As you can see from the figure 2 above, the dotnet new cli command has created a set of boiler plate files necessary to start an empty ASP.NET Core project. Before we start working with this project, we need to load it in VS Code editor. Click on the File menu –> Open Folder and select the AlumniConnect folder from the dialog to load the folder. As soon as the folder is selected, VS Code will load the folder and detect that its loading an ASP.NET Core application. Once the folder is loaded at the Integrated Terminal, type the dotnet restore command to restore all the NuGet packages for the project. Note: in case you are getting errors on dotnet restore, especially project file lock errors please ensure that you have write permissions on the folder where the project is located. You can also try sudo dotnet restore if the folder needs elevated permissions. Once the packages are restored, run the dotnet build command to build the project. On successful build, run the dotnet run command to start hosting the ASP.NET Core application for development. Open your browser and point it to http://localhost:5000 to load the scaffold ASP.NET Core application. You should be able to see the default ASP.NET Core homepage in the browser! Ya! we have our first ASP.NET Core application running on Ubuntu 16.04! Here we can see in action one of the new useful features of ASP.NET Core is that it supports folder based projects like other popular open source frameworks like NodeJS. It makes project loading much faster and any kind of text editor can be used in combination with the CLI tools to build ASP.NET Core application.
Configuring the ASP.NET Core application to use PostgreSQL
The default template generated by the dotnet new cli command creates a project configured to use the SQLLite database. Since we wish to use PostgreSQL as our database one of the first things to do is to add the Npgsql entity framework core provider to our project. To add a new nuget package to our project, run the commands shown in listing 2 in the Integrated Terminal window.
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL sudo dotnet restore sudo dotnet build
Listing 2: Adding Npgsql Entity Framework Core packages to project
The updated .NET SDK adds a new CLI command dotnet add package <package name> to add NuGet packages to our project without manually editing and project files. You need to run the dotnet restore command after adding any package to ensure that dependent packages are also downloaded and added to the project. The editor also prompts you to Restore Packages whenever it detects that new dependencies need to be downloaded. Run the dotnet build command to ensure that all required packages have been downloaded and the project is building correctly.
Once we have the required packages we need to make a few changes to the existing generate code to use PostgreSQL instead of the default SQL Lite database. Open up the appsettings.json file in the editor and change the DefaultConnection property to point to our PostgreSQL database. You will note that ASP.NET Core has removed the web.config file and now web application related settings can be stored in the appsettings.json file. Make sure you replace the Username and Password in the connect string shown in Listing 3 below with the right one for your PostgreSQL database.
{
"ConnectionStrings": {
"DefaultConnection":"Host=localhost;Database=AlumniConnectDB;Username=postgres;Password=mypassword"
},
.....
}
Listing 3: appsettings.json file with updated connection string
Next, open the Startup.cs file in the VS Code editor and include the Using Npgsql.EntityFrameworkCore.PostgreSQL; to reference the PostgreSQL namespace. Then under the ConfigureServices method update the code so that the DBContext uses Npgsql (line 45) as the database provider instead of SQL Lite. Listing 4 shows the updated Startup.cs class. Just with these small changes we have indicated to ASP.NET Core and Entity Framework Core to use PostgreSQL as our database instead of SQL Lite. Entity Framework Core too has come a long way to embrace several open source databases as its first class citizens making it fairly easy to switch databases without making massive code changes.
using System;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using AlumniConnect.Data;
using AlumniConnect.Models;
using AlumniConnect.Services;
using Npgsql.EntityFrameworkCore.PostgreSQL;
namespace AlumniConnect
{
public class Startup
{
public Startup(IHostingEnvironment env)
{
var builder = new ConfigurationBuilder()
.SetBasePath(env.ContentRootPath)
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
if (env.IsDevelopment())
{
// For more details on using the user secret store see https://go.microsoft.com/fwlink/?LinkID=532709
builder.AddUserSecrets();
}
builder.AddEnvironmentVariables();
Configuration = builder.Build();
}
public IConfigurationRoot Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
// Add framework services.
services.AddDbContext(options =>
options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection")));
services.AddIdentity()
.AddEntityFrameworkStores()
.AddDefaultTokenProviders();
services.AddMvc();
// Add application services.
services.AddTransient();
services.AddTransient();
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
{
loggerFactory.AddConsole(Configuration.GetSection("Logging"));
loggerFactory.AddDebug();
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
app.UseDatabaseErrorPage();
// Browser Link is not compatible with Kestrel 1.1.0
// For details on enabling Browser Link, see https://go.microsoft.com/fwlink/?linkid=840936
// app.UseBrowserLink();
}
else
{
app.UseExceptionHandler("/Home/Error");
}
app.UseStaticFiles();
app.UseIdentity();
// Add external authentication middleware below. To configure them please see http://go.microsoft.com/fwlink/?LinkID=532715
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Home}/{action=Index}/{id?}");
});
}
}
}
Listing 5: Changes to Startup.cs class to add PostgreSQL Support
The above changes are enough to switch from SQL Lite to PostgreSQL, although right now there is one more hack that we need to make to ensure that we can run the Entity Framework Migration tools from the CLI to update our database. I hope that its some kind of bug in the currently generated templates and future versions should not need this hack to enable the Entity Framework Tools. Open the AlumniConnect.csproj project file in the Visual Studio editor and add the Microsoft.EntityFrameworkCore.Tools.DotNet package definition to the DotNetCliToolReference reference section (lines 26 to 28) as shown in Listing 6 below.
<Project Sdk="Microsoft.NET.Sdk.Web">
<PropertyGroup>
<TargetFramework>netcoreapp1.1</TargetFramework>
<UserSecretsId>aspnet-AlumniConnect-3EC0E2B9-6463-4A54-9EB7-EA6C51F0F7CC</UserSecretsId>
</PropertyGroup>
<ItemGroup>
<None Update="AlumniConnect.db" CopyToOutputDirectory="CopyIfNewer" />
</ItemGroup>
<ItemGroup>
<PackageReference Include="Microsoft.AspNetCore" Version="1.1.0" />
<PackageReference Include="Microsoft.AspNetCore.Authentication.Cookies" Version="1.1.0" />
<PackageReference Include="Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore" Version="1.1.0" />
<PackageReference Include="Microsoft.AspNetCore.Identity.EntityFrameworkCore" Version="1.1.0" />
<PackageReference Include="Microsoft.AspNetCore.Mvc" Version="1.1.1" />
<PackageReference Include="Microsoft.AspNetCore.StaticFiles" Version="1.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="1.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="1.1.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Design" Version="1.1.0" PrivateAssets="All" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="1.0.0-msbuild3-final" PrivateAssets="All" />
<PackageReference Include="Microsoft.Extensions.Configuration.UserSecrets" Version="1.1.0" />
<PackageReference Include="Microsoft.Extensions.Logging.Debug" Version="1.1.0" />
<PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL">
<Version>1.1.0</Version>
</PackageReference>
</ItemGroup>
<ItemGroup>
<DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="1.0.0-msbuild3-final"/>
</ItemGroup>
</Project>
Listing 6: Updated AlumniConnect.csproj file
The updated .NET SDK has moved away from the JSON based project.json file to the XML based project.csproj file format to be compatible with older versions of ASP.NET project files. The NuGet pakcage definitions which used to be defined in project.json file have been shifted into the project.csproj amongst other project related settings. Once you save the AlumniConnect.csproj file, run the sudo dotnet restore command to download and install the Microsoft.EntityFrameworkCore.Tools.DotNet package. In my tests, I found that you need to provide elevated permissions after making this change since it downloads the NuGet package in outside the project folder for EF CLI support, so make sure to run this command with elevated permissions. Once it restores the package it will give our project Entity Framework Core CLI commands support.
Now that we have our Entity Framework setup completed, use the Integrated Terminal to fire of the command shown in listing 7 to publish our entities to the PostgrerSQL database.
sudo dotnet ef database update
Listing 7: Publish PostgreSQL Database
The command shown in Listing 7 will create the AlumniConnectDB PostgreSQL database, if it does not exist as well as publish the schema based on the models required for the project. Please make sure that you have correctly configured the connection string to the PostgreSQL in appsettings.json file as well as run this command as well with elevated privileges to ensure it can create the database correctly. If you have PGAdmin3 installed, then you can browse the AlumniConnectDB and see the schema generated by the EF CLI tool as shown in Figure 3 below.
Figure 3: PGAdmin3 AlumniConnectDB Schema
Now that we have our EF CLI tools setup we can easily make further changes to the applications Models and use the EF Migrations CLI command to build schema migrations. You can learn more about the EF CLI commands from here. Run the dotnet build and dotnet run commands again to launch the application. Point your web browser to https://localhost:5000 to load the ASP.NET Core application. This time you can go to the Register link to register a new account (which creates an ASP.NET Identity account in PostgreSQL) as shown in Figure 4.
Figure 4: Register new account
This completes our short look at running ASP.NET Core 1.1 application on Ubuntu 16.04 using PostgreSQL database. In this blog post I have tried to cover some of the key new features provided by the ASP.NET Core and the .NET CLI. You can easy it very easy and seamless to integrate PostgreSQL into your ASP.NET Core application, almost making it a first class support database in the ASP.NET Core universe. I think this is a great step forward, to be able to unshackle the ASP.NET Framework from Windows Only, IIS Only, Visual Studio Only platform and make it a truly open source platform for development. We can clearly see that ASP.NET team is continuing to make strides to enhance the ASP.NET Core framework with additional features to bring it on par with earlier ASP.NET MVC 5 as well as make it completely platform and tool agnostic. I see great opportunity in the market for ASP.NET to be adopted for lower cost solutions as well.
You can find the source for this project here.