Problems Using Stored Procedures for LINQ to SQL Data Retrieval

 

Scott Guthrie posted LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures) on August 16, 2007 and I dutifully added it as an update to my LINQ Updates for 8/15/2007 compendium. Scott says in his post's "To SPROC or not to SPROC? That is the Question" section:

As you'll learn in this post, you can also optionally map SPROCs in the database to your LINQ to SQL DataContext class, which allows you to alternatively retrieve the same Product objects by calling a stored procedure instead. ... This ability to use both dynamic SQL and SPROCs with a clean data model layer is pretty powerful, and provides a great deal of flexibility when working on projects. [Emphasis added.]

My tests show that you don't "retrieve the same Product objects by calling a stored procedures instead." LINQ to SQL's dynamic SQL statements retrieve a DataContext.Table<TEntity> collection, while stored procedures retrieve a DataContext.ISingleResult<T> collection or DataContext.IMultipleResults collection for stored procs that return more than one resultset. Differences between the DataContext.Table<TEntity> and DataContext.ISingleResult<T> types are more significant with smart-client (i.e., WinForm) than Web projects.

Substitution of stored procedures for dynamic SQL statements for retrieving data with the LinqDataSource in simple ASP.NET Web sites with self-contained LINQ to SQL classes works as expected and requires only adding three lines of code to the PageName.aspx.cs or .vb file. However, changing to stored procedures disables one of the LinqDataSource's most important features, server-side paging (see David Hayden's LinqDataSource - LINQ to SQL ASP.NET Website Rapid Application Development post.)

Update 9/18/2007: Problems encountered when substituting stored procedures for dynamic T-SQL when populating EntitySet and EntityRef associations of DataContexts generated by LinqDataSources have been resolved.

Update 9/17/2007: There are serious issues with the GridView's ad hoc sorting when emulating the LinqDataSource's server-side sorting feature with stored procedures (see below and Dynamic ORDER BY Clauses for ASP.NET Server-Side Paging with Stored Procedures of 9/18/2007.)

Update 9/14/2007: The problem with server-side paging is fixed (see below.) 

Update 9/12/2007: This post went through a major edit on 9/12/2007. Content on the WinForms test harness has moved to another post in progress due to the length of this post.

Stored Procedures versus Dynamic SQL for LINQ to SQL Data Layers

I ordinarily use stored procedures (SPs) for updating tables that persist LINQ to SQL objects, but I rely on dynamic T-SQL parameterized prepared statements (PPSs) to retrieve data for persisted objects. Since the arrival of SQL Server 7.0, SPs have not been compiled. SPs and PPSs now deliver identical performance because their execution plans are cached in an identical manner. Adding parameters to prepared statements eliminates the SQL injection threat.

However, I'm acquainted with several DBAs for Fortune-500 firms who insist on the use of SPs for all access to "their" databases (except by themselves, of course.) It's not a good consulting practice to tell a DBA to "take a hike" when objections to using PPSs for SELECT queries arise. So I decided to look into issues that might arise when substituting SPs for T-SQL PPSs used to hydrate LINQ to SQL objects.

I also make it a practice to create data access layers (DALs) with LINQ to SQL classes compiled to class libraries. This approach delivers "plain old CLR objects" (POCOs) to business logic and higher layers with an acceptable level of persistence ignorance, which I achieve by setting the DataContext class's Access property value to Internal.(For more about persistence ignorance, see Ian Cooper Takes on DDD, TDD and PI with LINQ to SQL and Linq To Sql POCO Support.) I also wrap List<T> types with BindingListView to deliver IBindingListView collections that support filtering and advanced sorting by DataGridView controls (see Support Sorting and Filtering for List<T> Collections.)

Here's my shortlist of problems and issues that arose when I tried retrieving persisted objects to the DataContext with SPs in several trial projects, with or without the LINQ to SQL class in its own DAL assembly:

