Foreign Key violation when inserting nested collections in EF6 (database first)

Question

I have a nested collection in a database first project using Identifying Relationships

For example (showing the primary entity keys only):

+--Quotes--+          +------State-----+           +---State Info---+
|ID (int)  | -(1-*)-> | Quote_ID (int) |  -(1-*)-> | Quote_ID (int) |
+----------+          | State_ID (int) |           | State_ID (int) |
                      +----------------+           | Info_ID (int)  |
                                                   +----------------+

Note that in the first association, it is a one-to-many association where Quotes.ID --> Quote_ID. The second association is a one-to-many composite key who's principal keys are Quote_ID/State_ID.

Now, let's say that Quote already exist (as theQuote) and I'm adding a new state:

theQuote.States.add(new State with {.State_ID=5})

And now I'm adding a new state info:

StateObj.StateInfos.add(New StateInfo with {.Info_ID=38})

Now I hook up a SQL profiler and save it:

db.SaveChanges()

What is happening is that I see two inserts:

exec sp_executesql N'INSERT [dbo].[States]([Quote_ID], [State_ID])
VALUES (@0, @1)',@0=153888,@1=5

exec sp_executesql N'INSERT [dbo].[StateInfo]([Quote_ID],[State_ID],[Info_ID])
values (@0, @1, @2)',@0=0,@1=0,@2=38

Note that in the first insert, the child collection, EF understands the relationship and automatically inserts the correct quote_id. However, in the second insert, the grandchild collection, it doesn't properly synch up the association and does not know the proper values. This results in an unfortunate "the Insert statement conflicted with the FOREIGN KEY constraint ..." SQL error.

Now this scenario works just fine if the StateObj already exists and I'm only inserting StateInfo. I need to be able to insert both State and StateInfo.

Seems like a reasonable request of EF6, but for some reason, I cannot get this to work. I've tried changing the StoreGeneratedPattern property of the entity keys to "Identity" but to no avail.

Any thoughts?

John · Answer 1 · 2018-08-01T13:23:56.463

I have a reason why this is happening. Apparently, I have an overlapping key.

The table model I presented was not complete. I have an additional association that apparently creates the issue:

+--Quotes--+          +------State-----+           +---State Info---+
|ID (int)  | -(1-*)-> | Quote_ID (int) |  -(1-*)-> | Quote_ID (int) |
+----------+          | State_ID (int) |           | State_ID (int) |
                      | StateView (nav)|           | Info_ID (int)  |
                      +----------------+           +----------------+
                              ^                    
             +-StateView-+    | (1-*)
             | ID (int)  |----+
             +-----------+

The association is with StateView.ID as the primary and State.State_ID as the foreign key. If this relationship is removed, db.savechanges works as expected.

Alternatively, instead of using

theQuote.States.add(new State with {.State_ID=5})

Do this instead:

dim StateViewObj as StateView=db.StateView.Find(5)
theQuote.States.add(new State with {.StateView=StateViewObj})

This seems to work.

Foreign Key violation when inserting nested collections in EF6 (database first)

1 Answers1