I can't determine why this isn't working because other similar tables seem to work. I have two tables: client (Primary) and client_contact that each link on the client_id field. Both fields are indentical and I have a RI constraint on the client_contact table. Client_id (primary key) on the client table is a Number field that is based on a sequence. I can see the relationship in the designer and the generated code, but when I try to create a new client and add new contacts, it fails to synchronize the client_id; it fails to add the contact because the client_id from client is not set. Any thoughts as to what I'm doing wrong?

            using (DataAccessAdapter adapter = new DataAccessAdapter(conn))
            {
                ClientEntity client = new ClientEntity();
                client.ClientName = "Test Client";
                client.City = "New York";
                ClientContactEntity cce = (ClientContactEntity) client.ClientContacts.AddNew();
                cce.FirstName = "Michael";
                cce.LastName = "Smith";             
                adapter.SaveEntity(client);
            }

Exception Message on SaveEntity: An exception was caught during the execution of an action query: ORA-02291: integrity constraint (DAVE.CLIENT_ID_FK1) violated - parent key not found.

If I take a client that already exists and add new contacts, it works fine. It only fails if I'm adding a new client and new contacts at the same time. I've noticed that the client_id sequence is increasing each time I try the SaveEntity method, even though it fails to save the client row. I'm using LLBLGen Pro 1.0.2005.1 Final version and Oracle 8.1.7.

Hi,

Can you try enforcing the relation from the other side?

...
ClientContactEntity cce = (ClientContactEntity) client.ClientContacts.AddNew();
cce.Client = client;
...

I personnaly think that's the correct way to do it, because the relation resides in the foreign key, which is what you want to set.

Now I read that people now usually increment the child collections directly, and there seems to be some code coping with injecting the reference.

So it might be a bug in your precise case, but I'm pretty sure it should work and does not harm your code doing it the other way as illustrated: You don't even have to touch the entity collection since setting cce.Client automatically increments the collection.

Hope that helps.

Thanks, but that didn't work (same exception).

Have you set the sequence in the designer, in the entity editor? (So the field has a sequence associated with it? If not, please do so, regenerate the code and test again)

Yes, the sequence is set. This is what I see in the QueryExecuted:

Query: INSERT INTO "DAVE"."CLIENT_CONTACT" ("CLIENT_CONTACT_ID", "CLIENT_ID", "LAST_NAME", "FIRST_NAME") VALUES (:ClientContactId1, :ClientId2, :LastName3, :FirstName4)
Parameter: :ClientContactId1 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 287.
Parameter: :ClientId2 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 118.
Parameter: :LastName3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Smith.
Parameter: :FirstName4 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Michael.

This is the trace log:

Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO "DAVE"."CLIENT" ("CLIENT_ID", "CLIENT_NAME", "CITY") VALUES (:ClientId1, :ClientName2, :City3) Parameter: :ClientId1 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0. Parameter: :ClientName2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Test Client. Parameter: :City3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: New York.

Sequence query: SELECT DAVE.CLIENT_SEQ.NEXTVAL FROM DUAL
Executes before INSERT: True

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Method Enter: Query.ReflectOutputValuesInRelatedFields Method Exit: Query.ReflectOutputValuesInRelatedFields Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO "DAVE"."CLIENT_CONTACT" ("CLIENT_CONTACT_ID", "CLIENT_ID", "LAST_NAME", "FIRST_NAME") VALUES (:ClientContactId1, :ClientId2, :LastName3, :FirstName4) Parameter: :ClientContactId1 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0. Parameter: :ClientId2 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 56. Parameter: :LastName3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Smith. Parameter: :FirstName4 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Michael.

Sequence query: SELECT DAVE.CLIENT_CONTACT_SEQ.NEXTVAL FROM DUAL
Executes before INSERT: True

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.Rollback Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.Rollback A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.SaveEntity(4)