• SPs don't populate EntityRef property values or EntitySet collections by default. If you plan to navigate associations, be ready to write code to populate them and update their contents when adding or deleting a member.
• DataContext objects with ISingleResult<T> objects returned by SPs don't offer the default lazy (delayed) loading option for EntityRefs or EntitySets.
• All associated entities must be eager-loaded by custom-written code.
• List<T> objects created from ISingleResult<T> objects returned by SPs don't support change-tracking directly*. Workarounds are kluges, at best, as noted below.
• The DataContext's IdentityManager doesn't recognize List<T> members generated from ISingleResult<T> objects until they're modified, which places them under the auspices of the ChangeTracker.
• The DataContext issues a dynamic T-SQL query for each LINQ query that uses a Table<TEntity> as its data source. Without Select permission on the targeted database table(s), SQL Server spews a stream of error messages. If there's a property setting to prevent this problem, I haven't been able to find it.
• The LinqDataSource control supports only DataContext objects as data sources in the first LinqDataSource Configuration wizard dialog, Choose a Context Object. If you set the DataContext object's access to Internal in your LINQ to SQL DAL, you can't use the LinqDataSource (or create partial DataContext classes.) If you change the DataContext object's access to Public, you lose persistence ignorance.
• The LinqDataSource control supports server-side paging with ad-hoc sort column sequence and direction when you retrieve data with dynamic SQL. Substituting SPs requires writing complex CASE statements to create dynamic ORDER BY clauses. Maintaining the sort sequence requires storing sort expressions in cookies or session state. Sorting by EntityRef values leads to exceedingly complicated logic in the SP. Server-side paging requires SQL Server 2005 or later. (Added 9/17/2007.) See Dynamic ORDER BY Clauses for ASP.NET Server-Side Paging with Stored Procedures of 9/18/2007.
• The ObjectDataSource control doesn't support ISingleResult<T> objects as its data source. You can select an ISingleResult<T> type as a business object in the first configuration wizard dialog, but the related SPs are methods of the DataContext object, not the BusinessObject, so they don't appear in the second dialog.
• The ObjectDataSource control requires adding custom SELECT, INSERT, UPDATE and DELETE methods to its BusinessObject but doesn't require making the DataContext object Public, assuming that you don't need to add code to the DataContext in a partial class file.
• DataGridView columns aren't sortable when bound to a List<T> object. A DataGridView bound though a BindindingSource component or directly to an IBindingList collection created by applying the DataTable<T>.GetNewBindingList() method has sortable columns if the source of the DataTable<T> is an SQL Server table or view object. However, you can't filter a BindindingSource populated by an IBindingList**.

* The Table<TEntity> of the designated SP object type will track edits to members of a List<T> object created by invoking the the ISingleResult<T> object's ToList<T> method. However, tracking object additions to and removals from List<T> objects with corresponding Table<TEntity> objects requires a substantial amount of hand-written code.

Note: Changing the sequence's type from ISingleResult<T> to List<T> by invoking the ToList() method is required to enable adding or removing rows of bound (directly or through a BindingSource component) DataGridView controls in WinForms but not GridView controls of WebForms. ASP.NET examples don't need to invoke the ToList() method unless you encounter "The query results cannot be enumerated more than once" exceptions or otherwise need to persist the ISingleResult<T> as a collection.

** You can enable advanced sorting and filtering of BindingSource components populated with List<T> or IBindingList collections by wrapping them with a BindingListView layer, as described in the Support Sorting and Filtering for List<T> Collections post.

Note: Hopefully, Entity Framework users won't encounter these problems with stored procedures for entity retrieval. It appears from Zlatko Michailov's posts in the ADO.NET Orcas forum's Sprocs and views in June CTP thread that the <Function ...> element in the Storage (SSDL) layer, <FunctionImportMapping ...> in the Mapping (MSL) layer and <FunctionImport ...> in the Conceptual (CSDL) layer makes basic entity retrieval operations transparent to stored procedures. However it's not clear how you can specify query paths with, for example ObjectContext.Order.Include("Order.Order_Detail"), or explicitly load related objects using Order.Order_Detail.Load() with stored procedures.

