Devart dotconnect - Is there a way to force the commit order?

Question

I am using devart dotconnect for Oracle v7.7, and am getting a unexpected error. I am inserting a record into table A, and then another a number of records into table B where B has a foreign key to A, and I'm getting a parent key not found error.

SsinpatDataContext dc = new SsinpatDataContext();
Document doc = new Document();
doc.Text = "bla bla bla";
var id = dc.ExecuteQuery<decimal>("SELECT DOCUMENT_SEQ.NEXTVAL FROM DUAL");
doc.Id = id.ElementAt(0);
dc.Documents.InsertOnSubmit(doc);

DocumentRows dr = new DocumentRows();
dr.Text = "bla bla bla";
dr.DocId = doc.Id;
dc.DocumentRows.InsertOnSubmit(dr);

dc.SubmitChanges();

this throws an exception with the text "ORA-02291: integrity constraint violated-parent key not found" It seems to me that devart is trying to commit the DocumentRows object first, and only then the Document object...

Now, my question here is whether there is a way we can force the commit execution order.

Thanks.

Answer 1

The issue probably is that the IdGenerator is set to Sequence for the Id property of the entity class Document in your model. And, due to the fact that you are performing this query in your code

"SELECT DOCUMENT_SEQ.NEXTVAL FROM DUAL"

the next sequence value is queried twice and the discrepancy is the last value of the sequence occurs. You can check it via logging:

SsinpatDataContext dc = new SsinpatDataContext(){Log=Console.Out};

To avoid this error in your scenario, you should set the IdGenerator to None for the Id property of the entity class Document and re-generate the code for your model.

However, the better scenario would be the following:

1) set the IdGenerator to Sequence for the Id property of the entity class Document; specify the name of the sequence, in your case - DOCUMENT_SEQ;

2) re-write you code:

SsinpatDataContext dc = new SsinpatDataContext(){Log=Console.Out};
Document doc = new Document();
doc.Text = "bla bla bla";
DocumentRows dr = new DocumentRows();
dr.Text = "bla bla bla";
dr.Doc = doc;
dc.Documents.InsertOnSubmit(doc);
dc.SubmitChanges();