Menu

Getting Started with Entity Framework Core: Database-First Development

12 October 2017

Introduction

This article is the first part of the series on Getting Started with Entity Framework Core. In this post, we will build an ASP.NET Core MVC application that performs basic data access using Entity Framework Core. We will explore the database-first approach and see how models are created from an existing database. We will also take a look at how classes become the link between the database and ASP.NET Core MVC Controller.

Background

In Software Development world, most applications require data store or database. So we all need a code to read/write our data stored in database or data store. Creating and maintaining code for database is a tedious work and it is a real challenge for us. That's where Object Relational Mappers like Entity Framework comes into place.

What is an Object Relational Mapper?

An ORM enables developers to create data access applications by programming against a conceptual application model instead of programming directly against a relational storage schema. The goal is to decrease the amount of code and maintenance required for data-oriented applications. ORM like Entity Framework Core provides the following benefits:

Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members, and relationships.

Applications are freed from hard-coded dependencies on a particular data engine or storage schema.

Mappings between the conceptual model and the storage-specific schema can change without changing the application code.

Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in different database management systems.

What is Entity Framework Core?

According to the official documentation: Entity Framework (EF) Core is a lightweight, extensible, and cross-platform version of the popular Entity Framework data access technology. EF Core is an object-relational mapper (O/RM) that enables .NET developers to work with a database using .NET objects. It eliminates the need for most of the data-access code that developers usually need to write.

Design WorkFlows

Just like any other ORM, there are two main design workflows that is supported by Entity Framework Core: The Code-First approach which is you create your classes (POCO Entities) and generate a new database out from it. The Database-First approach allows you to use an existing database and generate classes based on your database schema. In the part let's explore Database-First approach first.

If you are still confuse about the difference and advantages between the two, don't worry as we will be covering that in much detail in the upcoming part of the series.

Let's Begin!

If you're ready to explore EF Core database-first development and get your hands dirty, then let's get started!

Database Creation

In this walkthrough, we will just be creating a single database table that houses some simple table properties for simplicity. Go ahead and open Microsoft SQL Server Management Studio and run the following SQL script below to create the database and table:

Name your project to whatever you like, but for this exercise, we will name it as “DBFirstDevelopment” to conform with the topic. Click OK and then select “Web Application” within ASP.NET Core templates as shown in the following figure below:

Figure 3: ASP.NET Core Web Application Template

Now click OK to let Visual Studio generate the required files needed for us to run the application. The figure below shows the default generated files:

Figure 4: Default ASP.NET Core Web Application Project Files

If you are not familiar with the new file structure generated in ASP.NET Core, then don’t worry as we will get to know them in the next part of this series. For now, let’s just keep moving.

First Run

Let's build and run the application by pressing CTRL + F5 or clicking on the IIS Express Play button at the toolbar. If you are seeing the following output below, then congrats, you now have a running ASP.NET Core web application.

Figure 5: First Run

The Model

Let's create a “Models” folder within the root of the application and under that folder, create another folder and name it as “DB”. Our project structure should now look something like below:

Figure 6: The Models Folder

The “DB" folder will contain our DBContextand Entity models. We are going to use Entity Framework Core as our data access mechanism to work with database. We will not be using the old-fashioned Entity Framework designer to generate models for us because EF designer (EDMX) isn’t supported in ASP.NET Core 1.1.

Integrating Entity Framework Core

ASP.NET Core is designed to be light-weight, modular and pluggable. This allows us to plug-in components that are only required for our project. Having said that, we need to add the Entity Framework Core packages in our ASP.NET Core app because we are going to need it. For more details about Entity Framework Core then check out the references section at the end of this article.

There are two ways to add packages in ASP.NET Core; you could either use the Package Manager Console, or via NuGet Package Manager (NPM). In this exercise, we are going to use NPM so you can have a visual reference.

Now, right-click on the root of your application and then select Manage NuGet Packages. Select the Browse tab and in the search bar, type in “Microsoft.EntityFrameworkCore.SqlServer”. It should result to something like this:

Figure 7: Manage NuGet Package

Select “Microsoft.EntityFrameworkCore.SqlServer” and click Install. The latest stable version as of this time of writing is v1.1.2. You would probably be prompted with the Review Changes and License Acceptance dialog. Just click “I Accept” and follow the wizard instructions until it completes the installation.

Since we are going to use Database-First development approach to work with existing database, we need to install the additional packages below as well:

Notes:
1. It is always recommended to install the latest stable version of each package to avoid unexpected errors during development.
2. If you are getting this error “Unable to resolve 'Microsoft.EntityFrameworkCore.Tools (>= 1.1.2)' for '.NETCoreApp,Version=v1.1'” when building the project, then just restart Visual Studio 2017.

When it’s done restoring all the required packages, you should be able to see them added to your project dependencies as shown in the figure below:

Figure 10: Entity Framework Core Packages Restored

