Introduction
There is a lot of debate online on how to handle Composite Keys when updating primary key values into the database.
With ASP.NET MVC, you cannot update a Primary Key in a table and it forces people with traditional database skills to adopt to introducing a surrogate key to their Composite key tables. I personally do not like to add surrogate keys to my composite key tables.
Schema
I Googled a lot for a solution but I was not able to find any answers. So I tried to solve this mystery myself. After a day of struggling, I came up with a very simple solution which I want to share with you in this article.
I have a 5 tables as shown below. I want to have a table where I can add a schedule for a location and I want to have the flexibility to change the schedule day, time and language. My Schedules table has four composite keys.
Basic Visual Studio MVC Project
Create a new MVC Project:
Add five models for our five tables:
- Locations
- Days
- Times
- Languages
- Schedule
Add Locations, Days, Times, Languages and Schedule Table fields as shown below:
public class Locations
{
[Key]
public int LocationID { get; set; }
public string Location { get; set; }
}
public class Days
{
[Key]
public int DayID { get; set; }
public string Day { get; set; }
}
public class Times
{
[Key]
public int TimeID { get; set; }
public string Time { get; set; }
}
public class Languages
{
[Key]
public string LanguageID { get; set; }
public string Language { get; set; }
}
public class Schedule
{
[Key]
[Column(Order = 0)]
public int LocationID { get; set; }
[Key]
[Column(Order = 1)]
public int DayID { get; set; }
[Key]
[Column(Order = 2)]
public int TimeID { get; set; }
[Key]
[Column(Order = 3)]
public string LanguageID { get; set; }
public string Notes { get; set; }
public virtual Locations Location { get; set; }
public virtual Days Day { get; set; }
public virtual Times Time { get; set; }
public virtual Languages Language { get; set; }
}
Open Visual Studio Package Manager Console from Tools -> Library Package Manager -> Package Manager.
Type “Install-Package EntityFramework” to Install EntityFramework for this project.
Once Installed, add another class inside the models folder and call it “LocationScheduleContext” and make sure to add DbContext to this class as shown below:
public class LocationScheduleContext: DbContext
{
}
Then add DbSet for every table inside the class as shown below:
public class LocationScheduleContext : DbContext
{
public DbSet<Locations> Locations { get; set; }
public DbSet<Languages> Languages { get; set; }
public DbSet<Days> Days { get; set; }
public DbSet<Times> Times { get; set; }
public DbSet<Schedule> Schedule { get; set; }
}
Build your project (CTRL + SHFT + B).
Now add Controllers to all your tables and make sure to select Controller with Views, using Entity Framework.
Add Controllers as LocationsController, DaysController, TimesController, LanguageController and finally ScheduleController.
Ensure to select Correct Model Class that matches with the controller name. (i.e., as shown in the below LocationsController is using Locations Model class).
Do not forget to select the context Class we created “LocationScheduelContext”.
Ensure all the other checkboxes are clicked as shown below and click Add.
Your controllers should look as shown below:
All the views are automatically created for you by Visual Studio.
Run the project by selecting your favourite browser and click to run.
The project will open an ASP.NET MVC default page. So add “Days” to view the Days page as shown below:
Click Create New button and Add all the Seven Days of the week (Sunday, Monday, Tuesday…) as shown below:
Now Open Times page by changing the Days to Times in the URL and add few times as shown below:
Open Languages page and add few Languages as shown below. (Make sure you give LanguageID as en for English, es for Spanish… etc.)
Make sure to go to locations page and add few locations as shown below:
Now go to Schedule page and add few Schedules as shown below:
Try clicking the Edit, Details or Delete button... you will see a Bad Request Error. This is because you have not passed the IDs to the View…
Go to Views and open Schedule Index page and add the Composite Primary Keys as shown below:
<td>
@Html.ActionLink("Edit", "Edit", new { LocationID = item.LocationID, DayID = item.DayID, TimeID = item.TimeID, LanguageId = item.LanguageID}) |
@Html.ActionLink("Details", "Details", new { LocationID = item.LocationID, DayID = item.DayID, TimeID = item.TimeID, LanguageId = item.LanguageID }) |
@Html.ActionLink("Delete", "Delete", new { LocationID = item.LocationID, DayID = item.DayID, TimeID = item.TimeID, LanguageId = item.LanguageID })
</td>
We need to pass these Composite Keys to our Edit, Delete and Details functions in our Schedule controller as shown below:
public ActionResult Edit(int LocationID, int DayID, int TimeID, string LanguageID)
{
Schedule schedule = db.Schedule.Find(LocationID, DayID, TimeID, LanguageID);
if (schedule == null)
{
return HttpNotFound();
}
ViewBag.DayID = new SelectList(db.Days, "DayID", "Day", schedule.DayID);
ViewBag.LanguageID = new SelectList(db.Languages, "LanguageID", "Language", schedule.LanguageID);
ViewBag.LocationID = new SelectList(db.Locations, "LocationID", "Location", schedule.LocationID);
ViewBag.TimeID = new SelectList(db.Times, "TimeID", "Time", schedule.TimeID);
return View(schedule);
}
Run your application and try to edit the existing rows. You will find all the composite key values are added to the query string and you will find that you can only add content to the notes field and does not have the facility to change your primary Key values and you don’t have access to change Day, Time, Language in our schedule table.
Because we don’t have a surrogate key, we cannot change values of LocationID, DayID, TimeId and LanguageID of the composite Key table. But we have a workaround to make this possible.
Open your Create.cshtml and Edit.cshtml from your Schedule folder. From Create.cshtml, copy form-group fields of LocaionID, DayID, TimeID and LanguageID. Now open Edit.cshtml and paste it above your hidden fields (LocaionID, DayID, TimeID and LanguageID) as shown below. Please make sure the hidden fields are not deleted.
<div class="form-horizontal">
<h4>Schedule</h4>
<hr />
@Html.ValidationSummary(true)
<div class="form-group">
@Html.LabelFor(model => model.LocationID, "LocationID", new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownList("LocationID", String.Empty)
@Html.ValidationMessageFor(model => model.LocationID)
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.DayID, "DayID", new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownList("DayID", String.Empty)
@Html.ValidationMessageFor(model => model.DayID)
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.TimeID, "TimeID", new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownList("TimeID", String.Empty)
@Html.ValidationMessageFor(model => model.TimeID)
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LanguageID, "LanguageID", new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.DropDownList("LanguageID", String.Empty)
@Html.ValidationMessageFor(model => model.LanguageID)
</div>
</div>
@Html.HiddenFor(model => model.LocationID)
@Html.HiddenFor(model => model.DayID)
@Html.HiddenFor(model => model.TimeID)
@Html.HiddenFor(model => model.LanguageID)
<div class="form-group">
@Html.LabelFor(model => model.Notes, new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.Notes)
@Html.ValidationMessageFor(model => model.Notes)
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" value="Save" class="btn btn-default" />
</div>
</div>
</div>
Now run the program and try to change one of the dropdown values for Time or Day, you will end up with an error “System.Data.Entity.Core.OptimisticConcurrencyException”. This pretty much says you cannot change your primary key values.
Now open your Schedule Controller and we will add few lines of code to the Edit Post function to capture Query string values and remove them from the database before we add new selected values to the database. When the form is submitted, we will remove the data using the query string values and update new changes we had performed to the form.
If you look at my ScheduleController below, you can see that when the ModelState.IsValid I captured the query string Values as OLocationID, OTimeID, ODayID, and OLanguageID and queried in the database and attached it to services variable.
Then I looped through the services and removed them from the database. My next function is adding all the new values and updating to the database. Then we will try to save the function. If we have an error, we will catch and return to Index page. This try catch function is mainly to avoid duplicates. If you are adding identical schedule data twice, we will be redirected to the Index page. You can use this try method to route to a different page and warn them about duplicate entries.
public ActionResult Edit([Bind(Include="LocationID,DayID,TimeID,LanguageID,Notes")] Schedule schedule)
{
if (ModelState.IsValid)
{
int OLocationID = Convert.ToInt32(Request["LocationID"]);
int OTimeID = Convert.ToInt32(Request["TimeID"]);
int ODayID = Convert.ToInt32(Request["DayID"]);
string OLanguageID = Request["LanguageID"].ToString();
var services = db.Schedule.Where(a => a.LocationID == OLocationID)
.Where(a => a.TimeID == OTimeID)
.Where(a => a.DayID == ODayID)
.Where(a => a.LanguageID == OLanguageID);
foreach (var s in services)
{
db.Schedule.Remove(s);
}
db.Schedule.Add(schedule);
try
{
db.SaveChanges();
}
catch
{
return RedirectToAction("Index");
}
db.Entry(schedule).State = EntityState.Modified;
return RedirectToAction("Index");
}
ViewBag.DayID = new SelectList(db.Days, "DayID", "Day", schedule.DayID);
ViewBag.LanguageID = new SelectList(db.Languages, "LanguageID", "Language", schedule.LanguageID);
ViewBag.LocationID = new SelectList(db.Locations, "LocationID", "Location", schedule.LocationID);
ViewBag.TimeID = new SelectList(db.Times, "TimeID", "Time", schedule.TimeID);
return View(schedule);
}
Now run the project and you will find you change all the primary key values for this composite key table.
Conclusion
This solution is a workaround to work with Composite Keys without adding surrogate key.
Enjoy…
