Custom Paging Using Linq and ObjectDataSource: Caching Results
This is a follow-up post to my previous explanation of writing custom Linq extension methods.
The declaration of a custom paging ObjectDataSource looks like this:
<asp:ObjectDataSource ID="SomeDataSource" EnablePaging="true" runat="server" SelectCountMethod="GetCount" SortParameterName="sSortColumn" SelectMethod="GetData" TypeName="SomeNamespace+SomeClass" StartRowIndexParameterName="iSkip" MaximumRowsParameterName="iTake" > <SelectParameters> <asp:Parameter Name="sSomeParameter" Type="String" /> </SelectParameters> </asp:ObjectDataSource> |
In this case we need to implement a class named SomeClass which has the following public methods: GetData() and GetCount(). We make use of the non-generic IQueryable extension methods from the previous post. Note that the call of the ToArrayExtension() may be necessary to stop the defered execution built into Linq. Otherwise we could run into an exception when the Linq context is disposed (our class implements IDisposable).
public class SomeClass : IDisposable { protected SomeDataContext db = new SomeDataContext(); public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter ) { return this.GetDataInternal( sSomeParameter ).OrderBySQLSyntax( sSortColumn ).SkipExtension( iSkip ).TakeExtension( iTake ).ToArrayExtension(); } public int GetCount( string sSomeParameter ) { return this.GetDataInternal( sSomeParameter ).CountExtension(); } protected IQueryable GetDataInternal( string sSomeParameter ) { return this.db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } ); } public void Dispose() { this.db.Dispose(); } } |
The above gets more useful with increased complexity of the returned data.
There is an alternative approach, which will be based on the fact that GridView/ObjectDataSource will usually call the data retrieval method first, and then call the count method. Therefore we could actually cache the value for the count method when the select method is called. This may also have a positive impact on performance. I am not sure though, if this order of calling can be relied on. Caching cannot be done in an instance variable, since ObjectDataSource apparently creates a new instance upon every call. The place where it makes most sense is withing the HttpContext.Current.Items location inside web applications. In case you are using several ObjectDataSources on one page you have to make sure the parameter name for the cached value is unique, so there are no collisions.
Making use of caching, our query can be further simplified as:
public class SomeClass { public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter ) { using ( SomeDataContext db = new SomeDataContext() ) { var v = db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } ); HttpContext.Current.Items[ "SomeClass.GetData" ] = v.Count(); return v.OrderBySQLSyntax( sSortColumn ).Skip( iSkip ).Take( iTake ).ToArray(); } } public int GetCount( string sSomeParameter ) { object o = HttpContext.Current.Items[ "SomeClass.GetData" ]; if ( o is int ) return (int)o; throw new Exception( "Caching does not work, since GetCount() was called before GetData()!" ); } } |
The order in which a GridView calls both methods may depend on the pagers used. Therefore a safe and well performing approach could be to combine the first and the second example.
public class SomeClass : IDisposable { protected SomeDataContext db = new SomeDataContext(); public IEnumerable GetData( int iSkip, int iTake, string sSortColumn, string sSomeParameter ) { var v = this.GetDataInternal( sSomeParameter ); HttpContext.Current.Items[ "SomeClass.GetData" ] = v.CountExtension(); return v.OrderBySQLSyntax( sSortColumn ).SkipExtension( iSkip ).TakeExtension( iTake ).ToArrayExtension(); } public int GetCount( string sSomeParameter ) { object o = HttpContext.Current.Items[ "SomeClass.GetData" ]; if ( o is int ) return (int)o; return this.GetDataInternal( sSomeParameter ).CountExtension(); } protected IQueryable GetDataInternal( string sSomeParameter ) { return this.db.tblSomeTable.Where( x => x.Value3 = sSomeParameter ).Select( x => new { ID = x.ID, V1 = x.Value1, V2 = x.Value2 } ); } public void Dispose() { this.db.Dispose(); } } |