This is somewhat strange. If I create two sample one-to-many tables with similar setup (pk, fk, sequence, etc.) the way I was coding does work. The only difference is that my tables in which I'm getting the exception are a little more complex (both the parent and child tables have additional joins to other tables). It's going to be a pain, but I'm going to slowly rebuild my sample tables (those that work) to the point where they are identical to the ones I'm getting the exception with. Hopefully I can spot a difference.

Also, in the output I pasted in the previous message, the ClientId is diffent becuase I rebuild the sequence. I'm reposting the results again, just so it's all clean.

QueryExecuted:

Query: INSERT INTO "DAVE"."CLIENT_CONTACT" ("CLIENT_CONTACT_ID", "CLIENT_ID", "LAST_NAME", "FIRST_NAME") VALUES (:ClientContactId1, :ClientId2, :LastName3, :FirstName4)
Parameter: :ClientContactId1 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 233.
Parameter: :ClientId2 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 62.
Parameter: :LastName3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Smith.
Parameter: :FirstName4 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Michael.

trace output:

Method Enter: DataAccessAdapterBase.SaveEntity(4) Method Enter: DataAccessAdapterBase.DetermineActionQueues(7) Method Exit: DataAccessAdapterBase.DetermineActionQueues(7) Method Enter: DataAccessAdapterBase.StartTransaction Method Enter: DataAccessAdapterBase.OpenConnection 'OracleTestClient.vshost.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC\Oracle.DataAccess\9.2.0.700__89b483f429c47342\Oracle.DataAccess.dll', No symbols loaded. 'OracleTestClient.vshost.exe' (Managed): Loaded 'C:\WINNT\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.Wrapper.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled. Method Exit: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.StartTransaction Method Enter: DataAccessAdapterBase.PersistQueue 'OracleTestClient.vshost.exe' (Managed): Loaded 'C:\Documents and Settings\mianz\My Documents\Visual Studio 2005\Projects\OracleTestClient\OracleTestClient\bin\Debug\SD.LLBLGen.Pro.DQE.Oracle.NET20.dll', No symbols loaded. Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO "DAVE"."CLIENT" ("CLIENT_ID", "CLIENT_NAME", "CITY") VALUES (:ClientId1, :ClientName2, :City3) Parameter: :ClientId1 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0. Parameter: :ClientName2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Test Client. Parameter: :City3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: New York.

Sequence query: SELECT DAVE.CLIENT_SEQ.NEXTVAL FROM DUAL
Executes before INSERT: True

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Method Enter: Query.ReflectOutputValuesInRelatedFields Method Exit: Query.ReflectOutputValuesInRelatedFields Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: CreateInsertDQ Method Enter: CreateSingleTargetInsertDQ Generated Sql query: Query: INSERT INTO "DAVE"."CLIENT_CONTACT" ("CLIENT_CONTACT_ID", "CLIENT_ID", "LAST_NAME", "FIRST_NAME") VALUES (:ClientContactId1, :ClientId2, :LastName3, :FirstName4) Parameter: :ClientContactId1 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 0. Parameter: :ClientId2 : Int64. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 62. Parameter: :LastName3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Smith. Parameter: :FirstName4 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: Michael.

Sequence query: SELECT DAVE.CLIENT_CONTACT_SEQ.NEXTVAL FROM DUAL
Executes before INSERT: True

Method Exit: CreateSingleTargetInsertDQ Method Exit: CreateInsertDQ Method Enter: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.ExecuteActionQuery Method Enter: DataAccessAdapterBase.Rollback Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.Rollback A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll Method Enter: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.CloseConnection Method Exit: DataAccessAdapterBase.SaveEntity(4)

The exception isn't something related to FK fields not being synced. They are, as you can see that the FK field in client_contact (clientid) is set to a value.

The thing is that the value to set isn't found in client as a PK (as it seems). As if there's another FK to another table from client_contact.clientid..

I'm too embarrassed to even disclose the resolution. I knew something was wrong when other tables were working fine and my sample tables were working as well. It turns out, there was an insert trigger on the primary table that was overriding the value of the client_id. Sorry for wasting your time, but I do appreciate you looking into this.