Can you pre-load related tables (in ContextCreated of LinqDataSource) when Selecting calls a method based on a SQL TABLE VALUED FUNCTION? RRS feed

  • Question

  • I am trying to reduce the huge number of SQL queries generated when databinding a grid that is bound to a LinqDataSource. The grid displays a number of attributes from related tables (e.g. if the grid is based on the Orders table, Order.ProductName is shown). Currently one query is generated for every row in the results to fetch the ProductName. Very inefficient and I thought it could be eaily solved by using the technique of specifying related tables to pre-load during the ContextCreated event (see High Performance Queries Using DataLoadOptions - Avoiding Database Roundtrips ).

    However, this does not seem to have any effect in my case. It may be because my LinqDataSource's Selecting event calls a method that is based on a SQL Table Valued Function (which takes several parameters). I thought this might be OK because a Table Valued Function can (in T-SQL itself) be joined to just like a table, so if LINQ was smart it could create a single select query joining the TVF to the related tables. But it doesn't. This is a bit confusing because my method based on the TVF returns a type that I thought could be re-used in LINQ queries.

    Its not an option to have the LinqDataSource pull its data directly from a table or view. I need the TVF so I can pass various parameters in from the UI and do some complex filtering based on them. It also doesn't work to substitute a stored procedure for a TVF, because SPs return a type that can't be re-used in LINQ queries.

    Does anyone have any ideas how to make the SQL more efficient in this scenario? Here's some of the relevant code:

            Protected Sub ldsOrders_ContextCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.LinqDataSourceStatusEventArgs) Handles ldsOrders.ContextCreated
                Dim options As New DataLoadOptions()
                options.LoadWith(Of Order)(Function(o As Order) o.Product)
                options.LoadWith(Of Order)(Function(o As Order) o.Customer)
                TryCast(e.Result,  MyDataContext).LoadOptions = options
            End Sub

            Protected Sub ldsOrders_Selecting(ByVal sender As Object, ByVal e As LinqDataSourceSelectEventArgs) Handles ldsOrders.Selecting
                Dim dc As New MyDataContext
                e.Result = dc.GetOrders(PortalId, SelectedProductID, SelectedGroupName, SelectedUserID, SelectedRoleID)
            End Sub



    Friday, June 4, 2010 1:04 PM