Fluent Where Clause

I like working with fluent APIs and using the fluent syntax with LINQ, it makes more sense to me and is easier to follow. With this in mind I thought it would be fun to create a simple fluent style where clause for ArcGIS queries or DefinitionExpressions. The main benefits of this approach are to enhance readability and to reduce runtime errors from using magic strings as usually you would construct these queries using string.Format(…). This is by no means a fully featured implementation but it serves as a starting point for anyone that is interested and I may extend it in the future.

At the center of this I created a new class called WhereClause. Within this class are a number of standard operations that you would use when constructing a query such as comparing values and joining statements. From each function I return the WhereClause object so that subsequent functions can be chained. The code looks like.

public class WhereClause
{
    StringBuilder _whereClause;
    int _nestLevel;

    const string OpenBracket = "(";
    const string CloseBracket = ")";

    const string EqualsOperator = "=";
    const string GreaterThanOperator = ">";
    const string LessThanOperator = "<";
    const string InOperator = "in ({0})";
    const string InForStringOperator = "in ('{0}')";

    const string AndOperator = "AND";
    const string OrOperator = "OR";
    const string LikeOperator = "LIKE '{0}%'";

    const string Separator = ",";
    const string SeparatorForString = "','";

    public static WhereClause Create()
    {
        return new WhereClause();
    }

    WhereClause()
    {
        _whereClause = new StringBuilder();
    }

    public WhereClause For<T>(T forObject, Expression<Func<string>> property) where T : class
    {
        AddCloseBracket();
        PropertyInfo propertyInfo;
        if (property.Body is MemberExpression)
        {
            propertyInfo = ((MemberExpression) property.Body).Member as PropertyInfo;
        }
        else
        {
            propertyInfo = ((MemberExpression) ((UnaryExpression)property.Body).Operand).Member as PropertyInfo;
        }
        if (propertyInfo != null) propertyInfo.SetValue(forObject, _whereClause.ToString().Trim(), null);

        return this;
    }

    public WhereClause Field<T>(Expression<Func<T>> expression)
    {
        return Field(GetPropertyName(expression));
    }

    public WhereClause Field(string name)
    {
        AddOpenBracket();
        AppendPart(name);
        return this;
    }        

    public WhereClause And()
    {
        AddCloseBracket();
        AppendPart(AndOperator);
        return this;
    }

    public WhereClause AndField<T>(Expression<Func<T>> expression)
    {
        return AndField(GetPropertyName(expression));
    }

    public WhereClause AndField(string name)
    {            
        AppendPart(AndOperator);
        AppendPart(name);
        return this;
    }

    public WhereClause Or()
    {
        AddCloseBracket();
        AppendPart(OrOperator);
        return this;
    }

    public WhereClause OrField<T>(Expression<Func<T>> expression)
    {
        return OrField(GetPropertyName(expression));
    }

    public WhereClause OrField(string name)
    {
        AppendPart(OrOperator);
        AppendPart(name);
        return this;
    }

    public WhereClause Equals<T>(T value)
    {
        if (typeof(T) == typeof(string) || typeof(T) == typeof(DateTime))
            return Equals(value.ToString());

        if (IsIEnumerableOfT(typeof(T)))
            throw new NotSupportedException("Collection should be of type list");

        AppendPart(EqualsOperator);
        AppendPart(value.ToString());
        return this;
    }

    public WhereClause Equals<T>(List<T> values)
    {
        if (typeof(T) == typeof(string) || typeof(T) == typeof(DateTime))
            return Equals(values.Cast<string>());

        AppendPart(string.Format(InOperator, string.Join(Separator, values)));
        return this;
    }

    WhereClause Equals(string value)
    {
        AppendPart(EqualsOperator);
        AppendPart("'" + value + "'");
        return this;
    }

    WhereClause Equals(IEnumerable<string> values)
    {
        AppendPart(string.Format(InForStringOperator, string.Join(SeparatorForString, values)));
        return this;
    }

    public WhereClause GreaterThan<T>(T value)
    {
        if (typeof(T) == typeof(string) || typeof(T) == typeof(DateTime))
            return GreaterThan(value.ToString());

        if (IsIEnumerableOfT(typeof(T)))
            throw new NotSupportedException();

        AppendPart(GreaterThanOperator);
        AppendPart(value.ToString());
        return this;
    }

    WhereClause GreaterThan(string value)
    {
        AppendPart(GreaterThanOperator);
        AppendPart("'" + value + "'");
        return this;
    }

