Getting Started With Entity Framework Core - Database-First Development

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 the Software Development world, most applications require a data store or database. So, we all need a code to read/write our data stored in the database or data store. Creating and maintaining code for the database is 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.
• Multiple conceptual models can be mapped to a single storage schema.
• Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

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.

To get more information about EF Core, I would recommend you to visit the official documentation here: https://docs.microsoft.com/en-us/ef/core/

Design WorkFlows

Just like any other ORM, there are two main design workflows that are 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 confused 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. Now, open Microsoft SQL Server Management Studio and run the following SQL script below to create the database and table:

CREATE DATABASE EFCoreDBFirstDemo
GO

USE [EFCoreDBFirstDemo]
GO

CREATE TABLE [dbo].[Student](
    [StudentId] [bigint] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](30) NULL,
    [LastName] [varchar](30) NULL,
    [Gender] [varchar](10) NULL,
    [DateOfBirth] [datetime] NULL,
    [DateOfRegistration] [datetime] NULL,
    [PhoneNumber] [varchar](20) NULL,
    [Email] [varchar](50) NULL,
    [Address1] [varchar](50) NULL,
    [Address2] [varchar](50) NULL,
    [City] [varchar](30) NULL,
    [State] [varchar](30) NULL,
    [Zip] [nchar](10) NULL,
 CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
    [StudentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The SQL script above should create the “EFCoreDBFirstDemo” database with the following table


Figure 1 dbo.Student Table Creating an ASP.NET Core Project

Our next step is to create a web page where we can send and retrieve data from our database.

Fire-up Visual Studio 2017 and let’s create a new ASP.NET Core Web Application project. To do this, select File > New > Project. In the New Project dialog select Visual C# > Web > ASP.NET Core Web Application (.NET Core) just like in the figure below,

Figure 2 New ASP.NET Core Web Application

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

Figure 3 Web Application Template

With the new template dialog, you can now choose to use ASP.NET Core 1.0 or 1.1 using the combo-box at the top of the screen. The default template as of this time of writing is ASP.NET Core 1.1. Additionally, you can choose to add Container support to your project with the checkbox at the bottom-left of the screen.  This option adds:

• Docker files to your project for building Container images
• a Docker Compose project to define how you instance your Containers
• Multi-project/container debugging support for Containers

For the simplicity of this exercise, let’s just omit the use of containers. 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 New 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 Chapter. For now, let’s just keep moving.

First Run

Let build and run the application by pressing CTRL + F5 or clicking on the IIS Express Play button. If you are seeing the following output below, then our app should be ready.

Figure 5 First Run

The Model

Now create the “Models” folder within the root of the application and under that folder, create another folder and it as “DB”. Your solution should now look something like below:


Figure 5 The Models Folder

The “DB" folder will contain our data access code. In this demo, we are going to use Entity Framework Core as our data access mechanism. This could mean that we will not be using the old 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

To give you a quick recap, Entity Framework 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. EF Core supports many database engines other than SQL Server.

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. In other words, we need to add the Entity Framework Core package in our ASP.NET Core app because we are going to need it. For more details about EF Core then check out the references section at the end of this Chapter.

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 in something like this:


Figure 6 Manage NuGet Package

Select “Microsoft.EntityFrameworkCore.SqlServer” and click Install. The latest stable version of this time of writing is v1.1.2.  You would probably be prompted by 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 the existing database, we need to install the additional packages below as well:

• EntityFrameworkCore.Tools (v1.1.1)
• EntityFrameworkCore.SqlServer.Design (v1.1.2)

Now go ahead and install them via Package Manager Console or NPM GUI as shown in the figures below:

Using PM Console

Tools > NuGet Package Manager -> Package Manager Console

Install-Package Microsoft.EntityFrameworkCore.Tools

Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design

Using NPM


Figure 7 Adding Microsoft.EntityFrameworkCore.Tools package

Figure 8 Adding Microsoft.EntityFrameworkCore.SqlServer.Design package

Notes

• It is always recommended to install the latest stable version of each package to avoid unexpected errors during development.
• 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 9 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 Chapter.

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.

Option 1: Using Package Manager Console

1. Go to Tools –> NuGet Package Manager –> Package Manager Console
2. And then run the following command to create a model from the existing database:

Scaffold-DbContext "Server=SSAI-L0028-HP\SQLEXPRESS;Database=EFCoreDBFirstDemo;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models/DB

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 exercise, 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:

1. Go to the root folder of your application. In this case the “DBFirstDevelopment”.
2. Do a Shift + Right Click and select “Open command window here”
3. Then run the following script:

dotnet ef dbcontext scaffold "Server=SSAI-L0028-HP\SQLEXPRESS;Database=EFCoreDBFirstDemo;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer --output-dir Models/DB

The script above is quite similar to the script we used in Option 1, except we use the command dotnet ef dbcontext scaffold and --output-dir to set the target location of the scaffolded files.

Note that you need to change the Server value based on your database server configuration. If you are using a different database name, you would need to change the Database value too.

The command above will generate models from the database within Models/DB folder. Here’s the screenshot below,


Figure 10Entity Framework Generated Models

Notes

• If you are still getting errors then you might want to upgrade the PowerShell to version 5. You can download it here.
• You need to change the value of Server and Database in your connection string based on your server configuration.

The reverse engineer process created the Student.cs entity class and a derived context (EFCoreDBFirstDemoContext.cs) based on the schema of the existing database. Here are the generated codes below:

Student Class

using System;
using System.Collections.Generic;

namespace DBFirstDevelopment.Models.DB
{
    public partial class Student
    {
        public long StudentId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
        public DateTime? DateOfBirth { get; set; }
        public DateTime? DateOfRegistration { get; set; }
        public string PhoneNumber { get; set; }
        public string Email { get; set; }
        public string Address1 { get; set; }
        public string Address2 { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Zip { get; set; }
    }
}

The entity class above is a simple C# objects that represent the data you will be querying and saving.

EFCoreDBFirstDemoContext Class

using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;

namespace DBFirstDevelopment.Models.DB
{
    public partial class EFCoreDBFirstDemoContext : DbContext
    {
        public virtual DbSet<Student> Student { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                #warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseSqlServer(@"Server=SSAI-L0028-HP\SQLEXPRESS;Database=EFCoreDBFirstDemo;Trusted_Connection=True;");
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Student>(entity =>
            {
                entity.Property(e => e.Address1).HasColumnType("varchar(50)");

                entity.Property(e => e.Address2).HasColumnType("varchar(50)");

                entity.Property(e => e.City).HasColumnType("varchar(30)");

                entity.Property(e => e.DateOfBirth).HasColumnType("datetime");

                entity.Property(e => e.DateOfRegistration).HasColumnType("datetime");

                entity.Property(e => e.Email).HasColumnType("varchar(50)");

                entity.Property(e => e.FirstName).HasColumnType("varchar(30)");

                entity.Property(e => e.Gender).HasColumnType("varchar(10)");

                entity.Property(e => e.LastName).HasColumnType("varchar(30)");

                entity.Property(e => e.PhoneNumber).HasColumnType("varchar(20)");

                entity.Property(e => e.State).HasColumnType("varchar(30)");

                entity.Property(e => e.Zip).HasColumnType("nchar(10)");
            });
        }
    }
}

The EFCoreDBFirstDemoContext context 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. 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,
   

   public EFCoreDBFirstDemoContext(DbContextOptions<EFCoreDBFirstDemoContext> options)
   : base(options)
   { }

   The constructor above will allow configuration to be passed into the context by dependency injection.

3. Open appsettings.json file and add the following script for our database connection string below,
   

   "ConnectionStrings": {
   i.  "EFCoreDBFirstDemoDatabase": "Server=SSAI-L0028-HP\\SQLEXPRESS;Database=EFCoreDBFirstDemo;Trusted_Connection=True;"
   }

4. Open Startup.cs
5. Add the following using statements at the start of the file,
   

   using DBFirstDevelopment.Models;
   using DBFirstDevelopment.Models.DB;
   using Microsoft.EntityFrameworkCore;

6. Add the following lines of code within ConfigureServices() method,
   

   // Add ASPNETCoreDemoDBContext services.
   services.AddDbContext<EFCoreDBFirstDemoContext>(options => options.UseSqlServer(Configuration.GetConnectionString("EFCoreDBFirstDemoDatabase")));

That’s it. Now we’re ready to work with data.

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

1. Right-click on the Controllers folder in Solution Explorer and select Add > Controller.
2. Select Full Dependencies and click Add.
3. 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.

1. Right-click on the Controllers folder in Solution Explorer and select Add > Controller.
2. Select MVC Controller with Views using Entity Framework.
3. Click Add.

In the “Add Controller” dialog, do the following:

1. Select Student as Model class.
2. Select EFCoreDBFirstDemoContext as the Data Context class
3. Tick the Generate Views option
4. In the “Use a layout page” option, browse through Views > Shared > _Layout.cshtml
   
   Here’s a screen capture of the example,
   
   
   

5. 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,
   
   
   
   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 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 Generated Code
   

   using DBFirstDevelopment.Models.DB;
   using Microsoft.AspNetCore.Mvc;
   using Microsoft.EntityFrameworkCore;
   using System.Linq;
   using System.Threading.Tasks;
   
   namespace DBFirstDevelopment.Controllers
   {
       public class StudentsController : Controller
       {
           private readonly EFCoreDBFirstDemoContext _context;
   
           public StudentsController(EFCoreDBFirstDemoContext context)
           {
               _context = context;
           }
   
           // GET: Students
           public async Task<IActionResult> Index()
           {
               return View(await _context.Student.ToListAsync());
           }
   
           // GET: Students/Details/5
           public async Task<IActionResult> Details(long? id)
           {
               if (id == null)
               {
                   return NotFound();
               }
   
               var student = await _context.Student
                   .SingleOrDefaultAsync(m => m.StudentId == id);
               if (student == null)
               {
                   return NotFound();
               }
   
               return View(student);
           }
   
           // GET: Students/Create
           public IActionResult Create()
           {
               return View();
           }
   
           // POST: Students/Create
           // To protect from overposting attacks, please enable the specific properties you want to bind to, for
           // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
           [HttpPost]
           [ValidateAntiForgeryToken]
           public async Task<IActionResult> Create([Bind("StudentId,FirstName,LastName,Gender,DateOfBirth,DateOfRegistration,PhoneNumber,Email,Address1,Address2,City,State,Zip")] Student student)
           {
               if (ModelState.IsValid)
               {
                   _context.Add(student);
                   await _context.SaveChangesAsync();
                   return RedirectToAction("Index");
               }
               return View(student);
           }
   
           // GET: Students/Edit/5
           public async Task<IActionResult> Edit(long? id)
           {
               if (id == null)
               {
                   return NotFound();
               }
   
               var student = await _context.Student.SingleOrDefaultAsync(m => m.StudentId == id);
               if (student == null)
               {
                   return NotFound();
               }
               return View(student);
           }
   
           // POST: Students/Edit/5
           // To protect from overposting attacks, please enable the specific properties you want to bind to, for
           // more details see http://go.microsoft.com/fwlink/?LinkId=317598.
           [HttpPost]
           [ValidateAntiForgeryToken]
           public async Task<IActionResult> Edit(long id, [Bind("StudentId,FirstName,LastName,Gender,DateOfBirth,DateOfRegistration,PhoneNumber,Email,Address1,Address2,City,State,Zip")] Student student)
           {
               if (id != student.StudentId)
               {
                   return NotFound();
               }
   
               if (ModelState.IsValid)
               {
                   try
                   {
                       _context.Update(student);
                       await _context.SaveChangesAsync();
                   }
                   catch (DbUpdateConcurrencyException)
                   {
                       if (!StudentExists(student.StudentId))
                       {
                           return NotFound();
                       }
                       else
                       {
                           throw;
                       }
                   }
                   return RedirectToAction("Index");
               }
               return View(student);
           }
   
           // GET: Students/Delete/5
           public async Task<IActionResult> Delete(long? id)
           {
               if (id == null)
               {
                   return NotFound();
               }
   
               var student = await _context.Student
                   .SingleOrDefaultAsync(m => m.StudentId == id);
               if (student == null)
               {
                   return NotFound();
               }
   
               return View(student);
           }
   
           // POST: Students/Delete/5
           [HttpPost, ActionName("Delete")]
           [ValidateAntiForgeryToken]
           public async Task<IActionResult> DeleteConfirmed(long id)
           {
               var student = await _context.Student.SingleOrDefaultAsync(m => m.StudentId == id);
               _context.Student.Remove(student);
               await _context.SaveChangesAsync();
               return RedirectToAction("Index");
           }
   
           private bool StudentExists(long id)
           {
               return _context.Student.Any(e => e.StudentId == id);
           }
       }
   }

Let’s take a quick look of what we did above.

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 correspoding 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 its 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 the 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 in the database based on the id.

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.



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,



Of course, the Edit, viewing of Details and Delete works too without doing anything on your part. Here are some screen captures.







Summary

In this 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 on Entity Framework model in just a few clicks.

References

• https://blogs.msdn.microsoft.com/webdev/2017/03/07/announcing-visual-studio-2017/
• https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db
• https://docs.microsoft.com/en-us/ef/core/providers/
• https://blogs.msdn.microsoft.com/webdev/2016/11/16/announcing-asp-net-core-1-1/
• https://docs.microsoft.com/en-us/ef/core/miscellaneous/configuring-dbcontext