Updating a list of foreign keys in EF4, using MVC 2 Repository Viewmodel Pattern.

Question

Okay, I'm really struggling with how to update a list of foreign keys in MVC2/EF4. I have a one to many relationship between a Template object which can have many or no TemplateScenario objects.

Essentially, I have an edit method in a controller that is trying to do this:

// POST: /Modes/Edit/1
        [HttpPost]
        public ActionResult Edit(int id, FormCollection formValues)
        {
            Template template = _templateRepository.GetTemplate(id);

            TemplateCreateViewModel viewModel = new TemplateCreateViewModel();
            viewModel.Template = template;
            viewModel.TemplateScenarioList = template.TemplateScenarios.ToList();

            //Update the model
            UpdateModel(viewModel);
            UpdateModel(viewModel.Template.TemplateScenarios, "TemplateScenarioList", new[] { "ScenarioID", "TemplateID" });

            _templateRepository.Save();

            return RedirectToAction("Edit", new { id = template.TemplateID });

        }

This code successfully updates the 'template' object. It also adds the 'templatescenario' child objects BUT only if it is the first time I have added 'templatescenarios' to this particular template. If any templatescenario objects already exist for a given template, and I try to update them based on the new list, I get this error:

"The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted."

The _templateRepository.Save(); is just calling the entities.SaveChanges() EF4 method.

I can solve this in a dirty way by passing down a list of templatescenario ids to my repository class in a custom 'update' method that looks like this:

public void Update(Template template, IList<int> templateScenarios)
    {

        //Delete Old Entries
        foreach (TemplateScenario ts in entities.TemplateScenarios)
        {
            if (ts.TemplateID == template.TemplateID)
            {
                if (templateScenarios == null)
                    entities.TemplateScenarios.DeleteObject(ts);
                else if (!templateScenarios.Where(tsl => tsl == ts.ScenarioID).Any())
                    entities.TemplateScenarios.DeleteObject(ts);
            }
        }

        //Don't need to add anything if they are null.
        if (templateScenarios == null)
            return;

        //Add New Entries
        foreach (int ts in templateScenarios)
        {
            if (!entities.TemplateScenarios.Where(tsc => tsc.ScenarioID == ts && tsc.TemplateID == template.TemplateID).Any())
            {
                TemplateScenario tempScenToAdd = new TemplateScenario();
                tempScenToAdd.ScenarioID = ts;
                tempScenToAdd.TemplateID = template.TemplateID;
                entities.TemplateScenarios.AddObject(tempScenToAdd);
            }
        }

    }

But that just feels dirty and I think I'm so close with the first, more automatic method. I've scoured the internet and found some similar posts on stackoverflow but am finding it difficult to reach that 'aha' moment.

Thanks,

Tom.

Answer 1

Incidently, I sorted out my problem.

The problem was my joining table was incorrectly using it's own primary key instead of using a composite key based on two foreign keys. This is obviously wrong /bad practice and EF4 and UpdateModel() don't play nice.

I had inherited the DB design from an ex-collegue and thus had taken the db design as correct without thinking too much about it. Very stupid of me, I know.