LINQ to SQL Stored Procedure ASP.NET Test Harness Background

In mid-August 2007, I created a simple ASP.NET Web Site test harness with a LinqDataSource control populated from a LINQ to SQL DataContext for the Northwind Customers table and bound a GridView to it. The project includes a reference to the LINQ to SQL class library that provides the DataContext. I added a similar page with a GridView bound to an ObjectDataSource connected to the DataContext.Customers table also. Both GridViews have server-side paging and sorting, selection, edits and inserts enabled. The two pages include buttons to add and remove fictitious BOGUS Customer, Order and OrderDetail objects programmatically. The BOGUS customer lets you edit data without changing the original Northwind database content.

Here's a screen capture of the ASP.NET test harness with a LINQ to SQL class library as a DAL:

Click the image to open a full-size version.

I changed the data retrieval source for the Customer class from an autogenerated SELECT prepared statement to a SELECT * FROM Customers stored procedure, usp_GetCustomers. I was unable to connect the LinqDataSource to the DataContext.usp_GetCustomers stored procedure to create editable DataGridViews. The second dialog, Configure Data Selection, doesn't show ISingleResult<T> objects in its Table list; it only lets you select Table<TEntity> objects. Here's the code for the LinqDataSource:

<asp:LinqDataSource ID="ldsCustomers" runat="server"
    ContextTypeName="NwindLinqSProcsVB.NorthwindDataContext"
    EnableDelete="True" EnableInsert="True" EnableUpdate="True"
    TableName="Customers">
</asp:LinqDataSource>

which invokes the DataContext.GetTable(Customer) method that generates this dynamic SQL:

SELECT COUNT(*) AS [value] FROM [dbo].[Customers] AS [t0]

SELECT TOP 10 [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]

Based on the issues I'd seen so far with SPs for LINQ to SQL data retrieval and the LinqDataSource control, I posted the following comment to Scott's post:

Thursday, August 16, 2007 7:47 PM by Roger Jennings

Hi, Scott,

Great post (as usual). I'm running into a wall trying to substitute stored procedures for dynamic T-SQL in a DataContext that serves as the data source for a LinqDataSource control, which would enable editing of a bound GridView or DetailsView. ISingleResult<EntityClass> implements IEnumerable<EntityClass> but not Table<EntityClass>, which is required for the EntityClass to be visible in the Data Source Wizard's second dialog. Is there a solution (other than moving to the ObjectDataSource) that isn't a hack?

See the bottom of oakleafblog.blogspot.com/.../linq-updates-for-8152007.html for more detail.

Thanks in advance,

--rj

Scott replied early the next morning:

Friday, August 17, 2007 2:05 AM by ScottGu [Emphasis added.]

Hi Roger,

...

Any chance you could send me an email (scottgu@microsoft.com) with a quick sample that demonstrates what you are trying to do here? If the object you are trying to edit is a Table on the DataContext, then I think you can handle this scenario by handling the Selecting event on the LinqDataSource. Alternatively, the cleanest way might be to use the ObjectDataSource.

Hope this helps,

Scott

It did help, but I wanted to do more testing before sending the sample code.

Specific Problems with ASP.NET Server-Side Paging and EntityRefs

Update 9/14/2007: The server-side paging problem was solved by a very minor fix (see below.) However, I'm still having problems with populating EntityRefs with stored procs (see end of post). 

The most vexing problems I encountered with the LinqDataSource control (other than its by-design lack of persistence ignorance) are what appears to be a bug in the way the control attempts to handle server-side paging and lazy loading with stored procedures.

Creating the DataContext in the UI Layer and Losing Server-Side Paging

