ASP.NET

.NET MVC5 - Entity Framework Simple Code First Database Approach

There has been a lot of buzz about Entity Framework in the tech world. The reason is simple as an Entity Framework is an overwhelming development communicator between our physical database engines and our code base. Entity Framework in ASP.NET MVC5 platform offers three approaches given below to connect with the database i.e.
  1. Code First Approach.
  2. Model First Approach.
  3. Database First Approach.

Today, I shall be demonstrating a simple Code First approach, using an Entity Framework in ASP.NET MVC5 platform.

Some prerequisites are given below before you proceed further in this tutorial:

  1. Knowledge of ASP.NET MVC5.
  2. Knowledge of HTML.
  3. Knowledge of JavaScript.
  4. Knowledge of Bootstrap.
  5. Knowledge of jQuery.
  6. Knowledge of C# Programming.

You can download the complete source code for this tutorial or you can follow the step by step discussion given below. The sample code is being developed in Microsoft Visual Studio 2013 Ultimate.

Let's begin now.

Step 1

Create a new MVC project in Visual Studio and name it EFCodeFirstMvc.

Step 2

On Models folder, right click and click New Item, as shown below i.e.



Step 3

Now, click ADO.NET Entity Data Model and name it EFCodeFirstDbContext, as shown below i.e.


Step 4

Choose Empty Code First model and click finish, as shown below i.e.



You will see that EFCodeFirstDbContext.cs file has been created under Models folder. We will change it later in this tutorial.

Step 5