    public WhereClause GreaterThanOrEquals<T>(T value)
    {
        if (typeof(T) == typeof(string) || typeof(T) == typeof(DateTime))
            return GreaterThanOrEquals(value.ToString());

        if (IsIEnumerableOfT(typeof(T)))
            throw new NotSupportedException();

        AppendPart(GreaterThanOperator + EqualsOperator);
        AppendPart(value.ToString());
        return this;
    }

    WhereClause GreaterThanOrEquals(string value)
    {
        AppendPart(GreaterThanOperator + EqualsOperator);
        AppendPart("'" + value + "'");
        return this;
    }

    public WhereClause LessThan<T>(T value)
    {
        if (typeof(T) == typeof(string) || typeof(T) == typeof(DateTime))
            return LessThan(value.ToString());

        if (IsIEnumerableOfT(typeof(T)))
            throw new NotSupportedException();

        AppendPart(LessThanOperator);
        AppendPart(value.ToString());
        return this;
    }

    WhereClause LessThan(string value)
    {
        AppendPart(LessThanOperator);
        AppendPart("'" + value + "'");
        return this;
    }

    public WhereClause LessThanOrEquals<T>(T value)
    {
        if (typeof(T) == typeof(string) || typeof(T) == typeof(DateTime))
            return LessThanOrEquals(value.ToString());

        if (IsIEnumerableOfT(typeof(T)))
            throw new NotSupportedException();

        AppendPart(LessThanOperator + EqualsOperator);
        AppendPart(value.ToString());
        return this;
    }

    WhereClause LessThanOrEquals(string value)
    {
        AppendPart(LessThanOperator + EqualsOperator);
        AppendPart("'" + value + "'");
        return this;
    }

    public WhereClause StartsWith(string value)
    {
        AppendPart(string.Format(LikeOperator, value));
        return this;
    }

    void AppendPart(string part)
    {
        if (_whereClause.ToString().EndsWith(" "))
            _whereClause.Append(part);
        else
            _whereClause.Append(" " + part);
    }

    static string GetPropertyName<T>(Expression<Func<T>> expression)
    {
        var exp = (MemberExpression)expression.Body;
        return exp.Member.Name;
    }

    static bool IsIEnumerableOfT(Type type)
    {
        return type.GetInterfaces().Any(t => t.IsGenericType && t.GetGenericTypeDefinition() == typeof(IEnumerable<>));
    }

    void AddOpenBracket()
    {
        AppendPart(OpenBracket);
        _nestLevel++;
    }

    void AddCloseBracket()
    {
        if (_nestLevel == 0) return;

        AppendPart(CloseBracket);
        _nestLevel--;
    }
}

In order to use this we need to do 3 things.

1. Create a new instance of a WhereClause
2. Add the statements we want to execute
3. Apply the statement to a property

An example is

var queryTask = new QueryTask("http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer/5");
queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;

var query = new Query { ReturnGeometry = true, OutSpatialReference = MyMap.SpatialReference };
query.OutFields.Add("*");

var states = new List<string> { "Florida", "Alaska", "Idaho", "Hawaii" };

// Example of using string values and chaining statements
WhereClause.Create()
    .Field("STATE_NAME").Equals(states)
    .Or()
    .Field("POP2007").LessThanOrEquals(1000000)
    .Or()
    .Field("STATE_NAME").StartsWith("Te")
    .For(query, () => query.Where);

queryTask.ExecuteAsync(query);

You should be able to see the 3 steps outlined above. The output from is looks like

( STATE_NAME in (‘Florida’,’Alaska’,’Idaho’,’Hawaii’) ) OR ( POP2007 <= 1000000 ) OR ( STATE_NAME LIKE ‘Te%’ )

This has already eliminated some potential typo’s but you can also use your own object properties for the statement field names as long as they match the underlying data. The advantage of this is that any errors will throw compile time errors as opposed to runtime errors.

An example of this is

var featureLayer = MyMap.Layers["MyFeatureLayer"] as FeatureLayer;

if (featureLayer == null || featureLayer.InitializationFailure != null)
    return;

// The property values here could be from your view model
WhereClause.Create()
    .Field(() => Pop1990).GreaterThan(Pop1990) // Using the property name as the query column
    .AndField("CITY_NAME").StartsWith("Bi")    // Using text for the query column
    .Or()
    .Field("CITY_NAME").StartsWith("Water")
    .For(featureLayer, () => featureLayer.Where);

featureLayer.Update();

( Pop1990 > 20000 AND CITY_NAME LIKE ‘Bi%’ ) OR ( CITY_NAME LIKE ‘Water%’ )

I’d be interested to hear what you think of this approach or any suggestions you may have. Thanks for reading.