Entity Framework Foreign Key violation with Save_Changes

Question

This goes around the delightful scenario in which Entity Framework refuses to allow the programmer the ability to change the order in which data is saved...

End Results: When I try to save my changes (commit to the SQLCE DB), I'm receiving an error with the inner exception of: "A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = ]"

Why this is happening: I have a section of my VB.Net program that allows the user to interact with the data residing in memory. As far as they user is concerned, it looks and feels like they are interacting with the Database. Once they are done with their work in this section, they can save/apply the changes or cancel them. I chose this route for easiest "backout" and reduced interactions with the DB (I didn't want to be making needless inserts/updates/deletes to the DB while the user changes things).

By canceling, I simply use the Context Refresh methods to reload the EF data from DB, thus wiping out anything they did. By saving/applying, I simply call the context's "Save_Changes" method to commit the current information once and for all. The DB is a local SQLCE and only the application with a single user is accessing it. I'm not concerned about multiple connections causing a concurrency issue with information.

The problem here, is that I have 3 tables, each with a one to many relationship for hierarchy: TableA 1->* TableB 1->* TableC

What is happening is that EF is trying to save changes in the DB to TableC first instead of TableA, so on and so forth. Well..... That kind of makes the FK constraint in the DB unhappy, because the DB constraint (which EF has been setup to match), complains that the entry being Saved in TableC cannot exist because the entry key referenced in TableB doesn't exist..... yet. Entity Framework is happy because all of these constraints are satisfied in the EF model in memory, but it's not committing them in the right order to the DB, thus, the violation.

I'm using Visual Studio 2010 Express (which limits me to SQLCE 3.5) and .net 4. I have ran all of the current Service Packs for VSE 2010, and to my knowledge, am running the latest version of EF allowed by this setup as my project references still show .net 4.0.0.0. I tried to implement EF 5, but it doesn't seem to be available for VSE 2010.

Will I simply be required to remove my constraints from the DB and use them solely in the Entity Framework Model? I really don't like that option, but if this EF version has a bug in which it is committing to the DB in the wrong order per the relationships, then I guess I don't have much of a choice until VSE 2012 is released....

EDIT: Here is the code that is responsible for populating the tables in memory. The file being read through has the order so that no child entry will be found without a parent already being added.

    Dim lines() As String = IO.File.ReadAllLines(dlgOpen.FileName)
    For Each line In lines
        'Remove surrounding whitespaces
        line = Trim(line)
        'Identify if line is MIB, Class or an Attribute
        If InStr(line, "MIB:", CompareMethod.Text) > 0 Then 'Found MIB entry

            'Create new entry in the OID_MIB table
            newMIB = SEDB.OID_MIB.CreateObject
            curMIB = Guid.NewGuid
            newMIB.M_ID = curMIB
            newMIB.Name = Split_Line(line, 3)
            newMIB.Description = ""
            SEDB.OID_MIB.AddObject(newMIB)
            MIBCount = MIBCount + 1
        ElseIf InStr(line, "Class", CompareMethod.Text) = 1 Then
            'Found Class entry
            'Create new entry in the OID_Class table
            newClass = SEDB.OID_Class.CreateObject
            newClass.M_ID = curMIB
            newClass.Name = Split_Line(line, 3)
            newClass.OID = Split_Line(line, 2)
            newClass.Description = ""
            newClass.Tip = ""
            SEDB.OID_Class.AddObject(newClass)
            ClassCount = ClassCount + 1
        ElseIf InStr(line, "attribute", CompareMethod.Text) = 1 Then
            'Found Attribute entry
            'Create new entry in the OID_Attribute table
            newAttribute = SEDB.OID_Attribute.CreateObject
            newAttribute.OID = Split_Line(line, 2)
            newAttribute.C_OID = Get_ClassOID(newAttribute.OID)
            newAttribute.Name = Split_Line(line, 3)
            newAttribute.Description = ""
            newAttribute.Tip = ""
            newAttribute.Type = ""
            SEDB.OID_Attribute.AddObject(newAttribute)
            AttributeCount = AttributeCount + 1
        End If
    Next

I would have a picture confirming proper setup of my constraints in both the Conceptual Model and DB, but I don't have enough reputation points to post pictures yet.

Answer: Well I'll be...... After doing some tinkering, I removed the constraint on the DB side for only TableC and TableB, if this was indeed an "order" issue, then it would still be broken, just with the constraint from TableB to TableA; however, it didn't' break.

Traversing the Saved data in the DB allowed me to identify two entries coming from the text file, in which would create an entry in the Attribute table (TableC) that did NOT have a corresponding value in the Class table (tableB). Thus, the FK violation.

The strange thing, is how this was NOT causing a violation during the import.

Regardless, I know the problem is in the data source and I will add logic to fix that. The problem is NOT in the EF order at save time, as already stated by Dabblernl.

Many thanks for your patience everyone.

Answer 1

After doing some tinkering, I removed the constraint on the DB side for only TableC and TableB, if this was indeed an "order" issue, then it would still be broken, just with the constraint from TableB to TableA; however, it didn't' break.

Traversing the Saved data in the DB allowed me to identify two entries coming from the text file, in which would create an entry in the Attribute table (TableC) that did NOT have a corresponding value in the Class table (tableB). Thus, the FK violation.

The strange thing, is how this was NOT causing a violation during the import.

Regardless, I know the problem is in the data source and I will add logic to fix that. The problem is NOT in the EF order at save time, as already stated by Dabblernl.

Many thanks for your patience everyone.