Entity Framework and Join Tables.

I had a true join table (two foreign keys which, together, were a composite key in the join table).  I followed the steps enumerated in Julie Lerman’s Blog Don't Be Iffy and got unexpected behavior.  I was not only inserting new rows where they belonged, but was adding to one of the tables with the foreign key!  Not good!

Since she is the “go-to-Guru” I was baffled!  Then I realized the issue, and used Julie’s Platinum Rule to solve the problem: “The Platinum Rule is that an object graph (for example when a customer is attached to an order which is attached to some line items) must be completely in or completely outside of the ObjectContext.”

Briefly, the Use Case went something like this:  School District personnel needed to register students into different schools.  However, they needed the flexibility to pause those registrations at any time, and then to pick up where they left off.  The students would not become part of the “real” tables in the database until all of that students registration information was complete.  As a DBA, I built a “side-by-side” infrastructure, and only when the finish button was pushed would I move the paused student over to the “real” tables.

I had tables and Entity Framework entities called:
Student
School
SpecialCircumstance
PausedStudent
PausedSchool

In addition, I had two join tables (for each join table: two foreign keys which, together, were a composite key in the join table).:
SchoolSpecialCircumstance
PausedSchoolSpecialCircumstance

These two join tables don’t show up as entities, but Navigation Properties in each of the foreign key entities.  So, in other words, PausedSchool had a NavigationProperty called SpecialCircumstance, and School had a NavigationProperty called SpecialCircumstance.  Likewise, SpecialCircumstance had two NavigationProperties, one called School and one called PausedSchool.

When the finish button was clicked I should have had one new row in SchoolSpecialCircumstance that had a FK to School and a FK to SpecialCircumstance.  That row was successfully inserted.  HOWEVER, in addition, I had a new row in SpecialCircumstance with a new ID number, which was the same ID number as the FK in SchoolSpecialCircumstance.  Not acceptable!  The SpecialCircumstance were a finite list of pre-defined items, and this was in effect duplicating that data.

The issue arose because I was adding objects to the School object, which had a FK to the Student object.  At the end of this “chaining” I called AddToStudent on the ObjectContext so that all the FKs were populated correctly:

newSchool.otherObject.Add(newObject);
newSchool.anotherObject.Add(newAnotherObject);
newSchool.SpecialCircumstance.Add(newSpecialCircumstance);
newStudent.School.Add(newSchool);
ctx.AddToStudent(newStudent);
ctx.SaveChanges();

So what is the [workaround]?  The ObjectContext has to know about the FK relationships before it can do anything with them!

newSchool.otherObject.Add(newObject);
newSchool.anotherObject.Add(newAnotherObject);
newStudent.School.Add(newSchool);
ctx.AddToStudent(newStudent);

newSchool.SpecialCircumstance.Add(newSpecialCircumstance);

ctx.SaveChanges();

And Voila!  Problem solved!  Just goes to prove that “timing is everything in this world!”

Print | posted on Tuesday, September 8, 2009 5:02 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © intermark

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski