Getting Started with Entity Framework Core: Database-First Development

• Download DBFirstDevelopment.zip - 600.4 KB

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

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 SQL Table

Nothing really fancy there. The dbo.Student table just contains some basic properties for us to use in our web application later on.

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: ASP.NET Core Web Application Project Template

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 DBContext and 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:

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

Now, go ahead and install them via Package Manager Console or Nuget Package Manager (NPM) GUI.

Using Package Manager Console

Go to Tools > NuGet Package Manager -> Package Manager Console and run the following command individually:

Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design

Using Nuget Package Manager GUI

Go to Tools > NuGet Package Manager > Manage NuGet Packages for Solution, then type in "Microsoft.EntityFrameworkCore.Tools" just like in the figure below:

Figure 8: Adding Microsoft.EntityFrameworkCore.Tools package

Click install and then do the same with "Microsoft.EntityFrameworkCore.SqlServer.Design".

Figure 9: Adding Microsoft.EntityFrameworkCore.SqlServer.Design package

Click install to restore the package to your project.

Quote:

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

1. Go to Tools –> NuGet Package Manager –> Package Manager Console
2. And then run the following command below 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 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:

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 that we used in Option 1, except we use the command dotnet ef dbcontext scaffold and --output-dir to set the target location of the scaffold files.

Quote:

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 existing database within Models/DB folder. Here’s the screenshot below:

Figure 11: Entity Framework Generated Models

Quote:

Tips

If you are still getting errors then you might want to upgrade the PowerShell to version 5.

• You can download it here: https://www.microsoft.com/en-us/download/details.aspx?id=50395
• You need to change the value of the Server and Database attributes 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.

The following are the codes generated.

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 nothing but a simple C# object that represents 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 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:

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 line 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:

Figure 12: Add Controller Dialog

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 Generated Code

Here's the generated code for the StudentsController class.

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

Download Source Code

Download DBFirstDevelopment.zip

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