Introduction
I’ve recently been working on a service-oriented WCF application that encapsulates all its data-access into a service layer. What I’ll show in this article is not limited to SOA or WCF applications and could be used just as well in OO or component-oriented projects that are designed to have all data-access in a layer whose responsibilities are limited to getting and putting data in a database. Technologically, I’m using LINQ-to-Entities, which has a significant impact on the code that follows, so if you are using something else (other than perhaps LINQ-to-SQL), this article may not apply.
The Problem
The interesting problem to solve in a data-access layer is retrieving a filtered, sorted and paged recordset for presentation in a UI grid. If you just want to show all the data, sorted and filtered in a default way, there’s nothing interesting to do. However, users will usually want to be able to specify something like: “Show all my customers whose last names are like ‘Smith’, first names are like ‘John’, who are currently active in the system. Sort the results by email ascending and then by last access date descending. And just show me the first page of 10 records.” On a different UI grid in your application, you’ll have different set of data filters, sort columns and paging criteria. Seems to me this is a perfect time to apply abstraction to come up with general-purpose code to handle repetitive logic.
Setup
I’ll try to reduce this just to the essentials, so forget that I’m really using WCF. Just think about this stuff as classes that retrieve data, living in any old .NET assembly. Also note that I won’t discuss the UI grid details beyond their general, obvious requirements, since maybe I want to support a mobile, Web and Windows client.
So let’s assume there’s a database table called Customer with columns: customerId, email, firstName, lastName, createdDate, lastAccessedDate, isActive, address, city, state, zip, and whatever others you can imagine. As usual, you’d point VS2012 at the database and generate an EDMX that maps database entities to C# types.
Let’s also assume that you don’t want to expose my EDMX entities beyond the data-access layer. While it requires some extra coding, it’s not hard and provides important separation of data-retrieval classes and data-transfer classes. Particularly in a SOA environment, you’ll want to keep the payload between services to a minimum, and LINQ-to-Entities classes are fairly heavy internally. So you create a light-weight DTO (data-transfer object) called CustomerDto. CustomerDto has nothing but the properties you wish to display in the UI grid. Remember, its job is just to contain data to be moved from service A to service B.
public class CustomerDto
{
public string FirstName { get; set; }
public string LastName { get; set; }
public bool IsActive { get; set; }
}
A simple data-access method that returns every record from the Customer table might be implemented like:
public CustomerDto[] SelectCustomers()
{
using (var context = new MyContext())
{
var dbCustomers = context.Customers;
var dtoCustomers = dbCustomers.ToDto();
return dtoCustomers;
}
}
OK, I slipped in a little technique that I like to use to encapsulate my object transforms and make my code more clean and readable. ToDto() is just an extension method I wrote to extend the type Customer[]. The method simply creates a CustomerDto[] and inserts each Customer as a similar CustomerDto.
public static CustomerDto[] ToDto(this Customer[] dbCustomers)
{
var customerDtos = new CustomerDto[dbCustomers.Length];
for (var i = 0; i < dbCustomers.Length; i++)
{
var currentDbCustomer = dbCustomer[i];
var customerDto = new CustomerDto
{
FirstName = currentDbCustomer.firstName,
LastName = currentDbCustomer.lastName,
IsActive = currentDbCustomer.isActive
}
customerDtos[i] = customerDto;
}
return customerDto;
}
At this point the code has just retrieved every customer in the table in the default order, which would have terrible performance implications for large amounts of data and horrible usability implications for users who want to get some work done efficiently.
Adding Filtering, Sorting and Paging
Clearly SelectCustomers() needs some parameters to specify which customers to select, and since we know we want to sort and page, it seems we want to specify that as a parameter as well. Thinking in SQL terms, I want to pass in values for the WHERE and ORDER BY as well as a SELECT that only grabs some arbitrary chunk of rows. In LINQ, these translate to Where(), OrderBy(), Skip() and Take(). There are many ways to accomplish this, but I’ve chosen to modify the signature as follows:
public CustomerDto[] SelectCustomers(
CustomerSearchCriteriaDto searchCriteria,
SortingPagingDto sortingPaging)
{
}
The requirements for sorting and paging are going to be pretty much the same for any UI grid, so I rolled up a DTO (pretty much everything in my SOA is a DTO if it’s a class that moves data between services) called SortingPagingDto, which is simply:
public class SortingPagingDto
{
public SortOrderDto[] SortOrders { get; set; }
public int PageNumber { get; set; }
public int NumberRecords { get; set; }
}
public class SortOrderDto
{
public enum SortOrder
{
Ascending,
Descending
}
public string ColumnName { get; set; }
public SortOrder ColumnOrder { get; set; }
}
As I thought about the design, I considered that a WHERE clause is just a list of column names with expected values or ranges of values. The following only deals with single values, and it’s left as an exercise to the reader to extend it for a range of values (I’ve always wanted to say “left as an exercise to the reader”). For a given UI grid, I imagined there will be a way to specify the customer filters the user wants: “First name like ‘john’, email like ‘@gmail.com’, etc.” So if I create CustomerSearchCriteriaDto with all fields as nullable types, I can look through each field and determine whether to use it for filtering. It’s pretty obvious, but here’s that DTO.
public class CustomerSearchCriteriaDto
{
public string FirstName {get; set;}
public string LastName {get; set;}
public bool? IsActive {get; set;}
public DateTime? CreatedDate {get; set;}
}
Now I have everything I need to create a dynamically filtered, sorted and paged recordset from LINQ-to-Entities. Or do I?
Two Handy Utilities
There are two excellent pieces of utility code that facilitate generating dynamic Where()and OrderBy() methods for Entity Framework. First up is LinqKit, and you can read about the benefits from here: http://www.albahari.com/nutshell/predicatebuilder.aspx The PredicateBuilder is a simple class that’s part of the LinqKit NuGet package that lets you “and” and “or” your way to dynamic where clause bliss. Similarly, Marc Gravell posted some extensions to OrderBy, OrderByDescending, ThenBy, and ThenByDescending up here http://stackoverflow.com/questions/41244/dynamic-linq-orderby, making it easy to build up an “order by” clause on the fly.
Phase 1 Complete
Using LinqKit and Marc Gravell’s extensions, there’s enough to build a functional implementation.
public CustomerDto[] SelectCustomers(CustomerSearchCriteriaDto
searchCriteria, SortingPagingDto sortingPaging)
{
using (var context = new MyContext())
{
var predicate = PredicateBuilder.True<Customer>();
if (!string.IsNullOrWhiteSpace(searchCriteria.FirstName))
{
predicate = predicate.And(x => x.firstName.Contains(searchCriteria.FirstName));
}
if (!string.IsNullOrWhiteSpace(searchCriteria.LastName))
{
predicate = predicate.And(x => x.lastName.Contains(searchCriteria.LastName));
}
if (searchCriteria.IsActive != null)
{
predicate = predicate.And(x => x.isActive == searchCriteria.IsActive);
}
var query = context.Customers.AsExpandable().Where(predicate) as IOrderedQueryable<Customer>;
var firstPass = true;
foreach (var sortOrder in sortingPaging.SortOrders)
{
if (firstPass)
{
firstPass = false;
query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending
? query.OrderBy(sortOrder.ColumnName) :
query.OrderByDescending(sortOrder.ColumnName);
}
else
{
query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending
? query.ThenBy(sortOrder.ColumnName) :
query.ThenByDescending(sortOrder.ColumnName);
}
}
var dbCustomers = query.Skip((sortingPaging.PageNumber - 1)*
sortingPaging.NumberRecords).Take(sortingPaging.NumberRecords).ToArray();
var customerDtos = dbCustomers.ToDto();
return customerDtos;
}
}
This works great, but that was a lot of typing. I immediately see two things wrong with the code. The really obvious thing is that sorting and paging is going to be common to all selection operations. I knew this beforehand, which is why the SortingPagingDto was created. It should be easy to extract that part into a common routine I can reuse. The other thing that leaps out at me is the pattern of the first half of the code, amounting to a heuristic like “if the search criteria is specified, apply it to the predicate” over and over. This is to me a violation of the DRY Principle: Don't Repeat Yourself.
The Easy Stuff: Sorting and Paging
There is nothing to extracting the sorting and paging into an extension method, but for article completeness, the implementation is shown below.
public static T[] ApplySortingPaging<T>(this IOrderedQueryable<T> query, SortingPagingDto sortingPaging)
{
var firstPass = true;
foreach (var sortOrder in sortingPaging.SortOrders)
{
if (firstPass)
{
firstPass = false;
query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending
? query.OrderBy(sortOrder.ColumnName) :
query.OrderByDescending(sortOrder.ColumnName);
}
else
{
query = sortOrder.ColumnOrder == SortOrderDto.SortOrder.Ascending
? query.ThenBy(sortOrder.ColumnName) :
query.ThenByDescending(sortOrder.ColumnName);
}
}
var result = query.Skip((sortingPaging.PageNumber - 1)*
sortingPaging.NumberRecords).Take(sortingPaging.NumberRecords).ToArray();
return result;
}
It’s generic, so I can reuse it for Customers, Orders, Schools, Busses, and what-have-you. What really makes me happy is, much like the ToDto() extension, I’ve reduced all that code to an method call:
var dbCustomers = query.ApplySortingPaging(sortingPaging);
The Hard Stuff: Dynamic Where
This took me a solid day to figure out, reading the docs, scouring stackoverflow.com, debugging, etc. I was pretty psyched when I got it working, and hence, this article. If you’re an Expression guru, this is probably obvious, but I’m not, and it was a mind-bender for me to figure out. Again, the basic problem is taking repetitive logic and abstracting it for general reuse. The logic is basically, “do I have a value, and if so, apply it to the predicate.” In some cases “apply it to the predicate” means do a Contains() for strings, but it might mean do an Equals() for a Boolean. Again, it’s left for an exercise to do something like a date range or numeric less-than/greater-than.
My first pass at the problem reduced the if-not-null-apply code from a block to a single line, but I still had one line per filter I wanted to apply, meaning maybe 20 lines of nearly-identical code. I really wanted the predicate building reduced to one line. Possible?! You bet!
The following code is commented pretty thoroughly, but I’ll describe it a bit more. The idea is to take a given SearchCriteriaDto (i.e. a class with only nullable fields in which non-null fields mean “apply these fields”) and build a predicate for the database type (i.e. table). In this case, I’m thinking that the more values specified by the user, the fewer results they want back. That is, first name like “John” AND last name like “Smith”. You may want to build a similar routine that does “ors” for further exercise. Then it’s a matter of looping over each property of the SearchCriteriaDto, and processing the property based on the property’s type.
One other thing I did to ease life, because I don’t always agree with the naming conventions used by the engineers that built the database, is to allow a mapping of the SearchCriteriaDto properties to the EF/database fields. I did this by way of a custom Attribute that I can apply to each property of a SearchCriteriaDto when I need it. The GetDbFieldName() method checks for that and goes with the property name itself if the Attribute wasn’t specified.
The rest… Well, it’s just Expression voodoo. Type “Expression[dot]” is Visual Studio and check out all the IntelliSense you get. I had no idea. BuildPredicate() uses Reflection to get info about the types and fields, and the Apply methods rely mainly on Expression parameters, member-accessors, constants, calls, and lambdas.
private static readonly MethodInfo StringContainsMethod =
typeof (string).GetMethod(@"Contains", BindingFlags.Instance |
BindingFlags.Public, null, new[] {typeof (string)}, null);
private static readonly MethodInfo BooleanEqualsMethod =
typeof (bool).GetMethod(@"Equals", BindingFlags.Instance |
BindingFlags.Public, null, new[] {typeof (bool)}, null);
public static Expression<Func<TDbType, bool>>
BuildPredicate<TDbType, TSearchCriteria>(TSearchCriteria searchCriteria)
{
var predicate = PredicateBuilder.True<TDbType>();
var searchCriteriaPropertyInfos = searchCriteria.GetType().GetProperties();
foreach (var searchCriteriaPropertyInfo in searchCriteriaPropertyInfos)
{
var dbFieldName = GetDbFieldName(searchCriteriaPropertyInfo);
var dbType = typeof (TDbType);
var dbFieldMemberInfo = dbType.GetMember(dbFieldName,
BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance).Single();
if (searchCriteriaPropertyInfo.PropertyType == typeof (string))
{
predicate = ApplyStringCriterion(searchCriteria,
searchCriteriaPropertyInfo, dbType, dbFieldMemberInfo, predicate);
}
else if (searchCriteriaPropertyInfo.PropertyType == typeof (bool?))
{
predicate = ApplyBoolCriterion(searchCriteria,
searchCriteriaPropertyInfo, dbType, dbFieldMemberInfo, predicate);
}
}
return predicate;
}
private static Expression<Func<TDbType, bool>> ApplyStringCriterion<TDbType,
TSearchCriteria>(TSearchCriteria searchCriteria, PropertyInfo searchCriterionPropertyInfo,
Type dbType, MemberInfo dbFieldMemberInfo, Expression<Func<TDbType, bool>> predicate)
{
var searchString = searchCriterionPropertyInfo.GetValue(searchCriteria) as string;
if (string.IsNullOrWhiteSpace(searchString))
{
return predicate;
}
var dbTypeParameter = Expression.Parameter(dbType, @"x");
var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
var criterionConstant = new Expression[] {Expression.Constant(searchString)};
var containsCall = Expression.Call(dbFieldMember, StringContainsMethod, criterionConstant);
var lambda = Expression.Lambda(containsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
return predicate.And(lambda);
}
private static Expression<Func<TDbType, bool>> ApplyBoolCriterion<TDbType,
TSearchCriteria>(TSearchCriteria searchCriteria, PropertyInfo searchCriterionPropertyInfo,
Type dbType, MemberInfo dbFieldMemberInfo, Expression<Func<TDbType, bool>> predicate)
{
var searchBool = searchCriterionPropertyInfo.GetValue(searchCriteria) as bool?;
if (searchBool == null)
{
return predicate;
}
var dbTypeParameter = Expression.Parameter(dbType, @"x");
var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
var criterionConstant = new Expression[] {Expression.Constant(searchBool)};
var equalsCall = Expression.Call(dbFieldMember, BooleanEqualsMethod, criterionConstant);
var lambda = Expression.Lambda(equalsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
return predicate.And(lambda);
}
private static string GetDbFieldName(PropertyInfo propertyInfo)
{
var fieldMapAttribute =
propertyInfo.GetCustomAttributes(typeof (FieldMapAttribute), false).FirstOrDefault();
var dbFieldName = fieldMapAttribute != null ?
((FieldMapAttribute) fieldMapAttribute).Field : propertyInfo.Name;
return dbFieldName;
}
And that’s all there is to it! It’s pretty easy to extend for additional cases, and now the data-access selection implementation is reduced to a few lines, most of which is highly reusable.
public CustomerDto[] SelectCustomer(CustomerSearchCriteriaDto searchCriteria,
SortingPagingDto sortingPaging)
{
using (var context = new MyContext())
{
var predicate = ExpressionExtensions.BuildPredicate<Customer,
CustomerSearchCriteriaDto>(searchCriteria);
var query = context.Customers.AsExpandable().Where(predicate)
as IOrderedQueryable<Customer>;
var dbCustomers = query.ApplySortingPaging(sortingPaging);
var customerDtos = dbCustomers.ToDto();
return customerDtos;
}
}
