Save a relation with between two entities an N-N association

Question

I've a Entity Framework 4.0, with poco object. the edmx model file is generated from the database.

This datacontext is accessed through WCF service, it's only mean that I receive some objects and I need to attach them to the current datacontext(or reload them with the key correspondance).

Everything seems to work fine, except for one case:

I've a N-N relationship between two table, so I've an association table, without any field other than ID of two tables: Database model

LINQ transform this into the following schema, this seems to be right. LINQ Data model

When I retrieve data, there is no problem, data I've inserted myself in the Right_group are correctly transformed into "new object in my collection of Rights/Groups".

But if I try to modify something and save, it doesn't work

 public void SaveRights(Group group, List<Rights> rights){
     //here, group and rights are objects attached to the database
     group.Rights.Clear();
     group.Rights.AddRange(rights);
     _dataContext.SaveChanges();
 }

So my question is: How to save this "relationship" of two objects ? Thank you!

**Doesn't work** is not the best description of unexpected behavior. So what happens if you execute SaveChanges? By attached to the database do you mean that you have load them on the same instance of _dataContext? — Ladislav Mrnka, Feb 17 '11 at 08:34
Excuse me, when the SaveChanges() is done, data are not saved in the database. I mean that when I send object to the WCF side, I detach object of my datacontext, and they aren't tracked anymore. So when I come in my server again, I need to attach these properties and apply changes — J4N, Feb 19 '11 at 07:31

score 8 · Accepted Answer · answered Feb 17 '11 at 06:59

If you want to avoid loading the objects from the database first you can do it like this(Code taken from one of my aplications so you will have to adapt it):

    public void AddAndRemovePersons(int id, int[] toAdd, int[] toDelete)
    {
        var mailList = new MailList { ID = id, ContactInformations = new List<ContactInformation>() };        
        this.db.MailLists.Attach(mailList);

        foreach (var item in toAdd)
        {
            var ci = new ContactInformation { ID = item };
            this.db.ContactInformations.Attach(ci);
            this.db.ObjectStateManager.ChangeRelationshipState(mailList, ci, ml => ml.ContactInformations, System.Data.EntityState.Added);
        }

        foreach (var item in toDelete)
        {

            var ci = new ContactInformation { ID = item };
            this.db.ContactInformations.Attach(ci);
            this.db.ObjectStateManager.ChangeRelationshipState(mailList, ci, ml => ml.ContactInformations, System.Data.EntityState.Deleted);
        }
    }

I found deleting the relationship as hard as creating it so I left that code in there. One thing about this solution is that both the maillist and the contacts exist prior to this function being run. I attach them to make the state manager track them.

If you are adding new objects that you also want to save you would use the

this.db.MailLists.AddObject(you new item here)

I hope that helps!

I found in parralells that using the ChangeRelationShipState was the key of my problem. It's a little dirty, but it works — J4N, Feb 17 '11 at 12:51
In fact, I think that because I'm using ObservableCollection instead of List in my POCO object, LINQ cannot track them as it wants. — J4N, Feb 19 '11 at 07:32
For EF and POCO I think you must use ICollection<> so that EF can override the collection. I can't find where I read it right now though:/ — Mikael Eliasson, Feb 19 '11 at 08:01

score 0 · Answer 2 · answered Feb 17 '11 at 07:06

Just a thought... how are the keys setup in the Right_Group table? If you use both IDRight and IDGroup together as primary key - this problem might occur. One suggetion is to add a new column (ID) into the Right_Group table, and having this ID as the primary key. Then use foreign keys on the other columns (IDRight, IDGroup) respectivly.

Save a relation with between two entities an N-N association

2 Answers2

Linked