Sunday, January 15, 2012

Adding dynamic ‘where’ clauses to a linq query using Builder and Specification pattern


On-the-fly building on where clauses of query statements to fire against a data store is a common problem in most of the applications. Linq is a concept of querying data a first class programming concept in .NET, and enables you to efficiently express queries in your programming language of choice. Linq allows you to write type-safe queries, most of the times you need to construct queries on the fly based on some filter conditions.
The Dynamic query library provided by Microsoft helps you to construct linq queries by passing a string value for where condition or an expression that can be evaluated at runtime. In this post we'll see how to construct expressions for the where clause dynamically using a combination of builder and specification pattern.
Creating a class for filter options
public class UsersFilterOption
{
    public string Name { get; set; }
    public string Country { get; set; }
    public string Role { get; set; }
    public string Email { get; set; }
    public string Telephone { get; set; }
}
The condition builder class is used to add expressions as specifications to the Specification based on the filter options.
public class UserWhereConditionExpressionBuilder
{
    private readonly UsersFilterOption _filterOptions;
    private Specification<User> _result = new TrueSpecification<User>();

    public UserWhereConditionExpressionBuilder(UsersFilterOption usersFilterOptions)
    {
        _filterOptions = usersFilterOptions;
    }

    public UserWhereConditionExpressionBuilder Name()
    {
        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Name);
        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => u.Name.ToUpper().Contains(_filterOptions.Name.ToUpper()));
            
        return this;
    }

    public UserWhereConditionExpressionBuilder Email()
    {
        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Email);
        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => u.Email.ToUpper().Contains(_filterOptions.Email.ToUpper()));
        return this;
    }

    public UserWhereConditionExpressionBuilder Country()
    {
        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Country) &&
                                        System.String.CompareOrdinal(_filterOptions.Country, "ALL") != 0;
        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => System.String.Compare(u.Country, _filterOptions.Country, System.StringComparison.OrdinalIgnoreCase) == 0);
        return this;
    }

    public UserWhereConditionExpressionBuilder Role()
    {
        var shouldBuild = !string.IsNullOrEmpty(_filterOptions.Role) &&
                                        System.String.CompareOrdinal(_filterOptions.Role, "ALL") != 0;
        if (!shouldBuild) return this;

        _result &= new DirectSpecification<User>(u => System.String.Compare(u.Role, _filterOptions.Role, System.StringComparison.OrdinalIgnoreCase) == 0);
        return this;
    }
       
    public Specification<User> GetWhereCondition()
    {
        return _result;
    }

}

The construct method on the builder class constructs the expression from the condition builder class. I've implemented the condition builder class as fluent interfaces for easy use.
public class UsersWhereCondition
{
    public static ISpecification<User> Construct(UsersFilterOption filterOptions)
    {
        var builder = new UserWhereConditionExpressionBuilder(filterOptions);
        return
            builder
            .Name()
            .Email()
            .Country()
            .Role()
            .GetWhereCondition();
    }
}
Using these classes you can construct the query condition for the dynamic linq clause as given below
var whereCondition = UsersWhereCondition.Construct(filterOptions);
               
var data = from user in userFactory.GetAll()
            .Where(whereCondition.SatisfiedBy());

3 comments:

George said...

Thanks for this, any chance of posting to 'Specification' class?

Prajeesh Prathap said...

Hi George,
There are some generic implementations for the specification pattern in codeplex which you can use.

for e.g http://efgenericrepository.codeplex.com/
http://linqspecs.codeplex.com/

If that didnt help you, let me know and i can mail you the code.

George said...

Hello Again, It would be very kind of you if you were able to mail me the code.

Thank you!

George