Click here to Skip to main content
Click here to Skip to main content

Tagged as

Building WPF Applications with Self-Tracking Entity Generator and Visual Studio 2012 - Sorting, Paging, and Filtering

, 19 Mar 2013
Rate this:
Please Sign up or sign in to vote.
This article describes how to do sorting, paging, and filtering with Self-Tracking Entity Generator and Visual Studio 2012.
  • Download source code from here
  • Please visit this project site for the latest releases and source code.

Contents

Introduction

The Self-Tracking Entity Generator for WPF/Silverlight can optionally generate ClientQuery and ClientFilter<T> class implementations that provide the capability to dynamically build type-safe queries for sorting, paging, and filtering. These queries can then be used as part of WCF Service calls and merged as part of LINQ to Entities queries. In this article, we will discuss the methods and properties of these two classes, and we will also see how to use these two classes with examples from our demo application. But, before we dig a little deeper, let us first begin with a simple example.

A First Example

Inside our demo application, let's say we want to find out all the courses matching the following conditions:

  • Filtering condition: course title should begin with "CS" and course enrollment number should be greater than 20
  • Sorting condition: all matching courses are first sorted by their instructor name in ascending order and then sorted by their enrollment number in descending order
  • Paging condition: only display the first page with a page size of five

To create such a search condition, we need to build a ClientQuery object as follows:

var clientQuery = ClientQuerySet.Courses
    .Where(n => n.Title.StartsWith("CS") && n.Enrollments.Count > 20)
    .OrderBy(n => n.Instructor.Name).ThenByDescending(n => n.Enrollments.Count)
    .Skip((CurrentPage - 1) * PageSize).Take(PageSize)
    .AsClientQuery();

The statement above begins with the static property Courses of the static class ClientQuerySet. The Courses property returns a new instance of ClientQuery<Course> where we can begin chaining our search methods. The static class ClientQuerySet is auto-generated, and it contains static properties for every entity defined by the Entity Data Model file SchoolModel.edmx.

The first chained method is Where(), and its only parameter is a C# predicate n => n.Title.StartsWith("CS") && n.Enrollments.Count > 20, which is exactly our first filtering condition: course title should begin with "CS" and course enrollment number should be greater than 20.

The next two chained methods are OrderBy() and ThenByDescending(). These two methods specify that all matching courses are first sorted by their instructor name in ascending order and then sorted by their enrollment number in descending order.

The fourth and fifth chained methods, Skip() and Take(), give us the capability to set up a paging condition. And, the last method, AsClientQuery(), combines all previously specified conditions and returns back a ClientQuery object. This object is then passed in as a parameter to the following asynchronous method GetCoursesAsync().

_schoolModel.GetCoursesAsync(clientQuery, "CoursePage");

The GetCoursesAsync() method eventually calls our WCF service, and reaches the server side method GetCourses() defined as follows:

public List<Course> GetCourses(ClientQuery clientQuery)
{
    using (var context = new SchoolEntities())
    {
        return context.Courses.ApplyClientQuery(clientQuery).ToList();
    }
}

On the server side, the only method of interest is ApplyClientQuery(). This method takes the deserialized Expression tree inside a ClientQuery object and applies the search condition we specified above against the ObjectQuery<Course> object. The generated SQL statement by Entity Framework is captured below:

We can clearly see that the SQL query first filters the courses with course title beginning with "CS" and course enrollment number greater than 20. It then sorts all matching courses by their instructor name in ascending order and their enrollment number in descending order. Since we only need to display the first page with a page size of five, the SQL SELECT statement returns the top five rows.

So, this first example shows that the search condition we specified on the client-side is being transferred over the wire, applied against a LINQ to Entities query, and finally, it is being transformed into a SQL query and runs against the database.

If this first example looks interesting to you, let's move on to go over the details of the class ClientQuery next.

The ClientQuery Class

