Insert statement conflicts with FK constraint

[Bastiaan]

Evaluating OpenAccess, I created two tables on SQL Server 2008 R2: Master and Detail.

Both have a primary key set which is also an identity. The Detail table contains an foreign key constraint on field MasterId to Id of the Master table.

When I execute the following code, the exception below is thrown:

using (EntitiesModel Model = new EntitiesModel())
            {
                Master NewMaster = new Master();
                NewMaster.Name = "Stane";

                Detail NewDetail = new Detail();
                NewDetail.Street = "Stane's new street";

                NewMaster.Details.Add(NewDetail);

                Model.Add(NewMaster);
                Model.SaveChanges();
            }

Insert of '1829379833-' failed: Telerik.OpenAccess.RT.sql.SQLException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Detail_Master". The conflict occurred in database "OATest", table "dbo.Master", column 'Id'.
The statement has been terminated.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
INSERT INTO [Detail] ([MasterId], [Street]) VALUES (?, ?)
select scope_identity()
(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Detail_Master". The conflict occurred in database "OATest", table "dbo.Master", column 'Id'.
The statement has been terminated.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)


Why is OpenAccess not handling the Id generation correctly?

Kind regards,
Bas

[IT-Als]

Hi Bas,

Have you tried doing the Model.Add(NewMaster) just after initializing it like:

Master NewMaster = new Master();
Model.Add(NewMaster);

Doing so will allow OA to handle change tracking of the added object.

Also if the above do not help. Check in the visual designer that both the Master and the Detail classes have their key generation property set up correctly.

Regards

Henrik

[Bastiaan]

Hi Henrik,

Thanks for the suggestion, that makes sense.

I made the change to my code:

Master NewMaster = new Master();
Model.Add(NewMaster);

NewMaster.Name = "Stane";

Detail NewDetail = new Detail();
NewDetail.Street = "Stane's new street";

NewMaster.Details.Add(NewDetail);

Model.SaveChanges(); // <-- exception

Still get an exception, but it is different now:

Insert of '1829379833-' failed: Telerik.OpenAccess.RT.sql.SQLException: Cannot insert the value NULL into column 'MasterId', table 'OATest.dbo.Detail'; column does not allow nulls. INSERT fails.
The statement has been terminated.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)
INSERT INTO [Detail] ([MasterId], [Street]) VALUES (?, ?)
select scope_identity()
(set event logging to all to see parameter values) Telerik.OpenAccess.RT.sql.SQLException: Cannot insert the value NULL into column 'MasterId', table 'OATest.dbo.Detail'; column does not allow nulls. INSERT fails.
The statement has been terminated.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.PreparedStatementImp.execute()
   at OpenAccessRuntime.Relational.conn.PooledPreparedStatement.execute()
   at OpenAccessRuntime.Relational.RelationalStorageManager.generateInserts(NewObjectOID oid, Int32 index, ClassMetaData cmd, PersistGraph graph, Int32[] fieldNos, CharBuf s, Object[] oidData, IntArray toUpdateIndexes)


I would expect that OA knows about this Id because of the association (FK constraint) between Master and Detail.

I checked the key generation for both tables and the Identity Mechanism properties are set to "DatabaseServerCalculated". I think this is the correct setting (as per other forum posts) since Default, or HighLow require the voa_keygen table inside the database for internal tracking of Id's.

[IT-Als]

Hi Bas,

Yes, this seems to be correct. This is a 1:m association right?
Can you check how this is mapped. As you are having af FK column in the Detail table I am guessing this is a 1:m mapping without a join table.
Please check if the association is "Managed". Per default all associations are unmanaged.

If you select an association as to be "Managed" OA wil manage the relationsship regardless of how you add items to the list involved in the association.
As a last resort try adding the Detail to the Context (Model) also.



Regards

Henrik

[Bastiaan]

The problem seemed to be the unmanaged navigation properties of both tables. Changing this to "true" immediately resolved the problem.

