I’m currently working with Linq-to-SQL, and have used attribute mapping to map my classes to database tables. In doing so, I’ve created a Database context that derives from DataContext (and takes my encrypted connection string stored in settings as a default parameter). To create a database connection, I simply use typical code as illustrated below.

Note that the following terms are referred to below, but not included for brevity:

• ViewModelBase – provides INotifyPropertyChanged implementation
• Customer – a typical class representing a Customer object

public class ExampleViewModel : ViewModelBase
{
    private Database _db;
    public Database Db
    {
        get { return _db; }
        set { _db = value; }
    }

    private ObservableCollection<Customer> _customerList;
    public ObservableCollection<Customer> CustomerList
    {
        get { return _customerList; }
        set { _customerList = value; NotifyPropertyChanged(() => CustomerList) }
    }

    public ExampleViewModel()
    {
        Db = new Database();
        CustomerList = new ObservableCollection<Customer>(GetCustomerList());
    }

    public List<Customer> GetCustomerList()
    {
        return Db.Customers.ToList();
    }
}

This is fairly self-explanatory, but I’ll go over it for my non-programming readers.

Line 1 declares my ExampleViewModel class.
Lines 3-8 declare a Database connection.
Lines 10-15 declare an ObservableCollection of customers.
Lines 17-21 declare my default constructor.
Lines 23-26 are a method to populate the list of customers.

Any object created from this class will contain a database connection (called “Db”) and a list of Customers (called “CustomerList”). In the constructor, we’re setting Db to a new instance of the Database object (line #19), which represents a database connection. This is followed by population of the CustomerList ObservableCollection property through the use of the GetCustomerList() method and the ObservableCollection(IList) overload.

GetCustomerList (lines #23-26) uses the database connection property of the ExampleViewModel to obtain all the customers contained in the Customers table (represented by “Db.Customers”). This is then cast to a List and returned to the calling code.

In Linq-to-SQL, changes to database objects are simple.

// Get customer from database and delete them
// This will create a pending database delete
Customer customerToDelete = Db.Customers.Single(c => c.Id = 991);
Db.Customers.DeleteOnSubmit(customerToDelete);

// Create a new customer and insert them into the database
// This will create a pending database insert
Customer customerToInsert = new Customer { FName = "Ima", LName = "Customer" };
Db.Customers.InsertOnSubmit(customerToInsert);

// Get customer from database and increase their store credit balance
// This will create a pending database update 
Customer customerToUpdate = Db.Customers.Single(c => c.Id = 992);
customerToUpdate.StoreCredit += 2.56;

Now, you’ll note that I stated the code above would create pending deletes, inserts, and updates. This is because the database connection doesn’t actually commit these pending changes until Db.SubmitChanges() is called.

There are times where I’d like to eliminate any pending database changes (suppose a user clicks a “Cancel” button). Typically, we could simply set our Db property to a new instance of the Database object. But I allow my database context to live throughout the life of the ViewModel for reasons beyond the scope of this article.

In order to easily clean my existing Database object’s set of pending changes, I wrote the following extension method:

public static void ClearChangeSet(this Database db)
{
    // Get the current change set
    ChangeSet pendingChanges = db.GetChangeSet();

    // Iterate through pending inserts and delete
    foreach (object obj in pendingChanges.Inserts)
    {
        var tableToDeleteFrom = db.GetTable(obj.GetType());
        tableToDeleteFrom.DeleteOnSubmit(obj);
    }

    // Iterate through pending deletes and insert
    foreach (object obj in pendingChanges.Deletes)
    {
        var tableToInsertInto = db.GetTable(obj.GetType());
        tableToInsertInto.InsertOnSubmit(obj);
    }

    // Restore all updates with original values
    db.Refresh(RefreshMode.OverwriteCurrentValues, pendingChanges.Updates);
}

This extension method (created by the inclusion of the this keyword prior to the expected parameter type) allows me to clear all the pending changes with one simple call. Assuming the ExampleViewModel code above, to clear the pending database changes we simply:

Db.ClearChangeSet();

Looking at the ClearChangeSet method code, we can see that the first instruction (line #4) creates a ChangeSet object by calling the Database connection’s GetChangeSet method. The database connection stores all pending changes in a ChangeSet, with pending deletions stored in a Deletes collection, pending insertions stored in a Inserts collection, and pending updates stored in a Updates collection.

To remove pending deletions or insertions, we simply need to obtain the object and add it to the Inserts or Deletes collection, respectively.

In line #7, we iterate through every object in the “Inserts” collection.
Then, for each item in the “Inserts” collection, we use the database connection’s GetTable(Type type) method to determine which database table stores the current Insertion object type.

In line #8, we simply delete the current database Insertion from the table found in line #7.

Once this loop has completed, every object that was pending as an insert was deleted from the database, effectively canceling out all pending inserts.

Lines #14-18 are very similar to the previous code block. We’re iterating through every pending deletion and adding it to be inserted into the database. Again, each time a pending deletion is added as an insertion, the two objects effectively cancel each other.

Line #21 handles reversion of pending updates. By utilizing the database connection’s Refresh method, we’re able to reset all changed property values back to their original values.

Once all the pending inserts have been deleted, all the pending deletes have been inserted, and all the pending updates have been reverted to original values, there’s no need to call the database connection’s SubmitChanges method. All items have already been removed from the ChangeSet.

That sums it up – a simple extension method to easily purge your DataContext’s pending changes without creating a new instance of the database connection.