The ClientQuery class is created for only one purpose: it stores a list of include paths and a serialized expression tree, it can be passed in as a parameter to a WCF Service call and merged as part of a LINQ to Entities query on the server side. In order to create a ClientQuery object with specified search conditions, we need the help of two other classes, ClientQuerySet and ClientQuery<T>.

The static ClientQuerySet class

The static class ClientQuerySet contains properties for every entity defined by the Entity Data Model file SchoolModel.edmx. This class is largely a convenience class, and is always used as the starting point for building our search conditions. Each property of the class returns a new instance of class ClientQuery<T> as the following code snippet shows:

public static class ClientQuerySet
{
    ......

    public static ClientQuery<Course> Courses
    {
        get { return new ClientQuery<Course>(); }
    }

    ......
}

The ClientQuery<T> Class and IClientQuery<T> Interface

The other helper class, ClientQuery<T>, implements interface IClientQuery<T> and this interface consists of the following members:

  • Include() specifies the related objects to include in the client query results. Query paths are all-inclusive and can be called multiple times on a ClientQuery<T> to specify multiple paths for the query.
  • Where() filters the client query results based on a predicate.
  • OrderBy() sorts the client query results in ascending order according to a key.
  • OrderByDescending() sorts the client query results in descending order according to a key.
  • ThenBy() performs a subsequent ordering of the client query results in ascending order.
  • ThenByDescending() performs a subsequent ordering of the client query results in descending order.
  • Skip() bypasses a specified number of elements in the client query results and then returns the remaining elements.
  • Take() returns a specified number of contiguous elements from the start of the client query results.
  • ApplyClientFilter() merges the search condition of a ClientFilter<T> into the calling ClientQuery<T>.
  • AsClientQuery() returns the search condition of the ClientQuery<T> as a ClientQuery.

Method Include() has two overload forms. For example, if we want to include all the courses taught by an instructor, we can either use ClientQuerySet.Instructors.Include("Courses") or ClientQuerySet.Instructors.Include(n => n.Courses). Since the latter enables type checking during compile time, it is always a preferred choice.

The next seven methods basically give us the capability to build filtering, sorting, and paging conditions. Method ApplyClientFilter() merges the search condition of a ClientFilter<T> into the calling ClientQuery<T> object, and we will discuss the ClientFilter<T> class in the next section. The last method AsClientQuery() is always used as the final chained method. It combines all previously specified conditions and returns back a ClientQuery object.

ObjectQuery<T> Extension Method

On the server side, there is an auto-generated static class ObjectQueryExtension that contains the method ApplyClientQuery() we discussed in the first example. This class is generated inside SchoolModel.Context.cs of our demo application and consists of the following two extension methods:

  • ApplyIncludePath() takes the list of include paths from a ClientQuery object and calls Include() on the ObjectQuery<T>.
  • ApplyClientQuery() takes both the list of include paths and deserialized Expression tree from a ClientQuery object, calls Include() and applies search conditions on the ObjectQuery<T>.

These two methods both take a single parameter of ClientQuery. The former calls Include() for the list of include paths only, while the latter applies both the list of include paths and search conditions.

Now we have covered the specifications of class ClientQuery and all its related helper classes, we are ready to look into how these classes and methods get used inside our demo application.

Client Side Example - CoursePageViewModel

The CoursePageViewModel class hosts most of the logic for querying the Course entity, and it is the place where we create a ClientQuery instance. This is usually a two-step process. First, we need to initialize a ClientQuery<Course> instance with the common settings. Since the related objects to include for Course entity is always the same inside this ViewModel class, we create a CourseClientQuery object that includes "Enrollments.Student" as follows:

private static readonly ClientQuery<Course> CourseClientQuery =
    ClientQuerySet.Courses.Include(n => n.Enrollments.Include(m => m.Student));

Next, inside the Constructor, we can simply create an instance of ClientQuery by first calling ApplyClientFilter() against the CourseClientQuery and then followed by calling Skip() and Take() to specify a paging condition.

