Saving Many to Many relationship - Entity Framework

Question

I have the following classes:

    public class item
    {
        public int NodeID
        {
            get ;
            set ;
        }
        public int Weight
        {
            get ;
            set ;
        }
        public int Category
        {
            get ;
            set ;
        }
    }
   public class Recipients
    {
        public int RecipientID
        {
            get ; set;
        }
    }

    public class Nodes
    {
        public List<int> RecipientList
        {
            get ;
            set ;
        }
        public item Item
        {
            get ; set;
        }
        public int Capacity
        {
            get ; set;
        }
        public int NodeID
        {
            get ; set;
        }
        public int Weight
        {
            get ; set;
        }
        public int Category
        {
            get ; set;
        }
    }

And I tried to save it in my existing DB that has the following tables:

1) Category
2) Items
3) Nodes (Node and Items has 1-1 relationship)
4) Recipients
5) NodeRecipients (This table show the many to many relationship between Nodes and Recipients)

I use VS2012 to create an EF model as illustrated by the diagram below (please note that Nodes derives from Items in the EF)

I have a method that tries to save the nodes and its recipients

   public void SaveNodeAndRecipient(List<Nodes> MyNodes)
    {
        using (var db = new MyEntities())
        {
            foreach (var n in MyNodes)
            {
                Node n1 = new Node() { NodeID = n.NodeID, categoryID = n.Category, Capacity = n.Capacity };
                db.Items.Add(n1);
                foreach (var r in n.RecipientList)
                {
                    Recipient rep;
                    if (!db.Recipients.Select(x => x.recipientID).Contains(r))
                    {
                        rep = new Recipient() { recipientID = r };
                        db.Recipients.Add(rep);
                    }
                    else
                    {
                        rep = db.Recipients.Where(x => x.recipientID == r).FirstOrDefault();
                    }
                    Node_Recipient nr = new Node_Recipient(){RecipientID=r,NodeID=n.NodeID};
                    n1.Node_Recipient.Add(nr);
                }
            }
            db.SaveChanges();
        }
   }

MyEntities is the EF model and was declared in the appconfig:

  <connectionStrings>
    <add name="MyEntities" connectionString="xxxxx" providerName="System.Data.EntityClient" />
  </connectionStrings>

Everything was fine and compiled with no problem until I tried to savechanges. I got this error (not very descriptive)

Anyone knows what is going on? I am under the impression that the many to many relationship is the culprit but can not pin point what is causing it. Please help!

Answer 1

The problem is that you're setting Node_Recipient.recipientID values, but from your description I'm pretty sure that these primary key values get generated by the database. So it is not guaranteed that the values (r) will be present when the objects get saved.

Worse yet - it's not even likely that the new Recipients will keep the assigned r values, so you may create false associations.

Here's what should work for you. See some comments below.

foreach (var n in MyNodes)
{
    Node n1 = new Node  { 
                          NodeID = n.NodeID,
                          categoryID = n.Category,
                          Capacity = n.Capacity
                        };
    foreach (var r in n.RecipientList)
    {
        Recipient rep = db.Recipients.Find(r);
        if (rep == null)
        {
            rep = new Recipient(); // see comment 1.
        }
        Node_Recipient nr = new Node_Recipient { 
                                                 Recipient = rep,
                                                 Node = n1
                                                 // See comment 2
                                               };
        n1.Node_Recipient.Add(nr);
    }
    db.Items.Add(n1); // see comment 3
}

1. No recipientId is set here.

2. Here you set the references in stead of the ID values. EF will assign the correct foreign key values "just in time" while saving changes.

3. This marks all object in the object graph under the new Node as Added, unless they are already know to the context, which is true for the Recipients you found by db.Recipients.Find(r).

As for the many-to-many association, the table Node_Recipient looks like a pure junction table, i.e. a table with nothing but two foreign keys. There must have been a reason why EF didn't generate a model with a transparent m:n association, without a Node_Recipient entity. Normally it would do that. When you generated the model, did Node_Recipient contain other columns that you removed later?

If you want this m:n association you may want to try and regenerate the model. This should produce a Nodes class with a Recipients collection and a Recipient with a Nodes collection. Setting the associations would then be a matter of adding new recipients to Node.Recipients.