Database First Entity Framework Curd Example
Example Code: https://drive.google.com/file/d/1OrCfLK7lHZPjwmCKCQp5qQOwhiAMCyIU/view?usp=sharin
In Last session we learnt about how to create a model and use linq queries against entities.
In this article we will learn about how to implement select with search, create , update and delete operations using entity framework database first approach.
for this example we are using following database structure –
AreaTbl
DeptTbl
EmpTbl
Create Model using previous article as follows –
The entity class generate will be as follows –
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
public partial class PracticeDBEntities : DbContext {
public PracticeDBEntities()
: base("name=PracticeDBEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder) {
throw new UnintentionalCodeFirstException();
}
public virtual DbSet<AreaTbl> AreaTbls { get; set; }
public virtual DbSet<CustomerTbl> CustomerTbls { get; set; }
public virtual DbSet<DeptTbl> DeptTbls { get; set; }
public virtual DbSet<EmpTbl> EmpTbls { get; set; }
}List Employees
Create test controller and index action with following code –
public ActionResult Index() {
ViewBag.DeptID=new SelectList(entity.DeptTbls.ToList(), "DeptID", "DeptName");
return View(entity.EmpTbls.ToList());
}public ActionResult SearchByName(string EName="") {
ViewBag.DeptID = new SelectList(entity.DeptTbls.ToList(), "DeptID", "DeptName");
if (EName == "")
return View("Index",entity.EmpTbls.ToList());
else {
var v = from t in entity.EmpTbls
where t.EmpName.StartsWith(EName)
select t;
return View("Index",v.ToList());
}
}
public ActionResult SearchByDept(Int64 DeptID=0) {
ViewBag.DeptID = new SelectList(entity.DeptTbls.ToList(), "DeptID", "DeptName");
if (DeptID == 0)
return View("Index", entity.EmpTbls.ToList());
else
{
var v = from t in entity.EmpTbls where t.DeptID == DeptID
select t;
return View("Index",v.ToList());
}
}@model IEnumerable<DbFirstEX.Models.EmpTbl>
@{ Layout = null; }
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<div>
<h2> Emp List </h2>
@Html.ActionLink("New Emp","Create","Test")
<table border="1">
<tr>
<td colspan="3">
@using (Html.BeginForm("SearchByName","Test",FormMethod.Post))
{
@:Emp Name:
@Html.TextBox("EName")
<input type="submit" value="Search" />
}
</td>
<td>
@using (Html.BeginForm("SearchByDept","Test",FormMethod.Post))
{
@:Select Dept:
@Html.DropDownList("DeptID")
<input type="submit" value="Search" />
}
</td>
</tr>
<tr>
<td>@Html.DisplayNameFor(p=>p.EmpID)</td>
<td>@Html.DisplayNameFor(p=>p.EmpName)</td>
<td>@Html.DisplayNameFor(p=>p.MobileNo)</td>
<td>@Html.DisplayNameFor(p=>p.Salary)</td>
<td>@Html.DisplayNameFor(p=>p.DeptTbl.DeptName)</td>
<td>@Html.DisplayNameFor(p=>p.DeptTbl.AreaTbl.AreaName)</td>
</tr>
@foreach (var temp in Model)
{
<tr>
<td>@Html.DisplayFor(p => temp.EmpID)</td>
<td>@Html.DisplayFor(p => temp.EmpName)</td>
<td>@Html.DisplayFor(p => temp.MobileNo)</td>
<td>@Html.DisplayFor(p => temp.Salary)</td>
<td>@Html.DisplayFor(p => temp.DeptTbl.DeptName)</td>
<td>@Html.DisplayFor(p => temp.DeptTbl.AreaTbl.AreaName)</td>
<td>@Html.ActionLink("Edit", "Edit", "Test", new { id = temp.EmpID }, null)</td>
<td>@Html.ActionLink("Delete", "Delete", "Test", new { id = temp.EmpID }, null)</td>
</tr>
}
</table>
@*<table border="1">
<tr>
<td>Emp ID</td>
<td>Emp Name</td>
<td>MobileNo</td>
<td>Salary</td>
<td>DeptName</td>
<td>Area Name</td>
</tr>
@foreach (var temp in Model) {
<tr>
<td> @temp.EmpID</td>
<td>@temp.EmpName</td>
<td>@temp.MobileNo</td>
<td>@temp.Salary</td>
<td>@temp.DeptName</td>
<td>@temp.AreaName</td>
</tr>
</table>*@
</div>
</body>
</html>
Navigation Properties
Navigation properties will be get created in entity model between the class only when tables has relationships in database liek primary key and foregin key.
In Entity Framework, an entity can be related to other entities through an association or relationship. Each relationship contains two ends that describe the entity type and the multiplicity of the type (one, zero-or-one, or many) for the two entities in that relationship.
A navigation property is an optional property on an entity type that allows for navigation from one end of an association to the other end. Unlike other properties, navigation properties do not carry data.
Create New Employee
Now when user click on Create new button it will divert to create action which will divert the user to Create View which will have the following –
[HttpGet]
public ActionResult Create()
{
ViewBag.DeptID = new SelectList(entity.DeptTbls.ToList(), "DeptID", "DeptName");
return View();
}
View Code Will be –
@model DbFirstEX.Models.EmpTbl
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Create</title>
</head>
<body>
<div>
<h2> New Emp </h2>
@using (Html.BeginForm())
{
<div>
@Html.LabelFor(p=>p.EmpName)
<div>
@Html.EditorFor(p=>p.EmpName)
</div>
</div>
<div>
@Html.LabelFor(p=>p.MobileNo)
<div>
@Html.EditorFor(p=>p.MobileNo)
</div>
</div>
<div>
@Html.LabelFor(p=>p.Salary)
<div>
@Html.EditorFor(p=>p.Salary)
</div>
</div>
<div>
@Html.LabelFor(p=>p.DeptTbl.DeptName)
<div>
@Html.DropDownList("DeptID")
</div>
</div>
<div>
<input type="submit" value="Save" />
</div>
}
</div>
</body>
</html>
To add a new record to the table we use following thing for entity framework –
To add a new record in entity framework we first create the object of that type that we want to add in a collection we can do it using add method as follows –
entitobject.collection.add(newentityobject)
Above line will just add new object to the collection will not reflect it in database. To reflect it to database we need to use save changes method as follows –
entitobject.savechanges()
So when we submit the form from above view it will be submitted to Create action decorated using [HttpPost] with following code –
[HttpPost]
public ActionResult Create(EmpTbl rec)
{
entity.EmpTbls.Add(rec);
entity.SaveChanges();
return RedirectToAction("Index");
}Here we do not need to create the object and load the data separately model biding will load that data into rec object.
Edit Employee
In similar way user can also click on edit button in the list of employees it will divert the user to edit action decorated using [HttpGet] here we will find the record, load the record in object and send it to view where we will display it in form and then we will do the modification their and heat the same button –
[HttpGet]
public ActionResult Edit(Int64 id)
{
// var rec = entity.EmpTbls.SingleOrDefault(predicate => predicate.EmpID == id);
var rec = entity.EmpTbls.Find(id);
ViewBag.DeptID = new SelectList(entity.DeptTbls.ToList(), "DeptID", "DeptName",rec.DeptID);
return View(rec);
}View Code for Edit –
@model DbFirstEX.Models.EmpTbl
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Edit</title>
</head>
<body>
<div>
<h2> Edit Emp </h2>
@using (Html.BeginForm())
{
@Html.HiddenFor(p=>p.EmpID)
<div>
@Html.LabelFor(p => p.EmpName)
<div>
@Html.EditorFor(p => p.EmpName)
</div>
</div>
<div>
@Html.LabelFor(p => p.MobileNo)
<div>
@Html.EditorFor(p => p.MobileNo)
</div>
</div>
<div>
@Html.LabelFor(p => p.Salary)
<div>
@Html.EditorFor(p => p.Salary)
</div>
</div>
<div>
@Html.LabelFor(p => p.DeptTbl.DeptName)
<div>
@Html.DropDownList("DeptID")
</div>
</div>
<div>
<input type="submit" value="Save" />
</div>
}
</div>
</body>
</html>
When user click on save button by model binding all data will be available to the object in Edit Action decorated using [HttpPost] as follows –
[HttpPost]
public ActionResult Edit(EmpTbl rec)
{
// var oldrec = entity.EmpTbls.Find(rec.EmpID);
// oldrec.DeptID = rec.DeptID;
// oldrec.EmpName = rec.EmpName;
// oldrec.MobileNo = rec.MobileNo;
// oldrec.Salary = rec.Salary;
// entity.SaveChanges();
//it will find and overwrite that instance.
entity.Entry(rec).State= System.Data.Entity.EntityState.Modified;
entity.SaveChanges();
return RedirectToAction("Index");
}Delete Employee
when user click on delete button in employee list we will call the delete action decorated using HttpGet and will pass the empid to that action using which we will find the record, remove it from collection and call the save changes.
[HttpGet]
public ActionResult Delete(Int64 id)
{
var rec = entity.EmpTbls.Find(id);
entity.EmpTbls.Remove(rec);
entity.SaveChanges();
return RedirectToAction("Index");
}