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

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:

Dim sTls = (From t In TechsLocationsDTOs Where t.Selected = True Order By t.LocationId Select t).ToList 

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.

Listing 1. Implementing IComparable in a DTO 

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.

Listing 2. Working with the TechsLocationsDTOs collection. 

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: