In my previous article I explained what Code First Approach in MVC with Entity Framework is. Now in this article I am going to show how we can create Primary Key and Foreign Key using code first approach in mvc with entity framework.
Here I am going to create 2 tables Students and Course. Both tables will have primary key and Student table will reference course table with CourseID as foreign key.
Now open Visual Studio 2012 -> New Project.
Image 1.
Image 2.
Now Right Click On Project Solution Explorer -> Click On Manage NuGet Packages.
Image 3.
Now here in this Project I am going to create 2 Tables One is Student table and second one is Course Table. Both Table Will have Primary Key and Student and Course table will have a foreign key CourseID.
So Here I am going to create 2 classes in Model Folder.
Student.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;
namespace CodeFirstApproachWithPrimaryForeignKey.Models
{
public class Student
{
public Student()
{
}
[Key]
public int Id { get; set; }
public string Name { get; set; }
[DataType(DataType.Date),
DisplayFormat(DataFormatString = "{0:dd/MM/yy}",
ApplyFormatInEditMode = true)]
public DateTime? DateOfBirth { get; set; }
public string EmailId { get; set; }
public string Address { get; set; }
public string City { get; set; }
public int CourseId { get; set; }
public Course Course { get; set; } // Navigation Property
[NotMapped]
public string CourseName { get; set; }
}
}
Here CourseName will not be a field because I set it as [NotMapped].
Now Course.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace CodeFirstApproachWithPrimaryForeignKey.Models
{
public class Course
{
public Course()
{
}
[Key]
public int CourseId { get; set; }
public string CourseName { get; set; }
public List<Student> Students { get; set; } // Navigation property
}
}
Now Again Right Click on Model Folder And Add New Class: StudentDBContext.cs
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace CodeFirstApproachWithPrimaryForeignKey.Models
{
public class StudentDBContext : DbContext
{
public StudentDBContext()
: base("StudentDbContext")
{
}
public DbSet<Student> Students { get; set; }
public DbSet<Course> Courses { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Course>().HasKey(p => p.CourseId);
modelBuilder.Entity<Course>().Property(c => c.CourseId)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Student>().HasKey(b => b.Id);
modelBuilder.Entity<Student>().Property(b => b.Id)
.HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
modelBuilder.Entity<Student>().HasRequired(p => p.Course)
.WithMany(b => b.Students).HasForeignKey(b => b.CourseId);
base.OnModelCreating(modelBuilder);
}
}
}
Here In this StudentDBContext.cs you can see I am using OnModelCreating. Here I define what will be primary key and what will be foreign Key.
Now Right Click on Controller -> Add -> Controller -> Student
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CodeFirstApproachWithPrimaryForeignKey.Models;
namespace CodeFirstApproachWithPrimaryForeignKey.Controllers
{
public class StudentController : Controller
{
//
// GET: /Student/
StudentDBContext objContext;
public StudentController()
{
objContext = new StudentDBContext();
}
#region List and Details student
public ActionResult Index()
{
var students = (from p in objContext.Students
join f in objContext.Courses
on p.CourseId equals f.CourseId
select new
{
Id = p.Id,
Name = p.Name,
DateOfBirth = p.DateOfBirth,
EmailId = p.EmailId,
Address = p.Address,
City = p.City,
CourseName = f.CourseName
}).ToList()
.Select(x => new Student()
{
Id = x.Id
Name = x.Name,
DateOfBirth = x.DateOfBirth,
EmailId = x.EmailId,
Address = x.Address,
City = x.City,
CourseName = x.CourseName
});
return View(students.ToList());
}
public ViewResult Details(int id)
{
//Student student = objContext.Students.Where(x => x.Id == id).SingleOrDefault();
var student = (from p in objContext.Students
join f in objContext.Courses
on p.CourseId equals f.CourseId
where (p.Id==id)
select new
{
Id = p.Id,
Name = p.Name,
DateOfBirth = p.DateOfBirth,
EmailId = p.EmailId,
Address = p.Address,
City = p.City,
CourseName = f.CourseName
}).ToList()
.Select(x => new Student()
{
Id = x.Id,
Name = x.Name,
DateOfBirth = x.DateOfBirth,
EmailId = x.EmailId,
Address = x.Address,
City = x.City,
CourseName = x.CourseName
}).SingleOrDefault();
return View(student);
}
#endregion
#region Create student
public ActionResult Create()
{
var data = from p in objContext.Courses
select new
{
CourseID = p.CourseId,
CourseName = p.CourseName
};
SelectList list = new SelectList(data, "CourseID", "CourseName");
ViewBag.Roles = list;
return View(new Student());
}
[HttpPost]
public ActionResult Create(Student student)
{
objContext.Students.Add(student);
objContext.SaveChanges();
return RedirectToAction("Index");
}
#endregion
#region Edit student
public ActionResult Edit(int id)
{
var data = from p in objContext.Courses
select new
{
CourseID = p.CourseId,
CourseName = p.CourseName
};
SelectList list = new SelectList(data, "CourseID", "CourseName");
ViewBag.Roles = list;
Student student = objContext.Students.Where(x => x.Id == id).SingleOrDefault();
return View(student);
}
[HttpPost]
public ActionResult Edit(Student model)
{
Student student = objContext.Students.Where(x => x.Id == model.Id).SingleOrDefault();
if (student != null)
{
objContext.Entry(student).CurrentValues.SetValues(model);
objContext.SaveChanges();
return RedirectToAction("Index");
}
return View(model);
}
#endregion
#region Delete student
public ActionResult Delete(int id)
{
Student student = objContext.Students.Find(id);
return View(student);
}
[HttpPost]
public ActionResult Delete(int id, Student model)
{
var student = objContext.Students.Where(x => x.Id == id).SingleOrDefault();
if (student != null)
{
objContext.Students.Remove(student);
objContext.SaveChanges();
}
return RedirectToAction("Index");
}
#endregion
}
}
Views Are for Create/Read/Details/Edit/Delete:
Index.cshtml
@model IEnumerable<CodeFirstApproachWithPrimaryForeignKey.Models.Student>
<h2>Showing All Students</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table style="width:100%;">
<tr>
<th >
@Html.DisplayNameFor(model => model.Name)
</th>
<th style="width:20%;">
@Html.DisplayNameFor(model => model.DateOfBirth)
</th>
<th style="width:20%;">
@Html.DisplayNameFor(model => model.EmailId)
</th>
<th>
@Html.DisplayNameFor(model => model.Address)
</th>
<th>
@Html.DisplayNameFor(model => model.City)
</th>
<th>
@Html.DisplayNameFor(model => model.CourseName)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.Name)
</td>
<td>
@Html.DisplayFor(modelItem => item.DateOfBirth)
</td>
<td>
@Html.DisplayFor(modelItem => item.EmailId)
</td>
<td>
@Html.DisplayFor(modelItem => item.Address)
</td>
<td>
@Html.DisplayFor(modelItem => item.City)
</td>
<td>
@Html.DisplayFor(modelItem => item.CourseName)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
@Html.ActionLink("Details", "Details", new { id=item.Id }) |
@Html.ActionLink("Delete", "Delete", new { id=item.Id })
</td>
</tr>
}
</table>
Create.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Student
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm())
{
@Html.ValidationSummary(true)
<fieldset>
<legend>Student</legend>
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.DateOfBirth)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.DateOfBirth)
@Html.ValidationMessageFor(model => model.DateOfBirth)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.EmailId)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmailId)
@Html.ValidationMessageFor(model => model.EmailId)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Address)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Address)
@Html.ValidationMessageFor(model => model.Address)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.City)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.CourseId)
</div>
<div class="editor-field">
@Html.DropDownListFor(m=>m.CourseId, ViewBag.Roles as SelectList, "Select ...", new { @class = "myClass", style = "width: 250px;" })
@* @Html.EditorFor(model => model.CourseId)*@
@Html.ValidationMessageFor(model => model.CourseId)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
Delete.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Student
@{
ViewBag.Title = "Delete";
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<table>
<tr>
<td>@Html.DisplayNameFor(model => model.Name)</td>
<td>@Html.DisplayFor(model => model.Name)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.DateOfBirth)</td>
<td>@Html.DisplayFor(model => model.DateOfBirth)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.EmailId)</td>
<td>@Html.DisplayFor(model => model.EmailId)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.Address)</td>
<td>@Html.DisplayFor(model => model.Address)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.City)</td>
<td>@Html.DisplayFor(model => model.City)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.CourseName)</td>
<td>@Html.DisplayFor(model => model.CourseName)</td>
</tr>
<tr style="background-color: orange; padding: 25px;">
<td></td>
<td>@Html.ActionLink("Edit", "Edit", new { id = Model.Id }) |
@Html.ActionLink("Back to List", "Index")</td>
</tr>
</table>
@using (Html.BeginForm())
{
<table>
<tr style="background-color: orange; padding: 25px;">
<td></td>
<td>
<input type="submit" value="Delete" />
@Html.ActionLink("Back to List", "Index")
</td>
</tr>
</table>
}
Details.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Student
@{
ViewBag.Title = "Details";
}
<h2>Details Of Student</h2>
<table>
<tr>
<td>@Html.DisplayNameFor(model => model.Name)</td>
<td>@Html.DisplayFor(model => model.Name)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.DateOfBirth)</td>
<td>@Html.DisplayFor(model => model.DateOfBirth)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.EmailId)</td>
<td>@Html.DisplayFor(model => model.EmailId)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.Address)</td>
<td>@Html.DisplayFor(model => model.Address)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.City)</td>
<td>@Html.DisplayFor(model => model.City)</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(model => model.CourseName)</td>
<td>@Html.DisplayFor(model => model.CourseName)</td>
</tr>
<tr style="background-color: orange; padding: 25px;">
<td></td>
<td>@Html.ActionLink("Edit", "Edit", new { id = Model.Id }) |
@Html.ActionLink("Back to List", "Index")</td>
</tr>
</table>
Edit.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Student
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Student</legend>
@Html.HiddenFor(model => model.Id)
<div class="editor-label">
@Html.LabelFor(model => model.Name)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Name)
@Html.ValidationMessageFor(model => model.Name)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.DateOfBirth)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.DateOfBirth)
@Html.ValidationMessageFor(model => model.DateOfBirth)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.EmailId)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.EmailId)
@Html.ValidationMessageFor(model => model.EmailId)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.Address)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.Address)
@Html.ValidationMessageFor(model => model.Address)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.City)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.City)
@Html.ValidationMessageFor(model => model.City)
</div>
<div class="editor-label">
@Html.LabelFor(model => model.CourseId)
</div>
<div class="editor-field">
@Html.DropDownListFor(m=>m.CourseId, ViewBag.Roles as SelectList, "Select ...", new { @class = "myClass", style = "width: 250px;" })
@* @Html.EditorFor(model => model.CourseId)*@
@Html.ValidationMessageFor(model => model.CourseId)
</div>
<p>
<input type="submit" value="Save" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
Now Againg Right Click On Controller -> Add-> Controller -> Course
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using CodeFirstApproachWithPrimaryForeignKey.Models;
namespace CodeFirstApproachWithPrimaryForeignKey.Controllers
{
public class CourseController : Controller
{
//
// GET: /Course/
StudentDBContext objContext;
public CourseController()
{
objContext = new StudentDBContext();
}
#region List and Details course
public ActionResult Index()
{
var courses = objContext.Courses.ToList();
return View(courses);
}
public ViewResult Details(int id)
{
Course course = objContext.Courses.Where(x => x.CourseId == id).SingleOrDefault();
return View(course);
}
#endregion
#region Create course
public ActionResult Create()
{
return View(new Course());
}
[HttpPost]
public ActionResult Create(Course course)
{
objContext.Courses.Add(course);
objContext.SaveChanges();
return RedirectToAction("Index");
}
#endregion
#region edit course
public ActionResult Edit(int id)
{
Course course = objContext.Courses.Where(x => x.CourseId == id).SingleOrDefault();
return View(course);
}
[HttpPost]
public ActionResult Edit(Course model)
{
Course course = objContext.Courses.Where(x => x.CourseId == model.CourseId).SingleOrDefault();
if (course != null)
{
objContext.Entry(course).CurrentValues.SetValues(model);
objContext.SaveChanges();
return RedirectToAction("Index");
}
return View(model);
}
#endregion
#region Delete course
public ActionResult Delete(int id)
{
Course course = objContext.Courses.Find(id);
return View(course);
}
[HttpPost]
public ActionResult Delete(int id, Course model)
{
var course = objContext.Courses.Where(x => x.CourseId == id).SingleOrDefault();
if (course != null)
{
objContext.Courses.Remove(course);
objContext.SaveChanges();
}
return RedirectToAction("Index");
}
#endregion
}
}
View are for Course: Index.cshtml
@model IEnumerable<CodeFirstApproachWithPrimaryForeignKey.Models.Course>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table>
<tr>
<th>
@Html.DisplayNameFor(model => model.CourseName)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.CourseName)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { id=item.CourseId }) |
@Html.ActionLink("Details", "Details", new { id=item.CourseId }) |
@Html.ActionLink("Delete", "Delete", new { id=item.CourseId })
</td>
</tr>
}
</table>
Create.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Course
@{
ViewBag.Title = "Create";
}
<h2>Create</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Course</legend>
<div class="editor-label">
@Html.LabelFor(model => model.CourseName)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.CourseName)
@Html.ValidationMessageFor(model => model.CourseName)
</div>
<p>
<input type="submit" value="Create" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
Delete.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Course
@{
ViewBag.Title = "Delete";
}
<h2>Delete</h2>
<h3>Are you sure you want to delete this?</h3>
<fieldset>
<legend>Course</legend>
<div class="display-label">
@Html.DisplayNameFor(model => model.CourseName)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.CourseName)
</div>
</fieldset>
@using (Html.BeginForm()) {
<p>
<input type="submit" value="Delete" /> |
@Html.ActionLink("Back to List", "Index")
</p>
}
Details.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Course
@{
ViewBag.Title = "Details";
}
<h2>Details</h2>
<fieldset>
<legend>Course</legend>
<div class="display-label">
@Html.DisplayNameFor(model => model.CourseName)
</div>
<div class="display-field">
@Html.DisplayFor(model => model.CourseName)
</div>
</fieldset>
<p>
@Html.ActionLink("Edit", "Edit", new { id=Model.CourseId }) |
@Html.ActionLink("Back to List", "Index")
</p>
Edit.cshtml
@model CodeFirstApproachWithPrimaryForeignKey.Models.Course
@{
ViewBag.Title = "Edit";
}
<h2>Edit</h2>
@using (Html.BeginForm()) {
@Html.ValidationSummary(true)
<fieldset>
<legend>Course</legend>
@Html.HiddenFor(model => model.CourseId)
<div class="editor-label">
@Html.LabelFor(model => model.CourseName)
</div>
<div class="editor-field">
@Html.EditorFor(model => model.CourseName)
@Html.ValidationMessageFor(model => model.CourseName)
</div>
<p>
<input type="submit" value="Save" />
</p>
</fieldset>
}
<div>
@Html.ActionLink("Back to List", "Index")
</div>
@section Scripts {
@Scripts.Render("~/bundles/jqueryval")
}
Now Run The Application:
Image 4.
Now see your Data base:
Image 5.
Click on Course -> Create New
Image 6.
Showing All Course List:
Image 7.
Click On Edit Course:
Image 8.
Detail of any course:
Image 9.
Delete any existing Course:
Image 10.
Now Click on Student-> Create New
Image 11.
List of all students.
Image 12.
Edit any student record:
Image 13.
Details of any student:
Image 14.
Delete any student record:
Image 15.
Now see your Data Base. See records in both tables and see primary key and foreign key existence:
Image 16.
If you want to run this application in your machine then just change connection string in web.config file.