Entity Framework 6: Update an Entity with a Many-to-Many Relation

Introduction

It’s a little bit frustrating to have to deal manually with a whole graph update with many-to-many relationships. I think this should be out-of-the-box in EF 6 and I will get back to this point.

For now, I want to share with you a quick solution that worked for me. There is for sure a better way to achieve what I am going to show here, but I had only a couple of hours to get it done. So, I coded it quickly and I have to refactor it sometime in the coming days.

So, there is the simplified situation. I am using a Route and Stops to illustrate the use case and keep it simple. A Route may have many stops. A Stop may be part of one or more routes.

I have an MVC 5 view which displays routes with their stops. When I add a new route, I get all available stops in database using an Ajax call and associate one or more to it. The stops are displayed as checkbox list and I check the ones I want to associate to the route. In edit mode also, I may check/uncheck stops to add/remove them. I think you get the idea, it’s basic and a pretty common scenario.

Now, I go and edit a route Id = 5, Success Route. I keep the following stops: StopId = 1 and I add a new one: StopdId = 4.

[HttpPost]
public ActionResult Edit(int id, RouteViewModel routeViewModel, FormCollection collection)
{
    try
    {
        var stopList = GetStopsFromFormCollection(collection);
        var route = new Route { Name = routeViewModel.Name, RouteId = id, Stops = stopList };
        _routeServices.UpdateRoute(route);
        return RedirectToAction("Index");
    }
    catch
    {
        return View();
    }
}

When I submit the form, I use the FormCollection as parameter to get the selected stops Ids and RouteViewModel object for anything else that may be updated. Note that the additional stops are displayed in partial view injected there via an Ajax call.

        /// <summary>
        /// 
        /// </summary>
        /// <param name="collection"></param>
        /// <returns></returns>
        private IList<Stop> GetStopsFromFormCollection(NameValueCollection collection)
        {
            var stopCollectionValues = collection.GetValues("SelectedForRoute");
            if (stopCollectionValues == null) return new List<Stop>();

            var stopList = new List<Stop>();
            foreach (var st in 
from id in stopCollectionValues 
where !string.IsNullOrWhiteSpace(id) 
select _routeServices.GetStop(int.Parse(id)))
            {
                st.IsSelectedForRoute = true;
                if (!stopList.Contains(st))
                    stopList.Add(st);
            }
            return stopList;
        }

Now I have the list of stops ids, the RouteViewModel object, I call my service layer to handle the update. Note that I use AutoMapper to convert my RouteViewModel to Route business object. Unity is my IoC also. But I am not going to discuss them as those topics are out of scope. But I mentioned it to guide you in understanding the snippet I am showing.

Once in my service layer, to be able to update stops that are associated with updated route, I have to get original stops of my route using lazy loading. I compare them to updated stops Ids list to extract the new added and removed ones.

Then I have to remove / add the stops to routeContext, make the state as modified. And I finally saveChanges.

public int UpdateRoute(Route route)
{
    //get original route
    var originalRoute = _repo.Find(route.RouteId);
    originalRoute.Name = route.Name;
    //get original stops id using lazy loading
    var originalStopsId = originalRoute.Stops.Select(f => f.StopId);
    //edited stop ids
    var editedStopsId = route.Stops.Select(f => f.StopId);
    // stops in editedStopsId and not in originalStopsId will be added          
    var stopToAdd = GetStopsToAdd(originalStopsId, editedStopsId);
    stopToAdd.ToList().ForEach(x => originalRoute.Stops.Add(x));
    //stops in originalStopsId and not in editedStopsId will be removed
    var stopsIdToRemove = originalStopsId.Except(editedStopsId);
    var stopsToRemove = GetStopsToRemove(originalRoute.Stops, stopsIdToRemove);
    stopsToRemove.ToList().ForEach(x => originalRoute.Stops.Remove(x));
    
    return AddRoute(originalRoute);
}

The following 2 functions are responsible for getting back the stops to remove and the ones to add:

private IEnumerable<Stop> GetStopsToRemove(IEnumerable<Stop> originalRouteStops, IEnumerable<int> stopToRemove)
{
    var stopsToRemove = new List<Stop>();
    foreach (var id in stopToRemove)
    {
        var stop = originalRouteStops.FirstOrDefault(x => x.StopId == id);
        if (!stopsToRemove.Contains(stop))
            stopsToRemove.Add(stop);
    }
    return stopsToRemove;
}

private IEnumerable<Stop> GetStopsToAdd(IEnumerable<int> originalStopsId, IEnumerable<int> editedStopsId)
{
    var stopToAdd = new List<Stop>();
    // stops in editedStopsId and not in originalStopsId will be added
    var stopIdsToAdd = editedStopsId.Except(originalStopsId);
    foreach (var id in stopIdsToAdd)
    {
        var stop = _repo.GetStop(id);
        if (!stopToAdd.Contains(stop))
            stopToAdd.Add(stop);
    }
    return stopToAdd;
}

The following AddRoute function uses two steps to add or update an object. Of course, it would be better to get them done in one step using facade pattern.

/// <summary>
/// 
/// </summary>
/// <param name="route"></param>
/// <returns></returns>
public int AddRoute(Route route)
{
    _repo.InsertOrUpdate(route);
    var nb = _repo.Save();
    
    return nb;
}

/// <summary>
/// 
/// </summary>
/// <param name="route"></param>
public void InsertOrUpdate(Route route)
{
    if (route.RouteId == default(int))
    {
        _routeContext.Routes.Add(route);
    }
    else
    {
        _routeContext.Entry(route).State = EntityState.Modified;
    }
}

// <summary>
/// 
/// </summary>
/// <returns></returns>
public int Save()
{
    return _routeContext.SaveChanges();
}

As you can see, I had to write a lot of code to get it done properly and not yet elegantly. And this is what makes me think that EF has to mature more. Because it should be smart enough to do the same thing to update as to add new route and stops. I was expecting only to give the RouteContext the new collection of stops and EF will figure things out to update/remove/add the related objects. Hope the next version will come up with something smoother to free developers from writing plumbing infrastructural code that has no value to business. I could refine my business model during the hours I spent writing those functions and testing them.

Still, I hope those snippets will help somebody else to avoid wasting the precious time we have to build amazing solutions!