EF7 many to many relationship. How to pass data from multi select dropdown to controller?

Question

My Model

      public class FlightBooking
        {
            public int Id { get; set; }                              
            public ICollection<FlightPassenger> Passengers { get; set; }            
            public DateTime DateJourney { get; set; }
            public virtual City FromCity { get; set; }
            public virtual City ToCity { get; set; }
        }

     public class FlightPassenger
       {
            public int FlightBookingId { get; set; }
            public FlightBooking FlightBooking { get; set; }

            public int CustomerId { get; set; }
            public Customer Passenger { get; set; }
       }

     public class Customer
     {
        public int Id { get; set; }       
        public string FirstName { get; set; }
        public string LastName { get; set; }       
        public string Gender { get; set; }
        public DateTime BirthDate { get; set; }        
        public ICollection<FlightPassenger> FlightPassengers { get; set; }

     }

And in the OnModelCreating I have added

modelBuilder.Entity<FlightPassenger>().HasKey(x => new { x.FlightBookingId, x.CustomerId });

This creates the 3 tables in the database. Customer, FlightBooking and FlightPassenger. All this is fine to represent the many to many relationship in EF7. Now I am trying to take this input from the user.

My view

<select asp-for="Passengers" asp-items="Enumerable.Empty<SelectListItem>()" class="form-control customer"></select>

I am getting the data properly using Ajax and able to select the multiple values in the dropdown. But in the controller no value is passed in Passengers and its count is 0. I checked for the value in the dropdown before posting and it shows ids of the selected customers with comma. I know Passengers is not an integer array but adding an integer array to the model gives another error, so I was thinking there has to be another way. I did a small hack to by adding a string to my view model and before posting adding this integer array to the string. This string has all the values (comma sep) in the controller. But I am sure there should be a better way. Any guidance on getting this value from the view and eventually storing in the database would be great.

Answer 1

In my current project I have a lot of many-to-many relationships. As far as I know EF Core does not yet support many-to-many so I assume it has to be done manually. I generalized the solution.

As I'm new to EF/MVC feedback is welcome:

First I created a JoinContainer to hold the necessary data for the many-to-many entity.

public class SimpleJoinContainerViewModel
{
    public int[] SelectedIds { get; set; }
    public IEnumerable<SelectListItem> SelectListItems { get; set; }

    // keeping track of the previously selected items
    public string PreviousSelectedHidden { get; set; }
    public int[] PreviousSelectedIds
    {
        get
        {
            // if somebody plays around with the hidden field containing the ints the standard exception/error page is ok:
            return PreviousSelectedHidden?.Split(' ').Where(s => !string.IsNullOrEmpty(s)).Select(int.Parse).ToArray();
        }
        private set { PreviousSelectedHidden = value == null ? "" : string.Join(" ", value); }
    }

    /// <summary>
    /// Call when form is loaded - not on post back
    /// </summary>
    /// <param name="selectListItems"></param>
    /// <param name="selectedIds">Currently selected referenced ids. Get via m:n/join-table</param>
    public void Load(IEnumerable<SelectListItem> selectListItems, IEnumerable<int> selectedIds)
    {
        SelectListItems = selectListItems;
        SelectedIds = selectedIds?.ToArray();
        PreviousSelectedIds =  SelectedIds;
    }
}

In the view model (of FlightBooking):

[Display(Name = "Passengers")]
public SimpleJoinContainerViewModel PassengersJoinContainer { get; set; } = new SimpleJoinContainerViewModel();

In the GET action I use the Load() method to fill the Container with the data:

viewModel.PassengerJoinContainer.Load(
    DbContext.Customers
        .Select(s => new SelectListItem
        {
            Text = s.LastName,
            Value = s.Id.ToString()
        }),
    flightBookingEntity?.Passengers?.Select(p => p.CustomerId));

In the view I use the properties of the JoinContainer:

<div class="form-group">
    <label asp-for="PassengersJoinContainer" class="col-sm-3 control-label"></label>
    <div class="col-sm-9">
        <div class="nx-selectize">
            @Html.ListBoxFor(m => m.PassengersJoinContainer.SelectedIds, Model.PassengersJoinContainer.SelectListItems)
        </div>
        @Html.HiddenFor(m => m.PassengersJoinContainer.PreviousSelectedHidden)
        <span asp-validation-for="PassengersJoinContainer" class="text-danger"></span>
    </div>
</div>

Then I have a generalized Update class/method.

public class SimpleJoinUpdater<T> where T : class, new()
{
    private DbContext DbContext { get; set; }
    private DbSet<T> JoinDbSet { get; set; }
    private Expression<Func<T, int>> ThisJoinIdColumn { get; set; }
    private Expression<Func<T, int>> OtherJoinIdColumn { get; set; }
    private int ThisEntityId { get; set; }
    private SimpleJoinContainerViewModel SimpleJoinContainer { get; set; }

