Upgrade to LINQ to SQL's WinForm Databinding with Stored Procedures Scheduled for RTM

According to an email I received late last night from Dinesh Kulkarni, LINQ to SQL WinForm databinding will change by RTM for data retrieval with stored procedures. Dinesh slipped the upgrade into the Visual Studio 2008 development schedule one day before change lockdown.

What the change will do:

• Databinding with sprocs will become like that with the DataQuery<T> implementation for dynamic SQL when you specify the sproc's Return Type property value as an entity in the O/R Designer's Methods pane. (Or drag the sproc from Server Explorer and drop on the entity widget in the main Designer pane.)
• Your DataContext objects will have Table<TEntity> properties that behave like those from data retrieval with dynamic SQL.
• You'll be able to sort DataGridView columns without using a third-party IBindingListView implementation like that I described in my Support Sorting and Filtering for List<T> Collections post of September 7, 2007. Table<TEntity> has a GetNewBindingList() method. 

What the change won't do:

• Dinesh says the requirement to override dynamic T-SQL for INSERT, UPDATE, and DELETE operations with the Configuration Behavior dialog won't change.
• He also says the need to write LoadEntityName method overrides to easy- or eager-load EntityRefs and EntitySetss, as described in the "Specific Problems with ASP.NET Server-Side Paging and EntityRefs" section of my Problems Using Stored Procedures for LINQ to SQL Data Retrieval post, will remain.
• The upgrade won't relieve the need to make a server round-trip for each EntityRef and EntitySet, as noted by Matt Warren in his last answer to my Problem Populating a LinqDataSource's EntityRef Association with a Stored Proc post to the LINQ Project General forum.
• You'll still need to write custom stored procedures to handle ad-hoc sorting of GridView columns with the LinqDataSource's server-side paging feature.
• Applying a filter to or enabling advanced (multi-column) sorting a DataGridView bound to a BindingSource component will still require an IBindingListView wrapper. 

What remains to be resolved and/or disclosed:

• It's not clear at this point what effect the upgrade will have on ASP.NET's LinqDataSource. I've sent a message to Scott Guthrie requesting clarification.
• LINQ queries executed against Table<TEntity> instances generate dynamic SQL, as Matt Warren notes in his two answers in the Re: SPROCS, multiple results, and associations thread of 9/12/2007. It would be preferable to direct LINQ queries to the Table<TEntity> instances rather than the data store when the data source is a sproc. Hopefully, Dinesh will address this later.

I'm sure I'll have other questions as I recreate my Problems Using Stored Procedures for LINQ to SQL Data Retrieval post to account for the forthcoming changes.

DataQuery<T> and Table<TEntity> vs. ISingleResult<T> and List<T> Collections

DataQuery<T> is an internal sealed class (Friend NotInheritable Class in VB), so you won't see it in Object Browser. Lutz Roeder's .NET Reflector app shows that DataQuery<T> implements IOrderedQueryable<T>, IQueryable<T>, IQueryProvider, IEnumerable<T>, IOrderedQueryable, IQueryable, IEnumerable, and IListSource:

 

Executing a DataQuery<T> instance, which represents an expression tree, attaches a Table<TEntity> type to the active DataContext object. Only Table<TEntity> types support EntityRef<TEntity> and EntityType<TEntity> associations. 

Note: Frans Bouma, creator of the LLBLGenPro commercial Object/Relational Mapping (O/RM) tool, made the wise decision on Developing Linq to LLBLGen Pro, Day 1 to implement DataQuery<T> as LLBLGenProQuery<T> for his LINQ to LLBLGenPro implementation. (Frans is a proponent of dynamic SQL for O/RM applications, as you can see by reading his Stored procedures are bad, m'kay? post. Frans' Yay! A new Stored Proc vs. Dyn. Sql battle! item also has links to other posts, both pro and con.)

It's important to note that LINQ to SQL's IdentityManager recognizes Table<TEntity> types while they're attached to the active DataContext object. SPs return either ISingleResult<T> or IMultipleResult<T> collections, which are lightweight types that implement IEnumerable<T> but differ considerably from Table<TEntity> collections. IdentityManager has no knowledge of objects contained in ISingleResult<T> or elements of IMultipleResult<T> collections.

Table<TEntity> collections implement IQueryable<T>, IQueryProvider, IEnumerable<T>, ITable, IQueryable, IEnumerable, and IListSource. ITable exposes the Attach, AttachAll, GetModifiedMembers, and GetOriginalEntityState methods that ISingleResult<T> doesn't define. The GetModifiedMembers and GetOriginalEntityState methods are needed to support change tracking and optimistic concurrency checking. The GetNewBindingList method returns an IBindingList that supports sorting and change notification by bound DataGridView controls.

ISingleResult<T> collections only mplement IEnumerable<T>, IEnumerable, and IFunctionResult and IDisposable. Databinding features of IEnumerable<T> types are very limited; if the LINQ query returns a projection (an anonymous type), no data binding occurs; if the query returns the concrete source data type, such as Customer, grids can't add or delete records. So it's a common practice to apply the ToList<T> method to the ISingleResult<T> object to enable data binding to a List<T> collection.

List<T> generic collections implement IList<T>, ICollection<T>, IEnumerable<T>, IList, ICollection, IEnumerable. IList<T> is a decendant of ICollection<T>, which provides Add, Remove, Clear, Contains and CopyTo methods and the Count property to improve the data binding experience. However, the List<T> collection is no match for the IBindingList type from the Table<TEntity> collection that supports sorting and change notification by bound DataGridView controls. (Updated 9/19/2007 17:00 PDT)

Technorati Tags: LINQ to SQL, LINQ, Store Procedures, WinForms, LinqDataSource, DataBinding, DataGridView