Build Persisting Layer With ASP.NET Core And EF Core Using PostgreSQL And SQL Server 2016

This post is about developing ASP.NET Core application, using EF Core. We will demonstrate how we can connect in different ways to SQL Server database and PostgreSQL database.

Let’s start with the pre-requisites.

Visual Studio 2015 Update 3.
.NET Core 1.0.
SQL Server 2014 / 2016 Server Management Studio with a default localdb.
PostgreSQL

We will follow in this article the steps, which are given below.

Create database - samplecoreEF
Create an ASP.NET Core Web Application - Samples.AspCoreEF
Add Class Library Core to the solution - Samples.AspCoreEF.DAL.EF
Create model classes - Task and Person.
Add Context - TaskSystemDbContext.
Register the context class with Dependency Injection.
Auto-Create the database.
Add Web API controller and test, using Postman.

Create database - samplecoreEF

Create a new database “sampleCoreEF” in PostgreSQL.

Create a new database “sampleCoreEF” in SQL Server 2016.

Create an ASP.NET Core Web Application - Samples.AspCoreEF

Now, we Open Visual Studio > File > New Project> Select “ASP.NET Core Web Application” > Enter Name “Samples.AspCoreEF” & Location > OK.

Afterwards, we will select the template “Web Application” and we confirm the choice by clicking OK.

Add Class Library Core to the solution - Samples.AspCoreEF.DAL.EF

Now, we take a look to Samples.AspCoreEF project structure, as shown in the screenshot, given below.

The project Samples.AspCoreEF.DAL.EF will contain our EntityFramework models, migrations, context.

Create model classes - Task and Person

We will add to the Samples.AspCoreEF.DAL.EF project the Models folder that will contains two models: Person.cs and Task.cs.

In project.json

We install the Entity Framework Core package for the database provider from NuGet Package Manager Console in Visual Studio 2015.

Thus, “project.json” should have this content to be sure that we are adding the right version.

Add Context - TaskSystemDbContext

We have to add a new folder called EntityFramework, where we will add our context, as given below.

So, we will have this structure:

Register the context class with Dependency Injection

We will work now in ASP.NET Web Application to be able to register our context.

We will start by adding Samples.AspCoreEF.DAL.EF as a reference to Samples.AspCoreEF.

Afterwards, we will add the needed references to generate our database as before. Thus, our project.json will look, as shown below.

In the Startup.cs, we will add two blocks to show you how we can register context class in two different ways. Thus, this method looks like-

public void ConfigureServices(IServiceCollection services)
After: services.AddMvc();

We will add this block related to the connection to SQL Server database.

//Using SQL Server
var sqlconnection = @"Server=(localdb)\v11.0;Database=samplecoreEF;Trusted_Connection=True;";
services.AddDbContext<TaskSystemDbContext>(dbcontextoption => dbcontextoption.UseSqlServer(sqlconnection));

SQLconnection is a hardcoded string and we use UseSQLServer to connect to the database.

It’s better to add this string in the configuration file. Prior to it, we can add these strings in web.config but in Core, this file doesn’t exist anymore although we can use appsettings.json file, where you can add all the settings related to your Web Application so it resembles, as shown below.

Thus, we will call it in Startup.cs in the given way.

//Using Postgresql
var connectionString = Configuration["DbContextSettings:ConnectionString"];
services.AddDbContext<TaskSystemDbContext>(
opts => opts.UseNpgsql(connectionString)
);

Now, we will create our database.

Tools - NuGet Package Manager and then we click on Package Manager Console menu.

Type Add-Migration CoreMigration and enter.

We will have Migration folder added in the solution.

If we check the databases, we will find the tables are added automatically.

If the database exists, we use Update-database.

Sometimes, we get some error, as shown below.

To resolve it, we have to restart Visual Studio as an administrator and enable the migration.
PM> Enable-Migrations.

If you get this message, you have to update PowerShell version.

Add Web API controller and test, using Postman.

Now, we will add new API Controller called PersonController.

Test your API, using Postman.