Need a simple CRM and Project Management system?
Check out JobNimbus - CRM for Contractors and Service Professionals.

GridView ObjectDataSource LINQ Paging and Sorting

If you've attempted to create your own Data Access Layer for LINQ and want to use the built-in data binding for .NET controls like a GridView, you know the nightmare of trying to get these controls to work together with LINQ new IQueryable interface. After much pain and suffering, I was able to cobble together little tidbits from various posts to create a solid object that can be used as a "codeless" ObjectDataSource for any GridView AND support sorting, paging, and filtering just like if you were to databind to a LINQ to SQL object (like EVERY tutorial shows you how to do).

The biggest help by far in getting to this point was a very complete post from Brian Orrell that you can read here:



http://borrell.parivedasolutions.com/2008/01/objectdatasource-linq-paging-sorting.html

First, create a new Database Context (.dbml) object using the fabulous LINQ O/R mapper tool built into Visual Studio 2008 (you can see how to do that here:

http://weblogs.asp.net/scottgu/archive/2007/05/19/using-linq-to-sql-part-1.aspx

Once you've created this, make a new class in your project and call it something like "ClassNameLinq.cs" or "ClassName.cs".

For convenience, I added a private property of my context object to use across my object called "DbContext". The declaration looks like this:

private Dal.DbContextDataContext m_DbContext = new Dal.DbContextDataContext();
 
public Dal.DbContextDataContext DbContext
{
    get { return m_DbContext; }
    set { m_DbContext = value; }
}

 

NOTE: I am using a simple database with a table called "Projects" that has a column called "NAME" (varchar 50) and a column called "PRIMARYKEY" (int).

Now implement the following methods:

You can't use an IQueryable object to data bind to a GridView and still use Paging and Sorting. You will get this really annoying exception: The data source does not support server-side data paging. So we create a method in our class that returns back the query as a List<> using LINQ ToList() method.

public List<Project> SelectAllList(string sSortType, int iBeginRowIndex, int iMaximumRows)
{
    var query = from q in DbContext.Projects
                       select q;
 
    // sort 
    query = SelectAllSort(query, sSortType);
 
    // filter the list if needed
    query = SelectAllQuery(query);
 
    // paginate
    query = query.Skip(iBeginRowIndex).Take(iMaximumRows);
 
    // execute the query and convert to list
    return query.ToList();
}

 

Next add a method for getting the query as an IQueryable object to do less typing and provide a way for other consumers of our class to use the LINQ syntax directly on the object (when not using a databound object situation).

private IQueryable<Project> SelectAllQuery(IQueryable<Project> query)
{
    // add filtering here if needed
 
    return query;
}

 

Now we need to implement the methods for sorting, pagination, and optionally filtering.

private IQueryable<Project> SelectAllSort(IQueryable<Project> query, string sSortType)
{
    bool bIsSortDescending = false;
    if (!String.IsNullOrEmpty(sSortType))
    {
        string[] sValues = sSortType.Split(' ');
        if (sValues.Length > 1)
        {
            if (sValues[1].ToUpper() == "DESC")
            {
                bIsSortDescending = true;
            }
        }
    }
 
    if (!String.IsNullOrEmpty(sSortType))
    {
        query = DbContext.Projects.OrderBy(sSortType);
    }
    else
    {
        // use a default sort here
        if (bIsSortDescending)
        {
            query = query.OrderByDescending(q => q.NAME);
        }
        else
        {
            query = query.OrderBy(q => q.NAME);
        }
    }
 
    return query;
}

 

One thing to note here is that you could manually create a specific sort query for each colum with desc/asc sorts but I chose to use a Dynamic LINQ query here instead because it was a LOT less coding and same speed in terms of performance BUT if you want to use this code as is, you need to download the free System.Linq.Dynamic library that MS distributes. You can get this library here:

http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx

Now create a method to get the count of the objects in your query (we need this so that pagination will work correctly).

public int SelectCount()
{
    var query = from q in DbContext.Projects
                       select q;
 
    query = SelectAllQuery(query);
 
    // execute the query and return the count
    return query.Count();
}

 

Now make a web form "GridTest.aspx" and drag a GridView control onto it. Use the wizard to create a new ObjectDataSource and point it to this new class we created for your DAL layer. For the "Select" method, point to the SelectAllList() method. Then click Finish (NOTE: there is more to configure but the wizard break's down at this point and doesn't let us configure things properly. We need to go to the HTML code).

Your ObjectDataSource1 should look like this:

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
    EnablePaging="true" 
    SelectCountMethod="SelectCount"
    SelectMethod="SelectAllList"
    SortParameterName="sSortType"
    MaximumRowsParameterName="iMaximumRows"
    StartRowIndexParameterName="iBeginRowIndex"
    TypeName="Dal.ProjectController">
</asp:ObjectDataSource>

 

Things to note here:

- EnablePaging = true (the source will handle the paging for us)

- SelectCountMethod = SelectAllCount (point this to the method that you created to get the count of the query)

- SelectMethod = SelectAllList (point this to our method in the new class that returns the subset of the query)

SortParameterName = sSortType (this is the name of the parameter for our SelectAllList() method)

MaximumRowsParameterName = iMaximumRows (this is the name of the parameter for our SelectAllList() method)

StartRowIndexParameterName = iBeginRowIndex (this is the name of the parameter for our SelectAllList() method)

Now configure your GridView manually to look like this:

<asp:GridView ID="GridView1" runat="server" 
    AllowPaging="True" 
    AllowSorting="True"
    EnableSortingAndPagingCallbacks="True"
    AutoGenerateColumns="False" 
    DataSourceID="ObjectDataSource1" 
    EmptyDataText="No rows to display.">
    <Columns>
        <asp:BoundField DataField="PRIMARYKEY" HeaderText="PRIMARYKEY" 
            SortExpression="PRIMARYKEY" />
        <asp:BoundField DataField="NAME" HeaderText="NAME" 
            SortExpression="NAME" />
    </Columns>
</asp:GridView>

 

Notice a couple of things:

- EnableSortingAndPagingCallbacks = true (so we can catch the postback events to do the sorting [which is handled by the ObjectDataSource1]).

- AllowPaging = true (the wizard blocks us from doing this)

- AllowSorting = true (the wizard blocks us from doing this)

Now run the web form and you have automatic pagination, sorting, and filter built in with literally no code written in the code-behind for this page. AND this object can be reused over and over. You could do this for each of your tables and joins, enjoy all the benefits of LINQ, etc.

One other note. Performance is better with this implementation than other LINQ Data Access Layer's I've seen on the next because pagination here uses the Take() method so we only ever return a subset of the total rows so this can handle VERY large tables.

display two tables data in a single Gridview And also perform

display two tables data in a single Gridview And also perform Update delete operations,
any methos plz help me the complete simple example

Bonjour, j aimerais savoir

Petite info pour ceux qui ne l’auraient pas vu, je vous recommande le dernier hors-série du journal Le Monde. Chaque année il publie un atlas thématique, et celui de 2012 n’est pas sans lien avec notre Mouvement : L’atlas des utopies.

Great Solution indeed.

You can imagine in I discovered this in 2012. and it is still relevant.
It worked like Magic. Thanks

Life saving solution, thank you.

Thank you for this paging solution. It's something I was stuck on for a wee while. A question I have now however is with the TypeName="dal.ProjectConroller". Is dal.ProjectController the name of your database/table so I should enter: TypeName="MyDataBase" or The table I am working with so: TypeName="db.MyTable".(where db is object from Database data context)

TIA
Jason

trying to find a way to pass 'field' DESC to parameter sSortType

I am trying to find a way to pass 'field' DESC to the parameter sSortType below:

public List SelectAllList(string sSortType, int iBeginRowIndex, int iMaximumRows)

Any ideas?

WMopjnafFwelrj

Thank you so much Leda,I to have read that hydro therapy was bfiieicnal for such conditions, but I forgot all about it. Thank you for reminding me and for the great article. I also have read that magnesium is beneficial to take internally but never thought until now, that it could benefit the body from being concentrated in water like this.I'm going to look to see if there is a place here in Toronto where I can go. I hope there is.

it doesnt work with TemplateFields. Does it?

I am getting : Callbacks are not supported on TemplateField because some controls cannot update properly in a callback. Turn callbacks off on 'GridView1'.

Answer??? maybe

I changed this to false and now it works but not sure if i am breaking anything else:
EnableSortingAndPagingCallbacks = "true"

Cant execute the sample using my own table

I get the error below:

Method: SelectAllSort()
Line: query = UdsDataContext.JobHistories.OrderBy(sSortType);
Code:
if (!String.IsNullOrEmpty(sSortType))
{
query = UdsDataContext.JobHistories.OrderBy(sSortType);
}

Error 1 The type arguments for method 'System.Linq.Queryable.OrderBy(System.Linq.IQueryable, System.Linq.Expressions.Expression>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Why is that?

Thank you

Place in project where your model is

Place the Microsoft Dynamic LINQ class in the same project as where you have your LINQ to SQL model. Just put the class at the root of your project and then build. The class you download from Microsoft already has the correct name spacing so once you have it, you can add a using statement for the class where you are attempting to do your LINQ query code.

Download MS Linq library

Per the instructions above, you need to download the dynamic linq class from Microsoft and put it in your project to remove this error:

http://msdn2.microsoft.com/en-us/vcsharp/bb894665.aspx

added dynamic class still does not work

Error 1 The type arguments for method 'System.Linq.Queryable.OrderBy(System.Linq.IQueryable, System.Linq.Expressions.Expression>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Where???

Where???

Gridview

please tell me how we can explicitly limit the number of pages in Gridview without using page selection.

error on a.NAME lines

The error at a.NAME is "Late binding operations cannot be converted to an expression tree"

How do I fix that? Really appreciate your help.

-K

SelectAllSort

I don't get the sense of the SelectAllSort-Method, at least not the way it is.

First you check if the sSortType is empty. If not you split it to figure out ascending or descending sorting. So far, so good.

But then you check if the sSortType is empty again. If not you pass it straight to the DBContext, otherwise you use the default sorting.

In my opinion the first "If" does not do anything senseful, because if sSortType is empty, bIsSortDescending will remain "false" anyway.

You are correct

So, replacing this code :
bool bIsSortDescending = false;

if (!String.IsNullOrEmpty(sSortType))

{

string[] sValues = sSortType.Split(' ');

if (sValues.Length > 1)

{

if (sValues[1].ToUpper() == "DESC")

{

bIsSortDescending = true;

}

}

}

if (!String.IsNullOrEmpty(sSortType))

{

query = DbContext.Projects.OrderBy(sSortType);

}

else

{

// use a default sort here

if (bIsSortDescending)

{

query = query.OrderByDescending(q => q.NAME);

}

else

{

query = query.OrderBy(q => q.NAME);

}

}

WITH THIS :
if (!String.IsNullOrEmpty(sSortType))

{

query = DbContext.Projects.OrderBy(sSortType);

}

Will resolve the problem

Error in query.ToList()

When I try to execute the method SelectAllList(...), in the line return query.ToList(); I'm receiving the next error :
" The query has been canceled because the estimated cost of this query (1660) exceeds the configured threshold of 1500. Contact the system administrator."

How is the problem, my query is select top 251 c1, c2... from table1
Note that de top is only 251 rows, apreciate it your help.

Never seen this issue

I have never seen this issue but a quick Google search seems like this issue has to do with the setting in your SQL Server:

SET QUERY_GOVERNOR_COST_LIMIT

Try looking at that possibly.

Another technique for getting around this issue

In my case I was working with an API that returned an List of some object. I had a predictably small data set and simply wanted to use Linq to objects to implement sorting and filtering against the ArrayList.

I had done this in the past by using the Anonymous methods and it worked fine in this scenario (note you would not want to do this with large data sets).

After running the Linq sort operation I called the toList and used the List as my data source rather then the IQueryable.

please help me, i am beginner , i have an error about this code

if (!String.IsNullOrEmpty(sSortType))
{
query = DbContext.Projects.OrderBy(sSortType);
}

"query = DbContext.Projects.OrderBy(sSortType);" is error with me
error is "Error 1 The type arguments for method 'System.Linq.Queryable.OrderBy(System.Linq.IQueryable, System.Linq.Expressions.Expression>)' cannot be inferred from the usage. Try specifying the type arguments explicitly. "

how to fix it ? please help me :D

Fix

This is solved by making sure you have downloaded the latest Dynamic Linq library from Microsoft. Look at my article again. It explains that you must have this library included in your project or you will get this error.

Nice example

Really simple and concise example.
Thanks!

Thanks a lot for the example for

Thanks a lot for the example for sorting a List! Julia.

See the way LINQ works (and likes to

See the way LINQ works (and likes to work) is in a completely connected environment where the LINQ data object maintains the connection and information about the connections to the database. This code example here shows you how to abstract that out and make it more stateless.

I have many web methods using this code as a template and they work as expected. This code should work for what you want with a web service as well as ASPX page.

Thanks A lot but...can you tell me any

Thanks A lot but...can you tell me any problems or fixes needed when using a webmethod exposing the paging sorting functionality

Thanks so much for this excellent

Thanks so much for this excellent how-to. You saved me a lot of time and effort! :-)