MVC4 controller with Many to Many relationship

Question

I have the two entities, Arena and Regulator, which have a many to many relationship between them. I have implemented what seems to be the EF code first accepted solution (see below).

I am now stuck with implementing the controller views, so that when a user creates a regulator, he can select one or more arenas (probably with check boxes or multi select list), and when they create an arena, one or more regulators can be selected.

Is there a way for MVC4 to generate the controller and views for me, like it does for one to many relationships?

EDIT: From the initial comments, I now understand that I can add the selected arena to the Arenas navigation property of the regulator object. I have not been able to find the way to both add the selection list to the Edit (and Create) views, and then make the changes in the controller. Can anyone supply an example?

EDIT2: I have code for the Edit actions that should work if EF did indeed update relationships (regulator.ArenaIDs is a list of integers I added to the regulator class, to get the selected item IDS from the MultiSelectList):

<HttpPost()> _
<ValidateAntiForgeryToken()> _
Function Edit(ByVal regulator As Regulator) As ActionResult
    If ModelState.IsValid Then
        For Each i In regulator.ArenaIDs
            regulator.Arenas.Add(db.Arenas.Find(i))
        Next
        db.Entry(regulator).State = EntityState.Modified
        db.SaveChanges()
        Return RedirectToAction("Index")
    End If

    Return View(regulator)
End Function

I am using VS 2012 and EF 5.0

Here is my implementation:

Public Class Arena
    Public Property Id As Integer
    Public Property Name As String
    Public Overridable Property Regulators() As ICollection(Of Regulator)
End Class

Public Class Regulator
    Public Property Id As Integer
    Public Property Name As String
    Public Overridable Property Arenas() As ICollection(Of Arena)
End Class

with the following DbContext

Public Class TslilContext
    Inherits DbContext
    Public Property Arenas As DbSet(Of Arena)
    Public Property Regulators As DbSet(Of Regulator)

    Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
        modelBuilder.Entity(Of Arena)(). _
            HasMany(Function(c) c.Regulators). _
            WithMany(Function(p) p.Arenas). _
            Map(Function(m)
                    m.MapLeftKey("ArenaId")
                    m.MapRightKey("RegulatorId")
                    m.ToTable("Tiers")
                End Function)
    End Sub

You do not need to access to the join table. You have the navigation properties to read existing relations and add new one. And you have the DbSet(s) to list the existing entites. No need for more. For the controller... you will have to write it by yourself — tschmit007, Jul 03 '13 at 09:03
But how will the join table be updated? Will the framework do that? — GilShalit, Jul 03 '13 at 09:31
Why do you need access to the intermediate table? what kind of actions you aren't able to perform? — haim770, Jul 03 '13 at 10:06
@GilShalit EF will update the join table when you add/change the navigation properties. If you change your answer to be more specific based on these comments then maybe we can show you an example. — SOfanatic, Jul 03 '13 at 11:56
You can try it along the lines of this example which covers the `Create` action: http://stackoverflow.com/a/16383982/270591 `Subscription` in that example is your `Regulator`, `Company` is your `Arena`. — Slauma, Jul 03 '13 at 17:32
@SOfanatic, I have found several references to the fact that EF will NOT update relationships - see here: http://stackoverflow.com/questions/3635071/update-relationships-when-saving-changes-of-ef4-poco-objects/3635326#3635326. Do you have a solution? See EDIT2 for my code — GilShalit, Jul 03 '13 at 20:10
The code in your example does not match the code of your implementation. You say you're using regulator.ArenaIDs but there is no ArenaIDs in your implementation. I really don't get what it is you're trying to do there, since if Regulator already has the ArenaID's, why do you need to re-add them? — Erik Funkenbusch, Jul 05 '13 at 21:07

score 2 · Accepted Answer · answered Jul 05 '13 at 19:09

