Problems Using Stored Procedures for LINQ to SQL Data Retrieval (Updated)

This is an updated summary of my experiences when using stored procedures for retrieving data to populate (hydrate) LINQ to SQL entities with stored procedures (SProcs).

Many issues reported in the original Problems Using Stored Procedures for LINQ to SQL Data Retrieval post of September 8, 2007 have workarounds or are scheduled for a fix before RTM. Remaining problems and workarounds have been reconfirmed in Visual Studio 2008 Beta 2 with one or more of six ADO.NET and ASP.NET test harnesses. The test harnesses accompany my forthcoming Leverage LINQ in ASP.NET Projects report for Wrox/Wiley (downloadable content, ISBN: 978-0-470-25910-8) and "Optimize LINQ to SQL Performance" article for the November 2007 issue of Visual Studio Magazine. The test harnesses will be available for download upon purchase and publication, respectively.

Why Use Stored Procedures?

There are two primary reasons for using SProcs to hydrate LINQ to SQL (or Entity Data Model) entities:

• Your organization's DBAs won't give users or applications SELECT privileges on production SQL Server tables.
• You want to control the number of records to populate EntitySet associations with a Top(n) limit or date-based criteria using eager or lazy loading. This option isn't available directly with autogenerated dynamic SQL.

Stored procedures offer no significant performance or security advantages over the parameterized prepared statements generated by LINQ to SQL (or the Entity Framework) and executed by the sp_executesql SProc.

Problems with Stored Procedures in ADO.NET and ASP.NET Projects

The following issues relating to use of SProcs for data retrieval and updates affect both .NET 3.5 Windows and Web forms:

1. You can't inhibit sending T-SQL statements to the server when you execute LINQ queries against Table<TEntity> (DataContext.TableName) objects. However, LINQ queries against BindingList<T>, collections created from Table<TEntity> objects operate on in-memory instances so they don't send T-SQL statements.
2. You must execute a stored procedure to retrieve each EntitySet or EntityRef object with eager or lazy loading. However, it's possible to preload EntitySets as described in the Save Server Round Trips by Preloading LINQ to SQL EntityRefs post.
3. You must override in a partial class the default functions that retrieve EntitySet and EntityRef objects to populate associations. The syntax for the required LoadEntitySet and LoadEntityRef overloads is undocumented as of this post's date.
4. You don't gain the benefit of minimal network traffic for updates by sending only the data for column(s) that change plus that required to support optional optimistic concurrency control (preferably a single timestamp field.)
5. Lazy-loading specified properties by setting their Delay Loaded property value to True in the O/R Designer doesn't work with SProcs.
6. The graphical O/R Designer doesn't support stored procedures that return multiple resultsets, so they require you to write a substantial amount of custom code.

In Beta 2 and earlier, SProcs return ISingleResult<T> instead of Table<TEntity> objects. ISingleResult<T> objects are second-class objects that don't provide important Table<TEntity> methods, such as Attach(), Detach() and ToBindingList(). Applying the ToList() method to data-bind BindingSource components or DataGridView controls results in non-sortable data. The LINQ to SQL team has committed to a fix for this problem by RTM (see Upgrade to LINQ to SQL's WinForm Databinding with Stored Procedures Scheduled for RTM.) It's not known if the change will have any affect on the ASP.NET LinqDataSource control.

Problems with Stored Procedures That Affect ASP.NET Projects Only

SProc issues affecting ASP.NET projects that use GridView controls bound to LinqDataSource controls primarily relate to server-side paging and sorting:

1. The LinqDataSource control supports server-side paging with ad-hoc sort column sequence and direction when you retrieve data with dynamic SQL. Server-side paging requires SQL Server 2005 or later. Substituting SPprocs requires writing complex CASE statements to create dynamic ORDER BY clauses.
2. Server-side sorting by EntityRef values leads to exceedingly complicated logic in the SProcs(see Dynamic ORDER BY Clauses for ASP.NET Server-Side Paging with Stored Procedures of 9/18/2007.)

Here's a screen capture of the OrdersLinqDataSourceSP.sln test harness for pre-loading EntityRef objects and server-side sorting (by Employee.LastName) with a LinqDataSource control:

Click image for full-size screen capture.

The number of issues is greatly reduced from that of September 8 because of the forthcoming ISingleResult<T> fix and validation of workarounds for other initial problems.

Updated 10/1/2007: Added item #5 to "Problems with Stored Procedures in ADO.NET and ASP.NET Projects" topic.

Technorati Tags: LINQ to SQL, LINQ, DataGridView, GridView, LinqDataSource, Stored Procedures, Dynamic Queries