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?