How to properly use a LINQ2SQL DataContext object; creating anew each use causes headaches

Question

So, in learning LINQ2SQL (don't tell me it's a dead technology please, I'm not switching to EF for simple applications) I have read numerous times that the DataContext class is meant to be created and destroyed frequently. Great, a lightweight object that I can use to access my database. However, in practice this seems to cause nothing but trouble. For example, if I load up a DataGrid like so changes to entity objects don't seem to be tracked and calling DataContext.SubmitChanges has no effect:

void LoadUI()
{
    using( var db = new TestDataContext() )
    {
        // use DataLoadOptions.LoadWith<T> if we need 
        // access to foreign key/ deferred objects.
        masterGrid.ItemsSource = db.Customers.ToList();
        detailsGrid.ItemsSource = // this is always set to a collection of Settings
                                  // for the currently selected user in masterGrid.
                                  // just a simple foreign key relationship
    }
}

void UpdateName( string newName )
{
    using( var db = new TestDataContext() )
    {
        var customer = ((Customer)masterGrid.SelectedItem);
        customer.Name = newName;
        db.SubmitChanges() // !!! database is not updated !!!    
    }
}

So what gives? Creating a new DataContext object when accessing the DB seems to be the accepted/preferred practice, yet it doesn't seem to work in the most trivial of cases. It appears that object tracking doesn't work in this scenario, so how could one possibly use the DataContext class like this in practice? Am I missing something here? (Note: I really do hope I am actually missing something simple.

I have to assume that is the case, but I don't know what the answer is yet. Currently I am just keeping a single DC around for the life of the app, which I know is probably not a good idea). Thanks in advance for any advice you can offer.

BTW, if any of you know of a comprehensive article/serie4s of articles which describe the ins-and-outs of the DataContext class I would love to read it. I haven't found any examples which go beyond trivial updates/inserts/deletes.

Answer 1

Changed objects can only be updated with the datacontext with which they were created. A datacontext is typically created once for every unit of work (e.g. a page or form that gets some objects, does some things with it and updates them in the db).

EDIT 2, In reaction to FooWombat's comment : For a more real-world example, read Datacontext Lifetime Management on Rick Strahl's blog. I goes in depth how to implement handling datacontext so that everything happens behind the scenes and you can select/update/delete your linq2sql objects without thinking (much) about it.

EDIT 1 : Also, if you google around a bit, you will also find a method called Attach. This method is meant to be used for objects that have been deserialized and then serialized back. It mentions to not try to attach objects from another datacontext. However, in this blogpost you have a example to (ab)use Attach so that you exactly can do this. I would only use it if you are in a pinch and really need to transfer objects from one datacontext to another. It's not really a recommended practice.

From the blogpost :

public partial class Employee
{
   public void Detach()
   {
       this.PropertyChanged = null; this.PropertyChanging = null;
       // Assuming there's a foreign key from Employee to Boss
       this.Boss = default(EntityRef<Boss>);
       // Similarly set child objects to default as well
       this.Subordinates = default(EntitySet<Subordinate>);
   }
}

You can use this Detach method to do this :

public static void UpdateEmployee(Employee employee)
{
    using (HRDataContext dataContext = new HRDataContext())
    {
        //attach to datacontext
        employee.Detach();
        dataContext.Employees.Attach(employee);
        //save changes
        dataContext.SubmitChanges();
    }
}

Answer 2

The magic words are "Data Binding." This is what you need to display data, and it's also what you need to save data back to the database.

The reason that your above code doesn't work is that it provides a read data binding path, but not a write data binding path. In other words, you've created a read-only list from the DataContext, but you haven't provided an actual way to write the changes back to the database.

The easiest solution might be to create a writable data path for your data grid, rather than trying to track and save the changes from the data grid manually. There's a number of ways to do this. One way that I know of is to use an ObservableCollection. The ObservableCollection provides notifications when items get added, removed, or when the entire list is refreshed. This allows you to hook code into those events to provide the saving function.

However, initially your best bet might be this search.

Answer 3

The answer is that you have 2 ways (and a lot more!) to solve this problem.

The Object Instance in your grid belongs to DataContext #1. In your UpdateName method you create a new DataContext #2. How can DataContext #2 know that an object from DataContext #1 has changed? Never. So: Use DataContext #2 to retrieve the Customer Object from the database and use the retrieved Object to change the property.

Another way is to use DTO´s. This way is far to long to describe, but the principle is that you create a class that has got all Properties a Customer has but nothing else (=> this class is not connected to your DataContext anymore) and after changing properties on it you reconnect the class to your DataContext and save it.

Hope i could help you.

void LoadUI()
{
    using( var db = new TestDataContext() )
    {
        // use DataLoadOptions.LoadWith<T> if we need 
        // access to foreign key/ deferred objects.
        masterGrid.ItemsSource = db.Customers.ToList();
        detailsGrid.ItemsSource = // this is always set to a collection of Settings
                                  // for the currently selected user in masterGrid.
                                  // just a simple foreign key relationship
    }
}

void UpdateName( string newName )
{
    using( var db = new TestDataContext() )
    {
        var customer = ((Customer)masterGrid.SelectedItem);
        var freshLoadedCustomer = db.Customers.FirstOrDefault(cust=>cust.Id == customer.Id);
        if(freshLoadedCustomer != null)
        { freshLoadedCustomer.Name = newName; db.SubmitChanges();}
    }
}