Using GridView, Entity Framework, LINQ, and an ObjectDataSource to implement a GridView that sorts and filters.

I went in search of an elegant and flexible way to implement a GridView that supports sorting (and the ability to add custom paging if I need it later, though I won’t cover that here) and uses a Data Access Layer (DAL). The filtering I am looking for is the ability to add any number of controls on the page and have them filter the results that are shown in the GridView. Based on the values of these user controls, I want to be able to do a begins with, or a range, or choose from a list of values, etc. I don’t want to be limited to just one value.

I am a believer that like SQL, you don’t want your LINQ queries all over the place. I believe a Data Access Layer (DAL) is a good place to put all your LINQ queries.

At the present time, this means that the ObjectDataSource is probably the best choice because it can call the DAL to do the query and not embed it in the EntityDataSource or LinqDataSource.

It is possible to get pretty good filtering and little to no code to do this using the Dynamic Data Future, but even then I using the DynamicFilter I found it difficult to modify the query to do things like ranges, or a begins with search for example. If you decide to go down that road, you might also want to check out the following post on how to do this in your own project. It makes searching based on a DropDownList or an AutoComplete field very easy. I wanted more flexibility than that. You can also get much of that same functionality from VS 2008 SP1 (yes the SP1 is required to get this functionality since SP1 is essentially a feature release, not a bug fix release).

The hard part of this is writing the DAL method, but is actually much easier than it used to be now that we have LINQ. In this case, I am using LINQ to Entities to query the Entity Framework.

Here is my DAL:

public class DAL
{
private MyEntities ctx = new MyEntities();

public IQueryable GetPerson(string firstName, string lastName, bool hasChildren, int? age, string sortExpr)
{
    // set a default sort order
    if (string.IsNullOrEmpty(sortExpr))
    {
        sortExpr = "FName";
    }

    var people = from p in ctx.Person
                    select new
                    {
                        ID = p.ID,
                        FName = p.FName,
                        LName = p.LName,
                        Age = p.Age,
                        NumChildren = p.NumChildren
                    };

    if (hasChildren)
        people = people.Where(p => p.NumChildren > 0);

    if (!string.IsNullOrEmpty(firstName))
        people = people.Where(p => p.FName.StartsWith(firstName));

    if (!string.IsNullOrEmpty(lastName))
        people = people.Where(p => p.LName.StartsWith(lastName));

    if (!age.HasValue)
        people = people.Where(p => p.Age > age);

    var sortedPeople = people.OrderBy(sortExpr)
        .Select("new(ID, FName, LName, Age, NumChildren)");

    return sortedPeople;
}
}

You may notice that the .OrderBy() method gives you a compiler error or is not in your Intellisense. You need to download it from Microsoft. Click here to download. In the zip file, look for the Dynamics.cs file. You can include it in your project or you can build the project that comes with and include the assembly it builds in your project. It is one file, so I like putting it in my project as source code.

This Dynamic class works very in scenarios like this because it actually supports the same syntax as the ObjectDataSource uses which is <ColumnName> <SortDirection>. If the sort direction is Ascending, then no direction is specifed by the ObjectDataSource. For example the syntax for sorting my FName in Ascending order, the sortExpression would be “FName” or “FName Descending” if you wanted to sort in Descending order.

You may also notice that I use Lambda expressions to do the additional where statements. Be sure to use the value returned by the Where() method since the Where() call doesn’t change (or even query the database). All the Where() does is adds another condition to the existing where clause in the generated sql. Each time Where() is called, the statement is ANDed to the existing where clause. The code is very optimized. I am quite impressed with the code generation.

For related details on sorting with the ObjectDataSource, check out my other post.

Below is the aspx code. The most important thing you get right is the SelectParameters/ControlParameters. The ControlID property needs to match the ID of the Controls you are using for Filtering. The Name property needs to match the parameter names in the DAL method you specified in the ObjectDataSource SelectMethod property.

First Name starts with: <asp:TextBox ID="FirstNameFilter" runat="server"></asp:TextBox><br />
Last Name Starts with: <asp:TextBox ID="LastNameFilter" runat="server"></asp:TextBox><br />
Has Children: <asp:CheckBox ID="cbHasChildren" runat="server" /><br />
<asp:Button ID="Button1" runat="server" Text="Apply Filter" /> 

<asp:GridView ID="GridView1" runat="server" AllowSorting="True" 
AutoGenerateColumns="False" DataKeyNames="ID"  
DataSourceID="dsPeople"
    >
    <Columns>
        <asp:HyperLinkField DataNavigateUrlFormatString="PersonDetail.aspx?ID={0}" Text="View" DataNavigateUrlFields="ID" />
        
        <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="True" 
            SortExpression="ID" Visible="False"/>
        <asp:BoundField DataField="FName" HeaderText="First Name" 
            SortExpression="FName" />
        <asp:BoundField DataField="LName" HeaderText="Last Name" 
            SortExpression="LName" />
        <asp:BoundField DataField="NumChildren" HeaderText="Number of Children" 
            SortExpression="NumChildren" />
        <asp:BoundField DataField="Age" HeaderText="Age" 
            SortExpression="Age" />
    </Columns>
</asp:GridView>
    
<asp:ObjectDataSource ID="dsPeople" runat="server" 
    SelectMethod="GetPerson" 
    TypeName="MyNameSpace.DAL"
    SortParameterName="sortExpr"
    >
    <SelectParameters>
        <asp:ControlParameter ControlID="cbHasChildren" Name="hasChildren" Type="Boolean"/>
        <asp:ControlParameter ControlID="FirstNameFilter" Name="firstName" Type="String"/>
        <asp:ControlParameter ControlID="LastNameFilter" Name="lastName" Type="String"/>
    </SelectParameters>
    
</asp:ObjectDataSource>