INSERT Order issue leads to foreign key violation

Posts   
 
    
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 27-Jan-2018 16:17:59   

Hi,

I'm having an issue when saving an entity graph. There are two sub-entities in this graph, that have a foreign key relation to each other. When two such entities are added, the save operation inserts them in the wrong order, resulting in a foreign key violation.

There are three Tables: - TRoot - TSqlServer - TApplication; has a foreign key to TSqlServer

The operation consists of fetching the root entity, manipulating it on a form and then saving it again. The root entity is fetched with the following prefetch:

path = new PrefetchPath2(RootEntity.EntityType) path.Add(RootEntity.PrefetchPathTSqlServer_TRoot_Id_EntityCollection) path.Add(RootEntity.PrefetchPathTApplication_TRoot_Id_EntityCollection)

The user then adds a TSqlServer Entity as well as a TApplication Entity referencing the new TSqlServer Entity.

All Primary Keys involved are GUIDs that are generated upon INSERT by SQL Server.

When saving the entity (with recursive=true), I get an Exception "The INSERT statement conflicted with the FOREIGN KEY constraint FK_TApplication_TSqlServer...

I'm using SD.LLBLGen.Pro Runtime Framework with version 5.0.6.

I'm probably missing somthing.

Thanks!

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 29-Jan-2018 06:31:31   

acl wrote:

  • TRoot
    • TSqlServer
    • TApplication; has a foreign key to TSqlServer

Hi acl. Please describe the relations between tables (1:n, m:1, etc). If you could include the DDL script for those three tables, better.

acl wrote:

path = new PrefetchPath2(RootEntity.EntityType) path.Add(RootEntity.PrefetchPathTSqlServer_TRoot_Id_EntityCollection) path.Add(RootEntity.PrefetchPathTApplication_TRoot_Id_EntityCollection)

From this path doesn't seem like the entities are referencing each other.

acl wrote:

When saving the entity (with recursive=true), I get an Exception "The INSERT statement conflicted with the FOREIGN KEY constraint FK_TApplication_TSqlServer...

I tried a similar setup with no issues. I used Northwind and added a Shipper.ContactEmployeeId to force a graph like yours, so:

var adapter = new DataAccessAdapter();

var order = new OrderEntity(10248);
var path = new PrefetchPath2((int)EntityType.OrderEntity);
path.Add(OrderEntity.PrefetchPathEmployee);
path.Add(OrderEntity.PrefetchPathShipper);

adapter.FetchEntity(order, path);
            
var newEmployee = new EmployeeEntity();
newEmployee.FirstName = "David";
newEmployee.LastName = "Elizondo";

var newShipper = new ShipperEntity();
newShipper.CompanyName = "ACME, Inc";
newShipper.ContactEmployee = newEmployee;

order.Shipper = newShipper;
            
adapter.SaveEntity(order, true, true);

Does it looks like your example?

David Elizondo | LLBLGen Support Team
acl
User
Posts: 91
Joined: 28-Mar-2012
# Posted on: 29-Jan-2018 10:12:48   

Thanks, Daelmo!

It's working now.

The crucial bit that I was missing is that the foreign key Entity has to be initialized.

So instead of

order.Shipper = newShipper;

I only assigned the foreign key value:

order.Shipper_Id = newShipper.Id;

Thanks for you help.