LINQ to SQL's WinForms Databinding Story

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:

1. 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.*
2. 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).
3. 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.

Patterns: Typed and untyped DataSets implement the Table Module and Unit of Work patterns and can be used as Data Transfer Objects. See Data Transfer Object, Implementing Data Transfer Object in .NET with a DataSet and Implementing Data Transfer Object in .NET with a Typed DataSet from Microsoft patterns & practices. Encapsulating TableAdapter and DataSet code in different projects aids in maintaining Separation of Concerns (SoC).

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.

A Partial Demonstration of LINQ to SQL DataBinding, Presumably with VS 2008 Beta 2

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:

1. 16:00 He adds a new Data Source from a DataContext's Customer entity.
2. 17:00 He explains the the association between the Customer entity and Customer.Orders property.
3. 18:00 Mr. Joo drags the Customer node to the form to add the CustomerBindingSource, CustomerBindingNavigator, and CustomerDataGridView controls.
4. 18:10 He Drags the Customer.Orders node to the form to add the OrdersBindingSource and OrdersDataGridView controls.
5. 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."
6. 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.
7. 20:45 He mentions that if had an Order Details grid, it would lazy load as he selects the associated order row.
8. 21:05 "One cool thing about LINQ to SQL is that it already takes care of all the hierarchical updates for you." (Emphasis added)
9. 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]."
10. 21:45 "It will order them in such a way that you don't get referential integrity errors." (Emphasized content rephrased to represent intent.)
11. 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.)
12. 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: 

1. 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.
2. 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:

UPDATE [dbo].[Orders]
SET [CustomerID] = @p13
WHERE ([OrderID] = @p0) AND ([CustomerID] = @p1) AND ([EmployeeID] = @p2) AND ([OrderDate] = @p3) AND ([RequiredDate] = @p4) AND ([ShippedDate] IS NULL) AND ([ShipVia] = @p5) AND ([Freight] = @p6) AND ([ShipName] = @p7) AND ([ShipAddress] = @p8) AND ([ShipCity] = @p9) AND ([ShipRegion] = @p10) AND ([ShipPostalCode] = @p11) AND ([ShipCountry] = @p12)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [11123]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- @p2: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/22/2007 12:00:00 AM]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/5/2007 12:00:00 AM]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p6: Input Currency (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p7: Input String (Size = 20; Prec = 0; Scale = 0) [Bogus Software, Inc.]
-- @p8: Input String (Size = 13; Prec = 0; Scale = 0) [1220 Broadway]
-- @p9: Input String (Size = 7; Prec = 0; Scale = 0) [Oakland]
-- @p10: Input String (Size = 2; Prec = 0; Scale = 0) [CA]
-- @p11: Input String (Size = 5; Prec = 0; Scale = 0) [94612]
-- @p12: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- @p13: Input String (Size = 0; Prec = 0; Scale = 0) []
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

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.)

Bug Report: I consider leaving orphaned records in the persistence store to be a non-blocking bug. The Microsoft Connect report is 306378, Deleting a LINQ to SQL EntitySet Member in a Bound DataGridView Orphans Table Records (October 23, 2007).

Update October 30, 2007: Microsoft still hadn't acknowledged my week-old bug report.

Workarounds for the Two Deletion Issues

Following are event handlers that execute LINQ to SQL queries to remove 1) Order and Order_Detail and 2) Order_Detail entities:

Here's the typical T-SQL emitted, which includes a query to retrieve the Customer entity from the persistence store:

SELECT TOP 1 [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CustomerID] = @p0
-- @p0: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

DELETE FROM [dbo].[Order Details] WHERE ([OrderID] = @p0) AND ([ProductID] = @p1) AND ([UnitPrice] = @p2) AND ([Quantity] = @p3) AND ([Discount] = @p4)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [11126]
-- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
-- @p2: Input Currency (Size = 0; Prec = 19; Scale = 4) [20.0000]
-- @p3: Input Int16 (Size = 0; Prec = 0; Scale = 0) [24]
-- @p4: Input Single (Size = 0; Prec = 0; Scale = 0) [0.1]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