Scott's suggestion to handle the LinqDataSource.Selecting event worked as expected. The following two lines manipulate the DataContext object to return an ISingleResult<Customer> object:

Protected Sub ldsCustomers_Selecting(ByVal sender As Object, _
        ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) _
        Handles ldsOrders.Selecting
    Dim dcNwind = New NorthwindDataContext()
    e.Result = dcNwind.usp_GetCustomers
End Sub

which generates this T-SQL:

declare @p3 int
set @p3=NULL
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[usp_GetCustomers] ',
N'@RETURN_VALUE int output', @RETURN_VALUE=@p3 output
select @p3

Note: All T-SQL PPSs and stored procedure calls are from SQL Server Profiler, not the DataContext.Log property. Server-side paging is available only with SQL Server 2005+ as the back-end.

Unfortunately, the DataContext object exposes critical properties that should be kept private from upper layers, such as the database connection string.

Notice that there's no SELECT COUNT(*) AS [value] FROM [dbo].[Customers] AS [t0] statement in the preceding batch. Without the TotalRowCount value, paging reverts to the traditional (for ASP.NET) client-side paging that requires all Customer objects to be retrieved. Moving from server-side to client-side paging greatly increases the load on the database server for long lists.

Attempts to Fix the Server-Side Paging Problem

It should be possible to regain server-side paging by executing a stored procedure that emulates the LinqDataSource's PPS. Here's the stored procedure equivalent of the dynamic SQL statement for server-side paging:

CREATE PROCEDURE [dbo].[usp_GetCustomersPaged] (
@maximumRows int = 10,
@startRowIndex int = 20)
-- Created from query generated by LinqDataSource
AS
SET NOCOUNT ON
SELECT TOP (@maximumRows) [t1].[CustomerID], [t1].[CompanyName],
    [t1].[ContactName], [t1].[ContactTitle], [t1].[Address],
    [t1].[City], [t1].[Region], [t1].[PostalCode], [t1].[Country],
    [t1].[Phone], [t1].[Fax]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[CustomerID],
       [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
       [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode],
       [t0].[Country], [t0].[Phone], [t0].[Fax]) AS [ROW_NUMBER],
       [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]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] > @startRowIndex
SET NOCOUNT OFF;

You execute the preceding stored proc with the following Selecting event handler:

Protected Sub ldsCustomers_Selecting(ByVal sender As Object, _
      ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) _
      Handles ldsOrders.Selecting
   Dim dcNwind = New NorthwindDataContext()
   e.Arguments.RetrieveTotalRowCount = False
   e.Arguments.TotalRowCount = 91
   Dim intMaxRows As Integer = e.Arguments.MaximumRows
   Dim intStartRow As Integer = e.Arguments.StartRowIndex
   Dim intRowCount As Integer = 0
   e.Result = dcNwind.usp_GetCustomersPaged(intMaxRows, intStartRow)
End Sub

Note: If you don't set e.Arguments.RetrieveTotalRowCount = False, paging is disabled. If you don't supply a value for e.Arguments.TotalRowCount, only a header with no rows or paging elements appears. The fixed value 91 is present for testing only. The Beta 2 version of LINQ to SQL doesn't codegen output parameter code in Public Function StoredProcName(Parameters) blocks, so you must execute a SELECT COUNT(*) FROM TableName stored proc with a DataContext.ExecuteQuery(Of Integer)("EXEC usp_GetCustomersCount").First() or similar expression.

Unfortunately, the LinqDataSource control only correctly executes queries with 0 as the StartRowIndex value. Other values result in an empty GridView control, despite the fact that the SELECT stored procedure returns the proper rowset. This appears to be a bug.

Update 9/14-15/2007: D'oh! All that was needed to make stored procedure paging work past page 1 was to set the LinqDataSource.AutoPage property value to False. If you download the LinqDataSourcePagingTestHarness.zip sample, just make the change and you're good to go with the stored procedure provided.

