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.
create a new project

Select SQL Server Database Project
database project

Then we create two basic tables (Product and ProductType)
product table

product type table

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

We build and then we can publish the Db
publish 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.
setup publish db
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.
db connection

save connection
Then we click on Save Profile As

load profile
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
publish results

And this
db results
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.
solution root - new project

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

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
new web project

We are going to set the name Abc.Web
name project

Setting up Web

We select Web Application Template
web application template

We are going to install these packages

1
2
3
4
- 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.
project.json
Using the nugget package manager console (Tools-> Nugget Package Manager-> Package Manager Console) don’t forget to select the proper project
package manager console

Let’s install the packages, in order we should

packages installation

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

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

Scaffold-DbContext

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

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

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
entities in DAL

Now we can remove Models folder in Abc.Web

Setting up DAL

We should install the packages red marked in the using
dependencies in DAL

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)
DbContext in Dal

We will have something like this
DbContext modified in Dal

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

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

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”
DBContext in DAL

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
appsettings.json in Web

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

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

Would be something like this
appsettings.json modified in Web
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)
startup

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

ConfigureServices

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

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.
sql statements

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.
LinQ query

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

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