Tuesday, October 23, 2007

Drag-and-drop databinding of LINQ to SQL DataContexts—like that for ADO.NET DataSets—is a "value added" Visual Studio feature that you see more often in demonstrations and conference sessions than in deployed production applications. The objective is to promote Visual Studio's "almost codeless" or "nearly codeless" databinding scenarios.

Editing online transaction processing (OLTP) data in DataGridView controls on Windows forms or ASP.NET GridView controls is a relatively uncommon practice. But databound controls that are more common in today's production applications, such as TextBoxes, ListBoxes, DropDownLists, CheckBoxes, RadioButtons and the like, have databinding characteristics similar to the DataGridView and GridView control. If one can demonstrate grid-based data display with full editing capabilities in DataGridView and with the exception of insertions in GridView controls, it follows—at least superficially—that other databound controls will behave as expected.

The Visual Studio team touts LINQ to SQL as an object-oriented substitute for the venerable DataSet but has been remarkably reluctant to demonstrate the databinding features of LINQ to SQL in Windows forms with master-child or master-child-grandchild DataGridViews. All examples I've seen that persist insertions to or deletions of LINQ to SQL objects do so with code rather than by adding or deleting DataGridView rows in the UI.

LINQ to SQL doesn't have an out-of the box multi-tier story but it does have a WinForms databinding story. The problem is that the LINQ to SQL team hasn't told it. VS 2008 Beta 2's online help provides only a trivial, single-grid databinding example. This post fills the gap with a full comparison of LINQ to SQL's fledgling databinding feature set for the presentation layer with that of the more mature typed DataSet.

The DataSet Approach to DataBinding Demos

The penultimate databinding demonstration is a three-level hierarchical set of Northwind Customer and related Order and Order_Detail objects displayed in three DataGridView controls bound to BindingSource components, which in turn bind to DataSet.TableAdapters. You drag the Customer node to a Windows form to autogenerate CustomersBindingNavigator, CustomersBindingSource, and CustomersDataGrid controls. Then you drag the FK_Orders_Customers relationship and FK_Order_Details_Orders relationship nodes to the form for editing the related Orders and Order_Details records.

VS 2008 (not .NET) adds the TableAdapterManager class, which autogenerates the sequence in which multiple DataSet insertion, modification, and deletion changes are applied to the underlying database. Online help for the TableAdapterManager class calls the technique hierarchical updates. Eliminating referential integrity conflicts requires only the following simple changes to the code generated by the Data Source Wizard:

Open the DataSet Designer's Relation dialog for each relationship, change the Foreign Key Only to the Both Relation and Foreign Key Constraints option, and change the Update Rule and Delete Rule from None to Cascade.*

Invert the sequence of the FormName_Load event handler's TableAdapter.Fill() methods so that the TableAdapters fill from the top of the hierarchy down (Customers, Orders, Order_Details).

