Greetings.

I have an enity called DisputeMaster that has a 1:m relationship with a collection of dispute details. This DisputeDetailEntity has a 1:1 relationship with a DisputeDetailDocument entity. I can create a new dispute master entity and move existing dispute details to it fine by updating the PK. I am running into a FK constraint exception when I try to attach the disputedetaildocument entity to a disputedetail that I am updating the PK on in the same transaction. It seems to be the sequence that LLBL is generating the two update statements. Any help would be greatly appreciated.

I'm not sure what you mean by updating the PK.

But anyway could you please post a simple code snippet that reproduces your issue.

//Move approved details to approved side DisputeDetailEntity approvedDetail = new DisputeDetailEntity(); for (int i = _approveddisputedetail.Count - 1; i >= 0; i--) { approvedDetail = _approveddisputedetail[i]; int claimdetailid = approvedDetail.ClaimDetailId; approvedDetail.ClaimId = _dmApproved.ClaimId; _dmApproved.DisputeDetailCol.Add(approvedDetail); _approveddisputedetail.Remove(approvedDetail); _dmDenial.DisputeDetailCol.Remove(approvedDetail); }

//When I save _dmApproved the disputedetail the claimid properly gets upddated

//if I add that line of code it throws a FK on an update statement //Move approved details to approved side DisputeDetailEntity approvedDetail = new DisputeDetailEntity(); for (int i = _approveddisputedetail.Count - 1; i >= 0; i--) { approvedDetail = _approveddisputedetail[i]; int claimdetailid = approvedDetail.ClaimDetailId; approvedDetail.ClaimId = _dmApproved.ClaimId; //added approvedDetail.DisputeDetailDocument.ClaimID = DisputeDetailDocument.ClaimId //end added line _dmApproved.DisputeDetailCol.Add(approvedDetail); _approveddisputedetail.Remove(approvedDetail); _dmDenial.DisputeDetailCol.Remove(approvedDetail); }

It seems that _dmApproved is a new entity.

So it got inserted first then the PK value is fetched from the database and used to set the ClaimID of the related child entity (approvedDetail ) when being inserted.

approvedDetail.DisputeDetailDocument.ClaimID = DisputeDetailDocument.ClaimId

The above line actually sets the DisputeDetailDocument.ClaimID to Zero. As there is no value for the DisputeDetailDocument.ClaimId at runtime.

And there is nothing to do to tell the framework to update this value when inserting.

IMHO, having ClaimID in the DisputeDetailDocument is redundant and not a normalized design, as it already got that reference through DisputeDetail.

I agree that having claimid is redundant and not normalized but we cannot change the structure of the database at this point because there are over 67 mill records in the DisputeDetail table.

Quote: And there is nothing to do to tell the framework to update this value when inserting.

By this do you mean that LLBL does not know that these entities are related by a 1:1 relationship? I would think that it should be able to update the DisputeDetail record to the new claimid and then insert the DisputeDetailDocument with the claimid updated.

By this do you mean that LLBL does not know that these entities are related by a 1:1 relationship? I would think that it should be able to update the DisputeDetail record to the new claimid and then insert the DisputeDetailDocument with the claimid updated.

LLBLGen Pro Framework knows that these 2 fields have a 1:1 relation, but the ClaimId has nothing to do with this relation, am I correct? If yes, then nothing relates the ClaimId fields in both sides. And nothing says which side should copy the value in the other side.

The claimid is part of the primary key in both the DisputeDetailEntity and the DisputeDetailDocument Entity as well as part of the foreign key(claimid,claimdetailid) in the DisputeDetailDocumentEntity.

I'll jump in here as I'm working with Jeff to help him get this working.

I've attached a zip file with a sample llblgen project file, database creation script and a vs2010 c# project with comments that we can use to work through specifics.

As Jeff said we are not at liberty to change the primary key structors of the fk constraints.

DisputeMaster PK identity int (claimId) DisputeDetail PK int (claimId) and int identity (claimDetailId) DisputeDetailDescription PK int (claimId) and int (claimDetailId)

DisputeMaster 1-N DisputeDetail DisputeDetail 1-1/0 DisputeDetailDescription

We have DisputeDetails that are currently in a collection on an existing DisputeMaster, they may or may not have associated DisputeDetailDescription.

We want to "move" a DisputeDetail and associated DisputeDetailDescription to a newly created DisputeMaster. and save both existing and new DisputeMasters to the database with the associated child changes.

Our current code (see attached project) isn't working. How can we make this work?

Modifying the PK's in a hierarchy is tricky. I would do it this way, which is a little more clearer:

1. Fetch the existing entity A with its graph A.x <- A.x.y
2. Deep clone the child A.x <- A.x.y
3. Create a new object B and attach the cloned hierarchy B.x <- B.x.y
4. Delete the existing A.x <- A.x.y
5. Save recursively B (so B, B.x, B.x.y).

Example:

// Setup database data  
var adapter = new DataAccessAdapter();
var data01 = ActionProcedures.CreateData01(adapter);

// get dme01
var dmCollection = GetDisputeMasterEntities(adapter);               
var dme01 = dmCollection[0];

// create new DisputeMaster to move DisputeDetail and DisputeDetailDescription to
var dme02 = new DisputeMasterEntity();
dme02.ClaimAmount = 0;
dme02.ClaimDesc = "New Dispute";

// create a copy of dde02 and add it to dme02 (this is recursive so the ddd02 is cloned too).
DisputeDetailEntity dde02 = (DisputeDetailEntity) CloneHelper.CloneEntity(dme01.DisputeDetails[2]);         
dme02.DisputeDetails.Add(dde02);

try
{
    // save stuff
    adapter.StartTransaction(System.Data.IsolationLevel.Serializable, "SaveMyStuffTx");
                
    adapter.DeleteEntity(dme01.DisputeDetails[0].DisputeDetailDescription);
    adapter.DeleteEntity(dme01.DisputeDetails[0]);
    adapter.SaveEntity(dme02, false, true);
                
    adapter.Commit();
}
catch (Exception e)
{
    adapter.Rollback();
    // handle..
}   

I attached the relevant modified code which includes a CloneHelper. Hope helpful.

I don't see why you'd need a clone. Moving entity A, which is first related to Y to be related to Z is simply set the property of A which is mapped onto the relationship to Z.

Looking at the code, I don't get why it fails:

var dde02 = dme01.DisputeDetails[2];
var ddd02 = dde02.DisputeDetailDescription;

// This doesn't work
dme01.DisputeDetails.Remove(dde02);
            
dme02.DisputeDetails.Add(dde02);

uow.Commit(adapter, true);

The .Remove(dde02) action resets the FK field in DisputeDetail entity dde02. Adding it to the collection of dme02 should make it get an update to the PK field, as you can update PK fields. My guess is that it doesn't do that in the case of an FK field sync (which this is), only in the case of an active PK field update by user code.

I'll look into why it fails, as I personally think it should work. In the mean time you can use David's workaround, as it makes the data get updated the way you wanted.

The problem is caused by this: Everything goes as planned, except when the FK field ClaimId in DisputeDetail dde02 has to be set to the new ClaimId pk value of its new master dme02: the code only syncs FK fields which are not a PK or when the entity is new. As the entity isn't new and the field to sync the value of is a PK field, it doesn't allow it.

This leaves the FK field ClaimId in DisputeDetailEntity dde02 as-is and it's therefore not changed during the save actions, and nothing is done.

Question now is: is that check (do not sync PK fields) there for a reason or not. We have to dig into history logs of the sourcecontrol system / design docs to find out. It's likely a left-over from the early years when PK updates weren't allowed. This was added later on. If it's safe to update this check, we'll remove it and your code should work as-is with the patched dll. But till then, use the workaround.

(edit) as I expected, it's been there since day 1 (we moved to Subversion in february 2004 from VSS, it's been this way since that initial day. PK updates were not possible back then, so the check was justified. Today, not so much)

We'll make the change and see whether it breaks anything.

(edit) heh This isn't really going to work with just our code. The problem is... DisputeDetailDescription, the one related to dde02. It points to dde02 using dde02's old PK value for ClaimId (e.g. 8 ). Saving the new master dme02 will for example result in the new value of 11 for ClaimId. However... it's not possible to change the PK of dde02's table row: the update fails, because the DisputeDetailDescription entity points to a disputeDetail with the old pk values, which is no longer there after the update, however first updating disputedetaildescription to the new value won't work either as dde02 isn't saved at that point with the new pk value: catch 22.

It could work if cascading updates are enabled in the DB, on the FK constraint. However, these are often overlooked when for example moving a schema or other things, and the application then breaks at runtime at some point.

I.o.w.: the workaround is actually the best way: copy the data, remove the old ones, re-insert the older data as new entities. Fix rolled back (as it doesn't really fixes all situations, only moves problems to a different location)

heh Regular Smiley This isn't really going to work with just our code. The problem is... DisputeDetailDescription, the one related to dde02. It points to dde02 using dde02's old PK value for ClaimId (e.g. 8 ). Saving the new master dme02 will for example result in the new value of 11 for ClaimId. However... it's not possible to change the PK of dde02's table row: the update fails, because the DisputeDetailDescription entity points to a disputeDetail with the old pk values, which is no longer there after the update, however first updating disputedetaildescription to the new value won't work either as dde02 isn't saved at that point with the new pk value: catch 22.

We don't need it to work with just your code. We would be willing to save and refetch the "new" DisputeMaster (dme02) first so that the new claimId would be known if that would help. We don't expect/require that the framework to do all the key syncing if that is impossible, but I do expect that with the proper code on our side and a known new claimId, we could get it to work without deleting and readding records at the database.

Here is how I think it could/should work

var dmCollection = GetDisputeMasterEntities(adapter);

 Debug.Assert(dmCollection.Count == 1);
 Debug.Assert(dmCollection[0].DisputeDetails.Count == 4);
                
 var dme01 = dmCollection[0];

 // create new DisputeMaster to move DisputeDetail and DisputeDetailDescription to
 var dme02 = new DisputeMasterEntity();
 dme02.ClaimAmount = 0;
 dme02.ClaimDesc = "New Dispute";

adapter.SaveEntity(dme02,true,true);
// Now we know the new Disputes ClaimId

UnitOfWork2 uow = new UnitOfWork2();
uow.AddForSave(dme01);
uow.AddForSave(dme02);

var dde02 = dme01.DisputeDetails[2];
var ddd02 = dde02.DisputeDetailDescription;

dme01.DisputeDetails.Remove(dde02);
dde02.DisputeDetaildescription = null;  // DeSync 1-1/0 record
            
dme02.DisputeDetails.Add(dde02);
dde02.DisputeDetaildescription = ddd02;  // Sync 1-1/0 record

uow.Commit(adapter, true);

dmCollection = GetDisputeMasterEntities(adapter);

Debug.Assert(dmCollection.Count == 2);
Debug.Assert(dmCollection[0].DisputeDetails.Count == 3);
Debug.Assert(dmCollection[1].DisputeDetails.Count == 1);

If that can't work because of incorrect PK syncing, what code can we add that would allow us to force the proper ClaimId updates in Disputedetail and DisputeDetailDocument. Such as adding dde02.ClaimId - dme02.ClaimId and ddd02.ClaimId = dme02.ClaimId at the appropriate points?

One of the reasons I don't like the clone work around is that it involves deleting existing records and adding their clones when all we want is an update of the claimId field on two rows to happen inside a transaction. This is bad because in the "real world" the set of tables and relationships is much more complicated.

Also with the current code I think there is clearly a bug. If you remove the 1-1/0 table from the process, the move still does not happen but there is nothing (no exception or save failure) that tells me that nothing happened. From a code standpoint it all looks A-Ok; I think this is bad.

You can force the Id to a new value

...
dde02.Fields[(int)DisputeDetailFieldIndex.ClaimId].ForcedCurrentValueWrite(dme02.ClaimId);
ddd02.Fields[(int)DisputeDetailDescriptionFieldIndex.ClaimId].ForcedCurrentValueWrite(dme02.ClaimId);
...

but you end with the same problem: What would you save first, dde02 or ddd02? Even if you add it to a UOW (which doesn't really help here) you have to answer that question. If you save dde02 first, you will have a FOREIGN VIOLATION because ddd02 is still pointing to the old dde02's PK. On the other hand, if you save ddd02 first, you will have a FK Exception because the new PK doesn't exist in its parent (dde02, which you haven't saved yet). So, it's the same catch22 situation Frans pointed before.

So, even if you don't like it, in this particular scenario the clone workaround is the best solution.

Yes, You are right. for some reason I was thinking that inside a transaction the fk constraint checking would be delayed until the commit, but this isn't so.

We will look at you cloning example and work out a work around .