// set current page
CurrentPage = 1;

// set current filter to order by CourseId
_currentFilter = new ClientFilter<Course>().OrderBy(n => n.CourseId);

// get courses
var courseQuery = CourseClientQuery
    .ApplyClientFilter(_currentFilter)
    .Skip((CurrentPage - 1)*PageSize).Take(PageSize)
    .AsClientQuery();
_schoolModel.GetCoursesAsync(courseQuery, "CoursePage");

From the code snippets above, we can see that using ClientQuery inside a ViewModel class is pretty simple and straight forward. In fact, most of the logic dealing with dynamically building filtering and sorting conditions is done through ClientFilter<T> class outside of this ViewModel class.

Server Side Example - SchoolService

On the server side, we call ApplyClientQuery() method to apply both the list of include paths and search conditions against a LINQ to Entities query as we have done in the first example.

public List<Course> GetCourses(ClientQuery clientQuery)
{
    using (var context = new SchoolEntities())
    {
        if (clientQuery.IncludeList.Count == 0)
        {
            return context.Courses.ApplyClientQuery(clientQuery).ToList();
        }
        var courseList = new List<Course>();
        foreach (var course in context.Courses.ApplyClientQuery(clientQuery).ToList())
        {
            var currentCourse = course;
            using (var innerContext = new SchoolEntities())
            {
                courseList.Add(
                    innerContext.Courses
                                .ApplyIncludePath(clientQuery)
                                .Single(n => n.CourseId == currentCourse.CourseId));
            }
        }
        return courseList;
    }
}

What have not been covered is that we can further specify server-side default filtering conditions along with the list of include paths and search conditions passed from the client side. To achieve that, we can use Entity SQL as the following code example shows:

public List<Course> GetCourses(ClientQuery clientQuery)
{
    using (var context = new SchoolEntities())
    {
        var query = context.Courses
                           .Where("it.StartDate >= DATETIME'2012-01-01 00:00'")
                           .ApplyClientQuery(clientQuery);

        if (clientQuery.IncludeList.Count == 0)
        {
            return query.ToList();
        }
        var courseList = new List<Course>();
        foreach (var course in query.ToList())
        {
            var currentCourse = course;
            using (var innerContext = new SchoolEntities())
            {
                courseList.Add(
                    innerContext.Courses
                                .ApplyIncludePath(clientQuery)
                                .Single(n => n.CourseId == currentCourse.CourseId));
            }
        }
        return courseList;
    }
}

Before calling ApplyClientQuery(), there is a call to Where("it.StartDate >= DATETIME'2012-01-01 00:00'"), which essentially limits all the course results with a start date after the beginning of 2012.

Moreover, if we prefer to use LINQ predicate instead of Entity SQL, we can choose a different approach listed in the next example. This time, we call Where(n => n.StartDate >= new DateTime(2012, 01, 01)) after ApplyClientQuery(), and the generated SQL statements are functionally the same for these two cases.

public List<Course> GetCourses(ClientQuery clientQuery)
{
    using (var context = new SchoolEntities())
    {
        var query = context.Courses
                           .ApplyClientQuery(clientQuery)
                           .Where(n => n.StartDate >= new DateTime(2012, 01, 01));

        if (clientQuery.IncludeList.Count == 0)
        {
            return query.ToList();
        }
        var courseList = new List<Course>();
        foreach (var course in query.ToList())
        {
            var currentCourse = course;
            using (var innerContext = new SchoolEntities())
            {
                courseList.Add(
                    innerContext.Courses
                                .ApplyIncludePath(clientQuery)
                                .Single(n => n.CourseId == currentCourse.CourseId));
            }
        }
        return courseList;
    }
}

So far, we have covered all the features of class ClientQuery, our next topic is about class ClientFilter<T>.

The ClientFilter<T> Class

