Entity Framework Database First In ASP.NET Core

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. 

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. 

CREATE TABLE [dbo].[tblEmployees](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [varchar](50) NULL,
    [PhoneNumber] [varchar](50) NULL,
    [SkillID] [int] null,
    [YearsExperience] [int] null,
PRIMARY KEY CLUSTERED
(
    [EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tblSkills](
    [SkillID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [SkillID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

insert into tblSkills
(Title) values('Visual Foxpro')

insert into tblSkills
(Title) values('C#')

insert into tblSkills
(Title) values('VB.NET')

insert into tblSkills
(Title) values('Delphi')

insert into tblSkills
(Title) values('Java')

insert into tblSkills
(Title) values('Power Builder')

insert into tblSkills
(Title) values('COBOL')

insert into tblSkills
(Title) values('Python')

Insert Into tblEmployees
(EmployeeName,PhoneNumber,SkillID,YearsExperience)
values ('Suhana Kalla','9869569634',2,'11')

Insert Into tblEmployees
(EmployeeName,PhoneNumber,SkillID,YearsExperience)
values ('Ashish Kalla','9869166077',8,'14')

Insert Into tblEmployees
(EmployeeName,PhoneNumber,SkillID,YearsExperience)
values ('Manoj Kalla','9869569634',1,'24')

Insert Into tblEmployees
(EmployeeName,PhoneNumber,SkillID,YearsExperience)
values ('Nirupama Kalla','9969359746',6,'20')

Insert Into tblEmployees
(EmployeeName,PhoneNumber,SkillID,YearsExperience)
values ('Rajesh Bohra','9869166012',7,'28')

Insert Into tblEmployees
(EmployeeName,PhoneNumber,SkillID,YearsExperience)
values ('Murli Vyas','9261166012',5,'18')

The data looks like the following: 

 

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.  

Image 1

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

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:

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.

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

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "ConnectionStrings": {
    "MbkDbConstr": "Data Source=(localdb)\\MBK;Database=MbkTest;"
  }
}

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.

Image No.5

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

Image No.6

Select Class in the items list and name it EmployeeContext.

Image No.7

Select Class in the items list and name it tblEmployee.

Image No.8

Select Class in the items list and name it tblSkill.

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.ComponentModel.DataAnnotations;

namespace EmployeeList_EF_DbFirst.Models
{
    public class EmployeeViewModel
{
[Key]
        public int EmployeeID { get; set; }

        public string EmployeeName { get; set; }

        public string PhoneNumber { get; set; }

        public string Skill { get; set; }

        public int YearsExperience { get; set; }
    }
}

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

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeList_EF_DbFirst.Models
{
    public class tblEmployee
   {
    [Key]
    public int EmployeeID { get; set; }

    [Display(Name = "Employee Name")]
    public string EmployeeName { get; set; }

    [Display(Name = "Contact Number")]
    public string PhoneNumber { get; set; }

    [Display(Name = "Your Skill")]
    public int SkillID { get; set; }

    [Display(Name = "Years of Experience")]
    public int YearsExperience { get; set; }
   }
}

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

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace EmployeeList_EF_DbFirst.Models
{
    public class tblSkill
   {
    [Key]
    public int SkillID { get; set; }

    [Display(Name = "Type of Skill")]
    public string Title { get; set; }
   }
}

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;

namespace EmployeeList_EF_DbFirst.Models
{
    public class EmployeeContext : DbContext
    {
        public EmployeeContext(DbContextOptions<EmployeeContext> options) : base(options)
        {

        }
        public DbSet<tblSkill> tblSkills { get; set; }
        public DbSet<tblEmployee> tblEmployees { get; set; }
    }
}

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using EmployeeList_EF_DbFirst.Models;

namespace EmployeeList_EF_DbFirst
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc();

            //Fetching Connection string from APPSETTINGS.JSON
            var ConnectionString = Configuration.GetConnectionString("MbkDbConstr");

            //Entity Framework
            services.AddDbContext<EmployeeContext>(options => options.UseSqlServer(ConnectionString));
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseBrowserLink();
            }
            else
            {
                app.UseExceptionHandler("/Error");
            }

            app.UseStaticFiles();
            app.UseMvcWithDefaultRoute();
            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

STEP 5. Create a Controller

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

Add a Controller HomeController.cs. Wait a little. 

Default code of HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;

namespace EmployeeList_EF_DbFirst.Controllers
{
    public class HomeController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }
    }
}

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:

 public HomeController(EmployeeContext dbContext)
 {
            _dbContext = dbContext;
 }

Index Action method
 public IActionResult Index()
 {
            var _emplst = _dbContext.tblEmployees.
                            Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,
                            (e, s) => new EmployeeViewModel
                            { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,
                                PhoneNumber = e.PhoneNumber, Skill = s.Title,
                                YearsExperience = e.YearsExperience }).ToList();
            IList<EmployeeViewModel> emplst  = _emplst;
            return View(emplst);
  }

Full code of HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using EmployeeList_EF_DbFirst.Models;

namespace EmployeeList_EF_DbFirst.Controllers
{
    public class HomeController : Controller
    {

        private readonly EmployeeContext _dbContext;

        public HomeController(EmployeeContext dbContext)
        {
            _dbContext = dbContext;
        }

        public IActionResult Index()
        {
            var _emplst = _dbContext.tblEmployees.
                            Join(_dbContext.tblSkills, e => e.SkillID, s => s.SkillID,
                            (e, s) => new EmployeeViewModel
                            { EmployeeID = e.EmployeeID, EmployeeName = e.EmployeeName,
                                PhoneNumber = e.PhoneNumber, Skill = s.Title,
                                YearsExperience = e.YearsExperience }).ToList();
            IList<EmployeeViewModel> emplst  = _emplst;
            return View(emplst);
        }
    }
}

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

Now, let's add a View. 

 

Now right click on the Index method in the above class to add a view.

 

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

 

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

Above NuGet message will appear on the screen.

Code of Index.cshtml

@model IEnumerable<EmployeeList_EF_DbFirst.Models.EmployeeViewModel>

@{
    ViewData["Title"] = "Index";
}

<h2>Index</h2>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
                <th>
                    @Html.DisplayNameFor(model => model.EmployeeName)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.PhoneNumber)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.Skill)
                </th>
                <th>
                    @Html.DisplayNameFor(model => model.YearsExperience)
                </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.EmployeeName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.PhoneNumber)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Skill)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.YearsExperience)
            </td>
            <td>
                <a asp-action="Edit" asp-route-id="@item.EmployeeID">Edit</a> |
                <a asp-action="Details" asp-route-id="@item.EmployeeID">Details</a> |
                <a asp-action="Delete" asp-route-id="@item.EmployeeID">Delete</a>
            </td>
        </tr>
}
    </tbody>
</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:

 

 

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!