# Building an ASP.NET Core 1.1 Application with PostgreSQL on Ubuntu Linux **by:** Saurabh Nandu **in:** [Programming](https://www.systenics.com/blogs/category/programming) **tags:** [*ASP.NET Core*](https://www.systenics.com/blogs/tag/aspnet-core) , [*Linux*](https://www.systenics.com/blogs/tag/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](https://www.amazonlightsail.com/)! 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](https://www.microsoft.com/net/core#linuxubuntu). 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](https://code.visualstudio.com/Download). 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 PostgreSQL is a popular open source relational database. Installation and configuration of PostgreSQL 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](https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04) 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. 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. ``` 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. ## 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](http://www.npgsql.org/efcore/index.html) 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 ** 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. 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. ``` 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()) { builder.AddUserSecrets(); } builder.AddEnvironmentVariables(); Configuration = builder.Build(); } public IConfigurationRoot Configuration { get; } public void ConfigureServices(IServiceCollection services) { services.AddDbContext(options => options.UseNpgsql(Configuration.GetConnectionString("DefaultConnection"))); services.AddIdentity() .AddEntityFrameworkStores() .AddDefaultTokenProviders(); services.AddMvc(); services.AddTransient(); services.AddTransient(); } public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory) { loggerFactory.AddConsole(Configuration.GetSection("Logging")); loggerFactory.AddDebug(); if (env.IsDevelopment()) { app.UseDeveloperExceptionPage(); app.UseDatabaseErrorPage(); } else { app.UseExceptionHandler("/Home/Error"); } app.UseStaticFiles(); app.UseIdentity(); 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. ``` netcoreapp1.1 aspnet-AlumniConnect-3EC0E2B9-6463-4A54-9EB7-EA6C51F0F7CC 1.1.0 ``` **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 PostgreSQL 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. 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](https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dotnet). Run the **dotnet build** and **dotnet run** commands again to launch the application. Point your web browser to [https://localhost:5000](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. You can find the source for this project [here.](https://github.com/saurabhnandu/AlumniConnect)