I am explaining the technique - "Database First Approach with CRUD Operation Using Stored Procedure with EF6" that worked best for me after lot of experimentation. The whole process includes steps at Database end and then at Application End, as described below for an entity - Employee (emp_Oct1 in my case, with eid & isdeletede as its Composite Keys):
DATABASE END
Step 1. I designed Schema as below (with composite keys):
Step 2. I created Stored Procedures in SQL Server for Create,Update & Soft Deletion for my entity "emp_Oct1" as shown below:
---- For SOFT DELETION -----
CREATE PROCEDURE [dbo].[sp_deleteempt1_Oc1]
-- Add the parameters for the stored procedure here
@eid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE emp_Oct1 SET isdeletede='1' WHERE eid=@eid
END
----- For CREATE & UPDATE -----
CREATE PROCEDURE sp_AddEditEmp_Oct1
-- Add the parameters for the stored procedure here
@eid int=0, --Named Parameter setting initial value with the parameter
@name varchar(100),
@cpf varchar(50),
@Designation varchar(50),
@Contact bigint,
@email varchar(255),
@emplocid int,
@Pwd varchar(100),
@Remarks varchar(200),
@isdeletede bit,
@elast_mod_dt datetime2,
@elast_mod_by varchar(255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
IF(@eid>0)
BEGIN
SET NOCOUNT ON;
UPDATE emp_Oct1 SET
name=@name,cpf=@cpf,Designation=@Designation,Contact=@Contact,email=@email, emplocid=@emplocid,
Pwd=@Pwd ,Remarks=@Remarks,isdeletede=@isdeletede,elast_mod_dt=CURRENT_TIMESTAMP,elast_mod_by=@elast_mod_by
WHERE eid=@eid
END
ELSE
BEGIN
INSERT INTO emp_Oct1(name,cpf,Designation,Contact,email,emplocid,Pwd,Remarks,isdeletede,elast_mod_dt,elast_mod_by)
VALUES(@name,@cpf,@Designation,@Contact,@email,@emplocid,@Pwd,@Remarks,@isdeletede,CURRENT_TIMESTAMP,@elast_mod_by)
SELECT @eid = SCOPE_IDENTITY()
END
SELECT @eid as eid
END
GO
APPLICATION END
Step 1. Create MVC Application, Add Entity Data Model, as described here
Step 2. I implemented my CRUD through Stored Procedures with the main idea from this sourceCRUD Operations using Stored Procedure in Entity Framework.
Step 3. Controller Code
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;
using llpv5;
using System.Data.Entity.Validation;
namespace llpv5.Controllers
{
public class emp_Oct1Controller : Controller
{
private MyEntity db = new MyEntity();
// Emp with their respective location can see only the data of their location
public ActionResult resplocemp_linq()
{
var usercpf = System.Web.HttpContext.Current.User.Identity.Name;
var userlocid = (from e in db.emp_Oct1 where e.cpf == usercpf select e.emplocid).FirstOrDefault(); //To find location ID of the logged in user
var linkresult = (from l in db.emp_Oct1
join loc in db.loc_Oct1
on l.emplocid equals loc.locid
where l.emplocid == userlocid
&& l.isdeletede == false
&& l.name != "Admin"
select l).ToList();
if (System.Web.HttpContext.Current.User.Identity.IsAuthenticated)
{
linkresult = (from l in db.emp_Oct1
join loc in db.loc_Oct1
on l.emplocid equals loc.locid
where l.emplocid == userlocid
&& l.isdeletede == false
&& l.name != "Admin"
select l).ToList();
}
return View(linkresult.ToList());
}
//For Admin only - Deleted items
// GET: emp_sd1
public ActionResult isdeindex()
{
var emp_sd1 = db.emp_Oct1.Include(e => e.loc_Oct1).Where(e => e.isdeletede).Where(e => e.name != "Admin");
return View(emp_sd1.ToList());
}
// GET: emp_Oct1
public ActionResult Index()
{
var emp_Oct1 = db.emp_Oct1.Include(e => e.loc_Oct1).Where(e => !e.isdeletede).Where(e => e.name != "Admin");
return View(emp_Oct1.ToList());
}
// GET: emp_Oct1/Details/5
public ActionResult Details(int? eid, bool isdeletede)
{
if (eid == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
if (emp_Oct1 == null)
{
return HttpNotFound();
}
return View(emp_Oct1);
}
// GET: emp_Oct1/Create
public ActionResult Create()
{
ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName");
return View();
}
// POST: emp_Oct1/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "eid,name,cpf,Designation,Contact,email,emplocid,Pwd,Remarks,isdeletede,elast_mod_dt,elast_mod_by")] emp_Oct1 emp_Oct1)
{
if (ModelState.IsValid)
{
emp_Oct1.elast_mod_by = System.Web.HttpContext.Current.User.Identity.Name;
db.emp_Oct1.Add(emp_Oct1);
db.SaveChanges();
return RedirectToAction("gindex_filter1");
}
ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName", emp_Oct1.emplocid);
return View(emp_Oct1);
}
// GET: emp_Oct1/Edit/5
public ActionResult Edit(int? eid, bool isdeletede)
{
if (eid == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
if (emp_Oct1 == null)
{
return HttpNotFound();
}
ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName", emp_Oct1.emplocid);
return View(emp_Oct1);
}
// POST: emp_Oct1/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "eid,name,cpf,Designation,Contact,email,emplocid,Pwd,Remarks,isdeletede,elast_mod_dt,elast_mod_by")] emp_Oct1 emp_Oct1)
{
if (ModelState.IsValid)
{
emp_Oct1.elast_mod_by = System.Web.HttpContext.Current.User.Identity.Name;
db.Entry(emp_Oct1).State = EntityState.Modified;
return RedirectToAction("gindex_filter1");
}
ViewBag.emplocid = new SelectList(db.loc_Oct1, "locid", "LocationName", emp_Oct1.emplocid);
return View(emp_Oct1);
}
// GET: emp_Oct1/Delete/5
public ActionResult Delete(int? eid, bool isdeletede)
{
if (eid == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
if (emp_Oct1 == null)
{
return HttpNotFound();
}
return View(emp_Oct1);
}
// POST: emp_Oct1/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int eid, bool isdeletede)
{
emp_Oct1 emp_Oct1 = db.emp_Oct1.Find(eid, isdeletede);
db.emp_Oct1.Remove(emp_Oct1);
db.SaveChanges();
return RedirectToAction("gindex_filter1");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
Step 4. Make appropriate changes in the auto-generated Views, using this link: how to map composite key in CRUD functionality
Sample View Code as follows:
@model IEnumerable<llpv5.emp_Oct1>
@{
ViewBag.Title = "Location-specific Users";
}
<center><h2>Location-specific Users</h2></center>
<table class="table table-hover table-bordered table-striped">
<tr class="table-primary">
<th>
@Html.DisplayNameFor(model => model.cpf)
</th>
<th>
@Html.DisplayNameFor(model => model.name)
</th>
<th>
@Html.DisplayNameFor(model => model.Designation)
</th>
<th>
@Html.DisplayNameFor(model => model.Contact)
</th>
<th>
@Html.DisplayNameFor(model => model.Pwd)
</th>
<th>
@Html.DisplayNameFor(model => model.loc_Oct1.LocationName)
</th>
<th></th>
</tr>
@foreach (var item in Model)
{
<tr>
<td>
@Html.DisplayFor(modelItem => item.cpf)
</td>
<td>
@Html.DisplayFor(modelItem => item.name)
</td>
<td>
@Html.DisplayFor(modelItem => item.Designation)
</td>
<td>
@Html.DisplayFor(modelItem => item.Contact)
</td>
<td>
@if (User.IsInRole("Admin"))
{ @Html.DisplayFor(modelItem => item.Pwd) }
</td>
<td>
@Html.DisplayFor(modelItem => item.loc_Oct1.LocationName)
</td>
<td>
@Html.ActionLink("Details", "Details", new { eid = item.eid, isdeletede = item.isdeletede }) <text> | </text>
@Html.ActionLink("Edit", "Edit", new { eid = item.eid, isdeletede = item.isdeletede }) <text> | </text>
@Html.ActionLink("Delete", "Delete", new { eid = item.eid, isdeletede = item.isdeletede })
</td>
</tr>
}
</table>
