Monday, November 15, 2010

LinqToSql. ASP.NET GridView with paging and sorting.

ObjectDataSource - DataSource class - LinqToSql - Dynamiq LINQ - GridView: almost painless grid with sorting and paging out of the box (first time there's always much work with configuration).

Let's have a look at the class consumed by ObjectDataSource control:

public class ListProductsDataSource
{
...
// The core method
public IQueryable<OrderProductsProjection> Select(string orderBy, IQueryable products, int startIndex, int rowsNumber)
{
LastCount = products.Count(); // save count for further use

if (!string.IsNullOrEmpty(orderBy))
products = products.OrderBy(orderBy).ThenBy(p => p.GUID).TakeIndex(startIndex, rowsNumber);
else
products = products.OrderBy(p => p.Site).ThenBy(p => p.GUID).TakeIndex(startIndex, rowsNumber);

// return projections - only fields needed and preprocessed
return ProjectProducts(products);
}

...

// Different methods called by ObjectDataSource for cases with different parameters
// e.g. when showing all reciords in the grid, when filtering by a string from text box
// or when using some complex conditions
public IQueryable<ListProductsProjection> SelectAll(int startIndex, int rowsNumber, string orderBy)
{
var products = ProductFacade.GetProducts();

return Select(orderBy, products, startIndex, rowsNumber);
}

public IQueryable<ListProductsProjection> SelectAll(int startIndex, int rowsNumber, SearchFields searchFields, string orderBy)
{
var products = ProductFacade.GetProducts(searchFields);

return Select(orderBy, products, startIndex, rowsNumber);
}

public IQueryable<ListProductsProjection> SelectAll(int startIndex, int rowsNumber, string searchString, string orderBy)
{
var products = ProductFacade.GetProducts(searchString);

return Select(orderBy, products, startIndex, rowsNumber);
}

...

// Return count for the ObjectDataSource control
public int GetCount()
{
return LastCount;
}

public int GetCount(SearchFields searchFields)
{
return LastCount;
}

public int GetCount(string searchString)
{
return LastCount;
}
}

Simply put OdjectDataSource control at the page and init it with data source class name, select method name, parameters if needed etc. Set SortExpression's for columns in GridView control and enable paging. Assign it's DataSourceID and all is done. As practice showed the approach is reusable, extensible and worked fine for quite big ASP.NET application with about 50 pages with different grids (sometimes absolutely different content, sometimes not). But there we had everything a bit complicated and tuned for our needs (Interfaces and abstract classes for data source objectm IoC container etc. - and that's a different question).

Points to highlight

1. Ordering
Ordering is implemented via Dynamic LINQ. Dynamic LINQ (http://msdn.microsoft.com/en-us/vcsharp/bb894665.aspx) is a cool lib that allows creating IQueryable expressions from directly strings. The case it's very convenient - sorting by column name against LinqToSql query. That makes possible passing orderBy param directly to IQueryable.OrderBy() method.

2. Paging

Simple extension method to IQueryable interface and it also works great with LinqToSql:

public static class LinqExtensions
{
public static IQueryable<T> TakePage<T<(this IQueryable<T> obj, int pageNum, int pageSize)
{
return obj.Skip(pageSize * --pageNum).Take(pageSize);
}

public static IQueryable<T> TakeIndex<T>(this IQueryable<T> obj, int startIndex, int rowsNumber)
{
return obj.Skip(startIndex).Take(rowsNumber);
}
}

NOTE 1
Due to a bug in LinqToSql shipped with .NET 3.5 you may get a mess in the records you get for the first and for the second page if no default ordering is specified. So ALWAYS USE DEFAULT ORDERING WHEN WORKING WITH LINQ2SQL if different ordering is not specified.

NOTE 2
The problem I faced when worked with Dynamiq LINQ is that if the field name passed to OrderBy method is the name of some data type (e.g. GUID) - Dynamic LINQ fails

NOTE 3
In order to be capable of adding additional ordering fields when working with the dynamic version of OrderBy method, change this method this way:

public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> source, string ordering, params object[] values)
{
return (IOrderedQueryable<T>)OrderBy((IQueryable)source, ordering, values);
}