Entity Framework Database First In ASP.NET Core

In this article, you will learn how to configure and use the Database First method of Entity Framework in an ASP.NET Core application.

This article covers the following topics:  

  • Setup Entity Framework Database First
  • Working with ViewModel
  • Display a list of Employees from a database

Setup and working Entity Framework Database First

Entity Framework's Database First approach allows developers to build software applications from their existing databases. You connect to an exisitng database and Visual Studio and EF build a data object model and the complete application for you with very little code. Let's try to understand some concepts used on the database first approach.

The Application

In this tutorial, we will build an application that will bring Employee data from a database and display them on a page, something like this. 

Entity Framework Database First In ASP.NET Core

For this purpose, we will need to create create two databases and the result UI will display the following data: 

SR. NO. FIELDNAME REMARKS
1 EmployeeName Name of Employee.
2 PhoneNumber Phone Number of Employee.
3 Skill We display skill value with the help of joining with tblSkills.
4 YearsExperience Years of Work Experience.

STEP 1. Create Database Tables

First, we're going to create two database tables, tblMembers and tblSkills. These tables store employee details and skills. You can create a new database in SQL Server or add these tables to an existing database.

The following scripts will create these database tables. If you want to use your existing database, you may skip this step. 
  1. CREATE TABLE [dbo].[tblEmployees](  
  2.     [EmployeeID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [EmployeeName] [varchar](50) NULL,  
  4.     [PhoneNumber] [varchar](50) NULL,  
  5.     [SkillID] [intnull,  
  6.     [YearsExperience] [intnull,  
  7. PRIMARY KEY CLUSTERED   
  8. (  
  9.     [EmployeeID] ASC  
  10. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  11. ON [PRIMARY]  
  1. CREATE TABLE [dbo].[tblSkills](  
  2.     [SkillID] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Title] [varchar](50) NULL,  
  4. PRIMARY KEY CLUSTERED   
  5. (  
  6.     [SkillID] ASC  
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  8. ON [PRIMARY]  

Now, let's add some seed data to our database tables. The following scripts insert data into our database tables.

  1. insert into tblSkills
  2. (Title) values('Visual Foxpro')
  3. insert into tblSkills
  4. (Title) values('C#')
  5. insert into tblSkills
  6. (Title) values('VB.NET')
  7. insert into tblSkills
  8. (Title) values('Delphi')
  9. insert into tblSkills
  10. (Title) values('Java')
  11. insert into tblSkills
  12. (Title) values('Power Builder')
  13. insert into tblSkills
  14. (Title) values('COBOL')
  15. insert into tblSkills
  16. (Title) values('Python')
  1. Insert Into tblEmployees
  2. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  3. values ('Suhana Kalla','9869569634',2,'11')
  4. Insert Into tblEmployees
  5. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  6. values ('Ashish Kalla','9869166077',8,'14')
  7. Insert Into tblEmployees
  8. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  9. values ('Manoj Kalla','9869569634',1,'24')
  10. Insert Into tblEmployees
  11. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  12. values ('Nirupama Kalla','9969359746',6,'20')
  13. Insert Into tblEmployees
  14. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  15. values ('Rajesh Bohra','9869166012',7,'28')
  16. Insert Into tblEmployees
  17. (EmployeeName,PhoneNumber,SkillID,YearsExperience)
  18. values ('Murli Vyas','9261166012',5,'18')
The data looks like the following: 
 
Entity Framework Database First In ASP.NET Core


Entity Framework Database First In ASP.NET Core

STEP 2. Create a Web Application

Now, let's create an ASP.NET Core Web Application using Visual Studio. 

We've selected Visual C# > .NET Core > ASP.NET Core Web Application. Give project a name and a location. In our case, we have EmployeeList-EF-DbFist and d:\MBK respectively.  

Entity Framework Database First In ASP.NET Core
Image 1

On the next screen, make sure you've .Net Core and Asp.Net Core 2.0 and Web Application selected. 

Entity Framework Database First In ASP.NET Core
Image No.2

Press the OK button to create the project.

STEP 3. Project structure view, folders creation

Default view of project looks like the following:

Entity Framework Database First In ASP.NET Core
Image No.3

Now, we are going to add three folders to support the MVC architecture.

  • Models
  • Views
  • Controllers

Switch to Solution Explorer and right click on project name and select Add --> New Folder option.

Above step repeats two more times to add three folders: Models, Views, and Controllers.

Entity Framework Database First In ASP.NET Core
Image No.4

STEP 4. Set Connection, Create Models, Classes

How to set a Connection Strings in Asp.Net Core?

You can set a connection string inside appsettings.json file. 

Code of appsettings.json

  1. {  
  2.   "Logging": {  
  3.     "IncludeScopes"false,  
  4.     "LogLevel": {  
  5.       "Default""Warning"  
  6.     }  
  7.   },  
  8.   "ConnectionStrings": {  
  9.     "MbkDbConstr""Data Source=(localdb)\\MBK;Database=MbkTest;"  
  10.   }  
  11. }  

Creating Models

We are going to create following models:

CLASS NAME DESCRIPTION
EmployeeContext.cs To set entity framework.
tblEmployees.cs tblEmployees Model.
tblSkills.cs tblSkills Model.
EmployeeViewModel.cs View Model for employee listing.

Now right click on Models folder to add a class called EMPLOYEECONTEXT.

Entity Framework Database First In ASP.NET Core
Image No.5

After right clicking on Models folder the above options list will appear.

Entity Framework Database First In ASP.NET Core
Image No.6

Select Class in the items list and name it EmployeeContext.

Entity Framework Database First In ASP.NET Core
Image No.7

Select Class in the items list and name it tblEmployee.

Entity Framework Database First In ASP.NET Core
Image No.8

Select Class in the items list and name it tblSkill.

Entity Framework Database First In ASP.NET Core
Image No.9

Select Class in the items list and name it EmployeeViewModel.

Now double click on EmployeeViewModel.cs file declare some properties in the class. These will match with out database table columns.

We are creating a View model for display and creating properties as per display/view required.

Check our OUR TASK image.

First add the following namespace in the class.

using System.ComponentModel.DataAnnotations;

Code of EmployeeViewModel.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using System.ComponentModel.DataAnnotations;  
  6.   
  7. namespace EmployeeList_EF_DbFirst.Models  
  8. {  
  9.     public class EmployeeViewModel  
  10. {  
  11. [Key]  
  12.         public int EmployeeID { getset; }  
  13.   
  14.         public string EmployeeName { getset; }  
  15.   
  16.         public string PhoneNumber { getset; }  
  17.   
  18.         public string Skill { getset; }  
  19.   
  20.         public int YearsExperience { getset; }  
  21.     }  
  22. }  

Entity Framework Database First In ASP.NET Core
Image No.10

EmployeeViewModel properties.

Now double click on tblEmployee.cs file to define its properties.

First add following namespace to the class.

using System.ComponentModel.DataAnnotations;

Code of tblEmployee.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel.DataAnnotations;  
  4. using System.Linq;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace EmployeeList_EF_DbFirst.Models  
  8. {  
  9.     public class tblEmployee  
  10.    {  
  11.     [Key]  
  12.     public int EmployeeID { getset; }  
  13.   
  14.     [Display(Name = "Employee Name")]  
  15.     public string EmployeeName { getset; }  
  16.   
  17.     [Display(Name = "Contact Number")]  
  18.     public string PhoneNumber { getset; }  
  19.   
  20.     [Display(Name = "Your Skill")]  
  21.     public int SkillID { getset; }  
  22.   
  23.     [Display(Name = "Years of Experience")]  
  24.     public int YearsExperience { getset; }  
  25.    }  
  26. }  

What is KEY and Display attribute?

DATA-ANNOTATION DESCRIPTION
[KEY] Key attribute bind property with table as Primary-Key column in table.
[Display(Name=””)] To display title desired title in view.

Now double click on tblSkills.cs file to declare its properties.

First add following namespace to the class.

using System.ComponentModel.DataAnnotations;

Code of tblSkill.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel.DataAnnotations;  
  4. using System.Linq;  
  5. using System.Threading.Tasks;  
  6.   
  7. namespace EmployeeList_EF_DbFirst.Models  
  8. {  
  9.     public class tblSkill  
  10.    {  
  11.     [Key]  
  12.     public int SkillID { getset; }  
  13.   
  14.     [Display(Name = "Type of Skill")]  
  15.     public string Title { getset; }  
  16.    }  
  17. }  

Now double click on EmployeeContext.cs file and declare its properties.

First add following namespace to the class.

using Microsoft.EntityFrameworkCore;

Code of EmployeeContext.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.EntityFrameworkCore;  
  6.   
  7. namespace EmployeeList_EF_DbFirst.Models  
  8. {  
  9.     public class EmployeeContext : DbContext  
  10.     {  
  11.         public EmployeeContext(DbContextOptions<EmployeeContext> options) : base(options)  
  12.         {  
  13.   
  14.         }  
  15.         public DbSet<tblSkill> tblSkills { getset; }  
  16.         public DbSet<tblEmployee> tblEmployees { getset; }  
  17.     }  
  18. }  

To learn more about the DbSet class, visit here.

Setting the Startup Class

Double click on Startup.cs, which is located on the root of the folder.

Add the following namespaces: 

using Microsoft.EntityFrameworkCore;

using EmployeeList_EF_DbFirst.Models;

Above namespaces are used to access models from the Models folder.

For more detail on Microsoft.EntityFrameworkCore namespace visit this here.

Code of Startup.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Builder;  
  6. using Microsoft.AspNetCore.Hosting;  
  7. using Microsoft.Extensions.Configuration;  
  8. using Microsoft.Extensions.DependencyInjection;  
  9. using Microsoft.EntityFrameworkCore;  
  10. using EmployeeList_EF_DbFirst.Models;  
  11.   
  12. namespace EmployeeList_EF_DbFirst  
  13. {  
  14.     public class Startup  
  15.     {  
  16.         public Startup(IConfiguration configuration)  
  17.         {  
  18.             Configuration = configuration;  
  19.         }  
  20.   
  21.         public IConfiguration Configuration { get; }  
  22.   
  23.         // This method gets called by the runtime. Use this method to add services to the container.  
  24.         public void ConfigureServices(IServiceCollection services)  
  25.         {  
  26.             services.AddMvc();  
  27.               
  28.             //Fetching Connection string from APPSETTINGS.JSON  
  29.             var ConnectionString = Configuration.GetConnectionString("MbkDbConstr");  
  30.   
  31.             //Entity Framework  
  32.             services.AddDbContext<EmployeeContext>(options => options.UseSqlServer(ConnectionString));  
  33.         }  
  34.   
  35.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
  36.         public void Configure(IApplicationBuilder app, IHostingEnvironment env)  
  37.         {  
  38.             if (env.IsDevelopment())  
  39.             {  
  40.                 app.UseDeveloperExceptionPage();  
  41.                 app.UseBrowserLink();  
  42.             }  
  43.             else  
  44.             {  
  45.                 app.UseExceptionHandler("/Error");  
  46.             }  
  47.   
  48.             app.UseStaticFiles();  
  49.             app.UseMvcWithDefaultRoute();  
  50.             app.UseMvc(routes =>  
  51.             {  
  52.                 routes.MapRoute(  
  53.                     name: "default",  
  54.                     template: "{controller=Home}/{action=Index}/{id?}");  
  55.             });  
  56.         }  
  57.     }  
  58. }  

STEP 5. Create a Controller

Now right click on Controllers folder. Select Add -> New Item.

Entity Framework Database First In ASP.NET Core

Add a Controller HomeController.cs. Wait a little. 

Entity Framework Database First In ASP.NET Core

Default code of HomeController.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Mvc;  
  6.   
  7. namespace EmployeeList_EF_DbFirst.Controllers  
  8. {  
  9.     public class HomeController : Controller  
  10.     {  
  11.         public IActionResult Index()  
  12.         {  
  13.             return View();  
  14.         }  
  15.     }  
  16. }  

Now, we'll modify HomeController class.

Add the following namespace.

using EmployeeList_EF_DbFirst.Models;

Above namespace used to access the Models.

Add Context things:

private readonly MemberContext _dbcontext;

Create a constructor to the HomeController class:

  1.  public HomeController(EmployeeContext dbContext)  
  2.  {  
  3.             _dbContext = dbContext;  
  4.  }  
  5.   
  6. Index Action method  
  7.  public IActionResult Index()  
  8.  {  
  9.             var _emplst = _dbContext.tblEmployees.  
  10.                             Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,  
  11.                             (e, s) => new EmployeeViewModel  
  12.                             { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,  
  13.                                 PhoneNumber = e.PhoneNumber, Skill = s.Title,  
  14.                                 YearsExperience = e.YearsExperience }).ToList();  
  15.             IList<EmployeeViewModel> emplst  = _emplst;  
  16.             return View(emplst);  
  17.   }  

Full code of HomeController.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Mvc;  
  6. using EmployeeList_EF_DbFirst.Models;  
  7.   
  8. namespace EmployeeList_EF_DbFirst.Controllers  
  9. {  
  10.     public class HomeController : Controller  
  11.     {  
  12.   
  13.         private readonly EmployeeContext _dbContext;  
  14.   
  15.         public HomeController(EmployeeContext dbContext)  
  16.         {  
  17.             _dbContext = dbContext;  
  18.         }  
  19.   
  20.         public IActionResult Index()  
  21.         {  
  22.             var _emplst = _dbContext.tblEmployees.  
  23.                             Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,  
  24.                             (e, s) => new EmployeeViewModel  
  25.                             { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,  
  26.                                 PhoneNumber = e.PhoneNumber, Skill = s.Title,  
  27.                                 YearsExperience = e.YearsExperience }).ToList();  
  28.             IList<EmployeeViewModel> emplst  = _emplst;  
  29.             return View(emplst);  
  30.         }  
  31.     }  
  32. }  

Before we proceed further, let's build the project. Make sure there are no errors.

Entity Framework Database First In ASP.NET Core
Now, let's add a View. 
 
Now right click on the Index method in the above class to add a view.
 
Entity Framework Database First In ASP.NET Core

Now right click again on the Index method method to add a view.

Entity Framework Database First In ASP.NET Core
 
Entity Framework Database First In ASP.NET Core

Fill in the above form as per your Model class and Data Context class. 

Entity Framework Database First In ASP.NET Core

Above NuGet message will appear on the screen.

Code of Index.cshtml

  1. @model IEnumerable<EmployeeList_EF_DbFirst.Models.EmployeeViewModel>  
  2.   
  3. @{  
  4.     ViewData["Title"] = "Index";  
  5. }  
  6.   
  7. <h2>Index</h2>  
  8.   
  9. <p>  
  10.     <a asp-action="Create">Create New</a>  
  11. </p>  
  12. <table class="table">  
  13.     <thead>  
  14.         <tr>  
  15.                 <th>  
  16.                     @Html.DisplayNameFor(model => model.EmployeeName)  
  17.                 </th>  
  18.                 <th>  
  19.                     @Html.DisplayNameFor(model => model.PhoneNumber)  
  20.                 </th>  
  21.                 <th>  
  22.                     @Html.DisplayNameFor(model => model.Skill)  
  23.                 </th>  
  24.                 <th>  
  25.                     @Html.DisplayNameFor(model => model.YearsExperience)  
  26.                 </th>  
  27.             <th></th>  
  28.         </tr>  
  29.     </thead>  
  30.     <tbody>  
  31. @foreach (var item in Model) {  
  32.         <tr>  
  33.             <td>  
  34.                 @Html.DisplayFor(modelItem => item.EmployeeName)  
  35.             </td>  
  36.             <td>  
  37.                 @Html.DisplayFor(modelItem => item.PhoneNumber)  
  38.             </td>  
  39.             <td>  
  40.                 @Html.DisplayFor(modelItem => item.Skill)  
  41.             </td>  
  42.             <td>  
  43.                 @Html.DisplayFor(modelItem => item.YearsExperience)  
  44.             </td>  
  45.             <td>  
  46.                 <a asp-action="Edit" asp-route-id="@item.EmployeeID">Edit</a> |  
  47.                 <a asp-action="Details" asp-route-id="@item.EmployeeID">Details</a> |  
  48.                 <a asp-action="Delete" asp-route-id="@item.EmployeeID">Delete</a>  
  49.             </td>  
  50.         </tr>  
  51. }  
  52.     </tbody>  
  53. </table>  

Now remove the Pages folder from the project.

STEP 6. Build and Run
 
Now, press F5 to build and run the project. The output should look like this:
 
Entity Framework Database First In ASP.NET Core
 
That's all. 

NEXT ARTICLE

You will learn how to bind a Dropdown List in an Asp.Net Core with Entity Framework Core.

Thank you very much.

Happy Coding!