Generating Checkbox Lists with MVC3 using a complex viewmodel and a cross table with Linq to SQL

Question

I've inherited my first MVC project and it involves using MVC3 on top of Linq to SQL. I've been trying to find a way to generate a check box list based on a many to many relationship involving a cross table.

I have a systemFailureType table that maps to a SystemFailureProblem table via a cross table.

Here is my designer layout for the tables:

Designer Layout And here are my models:

     [MetadataType(typeof(SystemFailureProblemMetadata))]
    public partial class SystemFailureProblem
    {        
        private class SystemFailureProblemMetadata
        {
            public int ID { get; set; }

            [Required]
            [StringLength(200)]
            [DisplayName("Problem Description")]
            public String Description { get; set; }

            public IList<xSystemFailureProblemToType> FailureTypeCategories { get; set; }

        }        

}



[MetadataType(typeof(SystemFailureTypeMetaData))]
    public partial class SystemFailureType
    {
        private class SystemFailureTypeMetaData
        {
            public int ID { get; set; }

            [Required]
            [StringLength(200)]
            public String Description { get; set; }
        }
    }

My current view code uses a view model that contains a problem object. So the current code I have for generating the checkbox list looks like this:

 @for(int i=0;i < Model.problem.FailureTypeCategories.Count(); i++)
 {
       @Html.CheckBox("FailureTypeCategories["+i+"].ID", false)

 }

My main issue is that I'm getting some errors when I try to generate the checkbox list saying that the FailureTypeCategories collection doesn't exist. I suspect it may be related to how I have the models set up currently. My initial thoughts are leaning towards implementing a model for the cross table, though I'm not quite sure how I would integrate that. Is there an different way I should be going about this, or am I on the right track and just missing something?

Edit:

Here is the ViewModel

        public SystemFailureProblem problem { get; set; }

        public SystemFailureProblemViewModel() { }

        public SystemFailureProblemViewModel(SystemFailureProblem problem)
        {
            this.problem = problem;
        }

The controller method is very simple. It just returns a partial view of the form.

 public ActionResult Edit(int id)
    {            
        try
        {
            return PartialView("Form", context.SystemFailureProblems.Single(p => p.ID == id));
        }
        catch (Exception ex)
        {
            ModelState.AddModelError("", ex.Message);
            return PartialView("Form", null);
        }
    }

can you include the ViewModel code as well as the Controller action that returns this view? — Michael Hornfeck
– Michael Hornfeck, Commented Jan 18, 2012 at 19:12
is your intention to generate a checkbox for each SystemFailureType related to the SystemFailureProblem that is being edited? — Michael Hornfeck
– Michael Hornfeck, Commented Jan 18, 2012 at 20:48
Yes that would be the ideal situation. I would then also have to figure out how to get linq to generate the corresponding entries into the xSystemFailureProblemToType table as well — Philter
– Philter, Commented Jan 18, 2012 at 20:53

Michael Hornfeck · Accepted Answer · 2012-01-19 04:54:28Z

1

I came up with an idea based on this article which utilizes Entity Framework, but it wasn't too difficult to translate into LinqToSql classes.

First, I tweaked the ViewModel class. You'll need to store more information in there other than the SystemFailureProblem object, such as information pertinent to the collection of SystemFailureType assigned to that problem.

public class SystemFailureProblemTypeViewModel
{
    public int TypeID { get; set; }
    public string TypeDescription { get; set; }
    public bool Assigned { get; set; }
}

Next, I created the logic for the Edit actions (GET and POST). In the GET method, you find out which types are currently selected for the problem (from xSystemFailureProblemToType table) and construct a ViewModel using that data. This ViewModel gets passed to the View along with the SystemFailureProblem object.

public ActionResult Edit(int id)
    {
        SystemFailureProblem problem = (from p in context.SystemFailureProblems
                                        where p.ID == id
                                        select p).Single();

        PopulateSystemFailureProblemData(problem);

        return View(problem);
    }

    public void PopulateSystemFailureProblemData(SystemFailureProblem problem)
    {
        // get all failure types
        var allTypes = from t in context.SystemFailureTypes select t;

        // get al types joined with this problem using cross table
        var problemTypes = from x in context.xSystemFailureProblemToTypes
                           join t in context.SystemFailureTypes on x.SystemFailureTypeID equals t.ID
                           where x.SystemFailureProblemID == problem.ID
                           select t;

        // construct view model collection
        List<SystemFailureProblemTypeViewModel> viewModel = new List<SystemFailureProblemTypeViewModel>();
        foreach (var type in allTypes)
        {
            viewModel.Add(new SystemFailureProblemTypeViewModel
            {
                TypeID = type.ID,
                TypeDescription = type.Description,
                Assigned = problemTypes.Contains(type)
            });
        }

        ViewBag.Types = viewModel;
    }