Is there a specific reason why OA sets these by default to false? It seems to me that a relationship like this enforced with an FK constraint, using identity seeds on primary keys is one of the most common scenario's there is in the relational world.

I am just wondering if there is any specific design consideration why this was implemented this way. Since this is also not very straightforward from the documentation (howto's and so on) it might put off potential customers whom do not take the time to post it in a forum :-)

Anyway, thanks for your assistance.. it was really helpful.

[IT-Als]

Hi Bas,

Glad I could help out.

As for the design considerations for this being default unmanaged I am not sure, but I think I have read a post earlier, that this association management becomes quite expensive performance wise if you have deeply nested models with many (and I mean MANY) interrelated objects.
Maybe someone from Telerik can elaborate on the exact reasons?

Regards

Henrik

[Bastiaan]

For very big domain models I would definitely expect to see a performance penalty if the hierarchy goes many levels deep, or if the collection of data contained within even a smaller object hierarchy is simply enormous.

But I think these are likely to be more the exception than the rule.

So by default I would expect the framework to configure itself according to best practices or most common use cases. Even if such big domain models are encountered, OA could make potential performance issues known during the design phase in one way or another (during the use of the wizard, compile time warnings, etc).

I hope someone from Telerik will comment on the matter.

[Bastiaan]

Just did an "unmark as answer" so hopefully someone will notice the thread..

[Ivailo]

Hello Bastian,

Please accept my apologies for the late reply.

The reason behind keeping the IsManaged to False by default is purely due to performance considerations. Our assumption is that you will not need it switched to True for all the cases, but only for the navigation properties you are planning to use in this way. For instance, you have a linkage between two classes, but you might be using it for read-only purposes only or for updating the data without changing the link (by adding/removing items from the collection). For this reason we decided that switching this value to True should be left to your decision, and for explicit setup - it is a pure defensive measure.

You are right that the impact of this property is not very clear at the moment, so we will discuss possible ways to make it more obvious in order to prevent other clients from spending time on fighting with issues like the one you had. 

Thank you for your input and do not hesitate to get back to us if you have further comments or ideas.

All the best,
Ivailo
the Telerik team

[IT-Als]

Hello Ivailo,

Thanks for clearing up the reasons behind this.

I think it would be a nice idea to be very clear about this "Managed" collection being set to False by default.
As a developer I would intuitive expect that if I add an element to a list the reverse reference is also handled for me... the opposite might be a discussion worth..
While I can understand the reasons behind setting it to False, please be concise in docs about the impact.
I have already answered a lot of threads regarding this issue in the forums.

Regards

Henrik

[Ivailo]

Hi Henrik,

You are right - we have noticed this trend as well. We will perform the necessary changes in documentation and probably even other client resources where applicable.

Thanks for the input.

Greetings,
Ivailo
the Telerik team

[Adriaan]

Is there a way to change this default value on my local installation?
I am starting to lose my mind trying to find the navigation properties I forgot to change to managed!

[Kristian Nikolov]

Hi Adriaan,

While currently there is no configuration which allows to alter the default value of IsManaged, it is possible to quickly and easily alter the value for the multiple or all members in the model.

To do this you would need to use the Model Operations dialog. If all of the associations in your model need to have the same value for the IsManaged property you will be able to set it without the need to identify the exact properties that participate in the association. These are the steps you would need to go through to achieve this:

1. Open the Model Operations dialog.
2. Select Members for the Display Mode.
3. Select all members displayed in the grid.
4. Select the Change IsManaged model operation from the drop-down list.
5. Chose the required value and press the Execute button.

This will set the value of the IsManaged property for all properties which define an association to the chosen value. Note that properties which already have the same IsManaged value as the chosen one will be ignored.

I hope this helps. Feel free to post at our forums again should you have additional questions.

Regards,
Kristian Nikolov
Telerik