EF 4.3 - Why an existing navigation reference property gets inserted again into the database when assigned to parent?

Question

Issue:

The user creates a new account. One of the required fields is BusinessGroup. BusinessGroup is a navigation reference property. The user select the BusinessGroup from a drop down box, the code searches for the BusinessGroup in the database, retrieves it and assigns it to the Account. Sounds pretty simple, right?

For some reason every time you save a new account it also inserts another BusinessGroup in the database table for BusinessGroups even though it already exists as I retrieved it from the database and assigned it directly yo the account. EF context still thinks it is a new one.

BTW, I use Code-First as I am following TDD approach.

Here is my POCO:

    [Table("Account")]
    public class Account
    {
        [HiddenInput]
        public int Id { get; set; }

        [Required(ErrorMessage = "The Name is required")]
        public string Name { get; set; }

        public Guid? BusinessGroupId { get; set; }
        [Required(ErrorMessage = "The Business Group is required")]            
        public BusinessGroup BusinessGroup { get; set; }
    }

Because I want to override the naming convention of the foreign key that is generated in the database I also specified the foreign key, BusinessGroupId above.

Here is the BusinessGroup POCO:

        [Table("BsuinessGroup")]
        public class BusinessGroup
        {
            [HiddenInput]
            [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
            public Guid Id { get; set; }

            [Required]                
            public string Name { get; set; }
        }

Here is the code that fetches the BusinessGroup from the database based on what the user selected from a drop down box, the Id is as GUID (note that this code lives inside an MVC custom model binder so it exposes the ModelBinderContext:

        public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
        {
            //see if there is an existing model to update and create one if not
            var account = (Account) bindingContext.Model ?? new Account();
            //find out if the value provider has a required prefix
            bool hasPrefix = bindingContext.ValueProvider.ContainsPrefix(bindingContext.ModelName);
            var prefix = hasPrefix ? String.Format("{0}.", bindingContext.ModelName) : String.Empty;
            _context = bindingContext;
            _prefix = prefix;

            //map the fields of the model object
            account.Id = Convert.ToInt32((GetValue("Id")));
            account.Name = (GetValue("Name"));
            account.Phone = (GetValue("Phone"));
            account.Fax = (GetValue("Fax"));
            account.Email = (GetValue("Email"));
            account.Website = (GetValue("Website"));

            account.Audit = new Audit{Created = DateTime.Now, CreatedBy = "renso"};

            //I changed this to assign the ID rather than assign the BusinessGroup itself since that did not work 
            //and causes a new BusinessGroup to be inserted into the DB on every save of an account,
                           // this solution seems to work but I am not sure if this is the correct behavior.
            **var tempBusinessGroup = ((Controllers.AccountController)controllerContext.Controller).
                BusinessGroupRepository.Find(GetGuidValue("BusinessGroup"));
            account.BusinessGroupId = tempBusinessGroup.Id;**

            return account;
        }
        private Guid GetGuidValue(string key)
        {
            var vpr = _context.ValueProvider.GetValue(_prefix + key);
            return vpr == null ? new Guid() : new Guid(vpr.AttemptedValue);
        }
        private string GetValue(string key)
        {
            var vpr = _context.ValueProvider.GetValue(_prefix + key);
            return vpr == null ? null : vpr.AttemptedValue;
        }
.............
    }

The code in bold above where I assign a value (GUID) to the BusinessGroupId rather than the BusinessGroup seems to work correctly. Is that the intended behavior of EF, a bug or what?

If I changed it to the code below it causes the issue where a new BusinessGroup gets created when I save the new account:

   account.BusinessGroup = ((Controllers.AccountController)controllerContext.Controller).
        BusinessGroupRepository.Find(GetGuidValue("BusinessGroup"));

Here is my Action:

[HttpPost]
public ActionResult Create(Account account)
{
    if (ModelState.IsValid)
    {
        AccountRepository.InsertOrUpdate(account);
        AccountRepository.Save();
        return RedirectToAction("List");
    }
    var viewData = new AccountControllerViewData { Account = account, BusinessGroups = BusinessGroupRepository.All };
    return View(viewData);
}

The save also fails, for some reason I need to switch off ValidateOnSaveEnabled=false in the Save on the AccountRepository, not sure why for it to work at all, it complains about the BusinessGroup being missing as I set the BusinessGroupId property of the account:

public void InsertOrUpdate(Account account)
{
    if (account.Id == default(Int32))
    {
        // New entity
        _context.Account.Add(account);
    }
    else
    {
        // Existing entity
        _context.Entry(account).State = EntityState.Modified;
    }
}

public void Save()
{
    //need the validation switched off or it fails with an error
    _context.Configuration.ValidateOnSaveEnabled = false;
    _context.SaveChanges();
}

It feels like I am missing something here. I could not find the answer in EF 2ed, Code-First or DbContext books. It seem when you define your own foreign keys in your POCO, then whenever you want to assign a navigation reference to in my example from an Account to a BusinessGroup, you cannot assign to the BusinessGroup directly, you have to asign a value/key to the foreign key, in this example BusinessGroupId. Am I doing something wrong?

Answer 1

Most likely your BusinessGroupRepository and your AccountRepository are having their own context instances. When you load the BusinessGroup from the BusinessGroupRepository it happens in another context than the context in AccountRepository where you save the new account with. For the second context BusinessGroup is an unknown entity and EF will save it as a new entity.

Refactor your code to make sure that both repositories will use the same context instance. The context should not be created within the repositories but created outside and injected into both instead. Alternatively merge the two repositories into one single repository.

Answer 2

Indeed, re-factored the code so that it uses the same DbContext to retrieve the account and business group, that works as expected, Thanks! The question now is that with my repository pattern, I inherit from an abstract class that news up a new context for me, but that still wont solve the problem that I have that each repository has its own context, how did you guys resolve that issue? For example you create a new account, it has a navigation reference for business group, country, state (like PA, NJ), and others, each one has its own repository, for example to maintain lists of countries in the database. An account has a country navigation reference, what pattern should one follow to share the same context, one per http request I would think?