Undoubtedly, anyone who has evaluated LINQ to SQL has fond it a fairly powerful yet lightweight ORM technology which is less complex than the ADO Entity Framework yet utilizes the strength and power of Language Integrated Queries.
One problem with LINQ to SQL is the auto paging feature of the LinqDataSource. Below is a rough GridView which displays three columns, UserName, FirstName and LastName. This is just a rough demo, so we’re looking at paging.
If you simply drop a GridView and a LinqDataSource control onto a Web Form and configure the LinqDataSource (using Smart Tags) without specifying a Group By field or Order By field (Figure 1) then you will get fairly optimal database querying (Figure 2) although without any ordering.
Figure 1: Configure Linq Data Source
Figure 2: SQL Trace of a page load
While it is great for lightweight or simple applications, it’s rather unacceptable for use in anything serious (especially with more complex queries).
Should you supply a Group By/Order By field, the LinqDataSource control will query for a RowCount before executing a single query for each row in the range, i.e. if Page Count is 10, it will execute 10 queries after the initial row query (Figure 3).
Figure 3: SQL Trace when using Group By
This occurs when you choose to use a group by/order by sort – The select clause of the LinqDataSource becomes:
GroupBy="UserName" OrderGroupsBy="key" Select="new (key as UserName, it as Users)"
Obviously we’d like decent performance and the ability to sort/group our data. So there is a pretty low-overhead solution. The first (obvious) step is to disable the LinqDataSource AutoPage property. The next is to implement an event for LinqDataSource’s OnSelecting event, as so: (note: I’ve included the GridView for reference)
<form id="form1" runat="server">
<asp:GridView ID="UserGridView" runat="server" AllowPaging="True"
DataSourceID="UserLinqDataSource" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="UserName" HeaderText="UserName" ReadOnly="True"
SortExpression="UserName" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
</Columns>
</asp:GridView><asp:LinqDataSource ID="UserLinqDataSource" runat="server" AutoPage="False"
ContextTypeName="DataAccess.DataClassesDataContext"
OnSelecting="UserLinqDataSourceSelecting"
Select="new (UserName, FirstName, LastName)" TableName="Users">
</asp:LinqDataSource></form>
Now for our code behind, we only need to implement the Selecting event, and we have our smarter paging enabled.
/// <summary>
/// Implements Server Side Paging for the LinqDataSource
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void UserLinqDataSourceSelecting(object sender, LinqDataSourceSelectEventArgs e)
{
/*
When AutoPage is false, LinqDataSource requires that the user handle the paging
manually during the Selecting event. In this case, you need to set
DataSourceSelectArguments.TotalRowCount yourself and perform the paging manually
using DataSourceSelectArguments.StartRowIndex and
DataSourceSelectArguments.MaximumRows (pageSize).
*/
e.Arguments.StartRowIndex = 0;
e.Arguments.MaximumRows = 10; //add your paging limit requirement here
DataClassesDataContext dc = new DataClassesDataContext();
e.Arguments.TotalRowCount = dc.Users.Count(); //you could store this value or cache
//it to avoid the extra DB hit
//uses an example of ten records/page modify to fit your own paging
//requirements
e.Result = (from i in dc.Users select i).Skip(UserGridView.PageIndex * 10).Take(10);
//uses Linq’s Skip() and Take() functions to select a sub section
}
Let’s take a look at the SQL profile trace, just to be sure:
Figure 4: New SQL Trace with custom paging
So this is just a simple little scenario. It’s nothing big. It might help some people out there who are looking to use LinqDataSource but don’t like the overhead of the Auto Paging. You’d probably also need to take a look at supporting sorting (which would need to be factored in to the custom paging query).
This entry doesn’t discuss management of Data Contexts or a plethora of other considerations which you should take into consideration before implementing a solution. Please plan ahead accordingly before choosing an approach.
If you’re serious about using Linq to SQL I’d highly recommend you take a look at Patterns and Practices ‘ObjectContainerDataSource’ instead. It allows you to wire up your data access to queries or providers and is far more robust. There’s a good chance I might write a separate blog entry about the ObjectContainerDataSource.
More reading:
An alternative approach to custom paging
[ http://www.mikesdotnetting.com/Article.aspx?ArticleID=71 ]
Web Client Software Factory (Contains ObjectContainerDataSource)
[ http://msdn.microsoft.com/en-us/library/bb264518.aspx ]
Web Client Software Factory Source Code – February 2008
[ http://www.microsoft.com/downloads/details.aspx?FamilyId=8AF8F61D-558F-481F-BC83-E42D9B04C3E9&displaylang=en ]
How to use the ObjectContainerDataSource
[ http://msdn.microsoft.com/en-us/library/cc304832.aspx ]
