Entity Framework 5 won't insert self referencing many to many relationship

Question

I can't figure out why this configuration won't generate an insert statement to the db. I'm checking via SQL Profiler.

Here is my model via Visio:

visiomodel

Here is my edmx (this is database first). The circle shows the self referencing relationship back to GeoBoundary.

edmxmodel

Here is my code:

public void UpdateAssocs(Dictionary<int, List<int>> fromTo) {
  //iterate through each dictionary entry
  foreach (KeyValuePair<int, List<int>> entry in fromTo) {
    using (TransactionScope scope = new TransactionScope()) {
      //get a reference to the parent geoboundary for this entry
      GeoBoundary parent = contactContext.GeoBoundaries
        .FirstOrDefault(x => x.GeoID == entry.Key);
      //test to see if the parent is null, it shouldn't be b/c this dictionary was generated 
      // from a list of database values (but shit happens so throw an error if it is null)
      if (parent != null) {
        foreach (int childID in entry.Value) {
          //check to see if the child exists in the parents list of children
          GeoBoundary child = parent.GeoBoundaryAssocTo
            .FirstOrDefault(x => x.GeoID == childID);
          if (child == null) {
            //get a ref to the GeoBoundary that SHOULD be tied to the parent (it should exist but there just 
            // isn't an established relationship in the db)
            child = contactContext.GeoBoundaries
              .FirstOrDefault(x => x.GeoID == childID);
            //check the damn thing again b/c you never want to assume...
            // but if it's still null then do nothing!
            if (child != null) {
              parent.GeoBoundaryAssocTo.Add(child);                  
              contactContext.SaveChanges();
            }
          }
        }
      }
      else {
        throw new Exception(@"Parent GeoID passed to UpdateAssocs method or GeoID is null.");
      }
      scope.Complete();
    }
  }
}

When I get to parent.GeoBoundaryAssocTo.Add(child); in the debugger I made sure that parent and child both exist, then I step through but I get nothing in profiler. What gives? Is it a problem that both entities already exist in the db and I'm not changing anything but the relationship? If so then how can I mark the relationship as changed so EF will generate the insert?

EDMX Details:

    <AssociationSet Name="GeoBoundaryAssociation" Association="Contact.GeoBoundaryAssociation">
      <End Role="GeoBoundary" EntitySet="GeoBoundaries" />
      <End Role="GeoBoundary1" EntitySet="GeoBoundaries" />
    </AssociationSet>

    <Association Name="GeoBoundaryAssociation">
      <End Type="Contact.GeoBoundary" Role="GeoBoundary" Multiplicity="*" />
      <End Type="Contact.GeoBoundary" Role="GeoBoundary1" Multiplicity="*" />
    </Association>

@ScottStafford - In the case that something in the `Dictionary>` isn't right I want the whole thing to fail. — Chris, Jan 02 '13 at 19:06
Sure - but it'll do that anyway if you don't SaveChanges() on the contactContext and just throw the contactContext away instead. The context is a unit of work -- you only need TransactionScope when you want to be able to roll back AFTER SaveChanges() or things like that... — Scott Stafford, Jan 02 '13 at 19:39
Can you paste the relevant .edmx XML, like the answerer pastes here: http://stackoverflow.com/a/9760255/237091 — Scott Stafford, Jan 02 '13 at 19:39
.edmx XML posted, let me know if it's not the correct parts. — Chris, Jan 02 '13 at 19:57

score 1 · Answer 1 · answered Jan 02 '13 at 17:49

1

You need to make sure your navigation property is attached to the current DBContext. Otherwise EF will ignore it.

try this:

...

//get a reference to the parent geoboundary for this entry
GeoBoundary parent = contactContext.GeoBoundaries
    .include("GeoBoundaryAssocTo")
    .FirstOrDefault(x => x.GeoID == entry.Key);

...
// You may need to use include on your child entity too.

answered Jan 02 '13 at 17:49

Ben Tidman

2,129
17
30

did you try pulling in the navigation property for the child item too? – Ben Tidman Jan 02 '13 at 18:34
What about including both ends of the many to many on both like this: GeoBoundary parent = contactContext.GeoBoundaries .include("GeoBoundaryAssocTo").include("GeoBoundaryAssocFrom") .FirstOrDefault(x => x.GeoID == entry.Key); – Ben Tidman Jan 02 '13 at 19:02
I tried that too. I'm find blogs where others have been successful in making this work so I'm not losing hope. – Chris Jan 02 '13 at 19:07
What's interesting is that in the debugger I can see where the GeoBoundaryAssocTo collection counter ticks up by one when `parent.GeoBoundaryAssocTo.Add(child);` executes. It's like EF just doesn't recognize that it's changed. – Chris Jan 02 '13 at 19:10

score 0 · Accepted Answer · answered Jan 03 '13 at 13:30

Well I'm the source of my own woes. I didn't realize that I was reusing my contactContext (instantiated as a service class) and in an earlier method method call I had set AutoDetectChangesEnabled = false;. All I needed to do was switch the change tracking back on. Somehow it never occurred to me that the service class had maintained it's state from previous calls. Live and learn.

Entity Framework 5 won't insert self referencing many to many relationship

2 Answers2