2 questions: Transaction and Parent-Child

[dqminh]

Hi,

My first question is how to use transaction in Linq to Oracle?

The second question is: If I want to insert parent and child, both of them use sequence for primary key, it throw the exception about relationship not found (Oracle error: integrity constraint xxx violated - parent key not found)?

Database:

Code: Select all

CREATE TABLE v2_p_Contact(
    ContactId         NUMBER(10, 0)     NOT NULL,
    FullName          NVARCHAR2(50)     NOT NULL,
    Address           NVARCHAR2(255),
    Phone             VARCHAR2(20),
    Email             VARCHAR2(255),
    AdditionalInfo    NVARCHAR2(255),
    CONSTRAINT v2_PK12 PRIMARY KEY (ContactId)
)
;

CREATE TABLE v2_p_Dealer(
    DealerCode      VARCHAR2(30)      NOT NULL,
    ParentCode      VARCHAR2(30),
    ContactId       NUMBER(10, 0)     NOT NULL,
    DealerName      NVARCHAR2(256)    NOT NULL,
    CONSTRAINT v2_PK05 PRIMARY KEY (DealerCode)
)
;

ALTER TABLE v2_p_Dealer ADD CONSTRAINT Refv2_p_Contact40 
    FOREIGN KEY (ContactId)
    REFERENCES v2_p_Contact(ContactId)
;

ALTER TABLE v2_p_Dealer ADD CONSTRAINT Refv2_p_Dealer41 
    FOREIGN KEY (ParentCode)
    REFERENCES v2_p_Dealer(DealerCode)
;

CREATE SEQUENCE v2_seq_Contact MINVALUE 1 MAXVALUE  
999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20; 
 
CREATE OR REPLACE TRIGGER v2_p_Contact_U_PK BEFORE INSERT ON v2_p_Contact 
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 
BEGIN 
SELECT v2_seq_Contact.NEXTVAL INTO :NEW.ContactId FROM DUAL; 
END; 

DataContext: using Entity Developer, check ContactId is Auto Generated Value, Auto-Sync: On Insert

Test Code:

Code: Select all

PartDataContext db = new PartDataContext();

Contact c = new Contact() { FullName = "Fullname" };
Dealer d = new Dealer() { Name = "Name", Code = "Code", ParentCode = null, Contact = c };
db.Dealers.InsertOnSubmit(d);
db.SubmitChanges(); // error here

but if we save child first, it's ok (this is not a normal way)

Code: Select all

PartDataContext db = new PartDataContext();

Contact c = new Contact() { FullName = "Fullname" };
db.Contacts.InsertOnSubmit(c);
db.SubmitChanges();
Dealer d = new Dealer() { Name = "Name", Code = "Code", ParentCode = null, Contact = c };
db.Dealers.InsertOnSubmit(d);
db.SubmitChanges(); // ok

[AndreyR]

Thank you for the report. We have reproduced the error and now we are investigating it.