Jon's Blog

NOTE: If you set your LinqDataSource's AutoPage property to true and are using SQL Server (2005+ I believe) then it will automatically use Skip() and Take() as seen below. However, if you want to do it manually or wonder how it works then read on.

Creating a GridView (or other data control) with efficient paging is very easy with LINQ.  You can thank the Take and Skip operators for this; they allow you to only pull back the records you need.  In the simple example below we are using a LinqDataSource and handling its onselecting method to create our LINQ query and do our paging.  Set AutoPage to false since we are writing code to handle paging ourselves.  Also the PageSize property of the GridView control is being populated from a constant in the code-behind class.

ASPX:

<asp:LinqDataSource ID="linqDS" runat="server" AutoPage="false" 
    ContextTypeName="Namespace.MyDataContext" 
    onselecting="linqDS_Selecting" />
        
<asp:GridView ID="myGV" runat="server" DataSourceID="linqDS"
    AllowPaging="true" PageSize="<%# PAGE_SIZE %>"
    AutoGenerateColumns="false">
    <Columns>
        <!--Removed for simplicity -->
    </Columns>
</asp:GridView>

Code-behind:

// Const declared at top of code-behind
public const int PAGE_SIZE = 100;

protected void linqDS_Selecting(object sender, LinqDataSourceSelectEventArgs e)
{
    // LINQ query
    var query = from c in myDC.Class
                select c;

    // Do advanced query logic here (dynamically build WHERE clause, etc.)     
    
    // Set the total count     
    // so GridView knows how many pages to create    
    e.Arguments.TotalRowCount = query.Count();

    // Get only the rows we need for the page requested
    query = query.Skip(myGV.PageIndex * PAGE_SIZE).Take(PAGE_SIZE);

    e.Result = query;
}

As you probably know, you can implement caching in an ASPX page using the OutputCache directive.  However, you can't use this in an ASHX handler.  So what if you are using a handler to server binary files from the database and you want to improve peformance by using server side output caching?  You'll just need to add some of your own caching logic in your code-behind.  Here is some sample code of how you can do this.  In this very basic example we are passing the ID of our object on the query string.

// Get ID from query string
int myID = Convert.ToInt32(context.Request.QueryString["ID"]);

// Attempt to get item from cache if it exists
string myCacheKey = "MyKey" + myID; // Unique Identifier
MyObject myObject = context.Cache[myCacheKey] as MyObject;

if (myObject == null)
{
    // Item isn't cached; Grab business object from the database
    myObject = MyObject.GetMyObject(myID);

    // Add object to Cache using the cache key
    // In this example we are caching for 30 minutes
    context.Cache.Insert(myCacheKey, myObject, 
        null, DateTime.UtcNow.AddMinutes(30), 
        Cache.NoSlidingExpiration);
}

// Code to generate file from binary data here

Today I had a problem hitting a breakpoint in an .ASHX file I created.  I noticed the project was using the built-in Visual Studio Development Server.  Once I switched over to using IIS I had no problem hitting the breakpoint.  I thought it was very strange, but hopefully this might help someone else out if they manage to find this post via Google.