Error when inserting in to SQL Server through System.Data.Linq.DataContext

Question

I have a big bunch of inserts that I do through DataContext in transaction. I inserting two types of objects: A and B.

I do execute many sql inserts like:

INSERT INTO A (...) VALUES ('11111111-1111-1111-1111-111111111111',..)
INSERT INTO B (...) VALUES (
   '21111111-1111-1111-1111-111111111111', 
   '11111111-1111-1111-1111-111111111111',...)

separately in

db.ExecuteCommand(sql.ToString(), processedParams.ToArray());

table B have foreign key to A.

Sometimes I get error like: violation of FOREIGN KEY constraint "FK_B_A" on table "A". Column "GUID".

Sometimes all records successfully inserts in to DB. Data bunch always the same except dynamically generated GUIDs.

It looks like the insert in to B sometimes occurs before inserts to A.

Your error is giving away what's wrong.. within your code are you doing any Commits.. or are you doing like bulk Inserts and then a commit.. when doing via transactions remember the error will not be apparent until the commit call.. I would suggest doing single inserts and commits to figure out what duplicate key you are trying to insert.. are you using the C# method to generate and return new GIUD's those are generally unique keys so if you are hard coding and trying to insert the same key... well that will generate a foreign key constraint.. — MethodMan
– MethodMan, Commented Dec 19, 2011 at 17:00
You know the SQL inserts you have up there are incorrect right? INSERT INTO A (column names) VALUES(actual values) . A code sample from where you build your query strings might help locate this problem. — Malcolm O'Hare
– Malcolm O'Hare, Commented Dec 19, 2011 at 17:09
Sorry I just mistake when write sample. Sometimes all records successfully inserts in to DB. — gabba
– gabba, Commented Dec 19, 2011 at 17:09
Keys for records generates automaticly and always rights. The problem not with duplicate guids. Some time I have the violation of foregin key. — gabba
– gabba, Commented Dec 20, 2011 at 8:05
This error throws exactly when db.ExecuteCommand. I supose, because it gets in transaction. — gabba
– gabba, Commented Dec 20, 2011 at 8:08

aF. · Accepted Answer · 2011-12-19 17:06:22Z

4

The order might be swapped somehow.

I recomend you to first do all inserts in table A and only after that do inserts on table B.

Also, it seems that the inserts that you posted are incorrect.

See here for examples.

edited Dec 19, 2011 at 17:06

answered Dec 19, 2011 at 16:59

aF.

67k45 gold badges141 silver badges202 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

gabba Over a year ago

When I rewrite program and do all inserts in to table A before all inserts into table B. The error reproduces always.

gabba Over a year ago

But if I try to insert small count of records (10-20) records all ok.

aF. Over a year ago

That's kinda weird. Do you commit the inserts on table A before making the inserts on table B?

gabba Over a year ago

No. I need to commit transaction only after all inserts. And rollback if somthing wrong.

aF. Over a year ago

That will give the same problem. The order of execution might not be the one that you think. You really need to make 2 commits.

|

gabba · Accepted Answer · 2011-12-20 13:24:26Z

1

Thank you All for help.

It was side effect of bug in transaction cleanup process in my service. It rollback any long very long transaction. Error be thrown when cleanup occurs between insert into table A and table B, becasue record in A no longer existed.

answered Dec 20, 2011 at 13:24

gabba

2,9002 gold badges31 silver badges49 bronze badges

Collectives™ on Stack Overflow

Error when inserting in to SQL Server through System.Data.Linq.DataContext

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related