The ClientFilter<T> class is also created with only one purpose in mind: it provides the capability to dynamically build type-safe queries for sorting, paging, and filtering, which can then be merged as part of a ClientQuery instance. Additionally, it also has the capability to be persisted in a string format for later reuse. Following is a list of the class methods and properties:

  • And() returns a ClientFilter<T> that represents a conditional AND operation between the calling ClientFilter<T> and the where predicate parameter.
  • Or() returns a ClientFilter<T> that represents a conditional OR operation between the calling ClientFilter<T> and the where predicate parameter.
  • OrderBy() sorts the elements of a sequence in ascending order according to a key.
  • OrderByDescending() sorts the elements of a sequence in descending order according to a key.
  • Skip() bypasses a specified number of elements in a sequence and then returns the remaining elements.
  • Take() returns a specified number of contiguous elements from the start of a sequence.
  • ToString() returns the current ClientFilter<T> as a string.
  • Parse() converts the specified string representation of a ClientFilter<T> value to its ClientFilter<T> equivalent.
  • WhereExpression is read only, and returns the dynamically built where predicate.
  • SortExpressions is read only, and returns the dynamically built sort collection.
  • SkipCount is read only, and keeps the number of elements to skip before returning the remaining elements.
  • TakeCount is read only, and keeps the number of elements to return.

Dynamically Building Filtering and Sorting Conditions

Now let's go through some examples to see how to build type-safe queries dynamically. The first example is to query for all the courses with a start date after the beginning of 2012 and an end date at the end of year 2012. The results are sorted by course title in ascending order and then sorted by course start date in descending order.

// n => n.StartDate >= new DateTime(2012, 1, 1) && 
//      n.EndDate <= new DateTime(2012, 12, 31)

var clientFilter = new ClientFilter<Course>(n => n.StartDate >= new DateTime(2012, 1, 1));
clientFilter = clientFilter.And(n => n.EndDate <= new DateTime(2012, 12, 31));

clientFilter = clientFilter.OrderBy(n => n.Title) // Ascending
clientFilter = clientFilter.OrderByDescending(n => n.StartDate); // Descending

The source code above shows that we first create a new ClientFilter<Course> with a predicate of n => n.StartDate >= new DateTime(2012, 1, 1). Then, we call the method And() on the new ClientFilter<Course> with another predicate n => n.EndDate <= new DateTime(2012, 12, 31). These two steps create the filtering condition we just specified. Then, the next two lines of code add the ordering conditions one step at a time, which is sort by course title in ascending order and then sort by course start date in descending order. Please note that there is no method like ThenBy() or ThenByDescending() in the class ClientFilter<Course>. This makes building the sort orders dynamically a bit easier.

Our second example is a little more complicated, and we need to build a query for all the courses with title starting with "CS", enrollment total is either below 10 or above 20, and with a course start date from the beginning of year 2012. The question here is how do we deal with the parenthesis around the second condition.

// n => n.Title.StartsWith("CS") &&
//      (n.Enrollments.Count < 10 || n.Enrollments.Count > 20) &&
//      n.StartDate >= new DateTime(2012, 1, 1)

var inner = new ClientFilter<Course>(n => n.Enrollments.Count < 10);
inner = inner.Or(n => n.Enrollments.Count > 20);

var outer = new ClientFilter<Course>(n => n.Title.StartsWith("CS"));
outer = outer.And(inner.WhereExpression);
outer = outer.And(n => n.StartDate >= new DateTime(2012, 1, 1));

As the first two lines of the source code shown above, we need to build the parenthesized OR expression first. After that, we can combine the inner filtering condition with the outer condition using an And() method call: outer = outer.And(inner.WhereExpression).

Persisting ClientFilter<T> object To Database

The ClientFilter<T> class also has the capability to be persisted to and loaded back from a string format. This could be quite useful for applications where users need to perform some common search tasks. Instead of repeating these tasks again and again on a daily basis, we can create the search condition using a ClientFilter<T> instance, convert it to string format, and save to the database so that users can reuse it again later. To achieve that, we need the following two methods: ToString() and Parse().

