Inserting records into child tables whose primary key is the primary key of another base table

[Fiend]

Does anyone (or the Devart team) have an example/tutorial for inserting rows into a base table and child tables whose primary key is the primary key from the base table? I also have another table whose PK is a foreign key to the base table. These all need populated in the same transaction.

When I create a record for TableA, I want to be able to also insert a record into TableB and whose insert needs the PK from TableA inserted as its primary key. There is also a TableC whose PK is a FK to TableA.

Should I just be able to create a new context, populate my TableA, TableB, TableC entities with data and then have the context insert? Will this automatically take the PK from TableA and apply it to the PK of TableB and will the TableC PK automatically get inserted int the FK on the TableA record?

We are working with some inherited code and I can see through the model that TableA has its PK as autogenerated = false. I know for sure that this is a problem. We have sequences we are using along with triggers that I believe may have been generated from the dotConnect tool... the sequences are used for the primary keys on TableA and TableC. With the code we have, when the insert for this situation happens (new context instantiated, TableA, TableB, TableC entities populated and context attemps an insert on TableC), we get this error: "Can not insert entity with the same key if key is not database generated".

I'm in the middle of playing around with it but wanted to reach out to the community to see if there is an example somewhere for doing something like this.

[Fiend]

To sum up, I guess ideally what I would want is TableA and TableC have sequences that dotConnect uses to populate the primary keys. TableB uses the sequence PK generated for TableA as its primary key and the FK on TableA from the TableC PK gets inserted automatically into TableA along with the rest of the fields.

The wording of the error above makes it sound like I may just have a configuration issue in the model for the PK's on these tables.

[MariiaI]

We are sending you a sample project to the e-mail address you provided in your forum profile. The sample demonstrates how to make inserts to the base table and child tables with the autogenerated primary key.
If it's not what you mean, please make changes to our sample project and send it back to us or send us your sample, so that we are able to investigate it in more details.

Also, please set the TableA's primary key to 'Auto Generated Value' = true. This is necessary so that the engine 'knows' that it should get the generated key value back from the server. Otherwise, the entity object will have a zero key, which causes the error you are observing.

[Fiend]

I noticed in the documentation that there are a few ways to that the tool handles PK's. We are using Oracle. Ideally I would want to just use sequences without triggers. From the documentation it seems like this is possible.

If I wanted to elminate the use of triggers and just have the tool use sequences we've created, would I still need to set the auto-generated = true PK for a table and if I do need to (or don't for that matter), then what setting hooks up the sequence I would create to that table in the model? I'm assuming I would need to give the table in the model the name of the sequence to use.

[Fiend]

I also gathered from the documentation that inserts via the context are transactional. In my example in this post, we'd want that transaction to be atomic... all inserts succeed or nothing. From the documentation it seems to insinuate that dotConnect does this? If I was inserting a record into all three of my tables via a newly instantiated context and one insert failed for whatever reason the dotConnect engine will roll back everything, correct?

[MariiaI]

In the case you are working only with sequence you should also set 'Auto Generated Value' = true. As we have said above, this is necessary because the engine 'knows' that it should get the generated key value back from the server. Otherwise, the entity object will have a zero key.
For proper work with sequences for your primary keys, you should specify the name of the used sequence for the entity key:
- right-click the entity key (e.g., "ID" property of your entity class);
- select Properties->IdGenerator;
- set IdGenerator to 'Sequence' and specify its name.

If this sequence does not exist yet, it will be created while using the "Update Database from Model" wizard.

I also gathered from the documentation that inserts via the context are transactional... If I was inserting a record into all three of my tables via a newly instantiated context and one insert failed for whatever reason the dotConnect engine will roll back everything, correct?

Yes, LinqConnect transactions are a part of its internal architecture. LinqConnect starts a local DbTransaction and uses it to execute generated SQL commands if there is no explicitly opened transaction. When all SQL commands have been successfully completed, LinqConnect commits the transaction, otherwise the transaction is rollbacked.

For more information about the most common cases of update errors and the methods LinqConnect provides for processing them, please refer to http://www.devart.com/linqconnect/docs/ ... ssing.html.

Please tell us if this helps.