After a lot of research I have understood that EF cannot update relationships - very surprising and disappointing. Aparently, the suggested solution is a manual update of the join table and navigation properties - not very good. NHibernate apparently does do this out of the box and I fully intend to investigate next time I need this.

Luckily I came across a truly great solution from Refactor(This) that adds an extension method to DbContext, allowing automated updating of complex relationships. There is even a Nuget package!

So here is my full solution:

I've added an integer list to the Regulator class, which gets the IDs of the selected Arenas.

Public Class Regulator
    Public Property Id As Integer
    Public Property Name As String
    Public Property ArenaIDs() As ICollection(Of Integer)
    Public Overridable Property Arenas() As ICollection(Of Arena)
End Class

In the GET Edit action, this is taken care of and a MultiSelectList is created:

' GET: /Regulator/Edit/5

Function Edit(Optional ByVal id As Integer = Nothing) As ActionResult
    Dim regulator As Regulator = db.Regulators.Find(id)
    If IsNothing(regulator) Then
        Return HttpNotFound()
    End If
    For Each a In regulator.Arenas
        regulator.ArenaIDs.Add(a.Id)
    Next
    ViewBag.MultiSelectArenas = New MultiSelectList(db.Arenas.ToList(), "Id", "Name", regulator.ArenaIDs)
    Return View(regulator)
End Function

And the MultiSelectList is used in the View:

    <div class="editor-field">
        @Html.ListBoxFor(Function(m) m.ArenaIDs, ViewBag.MultiSelectArenas)
        @Html.ValidationMessageFor(Function(model) model.Arenas)
    </div>

In the POST Edit action the selection IDs are retrieved and used to update the Arenas collection. Then the magic comes in the with the UpdateGraph extension method that does what EF can not and updates the relationship!

' POST: /Regulator/Edit/5

<HttpPost()> _
<ValidateAntiForgeryToken()> _
Function Edit(ByVal regulator As Regulator) As ActionResult
    If ModelState.IsValid Then
        For Each i In regulator.ArenaIDs
            regulator.Arenas.Add(db.Arenas.Find(i))
        Next

        db.UpdateGraph(Of Regulator)(regulator, Function(map) map.AssociatedCollection(Function(r) r.Arenas))

        db.SaveChanges()
        Return RedirectToAction("Index")
    End If

Erik Funkenbusch · Answer 2 · 2013-07-05T21:50:02.187

The problem here is not that EF doesn't update relationships, it's that it doesn't automatically do so.

What you need to do is retrieve the current Arenas for the Regulator, then walk the list and delete any entries that exist that are not in your new list. Then you need to add any entries that do not already exist. Then SaveChanges.

This does work, but your problem is that you're trying to either blindly add relationships that may already exist, or trying to update ones that don't. You have to actually get the existing list and figure out which relationships to either add or delete.

I understand you've already found a solution that works for you, my point here is just that if you're trying to do a strict EF based solution, then you were going at it the wrong way.

I suppose another option would be to delete all the relationships, SaveChanges, then add the new set and SaveChanges again. This would delete some that already exist if there is overlap, but would be fairly straight forward and simple.

Another option is to Delete the existing, re-add them, then walk the original set and change the state of any that previously existed to Modified or None. A little more involved, but would only require on save.

score 0 · Answer 3 · answered Sep 04 '13 at 14:56

I have the same issue with EF not managing Many to Many relations. I disagree the solution provided by Mystere Man. I use RefactorThis.GraphDiff for all the collection updates using EF Code First. But it is not fair to say it is incorrect.

Like GilShalit has mentioned, nhibernate handles it brillinatly. It makes me fell, EF is not fully developed product. It is not our job to fetch all collection and check add/delete all these stuff. It is looks very nasty. while the collections are added, EF should handle which one to add and which one to remove. Thankfully RefactorThis.GraphDiff is helping for the time being. I prefer nhibernate not ef. Unfortunately I need to use EF in the current project.

MVC4 controller with Many to Many relationship

3 Answers3

Linked