Click here to Skip to main content
15,867,568 members
Articles / Programming Languages / SQL

Dynamic Querying with LINQ-to-Entities and Expressions

Rate me:
Please Sign up or sign in to vote.
4.95/5 (24 votes)
16 May 2013CPOL8 min read 135.5K   83   17
This article explains how to create a general-purpose data-access library for creating dynamic queries using LINQ-to-Entities.

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.

C#
public class CustomerDto
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool IsActive { get; set; }
    // etc.
}

A simple data-access method that returns every record from the Customer table might be implemented like:

C#
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.

C#
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
            // etc.
        }
        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:

C#
public CustomerDto[] SelectCustomers(
       CustomerSearchCriteriaDto searchCriteria, 
       SortingPagingDto sortingPaging)
{
    // More to come.
}

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:

C#
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.

C#
public class CustomerSearchCriteriaDto
{
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public bool? IsActive {get; set;}
    public DateTime? CreatedDate {get; set;}
    // etc.
}

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.

C#
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);
        }
        // etc., etc. with all criteria

        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.

C#
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:

C#
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.

C#
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>();

    // Iterate the search criteria properties
    var searchCriteriaPropertyInfos = searchCriteria.GetType().GetProperties();
    foreach (var searchCriteriaPropertyInfo in searchCriteriaPropertyInfos)
    {
        // Get the name of the DB field, which may not be the same as the property name.
        var dbFieldName = GetDbFieldName(searchCriteriaPropertyInfo);
        // Get the target DB type (table)
        var dbType = typeof (TDbType);
        // Get a MemberInfo for the type's field (ignoring case
        // so "FirstName" works as well as "firstName")
        var dbFieldMemberInfo = dbType.GetMember(dbFieldName, 
            BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.Instance).Single();
        // STRINGS
        if (searchCriteriaPropertyInfo.PropertyType == typeof (string))
        {
            predicate = ApplyStringCriterion(searchCriteria, 
              searchCriteriaPropertyInfo, dbType, dbFieldMemberInfo, predicate);
        }
        // BOOLEANS
        else if (searchCriteriaPropertyInfo.PropertyType == typeof (bool?))
        {
            predicate = ApplyBoolCriterion(searchCriteria, 
              searchCriteriaPropertyInfo, dbType, dbFieldMemberInfo, predicate);
        }
        // ADD MORE TYPES...
    }

    return predicate;
}

private static Expression<Func<TDbType, bool>> ApplyStringCriterion<TDbType, 
    TSearchCriteria>(TSearchCriteria searchCriteria, PropertyInfo searchCriterionPropertyInfo, 
    Type dbType, MemberInfo dbFieldMemberInfo, Expression<Func<TDbType, bool>> predicate)
{
    // Check if a search criterion was provided
    var searchString = searchCriterionPropertyInfo.GetValue(searchCriteria) as string;
    if (string.IsNullOrWhiteSpace(searchString))
    {
        return predicate;
    }
    // Then "and" it to the predicate.
    // e.g. predicate = predicate.And(x => x.firstName.Contains(searchCriterion.FirstName)); ...
    // Create an "x" as TDbType
    var dbTypeParameter = Expression.Parameter(dbType, @"x");
    // Get at x.firstName
    var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
    // Create the criterion as a constant
    var criterionConstant = new Expression[] {Expression.Constant(searchString)};
    // Create the MethodCallExpression like x.firstName.Contains(criterion)
    var containsCall = Expression.Call(dbFieldMember, StringContainsMethod, criterionConstant);
    // Create a lambda like x => x.firstName.Contains(criterion)
    var lambda = Expression.Lambda(containsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
    // Apply!
    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)
{
    // Check if a search criterion was provided
    var searchBool = searchCriterionPropertyInfo.GetValue(searchCriteria) as bool?;
    if (searchBool == null)
    {
        return predicate;
    }
    // Then "and" it to the predicate.
    // e.g. predicate = predicate.And(x => x.isActive.Contains(searchCriterion.IsActive)); ...
    // Create an "x" as TDbType
    var dbTypeParameter = Expression.Parameter(dbType, @"x");
    // Get at x.isActive
    var dbFieldMember = Expression.MakeMemberAccess(dbTypeParameter, dbFieldMemberInfo);
    // Create the criterion as a constant
    var criterionConstant = new Expression[] {Expression.Constant(searchBool)};
    // Create the MethodCallExpression like x.isActive.Equals(criterion)
    var equalsCall = Expression.Call(dbFieldMember, BooleanEqualsMethod, criterionConstant);
    // Create a lambda like x => x.isActive.Equals(criterion)
    var lambda = Expression.Lambda(equalsCall, dbTypeParameter) as Expression<Func<TDbType, bool>>;
    // Apply!
    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.

C#
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;
    }
}

License

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


Written By
Technical Lead
United States United States
Architect, designer and coder since the late 90's and still going strong. Cut my teeth on Perl and C++, but soon moved to VB6/ASP and then onto .NET bliss and here I remain (until something better comes along). Love talking and thinking about coding practices and techniques, so feel free to shoot me a line. Lately been getting stronger at SOA and WCF and messing around with jqGrid.

Comments and Discussions

 
QuestionSearcing inner collection Pin
Member 1377341811-Apr-18 21:12
Member 1377341811-Apr-18 21:12 
QuestionUsing collection inside search Criteria and using join for searching Pin
Member 1377341810-Apr-18 21:51
Member 1377341810-Apr-18 21:51 
GeneralUseful Article Pin
Alireza_13626-Apr-16 16:28
Alireza_13626-Apr-16 16:28 
GeneralThank You! Pin
MarkLTX12-Dec-14 7:14
MarkLTX12-Dec-14 7:14 
QuestionWell done. Pin
Mohammad Sepahvand29-May-14 2:30
Mohammad Sepahvand29-May-14 2:30 
QuestionNested or Child Entity condition Pin
ak.manoj18-Feb-14 9:58
ak.manoj18-Feb-14 9:58 
AnswerRe: Nested or Child Entity condition Pin
ak.manoj19-Feb-14 3:50
ak.manoj19-Feb-14 3:50 
QuestionFieldMapAttribute Pin
Member 101841451-Aug-13 17:24
Member 101841451-Aug-13 17:24 
AnswerRe: FieldMapAttribute Pin
Todd Sprang2-Aug-13 2:40
Todd Sprang2-Aug-13 2:40 
GeneralRe: FieldMapAttribute Pin
Member 101841452-Aug-13 10:11
Member 101841452-Aug-13 10:11 
QuestionVB.NET Pin
danoelle24-Jul-13 5:31
danoelle24-Jul-13 5:31 
QuestionCode Pin
Dewey16-May-13 22:26
Dewey16-May-13 22:26 
QuestionNullable DBType Pin
Member 998112216-May-13 6:03
Member 998112216-May-13 6:03 
AnswerRe: Nullable DBType Pin
y1o2u315-Jan-15 20:27
y1o2u315-Jan-15 20:27 
GeneralMy vote of 5 Pin
Sam Gorman31-Jan-13 23:26
Sam Gorman31-Jan-13 23:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.