You must customize your query to add custom WHERE clause constraints, such as the customer's Country field value in the Order subselect query below:

ALTER PROCEDURE usp_GetOrdersByCountryPaged (
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 18,
    @StartRowIndex int = 0)
AS
SET NOCOUNT ON
SELECT TOP (@MaximumRows) t1.OrderID, t1.CustomerID, t1.EmployeeID,
    t1.OrderDate, t1.RequiredDate, t1.ShippedDate, t1.ShipVia,
    t1.Freight, t1.ShipName, t1.ShipAddress, t1.ShipCity,
    t1.ShipRegion, t1.ShipPostalCode, t1.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY t0.OrderID DESC) AS ROW_NUMBER,
        t0.OrderID, t0.CustomerID, t0.EmployeeID, t0.OrderDate,
        t0.RequiredDate, t0.ShippedDate, t0.ShipVia, t0.Freight,
        t0.ShipName, t0.ShipAddress, t0.ShipCity, t0.ShipRegion,
        t0.ShipPostalCode, t0.ShipCountry
        FROM dbo.Orders AS t0
WHERE t1.ROW_NUMBER > @StartRowIndex AND
    CustomerID IN (SELECT CustomerID FROM Customers WHERE Country = @Country)
ORDER BY t1.OrderID DESC
SET NOCOUNT OFF;

Update 9/13/2007: The preceding issue is reported as Bug ID 297959. The simplified Web site test harness included with the bug report is available from SkyDrive as LinqDataSourcePagingTestHarness.zip. The connection string expects to find the Northwind database permanently attached to a local .\SQLEXPRESS instance. The T-SQL script to create the stored procedure is included with the source code.

Forcing Dynamic SQL by Replacing Foreign Key with EntityRef Values

Scott Guthrie demonstrated replacement of a Product object's SupplierID and CategoryID values with Supplier.CompanyName and Category.CategoryName EntityRef values in his July 16, 2007 LINQ to SQL (Part 5 - Binding UI using the ASP:LinqDataSource Control) post. Performing a similar change by altering the definition for an Order object's EmployeeID column to a Template Field as shown here:

<asp:templatefield HeaderText="EmployeeID">
    <edititemtemplate>
        <asp:TextBox ID="TextBox1" runat="server"
             Text='<%# Bind("EmployeeID") %>'></asp:TextBox>
    </edititemtemplate>
    <itemtemplate>
        <asp:Label ID="Label1" runat="server"
             Text='<%# Eval("Employee.LastName") %>'></asp:Label>
    </itemtemplate>
</asp:templatefield>

and substituting the code to display Employee.LastName causes the LinqDataSource control to issue the correct stored procedure call:

declare @p4 int
set @p4=NULL
exec sp_executesql N'EXEC @RETURN_VALUE = [dbo].[usp_GetOrdersByCountry] @Country = @p0',N'@p0 nvarchar(3),@RETURN_VALUE int output',@p0=N'USA',@RETURN_VALUE=@p4 output
select @p4

followed by this dynamic T-SQL statement to retrieve the employee object for each row until all nine unique employee objects have been retrieved:

exec sp_executesql N'SELECT [t0].[EmployeeID], [t0].[LastName],
    [t0].[FirstName], [t0].[Title], [t0].[TitleOfCourtesy],
    [t0].[BirthDate], [t0].[HireDate], [t0].[Address], [t0].[City],
    [t0].[Region], [t0].[PostalCode], [t0].[Country],
    [t0].[HomePhone], [t0].[Extension], [t0].[Notes], [t0].[ReportsTo]
FROM [dbo].[Employees] AS [t0]
WHERE [t0].[EmployeeID] = @p0',N'@p0 int',@p0=7

which would result in a stream of error messages due to lack of client select privileges.

