Click here to Skip to main content
15,868,016 members
Articles / Web Development / HTML

ASP.NET MVC Edit Primary Key Values for Composite Keys

Rate me:
Please Sign up or sign in to vote.
4.86/5 (16 votes)
23 Jul 2014CPOL5 min read 96.8K   920   20   16
ASP.NET MVC Edit Primary Key Values for Composite Keys

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.

Image 1

Basic Visual Studio MVC Project

Create a new MVC Project:

Image 2

Add five models for our five tables:

  • Locations
  • Days
  • Times
  • Languages
  • Schedule

Add Locations, Days, Times, Languages and Schedule Table fields as shown below:

C#
public class Locations
{
    [Key]
    public int LocationID { get; set; }
    public string Location { get; set; }
}

C#
public class Days

{
    [Key]
    public int DayID { get; set; }
    public string Day { get; set; }
}

C#
public class Times
{
    [Key]
    public int TimeID { get; set; }
    public string Time { get; set; }
}

C#
public class Languages
{
    [Key]
    public string LanguageID { get; set; }
    public string Language { get; set; }
}

C#
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.

Image 3

Type “Install-Package EntityFramework” to Install EntityFramework for this project.

Image 4

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:

C#
public class LocationScheduleContext: DbContext
{
}

Then add DbSet for every table inside the class as shown below:

C#
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.

Image 5

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.

Image 6

Your controllers should look as shown below:

Image 7

All the views are automatically created for you by Visual Studio.

Run the project by selecting your favourite browser and click to run.

Image 8

The project will open an ASP.NET MVC default page. So add “Days” to view the Days page as shown below:

Image 9

Click Create New button and Add all the Seven Days of the week (Sunday, Monday, Tuesday…) as shown below:

Image 10

Now Open Times page by changing the Days to Times in the URL and add few times as shown below:

Image 11

Open Languages page and add few Languages as shown below. (Make sure you give LanguageID as en for English, es for Spanish… etc.)

Image 12

Make sure to go to locations page and add few locations as shown below:

Image 13

Now go to Schedule page and add few Schedules as shown below:

Image 14

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:

ASP.NET
<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:

C#
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.

ASP.NET
<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.

C#
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…

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Archdiocese of Melbourne
Australia Australia
As a software developer working for a Catholic Organisation I look at programming as a knowledge to help people and resolve social issues. I am very firm believer in simple solutions to complex issues. I had been in IT industry for more than 13 years worked as an Operations Analyst, Business Analyst and now as a web programmer. I always like to learn new technologies and passionate about helping others.

Comments and Discussions

 
QuestionWhy Delete with one Key? Pin
Hong Tien Le27-Sep-21 20:00
Hong Tien Le27-Sep-21 20:00 
QuestionGreat tutorial, now I'm going to ignore it! Pin
phil24158-Apr-18 23:02
phil24158-Apr-18 23:02 
QuestionVery great tutorial! Pin
Musti T.15-Dec-17 3:28
Musti T.15-Dec-17 3:28 
SuggestionSmall change needed for ASP.NET Core Pin
Joe Gershgorin18-Jul-17 14:37
Joe Gershgorin18-Jul-17 14:37 
QuestionFive stars for the article. Zero stars for MVC scaffolding not handling this properly. Pin
scooternva14-Feb-17 13:36
scooternva14-Feb-17 13:36 
Questionwhat if Schedule is base table Pin
Hossam_Gamaleldin16-Jan-16 23:00
Hossam_Gamaleldin16-Jan-16 23:00 
Question(HttpPost) Edit: Why use db.saveChanges() and db.Entry simultaneously? Pin
Soccerkip10-Feb-15 22:37
Soccerkip10-Feb-15 22:37 
QuestionWhy use a data mart? Pin
jgakenhe4-Feb-15 9:09
professionaljgakenhe4-Feb-15 9:09 
QuestionGood article ... Pin
Georgi Hadzhigeorgiev22-Jul-14 22:36
Georgi Hadzhigeorgiev22-Jul-14 22:36 
AnswerRe: Good article ... Pin
Matthew Katakam23-Jul-14 4:25
professionalMatthew Katakam23-Jul-14 4:25 
GeneralRe: Good article ... Pin
Georgi Hadzhigeorgiev23-Jul-14 4:32
Georgi Hadzhigeorgiev23-Jul-14 4:32 
SuggestionSmall Suggestion Pin
Ranjan.D16-Jul-14 4:15
professionalRanjan.D16-Jul-14 4:15 
GeneralRe: Small Suggestion Pin
Matthew Katakam22-Jul-14 16:00
professionalMatthew Katakam22-Jul-14 16:00 
GeneralRe: Small Suggestion Pin
Ranjan.D23-Jul-14 7:24
professionalRanjan.D23-Jul-14 7:24 
QuestionToo much pictures Pin
thatraja16-Jul-14 3:05
professionalthatraja16-Jul-14 3:05 
AnswerRe: Too much pictures Pin
Matthew Katakam16-Jul-14 14:22
professionalMatthew Katakam16-Jul-14 14:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.