ASP.NET MVC coding with SQL Server database with composite keys & update cascade feature
I am using EF 6 with a database-first approach for my ASP.NET MVC project as I am more comfortable with SQL than C# & LINQ coding (using SQL Server 2008, Visual Studio 2015).
I have 4 tables in SQL Server each for :
Link, Employee, MapLinkEmp, Location
viz, link_sd1, emp_sd1, MapEmpLink_sd1, loc_sd1 respectively
A Link has many-to-many relations with Emp table for which I created another table referencing the composite primary keys of both the tables.
In order to implement soft deletes, I researched and ultimately followed the approach mentioned in Entity framework override save changes to soft delete entities, which suggests composite keys for which I referred to how to map composite key in CRUD functionality
At database end of my project:
I implemented cascading feature using composite keys in SQL Server
But at Application end :
I am unable to incorporate CRUD + other features (including soft delete, location-wise grouping of Emp & Link data, Emp with their respective location can update only their emp data and link data etc) when there are composite keys in my entities.
SQL Server code for cascading :
ALTER TABLE emplinkloc_sd1
ADD emid int
ALTER TABLE emplinkloc_sd1
ADD lmid int
ALTER TABLE emplinkloc_sd1
ADD isdl bit DEFAULT 0 NOT NULL
ALTER TABLE emplinkloc_sd1
ADD isde bit DEFAULT 0 NOT NULL
ALTER TABLE emplinkloc_sd1
ADD PRIMARY KEY (melid)
ALTER TABLE emplinkloc_sd1
ADD CONSTRAINT FK_ell_e_sd1
FOREIGN KEY (emid, isde) REFERENCES emp_sd1(eid, isdeleted)
ON UPDATE CASCADE;
ALTER TABLE emplinkloc_sd1
ADD CONSTRAINT FK_ell_l_sd1
FOREIGN KEY (lmid, isdl) REFERENCES link_sd1(lid, isdeleted)
ON UPDATE CASCADE;
I found many techniques like ISoftDelete, Interceptor etc, but I think such concepts are used in either code-first approaches or EF Core etc. Also these require a lot of programming that exceeds my current level of understanding.
I have done most in the Database end (as suggested in Entity framework override save changes to soft delete entities), how should I incorporate the features on the application side in the controller & views of ASP.NET MVC with such a database schema(having composite primary & foreign keys) using EF database-first approach?
Thanks in advance!
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>
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?