Practical .NET

Handle Many-to-Many Relationships in Entity Framework and ASP.NET MVC

In this month's column, Peter Vogel takes a single -- and common -- problem and looks at all the technologies you can use to solve it, from getting the data design correct through handling updates and finishing with a UI built in ASP.NET MVC.

• By Peter Vogel
• 06/20/2013

It's not unusual to have many-to-many relationships in an application. A recent project I worked on had multiple service technicians assigned to multiple locations, for instance. It's also not unusual for there to be data that applies only to the relationship. In my client's project, for example, at any location, a service technician has a specific position -- and that position can be different from one location to another. (For instance, at one location, the tech might be the "responsible authority"; at another location, the tech might be the "support technician.") In addition, at each location, a tech is assigned a phone extension number where he can be reached.

In this column, I'll work through this problem using Entity Framework Code First development, LINQ and (for the UI) ASP.NET MVC. By the end of the column you'll have a roadmap for handling many-to-many relationships in your applications.

The Data Design
My client for this project was a "Code First all the way" shop. My first step was to create the class that defines the many-to-many relationship between techs and locations (the Location, Job and ServiceTech classes were already defined):

<Table("TechsLocations")> _
Public Class TechsLocations
  Public Property TechId As Integer
  Public Property LocationId As Integer

  Public Overridable Property Tech As Tech
  Public Overridable Property Location As Location
  Public Overridable Property Job As Job
  Public Property Ext As String

End Class

This class establishes that TechsLocations has navigation collections between the two entities in the many-to-many relationship (Tech and Location); it also has a navigation collection with the entity with which it has a one-to-many relationship (Job). Finally, the class gives the TechsLocation entity a property to hold the phone extension number (Ext) for the tech at that location.

With all the entities defined, the next step is to add them to the DataContext:

Public Class DataContext
  Inherits DbContext

  Public Property Techs As DbSet(Of Tech)
  Public Property Locations As DbSet(Of Location)
  Public Property Jobs As DbSet(Of Job)
  Public Property TechsLocations As DbSet(Of TechsLocations)

In the OnModelCreating method, I turn off cascading deletes (I don't want to delete a location and find all my service techs deleted):

Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
  modelBuilder.Conventions.Remove(Of OneToManyCascadeDeleteConvention)()

Still in the OnModelCreating method, I establish the relationships between these entities. I begin by defining the composite key (TechId and LocationId) for my TechsLocations entity:

modelBuilder.Entity(Of TechsLocations).
  HasKey(Function(tl) New With {tl.TechId, tl.LocationId})

Next, I tell Entity Framework that a tech can have many TechsLocations, that there can't be a TechsLocations without a tech, and that the relationship between Techs and TechsLocations is built on the TechId property in the TechsLocations entity:

modelBuilder.Entity(Of Tech).
  HasMany(Function(t) t.TechsLocations).
  WithRequired(Function(tl) tl.Tech).
  HasForeignKey(Function(tl) tl.TechId)

I also need to do the same thing on the Location side of the relationship:

modelBuilder.Entity(Of Location).
  HasMany(Function(l) l.TechsLocations).
  WithRequired(Function(tl) tl.Location).
  HasForeignKey(Function(tl) tl.LocationId)

And, of course, I need to specify that TechsLocations has a one-to-many relationship with the Job table. That's easier to define:

modelBuilder.Entity(Of TechsLocations).
  HasRequired(Function(tl) tl.Job).
  WithMany.Map(Sub(tl) tl.MapKey("JobId"))

Adding, Deleting and Updating the Relationship
Now, to add a TechsLocations relationship, I first create (or retrieve) a Location and a Job:

Using dc = New DataContext
  Dim lcn As New Location() With {.Name = "MainSite"}
  dc.Locations.Add(lcn)

  Dim jb As New Job() With {.Title = "Responsible Authority"}
  dc.Jobs.Add(jb)

  dc.SaveChanges

Then I create (or retrieve) a Tech and create a TechsLocations object that will tie the Tech to the Location. Because I'm adding this TechsLocations object to the Tech object, I don't need to set the TechsLocations object's TechId property (Entity Framework will take care of that). I do, however, need to set the other properties (Location, extension and Job) for TechsLocations:

Dim tch As Tech
Dim tch = As New Tech() With {.TechName = "Peter Vogel"}
Dim tl As TechsLocations 
tl = New TechsLocations With
  {.Location = lcn,
  .Ext = "1234",
  .Job = jb})

After creating the TechsLocations object, I initialize the Tech's TechsLocations collection, add my TechsLocations object to the collection, add my Tech object to the data context's Techs collection and save my changes:

tch.TechsLocations = New List(Of TechsLocations)
tch.TechsLocations.Add(tl)
dc.Techs.Add(tch)
dc.SaveChanges

Deleting a Tech now requires two steps: first, delete the TechsLocations entities for the Tech and, after that, delete the Tech. The code to delete the Tech I just created looks like this:

Dim tls = From tl In dc.TechsLocations
          Where tl.TechId = tch.ID
          Select tl

For Each tl In tls
  dc.TechsLocations.Remove(tl)