    /// <summary>
    /// Used to update many-to-many join tables.
    /// It uses a hidden field which holds the space separated ids
    /// which existed when the form was loaded. They are compared
    /// to the current join-entries in the database. If there are 
    /// differences, the method returns false.
    /// Then it deletes or adds join-entries as needed.
    /// Warning: this is not completely safe. A race condition
    /// may occur when the update method is called concurrently
    /// for the same entities. (e.g. 2 persons press the submit button at the same time.)
    /// </summary>
    /// <typeparam name="T">Type of the many-to-many/join entity</typeparam>
    /// <param name="dbContext">DbContext</param>
    /// <param name="joinDbSet">EF-context dbset for the join entity</param>
    /// <param name="thisJoinIdColumn">Expression to the foreign key (Id/int) which points to the current entity</param>
    /// <param name="otherJoinIdColumn">Expression to the foreign key (Id/int) which points to the joined entity</param>
    /// <param name="thisEntityId">Id of the current entity</param>
    /// <param name="simpleJoinContainer">Holds selected ids after form post and the previous selected ids</param>
    /// <returns>True if updated. False if data has been changed in the database since the form was loaded.</returns>
    public SimpleJoinUpdater(
        DbContext dbContext,
        DbSet<T> joinDbSet,
        Expression<Func<T, int>> thisJoinIdColumn,
        Expression<Func<T, int>> otherJoinIdColumn,
        int thisEntityId,
        SimpleJoinContainerViewModel simpleJoinContainer
    )
    {
        DbContext = dbContext;
        JoinDbSet = joinDbSet;
        ThisJoinIdColumn = thisJoinIdColumn;
        OtherJoinIdColumn = otherJoinIdColumn;
        ThisEntityId = thisEntityId;
        SimpleJoinContainer = simpleJoinContainer;
    }


    public bool Update()
    {
        var previousSelectedIds = SimpleJoinContainer.PreviousSelectedIds;

        // load current ids of m:n joined entities from db:
        // create new boolean expression out of member-expression for Where()
        // see: http://stackoverflow.com/questions/5094489/how-do-i-dynamically-create-an-expressionfuncmyclass-bool-predicate-from-ex
        ParameterExpression parameterExpression = Expression.Parameter(typeof (T), "j");
        var propertyName = ((MemberExpression) ThisJoinIdColumn.Body).Member.Name;
        Expression propertyExpression = Expression.Property(parameterExpression, propertyName);
        var value = Expression.Constant(ThisEntityId);
        Expression equalExpression = Expression.Equal(propertyExpression, value);
        Expression<Func<T, bool>> thisJoinIdBooleanExpression =
            Expression.Lambda<Func<T, bool>>(equalExpression, parameterExpression);

        var joinedDbIds = JoinDbSet
            .Where(thisJoinIdBooleanExpression)
            .Select(OtherJoinIdColumn).ToArray();


        // check if ids previously (GET) and currently (POST) loaded from the db are still the same
        if (previousSelectedIds == null)
        {
            if (joinedDbIds.Length > 0) return false;
        }
        else
        {
            if (joinedDbIds.Length != previousSelectedIds.Length) return false;
            if (joinedDbIds.Except(previousSelectedIds).Any()) return false;
            if (previousSelectedIds.Except(joinedDbIds).Any()) return false;
        }


        // create properties to use as setters:
        var thisJoinIdProperty = (PropertyInfo) ((MemberExpression) ThisJoinIdColumn.Body).Member;
        var otherJoinIdProperty = (PropertyInfo) ((MemberExpression) OtherJoinIdColumn.Body).Member;

        // remove:
        if (joinedDbIds.Length > 0)
        {
            DbContext.RemoveRange(joinedDbIds.Except(SimpleJoinContainer.SelectedIds).Select(id =>
            {
                var e = new T();
                thisJoinIdProperty.SetValue(e, ThisEntityId);
                otherJoinIdProperty.SetValue(e, id);
                return e;
            }));
        }

        // add:
        if (SimpleJoinContainer.SelectedIds?.Length > 0)
        {
            var toAddIds = SimpleJoinContainer.SelectedIds.Except(joinedDbIds).ToList();
            if (toAddIds.Count > 0)
            {
                DbContext.AddRange(SimpleJoinContainer.SelectedIds.Except(joinedDbIds).Select(id =>
                {
                    var e = new T();
                    thisJoinIdProperty.SetValue(e, ThisEntityId);
                    otherJoinIdProperty.SetValue(e, id);
                    return e;
                }));
            }
        }
        return true;
    }
}

In the Post action I call this class/method:

    var flightPassengersUpdater = new SimpleJoinUpdater<FlightPassenger>(
            DbContext,
            DbContext.FlightPassengers,
            mm => mm.FlightBookingId,
            mm => mm.CustomerId,
            model.Id,  // model = current flightBooking object
            viewModel.PassengersJoinContainer);
    if (!flightPassengersUpdater .Update())
    {
        ModelState.AddModelError("PassengersJoinContainer", "Since you opened this form the data has already been altered by someone else. ...");
    }