Update 9/14-18/2007: Matt Warren demonstrated how to populate EntitySet associations with stored procedures in these two posts: Re: Populate association with SP (9/7/2007) and Re: SPROCS, multiple results, and associations (9/12/2007). As noted in my reply at the bottom of the second post, Matt's solution populates EntitySet associations, but not EntityRef associations, with stored procs. I haven't found any other source of information on this topic, so I had to guess at the EntityRef syntax. On 9/18/2007 Matt provided the answer to what was wrong with my original code in a reply to my Problem Populating a LinqDataSource's EntityRef Association with a Stored Proc post in the Linq Project General forum. These later changes are underlined.

Here's the VB 9.0 code required Orders page to use a stored procedure to populate eager-load the Order.OrderDetails EntitySet and three EntityRef associations from stored procedures when using a LinqDataSource populated by a stored procedure:

Protected Sub ldsOrders_Selecting(ByVal sender As Object, _
        ByVal e As System.Web.UI.WebControls.LinqDataSourceSelectEventArgs) _
        Handles ldsOrders.Selecting
    Dim dlOrder As New DataLoadOptions()
    dlOrder.LoadWith(Of Order)(Function(o) o.Customer)
    dlOrder.LoadWith(Of Order)(Function(o) o.Employee)
    dlOrder.LoadWith(Of Order)(Function(o) o.Shipper)
    dlOrder.LoadWith(Of Order)(Function(o) o.Order_Details)
    dcNwind.LoadOptions = dlOrder
    e.Arguments.RetrieveTotalRowCount = False
    e.Arguments.TotalRowCount = dcNwind.ExecuteQuery(Of Integer) _
        ("EXEC usp_GetOrdersCountByCountry 'USA'").First()
    e.Result = dcNwind.usp_GetOrdersByCountryPaged("USA", _
        e.Arguments.MaximumRows, e.Arguments.StartRowIndex).ToList()
End Sub

Note: The three commented EntityRef instructions throw exceptions when calling the e.Result assignment instruction. This appears to be a bug (see . However, they don't throw exceptions if added to the ldsOrders_ContextCreating event handler. These instructions don't throw exceptions when the correct override code is provided. (Updated 9/18/2007.)

Here's the correct method override code for executing the stored procedures to add to the Partial Public Class NorthwindDataContext class:

Private Function LoadOrder_Details(ByVal Ord As Order) _
        As IEnumerable(Of Order_Detail)
    Return usp_GetOrder_DetailsByOrderID(Ord.OrderID)
End Function

Private Function LoadCustomer(ByVal Ord As Order) As Customer
    Return usp_GetCustomerByID(Ord.CustomerID).FirstOrDefault()
End Function

Private Function LoadEmployee(ByVal Ord As Order) As Employee
    Return usp_GetEmployeeByID(Ord.EmployeeID).FirstOrDefault()
End Function

Private Function LoadShipper(ByVal Ord As Order) As Shipper
    Return usp_GetShipperByID(Ord.ShipVia).FirstOrDefault()
End Function

The method override code for many:1 associations hasn't been confirmed yet. I've started a new thread named Problem Populating a LinqDataSource's EntityRef Association with a Stored Proc in the Linq Project General forum. (Updated 9/18/2007.)

Remaining Issues with the LinqDataSource Not Related to Stored Procedures

1. Specifying eager loading with LoadWith expressions doesn't appear to save SQL Server 2005 round-trips when populating EntitySet and EntityRef associations of DataContexts created by LinqDataSource controls.

2. There doesn't appear to be an obvious property accessible from an event handler that exposes DataContexts created by LinqDataSource controls. This is required to enable local logging of T-SQL sent to the server if one doesn't have SQL Server Profiler handy.

(Updated 9/18/2007.)

Technorati Tags: LINQ to SQL, LinqDataSource, LINQ, ASP.NET, Persistence Ignorance, GridView, Stored Procedures, Paging, EntityRefs, VS 2008 Beta 2