The next code snippet is from the CoursePageViewModel class of our demo application, and the method OnCourseDefaultFilterMessage gets called whenever we need to save a ClientFilter<T> into the database:

/// <summary>
/// Message handler for CourseDefaultFilterMessage
/// </summary>
/// <param name="clientFilter"></param>
private void OnCourseDefaultFilterMessage(ClientFilter<Course> clientFilter)
{
  if (clientFilter != null)
  {
    var defaultFilter = new DefaultFilter
        {
            ScreenName = "CoursePage",
            Filter = clientFilter.ToString()
        };
        if (_defaultClientFilter == null) defaultFilter.MarkAsAdded();
        else defaultFilter.MarkAsModified();

        // persist the new default filtering conditions to database
        _schoolModel.SaveDefaultFilterAsync(defaultFilter);
        _defaultClientFilter = clientFilter;
  }
}

First, we can see that there is a call of clientFilter.ToString(), which converts the clientFilter into a string format. After that, we simply call _schoolModel.SaveDefaultFilterAsync(defaultFilter) to save the new search condition to the database.

In order to load a saved search condition from the database, we use the following line of code, and it is defined inside the constructor of the class CoursePageViewModel:

// get default filter
_schoolModel.GetDefaultFilterByScreenNameAsync("CoursePage");

When the call GetDefaultFilterByScreenNameAsync completes, the following event handler will be triggered. Inside this event handler, we first check whether there is any saved search condition returned. If there is, we then call ClientFilter<Course>.Parse(e.Results.Filter) to parse back that saved search condition.

/// <summary>
/// Event handler for GetDefaultFilterByScreenNameCompleted
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void _schoolModel_GetDefaultFilterByScreenNameCompleted(object sender, ResultArgs<DefaultFilter> e)
{
  if (!e.HasError)
  {
    // clear any previous error after a successful call
    _schoolModel.ClearLastError();

    if (e.Results != null && e.Results.ScreenName == "CoursePage")
    {
        _defaultClientFilter = ClientFilter<Course>.Parse(e.Results.Filter);
    }
  }
  else
  {
    // notify user if there is any error
    AppMessages.RaiseErrorMessage.Send(e.Error);
  }
}

Limitations

The ClientQuery and ClientFilter<T> classes give us the capability to dynamically build search conditions on the client side, and then the search conditions get executed on the server side through LINQ to Entities queries. But, we need to keep in mind that these dynamically built search conditions eventually will become part of LINQ to Entities queries, and they have to live within the limitations of LINQ to Entities. For example, if we create the following search condition and call to the server side, we will get an exception.

var clientQuery = ClientQuerySet.Courses
    .Where(n => n.Title.StartsWith("CS"))
    .Skip((CurrentPage - 1) * PageSize).Take(PageSize)
    .AsClientQuery();

_schoolModel.GetCoursesAsync(clientQuery, "CoursePage");

The error message coming from LINQ to Entities basically says that we cannot set a paging condition without first creating a sort condition. This makes sense because without an ORDER BY clause, the SQL query returns a set with no specific order. So, a paging condition cannot be used here.

Wrapping up

We have finished discussing how to do sorting, paging, and filtering with the Self-Tracking Entity Generator for WPF/Silverlight. To summarize, the class ClientQuery stores a list of include paths and a serialized expression tree, it can be passed in as a parameter to a WCF Service call and merged as part of a LINQ to Entities query on the server side. The other class, ClientFilter<T>, provides the capability to dynamically build type-safe queries for sorting, paging, and filtering, which can then be merged as part of the first class.

I hope you find this article useful, and please rate and/or leave feedback below. Thank you!

References

History

  • March, 2013 - Initial release.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Weidong Shen
Software Developer (Senior)
United States United States
Weidong has been an information system professional since 1990. He has a Master's degree in Computer Science, and is currently a MCSD .NET

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 19 Mar 2013
Article Copyright 2013 by Weidong Shen
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid