Today i came across some very odd behaviour using Linq to SQL.
I was trying to retrieve a record from the database to update one column then save the change to the database. The problem was happening when i tried to save the change. I was getting foreign constraint errors on related records that didn’t even exist!

Here is the DB structure:
T1 (id, value)
T2 (id, T1.id, values)
T3 (id, T2.id, values)

T1 existed but T2 and T3 didn’t. The foreign key constraint error i recieved was between T2 and T3 but how can that be when i didn’t create any records and they don’t exist?!

Here is the code i initially used:

DataContext Dal = new DataContext();
T1 table1 =  (from t in DC.T1

    where t.id == pID

    select t).SingleOrDefault();
table1.value = pNewValue;

Dal.SubmitChanges();

When i inspected the object VS showed that T1 had at least 1 T2 object and when i inspected T2 it had at least one T3 object.

The fix for this was to change the Linq select statement but i don’t for the life of me know why this made a difference. Here is the fixed version:

DataContext Dal = new DataContext();

T1 table1 =  Dal.T1.Where(a => a.id == pID).SingleOrDefault();

table1.value = pNewValue;

Dal.SubmitChanges();

Has anyone else ever experienced this?

You may also like

Newsletter sign up

Every couple of months we send out an update on what's been happening around our office and the web. Sign up and see what you think. And of course, we never spam.