Next
dc.SaveChanges()
dc.Techs.Remove(tch)  

Updating requires more complex code because, before doing any updates, I want to check to see if the tech's assignments have been changed. So the first thing I do is convert all of the tech's existing assignments into TechsLocationsDTO objects, like this:

Dim tchTls = (From tl In dc.TechsLocations
              Where tl.TechId = tch.ID
              Select New TechsLocationsDTO With 
                {
                  .Selected = True,	   
                  .Location = tl.Location,
                  .LocationId = tl.LocationId,
                  .Phone_Extension = tl.Ext,
                  .Job = tl.Job,
                  .JobId = tl.Job.ID}).ToList

The reason I convert my TechsLocations entities into TechsLocationsDTO objects is that I'm making an assumption: My code is passed not only a Tech object representing the current tech, but also a list of TechsLocationsDTO objects. Each of those TechsLocationsDTO objects represents a location (with associated information: job and extension) to which a tech could be assigned. If the tech has, in fact, been assigned to a location, the TechsLocationsDTO object's Selected property is set to True (in the next section I'll describe where that collection comes from in ASP.NET MVC). From the list of TechsLocationsDTO objects passed to me, I extract the locations to which the tech has been assigned:

Because I now have two collections of TechsCollectionsDTO objects -- and because my TechsCollectionsDTO implements the IComparable interface (as shown in Listing 1) -- I can use the SequenceEqual method to see if the two collections have the same objects.

Public Class TechsLocationsDTO
  Implements IComparable(Of TechsLocationsDTO)
  
    Public Property Selected As Boolean
    Public Property Location As Location
    Public Property LocationId As Integer?
    Public Property Ext As String
    Public Property Job As Job
    Public Property JobId As Integer?
    Public Property JobsList As List(Of SelectListItem)

    Public Function CompareTo(other As TechsLocationsDTO) As Integer _
      Implements IComparable(Of TechsLocationsDTO).CompareTo
      If (TextId - other.TechId) <> 0 Then
        Return TechID - TechId
      ElseIf (JobId - other.JobId) <> 0 Then
        Return JobID - JobId
      ElseIf (LocationId - other.LocationId) <> 0 Then
        Return PositionId - other.PositionId
      ElseIf (Ext.CompareTo(other.Ext)) <> 0 Then
        Return Ext.CompareTo(other.Ext)
      Else
        Return 0
      End If
    End Function
End Class

If SequenceEqual returns False, it indicates that some objects are either missing or changed between the objects. In that case, I throw away all the tech's current TechsLocations, create new ones from the selected TechsLocationsDTOs collection I received earlier, add the new TechsLocations objects to my tech object, and (eventually) save the changes to the database, as shown in Listing 2.

If Not sTls.SequenceEqual(tchTls) Then
  tch.TechsLocations.Clear()
  Dim tchLcn As TechsLocations
  For Each tchLcn In sTls
    tchLcn = New TechsLocations
    tchLcn.user = tch
    tchLcn.LocationId = tchLcn.LocationId
    tchLcn.Job = dc.Jobs.Single(Function(j) j.ID = tchLcn.JobId)
    tchLcn.Ext = lcn.Ext
    tch.TechsLocations.Add(tchLcn)
  Next
End If
dc.SaveChanges()

Getting Data to the UI
This leads to the obvious question: Where does that TechsLocationsDTOs collection come from? Up until now, everything I've discussed would work in any of the environments built on ASP.NET (Web Forms, Web API, MVC). I'd assume that in Web Forms, users were given a list of TechsLocations in a GridView that let the user select the locations he wanted; in the Web API I'd assume that you'd require the client to send the collection as part of calling whatever method in which this code goes. But supporting this collection in ASP.NET MVC requires some explanation.

As its name implies, the TechsLocationsDTO class is a data transfer object (DTO) for moving necessary data to the view. It's just a collection of properties -- one of which is a list of Jobs so that, in the view, I can limit the user to selecting from a dropdown list when assigning a tech to a job at a location.

For a View that lets users update the TechsLocations for a service technician, I begin by building two collections: all the Locations a tech can be assigned to and all the Locations assigned to the current tech:

Dim AllLocations = dc.Locations.ToArray

Dim tls = From tl In dc.TechsLocations
          Where tl.TechId = tch.ID
          Select tl

I then build the collection I'll pass to the view by joining those two locations in the LINQ equivalent of a left-outer join (that means using the DefaultIfEmpty method in LINQ). This will give me a collection of all of the locations, even where no matching TechsLocations exists. Where there are matching Locations and TechsLocations, I set the Selected property on my DTO to True and update the DTO with data from the matching TechsLocations entity, as shown in Listing 3 (which is a pretty ugly LINQ statement, but it does work).

Dim AllTechsLocationsDTOs = (From l In AllLocations
  Group Join tl In tls
    On l.ID Equals tl.LocationId
    Into tlcn = Group
  From tl In tlcn.DefatltIfEmpty()
  Select New TechsLocationsDTO With 
    {.Selected = (tl IsNot Nothing),
    .Location = l,
    .LocationId = l.ID,
    .Ext = 
      If(tl IsNot Nothing, 
         tl.Ext, 
         String.Empty),
    .Job = If(tl IsNot Nothing, 
              tl.Job, 
              Nothing),
    .JobId = If(tl IsNot Nothing, 
                tl.Job.ID, 
                Nothing)}).ToList

To support my dropdown list of Jobs, with the user's current Job already selected, I populate the JobsList property on my DTO with SelectListItem objects (the object used by ASP.NET MVC to fill its dropdown lists). I loop through my collection of DTOs and, for each DTO, I retrieve every possible Job, checking to see if it's the Job specified on that DTO so that I can set the Selected property on the SelectListItem:

Dim AllJobs = dc.Jobs.ToArray
For Each tld In AllTechsLocationsDTOs
  tld.JobsList = (From j In AllJobs
                  Select New SelectListItem With
                    {.Selected = (j.ID = jlj.JobId),
                     .Text = p.Name,
                     .Value = p.ID}).ToList
Next

Getting Data Back from the UI
I can now pass this collection to the view. In the view, I create a simple table with a row for each DTO. In the first column, I display a checkbox for the DTO's Selected property, text for the Location and phone extension properties, and a dropdown list for the Job property.

I can pass the collection of DTOs to the view in a number of ways, but if I put the collection in a property on the model object I use with the view, I can use the ValidationMessageFor in the view and the ModelState's AddModelError methods to display errors for the collection. (Because that made my life easier, that's what I did.)

