EF 4.1 code-first SQL ce 4.0 update collection exception

Question

I'm using EF 4.1 code first with SQL ce 4.0

I have two classes

public class Customer
{
    public int ID { get; set; }

    public string CompanyName { get; set; }

    public List<ContactPerson> ContactPersons { get; set; }
}

public class ContactPerson
{
    public int ID { get; set; }

    public string Name { get; set; }

}

and a DbContext

public class MyDB : DbContext
{
    public DbSet<ContactPerson> ContactPersons { get; set; }

    public DbSet<Customer> Customers { get; set; }

    public MyDB()
    {
        this.Configuration.LazyLoadingEnabled = true;
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Customer>()
            .HasMany(c => c.ContactPersons)
            .WithRequired()
            .WillCascadeOnDelete(true);
    }
}

At same time in the code I need to update the whole collection of ContactPerson of a Customer.

List<ContactPersons> modifiedContactPersons;
Customer customer = MyDB.Customers.Find(ID);
customer.ContactPersons = modifiedContactPersons;

when I call MyDB.SaveChanges() I get the following exception:

An error occurred while saving entities that do not expose foreign key properties for their relationships. The EntityEntries property will return null because a single entity cannot be identified as the source of the exception. Handling of exceptions while saving can be made easier by exposing foreign key properties in your entity types. See the InnerException for details.

with InnerException:

A relationship from the 'Customer_ContactPersons' AssociationSet is in the 'Deleted' state. Given multiplicity constraints, a corresponding 'Customer_ContactPersons_Target' must also in the 'Deleted' state.

I understand what this does imply but I cannot solve the problem by myself. Any suggestions?

score 8 · Accepted Answer · answered Jul 19 '11 at 09:58

The problem here is this:

customer.ContactPersons = modifiedContactPersons;

It replaces list of old related contact persons with list of new related contact persons and it does two operations:

It first mark relation to all previously related persons to Deleted
It then attach all new contract persons and mark relation with them as Added

The first operation is a problem because deleting relation doesn't delete related entity so you end up with ContactPerson which is not related to any Customer and it is not allowed by your mapping (FK is not nullable).

Solving this problem depends on the requirement you are trying to achieve. If you really want to delete all related persons first you must set state of each to deleted manually before you assign a new list. If you want to update existing persons you should not do this at all. You should iterate persons related to customer and modify their data from a new list. Why? Because:

If you delete entity and insert a new one instead of updating existing one you will have two database modification statements instead of one = two roundtrips to database instead of one and in case of many entities it will slow your application a lot
If you have any other entity dependent on ContractPerson you cannot delete it. Also if you use ContractPerson's Id somewhere and if it is auto generated it will not exists any more after you delete original record.
It is generally terribly wrong and lazy approach. Insert new records, modify existing records and delete only records which should really be deleted.

You write: "You should iterate persons related to customer and modify their data from a new list". How do I do that in the simplest way? — esba, Jul 21 '12 at 12:09

EF 4.1 code-first SQL ce 4.0 update collection exception

1 Answers1