Add BindingSource.EndEdit() methods to the BindingNavigator_SaveItem() event handler for the second and later foreign-key nodes you dragged to the form. (This step doesn't relate directly to hierarchical table binding.)

* You might find that the Beta 2 DataSet Designer's UI doesn't sync with the underlying changes to the underlying DataSetName.Designer.vb or .cs file's values for the rules.

Following is the source code required for these changes, plus an added Reload button for convenience in verifying database updates:

The upshot of this improved hierarchical updates scenario is that creating and testing a Windows form for a three-level hierarchy requires adding only four simple lines of code and setting six property values in the DataSet Designer. This usually takes less than half an hour. The Windows form handles hierarchical insertions, modifications, and deletions at all levels (Customers, Orders, and Order_Details) correctly.

ADO.NET 3.5's DataSet Designer also lets you autogenerate TableAdapter and typed DataSet code in different projects. This lets you protect the privileged information about the database and its connection string(s) from access by the presentation layer, which connects to TableAdapters only.

Hierarchical Updates to LINQ to SQL Entities

LINQ to SQL appears to emulate DataSet databinding but the behavior of editing operations differs, especially when deleting child records. Materializing the result of LINQ to SQL queries by invoking the DataContext.Table<TEntity>.ToList() method makes substantial changes to insertion and deletion operations.

Code examples for and demonstrations of entity insertions, updates, or deletions with LINQ to SQL seldom involve more than a single table and only a few utilize ADO.NET databinding. An exception is Young Joo's LINQ to SQL and the O/R Designer in VS 2008 Channel9 video interview with Beth Massi of August 27, 2007.

The timing of Young Joo's coverage of databinding a two-level hierarchy is as follows:

16:00 He adds a new Data Source from a DataContext's Customer entity.

17:00 He explains the the association between the Customer entity and Customer.Orders property.

18:00 Mr. Joo drags the Customer node to the form to add the CustomerBindingSource, CustomerBindingNavigator, and CustomerDataGridView controls.

18:10 He Drags the Customer.Orders node to the form to add the OrdersBindingSource and OrdersDataGridView controls.

18:48 Mr. Joo admits that "you do have to write a couple of lines of code ... to instantiate your DataContext" and set the CustomerBindingSource.DataSource property value to the new NorthwinDataContext.Customer Table<TEntity> instance. "Just two lines of code."

19:40 He assigns the DataContext.Log property to Console.Out to demonstrate the T-SQL statements that lazy-load the Order instances as he scrolls the CustomerDataGridView.

20:45 He mentions that if had an Order Details grid, it would lazy load as he selects the associated order row.

21:05 "One cool thing about LINQ to SQL is that it already takes care of all the hierarchical updates for you." (Emphasis added)

21:12 He enables the Save Data button and says, "All you have to do to save the changes back to the database is to call the DataContext.SubmitChanges() method. As you make changes to your object, the DataContext will keep track of all the changes that you made, whether you've updated, inserted, [or deleted]."

21:45 "It will order them in such a way that you don't get referential integrity errors." (Emphasized content rephrased to represent intent.)

21:25 "In the LINQ to SQL case here, I only wrote one line of code: DataContext.SubmitChanges() ... you don't have to worry about anything else; it will take care of everything." (Emphasis added.)

22:40 Mr. Joo demonstrates a "simple update case" by changing cell values in the two grids and shows the log entries in the Output window.

Mr. Joo must be using a different version of LINQ to SQL than my copy of VS 2008 Professional Edition Beta 2. Using Mr. Joo's default code, I encounter the following referential integrity and other other errors when deleting rows from the grid and submitting changes:

Attempts to delete a Customer instance by deleting a CustomerDataGridView row throw "The DELETE statement conflicted with the REFERENCE constraint "FK_Orders_Customers" exceptions, contrary to preceding items 8 and 10.

Attempts to delete an Order_Details EntitySet member by deleting an Order_DetailsDataGridView row throw an "An attempt was made to remove a relationship between a[n] Order and a[n] Order_Detail. However, one of the relationship's foreign keys (Order_Detail.OrderID) cannot be set to null," which contradicts preceding item 11. This error is fatal; the Order_Detail is deleted from the DataContext but not from the persistence store. You must reload the data before you can submit any other changes.

It's unfortunate that Mr. Joo didn't attempt to demonstrate deleting a row from the CustomerDataGridView before asserting that LINQ to SQL "takes care of all the hierarchical updates for you" and "you don't get referential integrity errors."

Oddly, removing an Order and its Order_Detail(s) instances by deleting an OrderDataGridView row succeeds. The T-SQL emitted for deletion sets the CustomerID to NULL with a batch like:

but leaves the orphaned records in the persistence store's Orders and Order Details tables. This is a truly curious approach, which probably works because the string CustomerID foreign key is nullable, whereas LINQ to SQL treats the Order Details record's integer OrderID foreign key as not nullable and throws an exception.

The DataSet Designer avoids these pitfalls by providing the Relation dialog with the ability to select the Update and Delete Rules as None, SetNull, SetDefault, or Cascade. (However, selection dropdowns in the Beta 2 version of the Relation dialog always show None and don't display the actual current setting.)

Adding a DeleteOnNull="true" attribute after the IsForeignKey="true" attribute of the <Association Name="CustomerOrder ... /> and <Association Name="OrderOrder_Detail" ... /> elements as described in Beth Massi's LINQ to SQL and One-To-Many Relationships post of October 2, 2007 throws an "Error DBML1055: The DeleteOnNull attribute of the Association element 'Customer_Order' can only be true for singleton association members mapped to non-nullable foreign key columns." The Yet another DeleteOnNull problem thread in the LINQ Project General forum illustrates other frustrations with the DeleteOnNull attribute.

It's a common practice to invoke the the DataContext.Table<Customer>.ToList() method to create a concrete List<Customer> instance (cache) and eliminate the need for a persistence store roundtrip for each LINQ to SQL query against the Table<TEntity>. The capability to pass List<T> instances between layers and serialize them for crossing process boundaries (tiers) lets you isolate the DataContext from the business logic and presentation layers.

Note: Lazy loading Orders and Order_Details EntitySets requires two round-trips to the persistance store for each row you select in the CustomerDataGridView. You can minimize the resources consumed by EntitySets by preloading them with the method described in my Save Server Round Trips by Preloading LINQ to SQL EntityRefs post of October 1, 2007. Stored procedures can use TOP n ... ORDER BY OrderDate DESC statements or otherwise restrict the number of EntitySet members.

Following are the problems you'll encounter when you bind the UI's BindingSources to concrete List<Customer> cache and its EntitySets:

Adding object(s) in the CustomerDataGridView and invoking DataContext.SubmitChanges() doesn't persist the change(s) to the underlying data store because the List<Customer> is a different data type than Table<Customer>. Therefore, the IdentityManager allows multiple copies of the instances in memory.

Attempts to delete a Customer instance by deleting a CustomerDataGridView row fail silently for the same reason.

Attempts to delete an Order_Detail instance by deleting an Order_DetailDataGridView row fail with the same "An attempt was made to remove a relationship between a[n] Order and a[n] Order_Detail" exception. (Same as for the default Table<TEntity> binding.)

If the Order has more than one Order_Details row, attempting to delete one Order_Detail instance causes attempts to delete an Order to throw the same same "An attempt was made to remove a relationship between a[n] Order and a[n] Order_Detail" exception.

The CustomerDataGridView doesn't support sorting (because List<T> doesn't implement IBindingList<T>) but sorting is enabled for the Orders and Order_Details EntitySets. (My Support Sorting and Filtering for List<T> Collections post of September 7, 2007 shows you how to add these features to a List<T> with a class library.)

The workaround for problems 1 through 4 is to process instances added or deleted in the UI in List<Customer> collections (lstCustomer and delCustomers):

Populate a form-scoped List<Customer> collection (delCustomers) with a DataContext.Table<Customer>.ToList() invocation.

Add a form-scoped List<Customer> collection to hold instances of deleted List<Customer> items, which you add in the CustomerDataGridView_UserDeletingRow() event handler. (A deletion confirmation message box is a useful addition to this event handler.)

In the event handler for saving changes, Invoke the DataContext.Table<Customer>.Add(entity) method to add each new Customer item.

For each deleted Customer item, invoke the DataContext.Table<Customer>.Remove(entity) method on its Order_Details and Orders EntitySets and finally on the Customer item.

Call the DataContext.SubmitChanges() method

Clear the delCustomers collection.

Here's the code to process the changes by adding or removing their instances from the DataContext:

The preceding example needs additional generic collections and code to save original and modified values for updated items if your DataContext runs out-of-process. One approach is to use a "maxi connectionless DataContext", as described in my LINQ to SQL Has No "Out-of-the-Box Multi-Tier Story!" post of October 18, 2007.

An alternative is to create a deep clone (which includes the EntitySets) of lstCustomer and a modCustomers list to store copies of updated records and call the DataContext.Table<Customer>.AttachAll(modCustomers, origCustomers) method, where origCustomers is the subset of lstCustomer that corresponds to modCustomers items. If you designate a timestamp field for concurrency conflict management, you don't need origCustomers.

Removing references to the DataContext in the presentation layer requires implementing AddCustomers(newCustomers), ModifyCustomers(modCustomers, origCustomers) and RemoveCustomers(delCustomers) methods in the DataContext class library. I'll cover this approach and providing default foreign-key and other property values for new entities in a later post.

Applicability of This Approach to the Entity Framework

The Entity Framework's ObjectContext is quite similar to LINQ to SQL's DataContext, so much of this code and that of later posts on this and related databinding topics should apply to the EF and LINQ to Entities. I'll provide some examples in future posts.

The dual Web role application has been running in Microsoft's South Central US (San Antonio) data center since September 2009. I believe it is the oldest continuously running Windows Azure application.

About Me

I'm a Windows Azure Insider, a retired Windows Azure MVP, the principal developer for OakLeaf Systems and the author of 30+ books on Microsoft software. The books have more than 1.25 million English copies in print and have been translated into 20+ languages.

Full disclosure: I make part of my livelihood by writing about Microsoft products in books and for magazines. I regularly receive free evaluation software from Microsoft and press credentials for Microsoft Tech•Ed and PDC. I'm also a member of the Microsoft Partner Network.