The code in my view to display each DTO checks the DTO's Selected property to determine if the user has been assigned to this location. The code then displays the DTO's Selected property in the first cell as a checkbox and the Location name (with a hidden field to hold the Location's ID) in the second cell:

@For Each tlj In Model.AllTechsLocationsDTOs
  @<tr>
  @If tlj.Position IsNot Nothing Then
    @<td>@Html.CheckBox("Selected", tlj.Selected)</td>
    @<td>                                
      @tlj.Location.Name 
      @Html.Hidden("LocationId", tlj.LocationId) 
    </td>

The next cell displays the dropdown list that holds the list of Jobs with the current job selected, followed by the phone extension for this user at this location:

@<td>
  @Html.DropDownList("JobId", tlj.JobsList) 
</td>
@<td>
  @Html.TextBox("Ext", tlj.Phone_Extension)
</td>

The code is simpler when there's nothing to display, as shown in Listing 4.

Else
  @<td>@Html.CheckBox(namePrefix & "Selected", False)</td>
  @<td>
    @tlj.Location.Name
    @Html.Hidden("LocationId", tlj.LocationId) 
  </td>
  @<td>
    @Html.DropDownList("PositionId", 
      CType(ViewBag.PositionsList, List(Of SelectListItem)))
  </td>
  @<td>
    @Html.TextBox("Ext")
  </td>                         
End If

ASP.NET model binding isn't able to reassemble this collection to return it to my ASP.NET MVC controller. But I'd rather have the collection passed as a separate object to the controller so that my controller's declaration would look like this:

Function Change(tchDTO As UserDTO, 
                TechsLocationsDTOs As TechsLocationsDTO()) As ActionRestlt

To get my table returned as a collection of TechsLocationsDTO objects, I need to set the name attribute on each tag in the table to the name of the class (TechsLocationsDTO) and the name of the property (Selected, LocationId, Ext and so on). To indicate that it's a collection of these objects, I have to number each entry using the syntax for an array followed by the class name (TechsLocationsDTO[0].Selected). So, for instance, the first entries in my table for my Selected and LocationId values will look like Listing 5 in the browser.

<td>
  <input type="checkbox" checked="checked" 
    name="TechsLocationsDTO[0].Selected" 
    id="TechsLocationsDTO_0__Selected"
    value="true" />  
</td>
<td>                                
  Regina 
  <input type="hidden"
    name="TechsLocationsDTO[0].Location_ID" 
    id="TechsLocationsDTO_0__Location_ID"   
    value="1" />
</td>

ASP.NET MVC generates the name and id attributes for each tag from the string I pass as the first parameter to the various methods on the HtmlHelper object (TextBox, CheckBox). So all I have to do is build a prefix for the property names and provide a counter that keeps track of which row in my DTOs collection I'm adding to the table. The revised code for the first row in the table looks like Listing 6.

@Code
  Dim ing As Integer = 0
  Dim namePrefix As String
End Code

@For Each tlj In Model.AllUserLocations
  namePrefix = "TechsLocationsDTO[" & ing.ToString & "]."
  @<tr>
    @If tlj.Position IsNot Nothing Then
    @<td>@Html.CheckBox(namePrefix & "Selected", tlj.Selected)</td>
    @<td>                      
          
    @* ... more rows in the table ... *@

  </tr>
  @Code
    ing += 1
  End Code
  Next
</table>
@Html.ValidationMessageFor(Function(model) model.User.UsersLocations)

And, finally, I'm done. I have a many-to-many relationship in my database (with associated information); I can display the relationships in a view so that users can make changes; and I can update, delete and change the data. While including a many-to-many relationship with associated data isn't an uncommon problem, as you've seen, the solution isn't trivial. But now you've got a roadmap for a complete solution.