Let's create our empty database without any tables into SQL Server database engine. I am using the script given below to create an empty database named db_code_first i.e.

  1. USE [master]  
  2.   
  3. GO  
  4.   
  5. /****** Object: Database [db_code_first] Script Date: 30-Mar-17 9:34:12 PM ******/  
  6.   
  7. IF EXISTS (SELECT name FROM sys.databases WHERE name = N'db_code_first')  
  8.   
  9. DROP DATABASE [db_code_first]  
  10.   
  11. GO  
  12.   
  13. /****** Object: Database [db_code_first] Script Date: 30-Mar-17 9:34:12 PM ******/  
  14.   
  15. IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = N'db_code_first')  
  16.   
  17. BEGIN  
  18.   
  19. CREATE DATABASE [db_code_first]  
  20.   
  21. CONTAINMENT = NONE  
  22.   
  23. ON PRIMARY  
  24.   
  25. ( NAME = N'db_code_first', FILENAME = N'C:\SQL Server DATA Path\db_code_first.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
  26.   
  27. LOG ON  
  28.   
  29. ( NAME = N'db_code_first_log', FILENAME = N'C:\SQL Server DATA Path\db_code_first_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)  
  30.   
  31. END  
  32.   
  33. GO  
  34.   
  35. ALTER DATABASE [db_code_first] SET COMPATIBILITY_LEVEL = 120  
  36.   
  37. GO  
  38.   
  39. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  40.   
  41. begin  
  42.   
  43. EXEC [db_code_first].[dbo].[sp_fulltext_database] @action = 'enable'  
  44.   
  45. end  
  46.   
  47. GO  
  48.   
  49. ALTER DATABASE [db_code_first] SET ANSI_NULL_DEFAULT OFF  
  50.   
  51. GO  
  52.   
  53. ALTER DATABASE [db_code_first] SET ANSI_NULLS OFF  
  54.   
  55. GO  
  56.   
  57. ALTER DATABASE [db_code_first] SET ANSI_PADDING OFF  
  58.   
  59. GO  
  60.   
  61. ALTER DATABASE [db_code_first] SET ANSI_WARNINGS OFF  
  62.   
  63. GO  
  64.   
  65. ALTER DATABASE [db_code_first] SET ARITHABORT OFF  
  66.   
  67. GO  
  68.   
  69. ALTER DATABASE [db_code_first] SET AUTO_CLOSE OFF  
  70.   
  71. GO  
  72.   
  73. ALTER DATABASE [db_code_first] SET AUTO_SHRINK OFF  
  74.   
  75. GO  
  76.   
  77. ALTER DATABASE [db_code_first] SET AUTO_UPDATE_STATISTICS ON  
  78.   
  79. GO  
  80.   
  81. ALTER DATABASE [db_code_first] SET CURSOR_CLOSE_ON_COMMIT OFF  
  82.   
  83. GO  
  84.   
  85. ALTER DATABASE [db_code_first] SET CURSOR_DEFAULT GLOBAL  
  86.   
  87. GO  
  88.   
  89. ALTER DATABASE [db_code_first] SET CONCAT_NULL_YIELDS_NULL OFF  
  90.   
  91. GO  
  92.   
  93. ALTER DATABASE [db_code_first] SET NUMERIC_ROUNDABORT OFF  
  94.   
  95. GO  
  96.   
  97. ALTER DATABASE [db_code_first] SET QUOTED_IDENTIFIER OFF  
  98.   
  99. GO  
  100.   
  101. ALTER DATABASE [db_code_first] SET RECURSIVE_TRIGGERS OFF  
  102.   
  103. GO  
  104.   
  105. ALTER DATABASE [db_code_first] SET DISABLE_BROKER  
  106.   
  107. GO  
  108.   
  109. ALTER DATABASE [db_code_first] SET AUTO_UPDATE_STATISTICS_ASYNC OFF  
  110.   
  111. GO  
  112.   
  113. ALTER DATABASE [db_code_first] SET DATE_CORRELATION_OPTIMIZATION OFF  
  114.   
  115. GO  
  116.   
  117. ALTER DATABASE [db_code_first] SET TRUSTWORTHY OFF  
  118.   
  119. GO  
  120.   
  121. ALTER DATABASE [db_code_first] SET ALLOW_SNAPSHOT_ISOLATION OFF  
  122.   
  123. GO  
  124.   
  125. ALTER DATABASE [db_code_first] SET PARAMETERIZATION SIMPLE  
  126.   
  127. GO  
  128.   
  129. ALTER DATABASE [db_code_first] SET READ_COMMITTED_SNAPSHOT OFF  
  130.   
  131. GO  
  132.   
  133. ALTER DATABASE [db_code_first] SET HONOR_BROKER_PRIORITY OFF  
  134.   
  135. GO  
  136.   
  137. ALTER DATABASE [db_code_first] SET RECOVERY FULL  
  138.   
  139. GO  
  140.   
  141. ALTER DATABASE [db_code_first] SET MULTI_USER  
  142.   
  143. GO  
  144.   
  145. ALTER DATABASE [db_code_first] SET PAGE_VERIFY CHECKSUM  
  146.   
  147. GO  
  148.   
  149. ALTER DATABASE [db_code_first] SET DB_CHAINING OFF  
  150.   
  151. GO  
  152.   
  153. ALTER DATABASE [db_code_first] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )  
  154.   
  155. GO  
  156.   
  157. ALTER DATABASE [db_code_first] SET TARGET_RECOVERY_TIME = 0 SECONDS  
  158.   
  159. GO  
  160.   
  161. ALTER DATABASE [db_code_first] SET DELAYED_DURABILITY = DISABLED  
  162.   
  163. GO  
  164.   
  165. EXEC sys.sp_db_vardecimal_storage_format N'db_code_first', N'ON'  
  166.   
  167. GO  
  168.   
  169. ALTER DATABASE [db_code_first] SET READ_WRITE  
  170.   
  171. GO 

This script is auto-generated from SQL Server. You need to replace SQL Server DATA Path with your SQL Server data storage path in the script given above.

Step 6

On View menu, click Server Explorer, as shown below i.e.



Step 7

Let's modify our database connection settings, so that when we create our schema from the code, it will be automatically updated into SQL Server database. On Server Explorer Window, right click EFCodeFirstDbContext connection and click Modifying connection, as shown below i.e.



Step 8

Provide your connection settings into connection settings Window and click OK i.e.

 
You will notice that your database is empty and there is no table currently existing, as shown below i.e.



Step 9

Open the EFCodeFirstDbContext.cs file and replace the code given below in it i.e.
  1. namespace EFCodeFirstMvc.Models  
  2.   
  3. {  
  4.   
  5. using System;  
  6.   
  7. using System.ComponentModel.DataAnnotations;  
  8.   
  9. using System.Data.Entity;  
  10.   
  11. using System.Linq;  
  12.   
  13. public class EFCodeFirstDbContext : DbContext  
  14.   
  15. {  
  16.   
  17. // Your context has been configured to use a 'EFCodeFirstModel' connection string from your application's  
  18.   
  19. // configuration file (App.config or Web.config). By default, this connection string targets the  
  20.   
  21. // 'EFCodeFirstMvc.Models.EFCodeFirstModel' database on your LocalDb instance.  
  22.   
  23. //  
  24.   
  25. // If you wish to target a different database and/or database provider, modify the 'EFCodeFirstModel'  
  26.   
  27. // connection string in the application configuration file.  
  28.   
  29. public EFCodeFirstDbContext()  
  30.   
  31. : base("name=EFCodeFirstDbContext")  
  32.   
  33. {  
  34.   
  35. }  
  36.   
  37. // Add a DbSet for each entity type that you want to include in your model. For more information  
  38.   
  39. // on configuring and using a Code First model, see http://go.microsoft.com/fwlink/?LinkId=390109.  
  40.   
  41. public virtual DbSet<LoginEntity> LoginEntities { get; set; }  
  42.   
  43. }  
  44.   
  45. public class LoginEntity  
  46.   
  47. {  
  48.   
  49. [Display(Name = "Id")]  
  50.   
  51. public int Id { get; set; }  
  52.   
  53. [Display(Name = "Enter Username")]  
  54.   
  55. public string Username { get; set; }  
  56.   
  57. [Display(Name = "Enter Password")]  
  58.   
  59. public string Password { get; set; }  
  60.   
  61. //[Display(Name = "Enter Full Name")]  
  62.   
  63. //public string FullName { get; set; }  
  64.   
  65. }  
  66.   
  67. } 

In the code given above, we have created our table called LoginEntities and  it tells our DB context about our table with the line given below i.e.

  1. public virtual DbSet<LoginEntity> LoginEntities { get; set; }

Notice, in our class LoginEntity, we have commented out last property. I will come back to this property when we perform table schema changes via code in Code First approach.

Step 10

Now, create a Controller and name it AccountController.cs under Controllers folder. Replace it with the code given below.

  1. using System;  
  2.   
  3. using System.Globalization;  
  4.   
  5. using System.Linq;  
  6.   
  7. using System.Security.Claims;  
  8.   
  9. using System.Threading.Tasks;  
  10.   
  11. using System.Web;  
  12.   
  13. using System.Web.Mvc;  
  14.   
  15. using Microsoft.AspNet.Identity;  
  16.   
  17. using Microsoft.AspNet.Identity.Owin;  
  18.   
  19. using Microsoft.Owin.Security;  
  20.   
  21. using EFCodeFirstMvc.Models;  
  22.   
  23. namespace EFCodeFirstMvc.Controllers  
  24.   
  25. {  
  26.   
  27. [Authorize]  
  28.   
  29. public class AccountController : Controller  
  30.   
  31. {  
  32.   
  33. public AccountController()  
  34.   
  35. {  
  36.   
  37. }  
  38.   
  39. //  
  40.   
  41. // GET: /Account/Register  
  42.   
  43. [AllowAnonymous]  
  44.   
  45. public ActionResult Register()  
  46.   
  47. {  
  48.   
  49. // Initialization.  
  50.   
  51. AccountViewModel model = new AccountViewModel();  
  52.   
  53. ////// DB Context.  
  54.   
  55. ////EFCodeFirstDbContext db = new EFCodeFirstDbContext();  
  56.   
  57. ////// Get Result  
  58.   
  59. ////model.ResultList = db.LoginEntities.Select(p => p).ToList();  
  60.   
  61. return View(model);  
  62.   
  63. }  
  64.   
  65. //  
  66.   
  67. // POST: /Account/Register  
  68.   
  69. [HttpPost]  
  70.   
  71. [AllowAnonymous]  
  72.   
  73. [ValidateAntiForgeryToken]  
  74.   
  75. public ActionResult Register(AccountViewModel model)  
  76.   
  77. {  
  78.   
  79. if (ModelState.IsValid)  
  80.   
  81. {  
  82.   
  83. // DB Context.  
  84.   
  85. EFCodeFirstDbContext db = new EFCodeFirstDbContext();  
  86.   
  87. // Setting.  
  88.   
  89. int idVal = db.LoginEntities.Select(p => p).ToList().Count > 0  
  90.   
  91. ? (db.LoginEntities.OrderByDescending(p => p.Id).Select(p => p.Id).FirstOrDefault()) + 1  
  92.   
  93. : 1;  
  94.   
  95. // Inserting.  
  96.   
  97. model.LoginEntityModel.Id = idVal;  
  98.   
  99. db.LoginEntities.Add(model.LoginEntityModel);  
  100.   
  101. db.SaveChanges();  
  102.   
  103. // Get Result  
  104.   
  105. model.ResultList = db.LoginEntities.Select(p => p).ToList();  
  106.   
  107. }  
  108.   
  109. // If we got this far, something failed, redisplay form  
  110.   
  111. return View(model);  
  112.   
  113. }  
  114.   
  115. }  
  116.   
  117. } 

In the code given above, we have written both HTTP GET and HTTP POST methods for our Register action. You can see some commented out code in HTTP GET method. I will come back to it, while in the HTTP POST method, I have added a simple logic to add my account information into my database by using Code First approach.

Step 11

Create a new model called AccountViewModel.cs under Models folder and replace it with the the code given below.

  1. using System;  
  2.   
  3. using System.Collections.Generic;  
  4.   
  5. using System.Linq;  
  6.   
  7. using System.Web;  
  8.   
  9. namespace EFCodeFirstMvc.Models  
  10.   
  11. {  
  12.   
  13. public class AccountViewModel  
  14.   
  15. {  
  16.   
  17. public LoginEntity LoginEntityModel { get; set; }  
  18.   
  19. public List<LoginEntity> ResultList { get; set; }  
  20.   
  21. }  
  22.   
  23. } 

The code given above is a simple model, which I will attach with my account registration view.

Step 12

Now, create Register.cshtml file under Views\Account folder and replace the code with the code, as shown below.

  1. @model EFCodeFirstMvc.Models.AccountViewModel  
  2.   
  3. @{  
  4.   
  5. ViewBag.Title = "Register";  
  6.   
  7. }  
  8.   
  9. <h2>@ViewBag.Title.</h2>  
  10.   
  11. @using (Html.BeginForm("Register", "Account", FormMethod.Post, new { @class = "form-horizontal", role = "form" }))  
  12.   
  13. {  
  14.   
  15. @Html.AntiForgeryToken()  
  16.   
  17. <h4>Create a new account.</h4>  
  18.   
  19. <hr />  
  20.   
  21. @Html.ValidationSummary("", new { @class = "text-danger" })  
  22.   
  23. <div class="form-group">  
  24.   
  25. @Html.LabelFor(m => m.LoginEntityModel.Username, new { @class = "col-md-2 control-label" })  
  26.   
  27. <div class="col-md-10">  
  28.   
  29. @Html.TextBoxFor(m => m.LoginEntityModel.Username, new { @class = "form-control" })  
  30.   
  31. </div>  
  32.   
  33. </div>  
  34.   
  35. <div class="form-group">  
  36.   
  37. @Html.LabelFor(m => m.LoginEntityModel.Password, new { @class = "col-md-2 control-label" })  
  38.   
  39. <div class="col-md-10">  
  40.   
  41. @Html.PasswordFor(m => m.LoginEntityModel.Password, new { @class = "form-control" })  
  42.   
  43. </div>  
  44.   
  45. </div>  
  46.   
  47. @*<div class="form-group">  
  48.   
  49. @Html.LabelFor(m => m.LoginEntityModel.FullName, new { @class = "col-md-2 control-label" })  
  50.   
  51. <div class="col-md-10">  
  52.   
  53. @Html.TextBoxFor(m => m.LoginEntityModel.FullName, new { @class = "form-control" })  
  54.   
  55. </div>  
  56.   
  57. </div>*@  
  58.   
  59. <div class="form-group">  
  60.   
  61. <div class="col-md-offset-2 col-md-10">  
  62.   
  63. <input type="submit" class="btn btn-default" value="Register" />  
  64.   
  65. </div>  
  66.   
  67. </div>  
  68.   
  69. }  
  70.   
  71. <h2>Result List</h2>  
  72.   
  73. @if (Model.ResultList != null)  
  74.   
  75. {  
  76.   
  77. for (int i = 0; i < Model.ResultList.Count; i++)  
  78.   
  79. {  
  80.   
  81. <div class="row">  
  82.   
  83. <div class="col-md-2">  
  84.   
  85. <p>@Model.ResultList[i].Id</p>  
  86.   
  87. </div>  
  88.   
  89. <div class="col-md-2">  
  90.   
  91. <p>@Model.ResultList[i].Username</p>  
  92.   
  93. </div>  
  94.   
  95. <div class="col-md-2">  
  96.   
  97. <p>@Model.ResultList[i].Password</p>  
  98.   
  99. </div>  
  100.   
  101. @*<div class="col-md-2">  
  102.   
  103. <p>@Model.ResultList[i].FullName</p>  
  104.   
  105. </div>*@  
  106.   
  107. </div>  
  108.   
  109. }  
  110.   
  111. }  
  112.   
  113. @section Scripts {  
  114.   
  115. @Scripts.Render("~/bundles/jqueryval")  
  116.   
  117. } 

In the above code, I have created a simple form for account registration and a result list which will display my data from "LoginEntities" table. You will see commented out property which we will come back to soon.

Step 13

Let's first execute the project and create a sample account. You will see the output, as shown below.

 
 

Step 14

As we have added a new account, let's see, if our table has been created in the database or not, so refresh the database connection in Server Explorer and expand Tables folder. You will notice that two tables have been created, as shown below i.e.

You will notice that there are two tables, which have been created; where one is the table; which we have defined at the code level and other is the migration history table. The migration history table will keep the history version of the changes, which you have made into the database tables and its structure.

When you expand your table, you will see your defined columns via code, as shown below.



Step 15

Now, let's add a new property into our table via code and by Entity Framework provided migration commands, we will signal our physical database about schema changes. Thus, uncomment all the code, which I have mentioned previously about being commented out in the Models, Views & Controllers folders.

Step 16

Let's signal our SQL Server about this schema. Prior to it, make sure that your NuGet Package is installed. If it is not installed, then install it via Tools-> Extensions & Updates, as shown below.

Step 17

Now, open Package Manager Console via Tools->Package Manager Console and type Enable-Migrations command. Hit enter and you will see the details, as shown below.



You will notice that a Migration folder has been created with a history version .cs file and configuration.cs file, which will maintain migration history at the code level and migration command settings in configuration file.



Step 18

Now, enter Add-Migration AddFullName command and you will see the result, as shown below.





In the command given above, notice that at AddFullName portion, we have written the name of our new column property after Add keyword i.e. FullName.

Step 19

Finally, we will update SQL Server database about the schema changes by entering Update-Database command.





Step 20

Refresh SQL Server connection and you will see that our new column is being reflected in SQL Server database, as shown below.


Step 21

Now, execute the project and register new account. You will see the result given below.







Step 22

Checkout your database in SQL Server. It will show your register accounts and migration history given below.



Conclusion

In this article, you will learn about an Entity Framework Code First approach in ASP.NET MVC5 platform. You will also learn about creating table schema via code and making table schema changes via code alongwith Entity Framework migration commands.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs