Foreign Key exception

Posts   
 
    
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 08-Aug-2008 04:21:39   

Hi there,

I have a many to many table structure as follows:

Contact -> ContactCode -> FundraiserCode

I bind a grid of codes to the current contact by setting the datasource of my grid to Contact.FundraiserCodeCollectionViaContactCode When new Codes are added to the current contact I simply

Contact.FundraiserCodeCollectionViaContactCode.Add(code);

and it works beautifully, but only when I'm working with a contact that already exists in the database.

When creating a new contact, and assigning new codes via FundraiserCodeCollectionViaContactCode.Add(code), the code is added to the collection, and the grid updated. However, when I save the entity I get the following exception.

An exception was caught during the execution of an action query: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_ContactCode_Contact". The conflict occurred in database "ct10012", table "dbo.Contact", column 'ContactID'. The statement has been terminated.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

It looks to me as if the ContactCode entity is being saved before Contact. Doesn't LLBLGen have code that knows in what order to save entities to the database? Or am I going about this wrong?

Thanks, Josh

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 08-Aug-2008 07:24:57   
David Elizondo | LLBLGen Support Team
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 09-Aug-2008 03:47:23   

I looked at the recommended threads, but I'm not sure they completely apply.

Sorry about neglecting the version info, I am using an older version of the .NET 2.0 libraries.

ORMSupport v2.0.07.1213 SQLServerDQE v2.0.07.0605 Self-Servicing.

The recursive save 'does' work when the entity is not new. Shouldn't the recursive save logic sort out the save order when an entity is new?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 09-Aug-2008 11:07:20   

Did you hide a side of the relations involved ? (or the fields mapped onto these relations) ? What is the code you use to save the entities?

Your runtime lib version does have the right routine for persisting graphs. Could you do a little test for me please? In the ORMSupportClasses there's a class called ObjectGraphUtils. Could you do the following?


List<ActionQueueElement<IEntity>> insertQueue = new List<ActionQueueElement<IEntity>>();
List<ActionQueueElement<IEntity>> updateQueue = new List<ActionQueueElement<IEntity>>();
ObjectGraphUtils ogu = new ObjectGraphUtils();
ogu.DetermineActionQueues(contact, ref insertQueue, ref updateQueue );

After this, the insertQueue and the updateQueue have to have the entities in the right order (inserts are ran first). Please check that for me. Oh, and provide exact code snippets what you're doing, otherwise we can only guess and that's time consuming and we can't help you the way we want to.

Frans Bouma | Lead developer LLBLGen Pro
tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 10-Aug-2008 21:15:50   

No relations are hidden. Contact is the base of an object hierarchy. In this case, the ContactEntity type is IndividualEntity. So I save the ContactEntity instance like so...

uow.AddForSave(_currentContact, true);
uow.Commit(new Transaction(IsolationLevel.ReadCommitted, "uow", true);

After running your object graph utility, I can see that it wants to add an IndividualEntity first, and ContactCode entity after, which is correct. Perhaps the Contact record (the supertype) isn't being added to the database first?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 11-Aug-2008 09:53:45   

When new Codes are added to the current contact I simply Code: Contact.FundraiserCodeCollectionViaContactCode.Add(code);

The collection_VIA_otherEntity is recommended to be used in a readOnly fashion.

If you want to save a m:n graph you have to specify the middle entity too, please check the following:

How do I add an entity A to an entity B's collection of A's if A and B have an m:n relation ?

Say you want to add an Employee to a Department and Employee and Department have an m:n relation. This means that there is an intermediate entity DepartmentEmployees which defines the m:n relation with a foreign key to both Employee and Department. Employee has a collection Departments which results in all Department entities the Employee works for and Department has a collection Employees which results in all Employees working for the Department. In LLBLGen Pro, m:n relations are read-only, therefore, to add an Employee to a Department, you've to use the following steps. The example uses department and employee which are filled in DepartmentEntity and EmployeeEntity instances. You can also use new entities, this doesn't matter.

// Selfservicing DepartmentEmployees newDepEmp = new DepartmentEmployees(); newDepEmp.Employee = employee; newDepEmp.Department = department; // the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) // to save the relation in the database. employee.Save(true);

// Adapter DepartmentEmployees newDepEmp = new DepartmentEmployees(); newDepEmp.Employee = employee; newDepEmp.Department = department; // the 3 entities are now linked. We can now save recursively any of the three (doesn't matter) // to save the relation in the database. adapter.SaveEntity(employee);

Keep in mind that the m:n relation is then saved in the database, but the situation in memory isn't updated, i.e.: employee.Departments doesn't contain 'department'. This is by design, as the m:n relation is based on a 3rd entity, which can be altered by any thread in the system, making the m:n relation, which is a result of the values of the intermediate entity, a 'view' of a situation at a given point in time

tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 11-Aug-2008 19:22:09   

I am creating the intermediate entity (ContactCode) and adding it to the UnitOfWork. This gets added before I add ContactEntity to the UnitOfWork.

I think the issue may revolve around the base class (or supertype) of the hierarchy not getting saved. What is not in the object graph, is ContactEntity, the base of IndividualEntity.

Something that is interesting is this; the object graph shows 'IndividualEntity' at index [0], and ContactCode at index [1]. However, in the actual SQL output, ContactCode is being added before any IndividualEntity (or ContactEntity), resulting in that fkey exception.

I have also checked that ContactEntity.IsNew is true before the UnitOfWork.Commit is fired.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 12-Aug-2008 10:35:52   

Would you please post a complete code snippet, showing the entire life cycle of the UoW?

tomahawk
User
Posts: 169
Joined: 02-Mar-2005
# Posted on: 13-Aug-2008 23:40:11   

In writing up a code sample, I saw what I was doing wrong. I was adding the m:n ContactCodeEntity to the UnitOfWork separately, in addition to the ContactEntity. Once I comment that line, the code works beautifully. Thanks for your patience.

                UnitOfWork uow = new UnitOfWork();
                IndividualEntity ie = new IndividualEntity();
                ie.ContactID = 999999;
                ie.LastName = "Doe";
                ie.FirstName = "John";
                CamelotAccess.CollectionClasses.FundraiserCodeCollection fcc = ie.FundraiserCodeCollectionViaContactCode;
                Debug.Assert(ie.IsNew);

                FundraiserCodeEntity fce = new FundraiserCodeEntity("001");
                Debug.Assert(!fce.IsNew);

                ContactCodeEntity cce = new ContactCodeEntity();
                cce.ContactID = 999999;
                cce.CodeNum = fce.CodeNum;
                ie.ContactCode.Add(cce);
                //uow.AddForSave(cce);  // THIS was causing the problem

                uow.AddForSave(ie, true);
                uow.Commit(new CamelotAccess.HelperClasses.Transaction(System.Data.IsolationLevel.ReadCommitted, "uow"), true);