ASP.NET Core - DAL and Entities from existing DB (using EF Core 1.1)

Hi guys, in this post I want to show you an approach for ASP.NET Core (in this moment we have the 1.0.3) to show information through a data access layer from an existing Database using the new Entity Framework Core (right now we have the EF Core 1.1). Let’s start!…

Database Project

First we create a blank solution, then we can add the project we want. In the solution root we add a new project.

Select SQL Server Database Project

Then we create two basic tables (Product and ProductType)

Then we create a simple stored procedure to get a product with a given Identifier

We build and then we can publish the Db

When we try to publish our DB at first time, we have to setup a profile to future deployments, in Database name I omit the dots and I use capital letters to keep a good convention (class naming) when we get the entities.

In Target database connection to get a proper connection string we click on Edit.

We set server name with “.” or “localhost” or the name of your machine. Authentication windows authentication or sql authentication in case we want it, we click on OK to get the connection string.

Then we click on Save Profile As

So in a future deployment we can reuse those settings clicking on Load Profile then searching the previous generated xml.

Let’s click on Publish to generate the DB, we will see something like this

And this

Now we have a DB.

Web and DAL Projects

Let’s create two projects, an asp.net core application project and a .net core library which will be the DAL.

We are going to create .net core library with the name Abc.DAL (data access layer)

According to Microsoft documentation we are not able to create Entities from an existing DB in a .net core library, that’s the reason we are going to get the entities using the asp.net core application and then move the entities in the DAL library.

Let’s create an asp.net core web application

We are going to set the name Abc.Web

Setting up Web

We select Web Application Template

We are going to install these packages

- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Tools –Pre
- Microsoft.EntityFrameworkCore.Design
- Microsoft.EntityFrameworkCore.SqlServer.Design

We can add them manually using project.json or we can use the nugget package manager.

Using the nugget package manager console (Tools-> Nugget Package Manager-> Package Manager Console) don’t forget to select the proper project

Let’s install the packages, in order we should

Then we can run the following instruction in the console to get the models from the existing db

Scaffold-DbContext "Server=localhost;Database=AbcDatabase;Trusted_Connection=True;MultipleActiveResultSets=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

-OutputDir “Folder” you can specify a particular folder name.

Now we can see a new folder with the models of the existing db

Now we can move the models to the .net core library. So let’s copy the Models folder and paste it on Abc.Dal project, we will have something like this

Now we can remove Models folder in Abc.Web

Setting up DAL

We should install the packages red marked in the using

For all the classes in Models folder we should update the namespace according to the project and remove the partial definition (partial means one class divided into different files)

We will have something like this

Let’s add the Abc.DAL reference in Abc.Web
Right click on References then click on Add Reference

We are going to add a Solution reference, so let’s check Abc.DAL then click on OK

Back in Abc.DAL project
In the Database Context there’s a suggestion that says
“To protect potentially sensitive information in your connection string, you should move it out of source code”

We will move the connection string to a particular file for security purposes.

Let’s copy the connections string, then go to the folder appsettings.json (this is a file to create any entry setting we need for our project) in the Abc.Web project

As we know it’s a json file so let’s create a new connection string object with the connection string we copied before

"ConnectionStrings": {
    "AbcDatabase": "Data Source=localhost;Initial Catalog=AbcDatabase;Integrated Security=True;MultipleActiveResultSets=True;"
  }

Would be something like this

Save the json file.

Then go to the Startup.cs in Abc.Web (remember this is a file where we inject our dependencies to the pipeline)

In configure services we can add any service we want in the pipeline, so we need the IConfigurationRoot (allows us to get access to configuration properties) and the database context (don’t forget that the order matters, so we will add them before Mvc).
It would be something like this

Let’s go back to Abc.DAL in our Database Context class

We will add a constructor and call the base class constructor as well, we will inject IConfiguration in the constructor (line 12). Then we will get the connection string using the configuration interface (line 23). Now we can save and go back to Abc.Web to test if everything works.
Just before going back to Abc.Web, let’s insert some records.

Go back to Abc.Web, to do a quick test just add in home controller a query to get all the products, so I’m going to use linq to perform the query.

We join the tables and return an anonymous type to make a custom result, let’s run the application.

Good news, now we have data from the db. Finally we have an approach to create a DAL from an existing DB in asp.net core.
You can download the source from the repository.

Bonus:

Please just before starting to code, play this track, with the forgiveness of the guitar hero Hendrix, this is my favorite version of Little Wing. Stevie Ray Vaughan great blues rock guitarist, he recorded a lot of great songs.
Stevie Ray Vaughan - Little Wing