- 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.
CurrentPage = 1;
_currentFilter = new ClientFilter<Course>().OrderBy(n => n.CourseId);
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.
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) clientFilter = clientFilter.OrderByDescending(n => n.StartDate);
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.
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:
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();
_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:
_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.
private void _schoolModel_GetDefaultFilterByScreenNameCompleted(object sender, ResultArgs<DefaultFilter> e)
{
if (!e.HasError)
{
_schoolModel.ClearLastError();
if (e.Results != null && e.Results.ScreenName == "CoursePage")
{
_defaultClientFilter = ClientFilter<Course>.Parse(e.Results.Filter);
}
}
else
{
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.
