Data Context's SubmitChanges method causing a entity reference to be set to null

I know this looks a bit long but i tried to explain the problem as throughly as i could.

We are having a very 'exotic' problem with the linq to sql data context class. We have a n-tiered architectured structured like this: We have 3 classes MotherClass, ChildClass, ChildChildrenClass

MotherClass looks something like this:

public class MotherClass
{
     private EntitySet<ChildClass> _Children;

     [Column]
     public int Id { get; set; }

     [Association(Storage = "_Children", ThisKey = "Id", OtherKey = "MotherId")]
     public EntitySet<ChildClass> Children
     {
           get { return _Children; }
           set { _Children= value; }
     }
}

And ChildClass looks something like:

public class ChildClass
{
     private EntityRef<MotherClass> _Mother;
     private EntitySet<ChildChildrenClass> _ChildChildren;

     [Column]
     public int Id { get; set; }

     [Column]
     public int MotherId { get; set; }

     [Association(Storage = "_Mother", IsForeignKey = true, ThisKey = "MotherId", OtherKey = "Id")]
     public MotherClass Mother
     {
           get { return _Mother.Entity; }
           set { _Mother.Entity = value; }
     }

     [Association(Storage = "_ChildChildren", ThisKey = "Id", OtherKey = "ChildId", DeleteRule = "NO ACTION")]
     pu开发者_开发问答blic EntitySet<ChildChildrenClass> ChildChildren
     {
           get { return _ChildChildren; }
           set { _ChildChildren= value; }
     }
}

And the third class that is magically named ChildChildrenClass:

public class ChildChildrenClass
    {
         private EntityRef<ChildClass> _Child;

         [Column]
         public int Id { get; set; }

         [Column]
         public int ChildId { get; set; }

         [Association(Storage = "_Child", IsForeignKey = true, ThisKey = "ChildId", OtherKey = "Id")]
         public ChildClass Child
         {
               get { return _Child.Entity; }
               set { _Child.Entity = value; }
         }
    }

The problem arises when we do an update on a ChildClass object and delete some of the ChildChildrenClass items that are associated with it.The code looks something like this:

DataContext dc = new DataContext(conStr);
dc.StartTransaction();//our custom method for handling transactions
ChildClass cclass = dc.ChildClass.GetById(id);//our method for getting the ChildClass from db
//... here we set some values we want to edit
//...
//...
dc.SubmitChanges(ConflictMode.FailOnFirstConflict);//these actions are cool
//after this the problems arise
 List<ChildChildrenClass> ccc = GetAllChildren();//method that gets all the childChildrenClass objects from db
foreach (ChildChildrenClass child in ccc)
{
     dc.GetTable(child.GetType()).DeleteOnSubmit(child);
}
dc.SubmitChanges(ConflictMode.FailOnFirstConflict);
//AFTER CALLING THIS METHOD THE PROBLEM APPEARS

The problem mentioned above is that the cclass.Mother property is magically set to null. After a lot of debugging (placing brakepoints in the Mother set method revealed this) we noticed that the property is being set to null during SubmitChanges() in some external code.

The SubmitChanges() method completes successfully (the ChildChildrenClass items are deleted) but this causes a problem with the code that runs after this. We are using the same DataContext (because of the transaction) and calling again the SubmitChanges() method that throws this exception:

System.InvalidOperationException: An attempt was made to remove a relationship between a MotherClass and a ChildClass. However, one of the relationship's foreign keys (ChildClass.MotherId) cannot be set to null. at System.Data.Linq.ChangeTracker.StandardChangeTracker.StandardTrackedObject.SynchDependentData() at System.Data.Linq.ChangeProcessor.ValidateAll(IEnumerable`1 list) at System.Data.Linq.ChangeProcessor.SubmitChanges(ConflictMode failureMode) at System.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode)

I have also encountered this issue long time ago when I wrote a blog engine. The problem arised after I deleted a couple of rows from a junction table. Deleting the things went okay, then no matter what I did, this exact exception came up on the next SubmitChanges().

After spending about a day or so solving the problem, I restorted to a workaround:

• Create a new instance of the DataContext
• Get a new instance of any entities in use from the new DataContext

I know this is very hacky, but this was the only way I could solve it. I see you use a transaction there, that would make this a little bit harder. Perhaps try to use two different transactions (one for the old DataContext, and one for the new), and roll back the first one if the second one fails?
I know this is hacky.

Perhaps try to use another ORM (eg. NHibernate) which doesn't have issues with this.

DataContext instances should never be reused. Every time Because the framework has no way to ensure that the data has not been changed between SubmitChanges() calls, the recommended approach is to dispose of the DataContext after changes have been submitted and create a new one if another transaction requiring a SubmitChanges() call is needed.

In addition, the DataContext object already wraps any inserts, changes, and/or deletes in a transaction.

Don't use LinQ-To-SQL, because

1. It's discontinued by Microsoft
2. It's a good tool, but only for very simple domains. It has its problems with relations.
3. There are plenty of alternatives. If you need Design-Time Support choose ADO.NET Entity Framework. If you have a complex domain to map try NHibernate, which also features great persistence ignorance. There're also many fans of subsonic out there.