DELETE FROM [dbo].[Orders] WHERE ([OrderID] = @p0) AND ([CustomerID] = @p1) AND ([EmployeeID] = @p2) AND ([OrderDate] = @p3) AND ([RequiredDate] = @p4) AND ([ShippedDate] IS NULL) AND ([ShipVia] = @p5) AND ([Freight] = @p6) AND ([ShipName] = @p7) AND ([ShipAddress] = @p8) AND ([ShipCity] = @p9) AND ([ShipRegion] = @p10) AND ([ShipPostalCode] = @p11) AND ([ShipCountry] = @p12)
-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [11126]
-- @p1: Input String (Size = 5; Prec = 0; Scale = 0) [BOGUS]
-- @p2: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [10/22/2007 12:00:00 AM]
-- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/5/2007 12:00:00 AM]
-- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
-- @p6: Input Currency (Size = 0; Prec = 19; Scale = 4) [10.0000]
-- @p7: Input String (Size = 20; Prec = 0; Scale = 0) [Bogus Software, Inc.]
-- @p8: Input String (Size = 13; Prec = 0; Scale = 0) [1220 Broadway]
-- @p9: Input String (Size = 7; Prec = 0; Scale = 0) [Oakland]
-- @p10: Input String (Size = 2; Prec = 0; Scale = 0) [CA]
-- @p11: Input String (Size = 5; Prec = 0; Scale = 0) [94612]
-- @p12: Input String (Size = 3; Prec = 0; Scale = 0) [USA]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

An alternative workaround, fixing the "one of the relationship's foreign keys (Order_Detail.OrderID) cannot be set to null'." exception, isn't easy as noted in my Cascade Deletion Problem with LINQ to SQL Beta 2 post.

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.

Patterns: According to Ian Cooper in his seminal Being Ignorant with LINQ to SQL post of June 10, 2007:

LINQ to SQL uses the Data Mapper architectural pattern. ... IQueryable resembles a Query Object; an IQueryable<T> also resembles what [Eric] Evans calls a Specification in Domain Driven Design in its composability. Collectively the DataContext and Table<T> ... do Metadata Mapping to handle loading and saving of objects from the store. DataContext implements the Unit of Work pattern; LINQ provides an Identity Map, a consequence of providing a unit of work, and also provides support for Lazy Loading. LINQ supports Foreign Key Mapping through the EntitySet and EntityRef collection types. LINQ to SQL also supports ... Single Table Inheritance as opposed to a table per type (Class Table Inheritance) or a table per concrete type (Concrete Table Inheritance).

AntiPatterns: This two-tier client/server configuration doesn't maintain SoC because the presentation layer's BindingSource components and code-behind connect directly to the DataContext, which exposes privileged database information and provides direct access to the connection string.

Caching DataContext.Table<TEntity> Data to Minimize Roundtrips and Separate Concerns

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:

1. 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.
2. Attempts to delete a Customer instance by deleting a CustomerDataGridView row fail silently for the same reason.
3. 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.)
4. 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.
5. 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):

1. Populate a form-scoped List<Customer> collection (delCustomers) with a DataContext.Table<Customer>.ToList() invocation.
2. 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.) 
3. In the event handler for saving changes, Invoke the DataContext.Table<Customer>.Add(entity) method to add each new Customer item.
4. 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. 
5. Call the DataContext.SubmitChanges() method 
6. 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.

Updated 10/24/2007: Added code and text to "Caching DataContext.Table<TEntity> Data to Minimize Roundtrips and Separate Concerns" topic.

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.

Updated 10/25/2007: Added Entity Framework applicability as an afterthought.

Technorati Tags: LINQ to SQL, Databinding, LINQ, DataSet, DataGridView, BindingSource, Table Model, Data Transfer Object, Query Object, Metadata Mapping, Unit of Work, Identity Map, Lazy Loading, Foreign Key Mapping, Single Table Inheritance, Doman-Driven Design, Test-Driven Development, Entity Framework, LINQ to Entities, Entity Data Model