For details about the new features in Entity Framework Core, check out the references section at the end of this article.

Creating Entity Models from Existing Database

Now, it’s time for us to create the Entity Framework models based on our existing database that we have just created earlier.

As of this time of writing, there are two ways to generate models from our existing database (a.k.a reverse engineer).

Option 1: Using Package Manager Console

Go to Tools –> NuGet Package Manager –> Package Manager Console

And then run the following command below to create a model from the existing database:

The Server attribute above is the SQL server instance name. You can find it in SQL Management Studio by right clicking on your database. For this example, the server name is “SSAI-L0028-HP\SQLEXPRESS”.

The Database attribute is your database name. In this case, the database name is “EFCoreDBFirstDemo”.

The –OutputDir attribute allows you to specify the location of the files generated. In this case, we’ve set it to Models/DB.

Option 2: Using Command Window

If for some unknown reason Option 1 will not work for you, try the following instead:

Go to the root folder of your application. In this case the “DBFirstDevelopment”.

Do a Shift + Right Click and select “Open command window here”
Then run the following script:

The EFCoreDBFirstDemoContext class represents a session with the database and allows you to query and save instances of the entity classes.

If you have noticed, the models generated are created as partial classes. This means that you can extend them by creating another partial class for each of the entity/model classes when needed.

Registering DBContext using Dependency Injection

The next step is to register our EFCoreDBFirstDemoContext class using Dependency Injection (DI). To follow the ASP.NET Core configuration pattern, we will move the database provider configuration to Startup.cs. To do this, just follow these steps:

(1) Open Models\DB\EFCoreDBFirstDemoContext.cs file

(2) Remove the OnConfiguring() method and add the following code below:

Scaffolding a Controller with Views

Now that our data access is in place, we are now ready to work with data by creating an ASP.NET Core MVC Controller for handling data and performing basic Create, Read, Update and Delete (CRUD) operations.

Enable scaffolding in the project:

Right-click on the Controllers folder in Solution Explorer and select Add > Controller.

Select Full Dependencies and click Add.

You can ignore or delete the ScaffoldingReadMe.txt file.

Now that scaffolding is enabled, we can scaffold a controller for the Student entity or just generate an Empty Controller. In this example, we are going to generate a Controller with Views using Entity Framework.

Right-click on the Controllers folder in Solution Explorer and select Add > Controller.

Just Click Add. The scaffolding will generate a Controller and a bunch of View files needed to run the application. Here’s a screen capture of the scaffold files:

Figure 13: The generated files

As you can see, scaffolding saves you a lot of time and effort as you don’t have to worry about generating your Views. All methods for performing CRUD operations are also generated for you without coding on your part, thus boosting your productivity. If there is a need for you to change something in the View or in the Controller methods, it would probably be minimal.

The StudentsController class uses Constructor Injection to gain access to the DBContext and DBSet defined within EFCoreDBFirstDemoContext. The DBContext contains virtual methods used to perform certain operations such as Add(), Update(), Find(), SaveChanges() and many more. It also contains their corresponding asynchronous methods such as AddAsync(), FindAsync() , SaveChangesAsyn() and so on. The EFCoreDBFirstDemoContext class only contains a single DBSet based on our example, and that is the Student entity which is defined as DBSet<Student>.

The Index() action method gets all Student records from the database. This method is defined as asynchronous which returns the corresponding Index View along with the list of Students entity.

The Details() action method gets the corresponding database record based on the parameter id. It returns NotFound() when the value of the parameter id is null, otherwise it fetches the record from the database based on the corresponding id using the LINQ syntax. If the LINQ SingleOrDefaultAsync() method returns a row then the Details() method will return the Student model to the View, otherwise it returns NotFound().

The Create() action method simply returns it's corresponding View. The overload Create() action method is decorated with the [HttpPost] attribute which signifies that the method can only be invoked for POST requests. This method is where we actually handle the adding of new data to database if the model state is valid on posts.

The Edit() action method takes an id as the parameter. If the id is null, it returns NotFound(), otherwise it returns the corresponding data to the View. The overload Edit() action method updates the corresponding record to the database based on the id.

Just like the other action, the Delete() action method takes an id as the parameter. If the id is null, it returns NotFound(), otherwise it returns the corresponding data to the View. The overload Delete() action method deletes the corresponding record to the database based on the id.

Testing the Application

Now build and run the application using CTRL + 5. In the browser, navigate to /students/Create. It should bring up the following page.

Figure 14: The Create New Student Page

Supply the fields in the page and then click the Create button. It should take you to the Index page with the inserted data as shown in the figure below:

Figure 15: The Index Page

Of course the Edit, viewing of Details and Delete works too without doing any code on your part. :)

Summary

In this part of the series, we’ve learned building an ASP.NET Core MVC application using Entity Framework Core with existing database. We also learned the power of scaffolding to quickly generate Controllers with Views based from Entity Framework model in just a few clicks.