LINQ Exchange

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.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.