In the POST method, we get a string[] parameter that tells us which checkboxes were checked. It is a list of SystemFailureType IDs. Loop through each SystemFailureType in the database, determine which ones are selected/unselected, and update xSystemFailureProblemToType table accordingly.

[HttpPost]
    public ActionResult Edit(int id, FormCollection collection, string[] selectedTypes)
    {
        SystemFailureProblem problem = (from p in context.SystemFailureProblems
                                        where p.ID == id
                                        select p).Single();

        // get all types joined with this problem using cross table
        var problemTypes = from x in context.xSystemFailureProblemToTypes
                           join t in context.SystemFailureTypes on x.SystemFailureTypeID equals t.ID
                           where x.SystemFailureProblemID == problem.ID
                           select t;

        problem.FailureTypes = problemTypes.ToList<SystemFailureType>();

        if (TryUpdateModel(problem, "", null, new string[] { "Types" }))
        {
            try
            {
                // loop through all types in the system
                foreach (var failureType in context.SystemFailureTypes)
                {
                    // determine if checkbox for current type was checked
                    if (selectedTypes.Contains(failureType.ID.ToString()))
                    {
                        // if no joining record exists (type not previously selected), create a joining record
                        if (!problemTypes.Contains(failureType))
                        {
                            context.xSystemFailureProblemToTypes.InsertOnSubmit(
                                new xSystemFailureProblemToType
                                {
                                    SystemFailureProblemID = problem.ID,
                                    SystemFailureTypeID = failureType.ID
                                });
                        }
                    }
                    else
                    {
                        // if type was unchecked but joining record exists, delete it
                        if (problemTypes.Contains(failureType))
                        {
                            xSystemFailureProblemToType toDelete = (from x in context.xSystemFailureProblemToTypes
                                                                    where x.SystemFailureProblemID == problem.ID &&
                                                                    x.SystemFailureTypeID == failureType.ID
                                                                    select x).SingleOrDefault();
                            context.xSystemFailureProblemToTypes.DeleteOnSubmit(toDelete);

                        }
                    }
                }
                context.SubmitChanges();
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }

        PopulateSystemFailureProblemData(problem);
        return View(problem);
    }

Lastly, I tweaked the View. This code will create 3 columns of checkboxes, the value attribute of each being the SystemFailureType ID it represents.

<div class="editor-field">
        <table>
            <tr>
                @{
                    int cnt = 0;
                    List<SystemFailures.Data.SystemFailureProblemTypeViewModel> types = ViewBag.Types;

                    foreach (var type in types) {
                        if (cnt++ % 3 == 0) {
                            @:  </tr> <tr> 
                        }
                        @: <td> 
                            <input type="checkbox" 
                                   name="selectedTypes" 
                                   value="@type.TypeID" 
                                   @(Html.Raw(type.Assigned ? "checked=\"checked\"" : "")) /> 
                            @type.TypeDescription
                        @:</td>
                    }
                    @: </tr>
                }
        </table>
    </div>

It may not be the most efficient, but I think it effectively solves the most complicated pieces of your problem. Let me know if I missed anything!

answered Jan 19, 2012 at 4:54

Michael Hornfeck

1,2721 gold badge16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Philter Over a year ago

Yeah I had an inkling that I would end up going this direction. I was just hoping there would be a better way using some built in binding, but it looks like that's not going to happen.

Michael Hornfeck Over a year ago

Yeah, I'm not sure of an easier way. It sounds like you are tied to using Linq2Sql, but if not, I recommend EF because it simplifies some things you are doing here (such as loading related objects).

Philter Over a year ago

So, for creating a new SystemFailureProblem, I would just pass a List of all of the failure types with none selected, correct?

Michael Hornfeck Over a year ago

Yes, that would be your Create action (GET). Your POST would receive a string[] containing selected values and iterate the same way we did in the Edit action.

Philter Over a year ago

One issue I ran into was that in order to insert/delete from a table Linq requires an index on the cross table. So I had to add that in.

|

Collectives™ on Stack Overflow

Generating Checkbox Lists with MVC3 using a complex viewmodel and a cross table with Linq to SQL

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related