A generic way to use LINQ to Entity with types and operations unknown until run time

Question

A question was asked here about reflection and LINQ to entity. I'm trying to modify the code that was presented to fit my needs.

Here's what I am trying to accomplish: I'm writing back-end support for a web site that will be able to run reports on data we have. I want to write this as generically as possible so that the UI has the latitude to query however it wants without having to change the back end.

Leaning heavily on the above-mentioned link, and also using LinqKit I've cobbled together something that appears to work, but it's kinda' ugly and I was hoping there was a way to slim it down a bit.

First, I want to be able to pass arbitrarily complex and/or statements to the search. This part I'm perfectly satisfied with, but I'm including it as a reference to the next part:

public class QueryGroup
{
    public readonly List<Tuple<string, CompareTypes, string>> conditions;
    public List<QueryGroup> qGroups;
    public Operators Operator { get; private set; }

    public QueryGroup(Operators op)
    {
        Operator = op;
        conditions = new List<Tuple<string, CompareTypes, string>>();
    }
}

public enum CompareTypes
{
    Equals, GreaterThan, LessThan
}

public enum Operators
{
    And, Or
};

The most important part to notice here is

public readonly List<Tuple<string, CompareTypes, string>> conditions;

This list of conditions is a way for us to say that some key (first string) relates in a specified way (CompareTypes like ==, <, >, etc.) to a given value (second string). So for an object Foo, if Foo had a member Bar and we wanted to compare the value of Foo.Bar, then the first string would be "Bar" (note that we will already know we are searching for properties inside of Foo). If we wanted to say that the value of "Bar" should be equal to, say, 5, then the CompareType would be CompareTypes.Equals and the last string would be "5".

OK, on to the ugly part...

Continuing on the Foo/Bar example from above, let's say I had a function like this:

public List<Foo> GetFoosBy(QueryGroup qGroup)
{
    var pred = parseQueryGroup<Foo>(qGroup);
    return _context.Foos.AsExpandable().Where(pred).ToList();
}

Again, this isn't so bad until we get into parseQueryGroup. Here's what that looks like (brace yourself):

private static Expression<Func<T, bool>> parseQueryGroup<T>(QueryGroup q)
{
    var retVal = q.Operator == Operators.And ? PredicateBuilder.True<T>() : PredicateBuilder.False<T>();

    if (q.qGroups != null && q.qGroups.Count > 0)
    {
        //must call .Expand on the subqueries:
        //https://stackoverflow.com/questions/2947820/c-sharp-predicatebuilder-entities-the-parameter-f-was-not-bound-in-the-specif
        foreach (QueryGroup subGroup in q.qGroups)
            retVal = q.Operator == Operators.And ? retVal.And(parseQueryGroup<T>(subGroup).Expand()) : retVal.Or(parseQueryGroup<T>(subGroup).Expand());
    }

    foreach (Tuple<string, CompareTypes, string> condition in q.conditions)
    {
        Tuple<string, CompareTypes, string> cond = condition;

        Expression<Func<T, string, bool>> expression = (ex, value) => value.Trim() == cond.Item3;
        MemberExpression newSelector = Expression.Property(expression.Parameters[0], cond.Item1);
        Expression<Func<T, bool>> lambda;

        if (newSelector.Type == typeof(string))
        {
            switch (condition.Item2)
            {
                case CompareTypes.Equals:
                    expression = (ex, value) => value == cond.Item3;
                    break;
                case CompareTypes.GreaterThan:
                    expression = (ex, value) => string.Compare(value, cond.Item3) > 0;
                    break;
                case CompareTypes.LessThan:
                    expression = (ex, value) => string.Compare(value, cond.Item3) < 0;
                    break;
                default:
                    throw new Exception("Unrecognized compare type");
            }
            newSelector = Expression.Property(expression.Parameters[0], cond.Item1); //do we need this?
            Expression body = expression.Body.Replace(expression.Parameters[1], newSelector);
            lambda = Expression.Lambda<Func<T, bool>>(body, expression.Parameters[0]);
        }
        else if (newSelector.Type == typeof(byte) || newSelector.Type == typeof(short) || newSelector.Type == typeof(int) || newSelector.Type == typeof(long))
        {
            long iCondItem3 = Convert.ToInt64(cond.Item3);
            Expression<Func<T, int, bool>> expression2;
            switch (condition.Item2)
            {
                case CompareTypes.Equals:
                    expression2 = (ex, value) => value == iCondItem3;
                    break;
                case CompareTypes.GreaterThan:
                    expression2 = (ex, value) => value > iCondItem3;
                    break;
                case CompareTypes.LessThan:
                    expression2 = (ex, value) => value < iCondItem3;
                    break;
                default:
                    throw new Exception("Unrecognized compare type");
            }
            newSelector = Expression.Property(expression2.Parameters[0], cond.Item1);
            var body = expression2.Body.Replace(expression2.Parameters[1], newSelector);
            lambda = Expression.Lambda<Func<T, bool>>(body, expression2.Parameters[0]);
        }
        else if (newSelector.Type == typeof(float) || newSelector.Type == typeof(double) || newSelector.Type == typeof(decimal))
        {
            decimal fCondItem3 = Convert.ToDecimal(cond.Item3);
            Expression<Func<T, decimal, bool>> expression2;
            switch (condition.Item2)
            {
                case CompareTypes.Equals:
                    expression2 = (ex, value) => value == fCondItem3;
                    break;
                case CompareTypes.GreaterThan:
                    expression2 = (ex, value) => value > fCondItem3;
                    break;
                case CompareTypes.LessThan:
                    expression2 = (ex, value) => value < fCondItem3;
                    break;
                default:
                    throw new Exception("Unrecognized compare type");
            }
            newSelector = Expression.Property(expression2.Parameters[0], cond.Item1);
            var body = expression2.Body.Replace(expression2.Parameters[1], newSelector);
            lambda = Expression.Lambda<Func<T, bool>>(body, expression2.Parameters[0]);
        }
        else if (newSelector.Type == typeof(bool))
        {
            bool bCondItem3 = Convert.ToBoolean(cond.Item3);
            Expression<Func<T, bool, bool>> expression2 = (ex, value) => value == bCondItem3;
            newSelector = Expression.Property(expression2.Parameters[0], cond.Item1);
            var body = expression2.Body.Replace(expression2.Parameters[1], newSelector);
            lambda = Expression.Lambda<Func<T, bool>>(body, expression2.Parameters[0]);
        }
        else if (newSelector.Type == typeof(DateTime))
        {
            DateTime dCondItem3 = DateTime.Parse(cond.Item3);
            DateTime dCondItem3_NextDay = dCondItem3.Date.AddDays(1);
            Expression<Func<T, DateTime, bool>> expression2;
            switch (condition.Item2)
            {
                case CompareTypes.Equals:
                    expression2 = (ex, value) => (value > dCondItem3.Date && value < dCondItem3_NextDay); //For == on DateTime, we only care about the date
                    break;
                case CompareTypes.GreaterThan:
                    expression2 = (ex, value) => value > dCondItem3;
                    break;
                case CompareTypes.LessThan:
                    expression2 = (ex, value) => value < dCondItem3;
                    break;
                default:
                    throw new Exception("Unrecognized compare type");
            }
            newSelector = Expression.Property(expression2.Parameters[0], cond.Item1);
            var body = expression2.Body.Replace(expression2.Parameters[1], newSelector);
            lambda = Expression.Lambda<Func<T, bool>>(body, expression2.Parameters[0]);
        }
        else
            throw new ArgumentException("Need to code for type " + newSelector.Type);

        retVal = q.Operator == Operators.And ? retVal.And(lambda) : retVal.Or(lambda);
    }

    return retVal;
}

2 things to note:

1. There is A LOT of repeated code
2. String, DateTime and bool are special in that you can't just do ==, >, and < on them
   • String requires string.Compare
   • For DateTime's ==, we don't want to require millisecond precision. In fact, for our purposes, just the date is good enough.
   • bool only has == because < and > didn't make any sense to me.

Even without these special cases (that is, even if it were all the same) I still can't figure out a way to trim this down and it sure seems like I should be able to. It's going to get even messier when I add new compare types (like !=, >=, <=, StartsWith, Contains, etc.)

Things I'm hoping to refactor:

• expression in Expression<Func<T, string, bool>> expression = (ex, value) => value.Trim() == cond.Item3; is only used in the next line MemberExpression newSelector = Expression.Property(expression.Parameters[0], cond.Item1);, which in turn is only used to determine the type (as in if (newSelector.Type == typeof(string))). newSelector is overwritten later and (with the exception of string) expression is never used again.

• The last 3 lines of each section are the same and so it would be nice if we could move that out somehow (but I don't know how we would do that since expression2 is a different signature for each)

  newSelector = Expression.Property(expression2.Parameters[0], cond.Item1);
  var body = expression2.Body.Replace(expression2.Parameters[1], newSelector);
  lambda = Expression.Lambda<Func<T, bool>>(body, expression2.Parameters[0]);
  

• Going along with the previous, I would love it if there was some sort of way to change Expression<Func<T, int, bool>> expression2; to something like Expression<Func<T, newSelector.Type, bool>> expression2;, but from what I can tell, such a thing is not possible (obviously it isn't allowed exactly like that, but if there was some way to manipulate it through reflection or something...)

Could this be refactored? I suppose this would be trivially simple if instead of using LINQ, I just built a raw SQL query, but I was hoping to avoid that, if possible.

Answer 1

String requires string.Compare

Not true. There's a more generic option:

    if (typeof(IComparable).IsAssignableFrom(newSelector.Type))
    {
        switch (condition.Item2)
        {
            case CompareTypes.Equals:
                expression = (ex, value) => value.CompareTo(cond.Item3) == 0;
                break;
            case CompareTypes.GreaterThan:
                expression = (ex, value) => value.CompareTo(cond.Item3) > 0;
                break;
            case CompareTypes.LessThan:
                expression = (ex, value) => value.CompareTo(cond.Item3) < 0;
                break;
            default:
                throw new Exception("Unrecognized compare type");
        }
        ...

That will handle string, char, DateTime, all numeric types, ... (NB I haven't tried compiling it: you might need to add suitable casts to IComparable inside the expressions).

Your treatment of DateTime will require special-casing. One option would be to put that before IComparable in the if-chain. Another might be to apply a projection before applying the comparison; for most types it would be the identity projection, but for DateTime it would be x => x.Date.

Answer 2

Look at the PredicateBuilder on LINQKit.

These code snippets are from the linked page:

public interface IValidFromTo
{
   DateTime? ValidFrom { get; }
   DateTime? ValidTo   { get; }
}

Now we can define a single generic IsCurrent method using that interface as a >constraint:

public static Expression<Func<TEntity, bool>> IsCurrent<TEntity>()
   where TEntity : IValidFromTo
{
   return e => (e.ValidFrom == null || e.ValidFrom <= DateTime.Now) &&
               (e.ValidTo   == null || e.ValidTo   >= DateTime.Now);
}

The final step is to implement this interface in each class that supports >ValidFrom and ValidTo. If you're using Visual Studio or a tool like SqlMetal to >generate your entity classes, do this in the non-generated half of the partial >classes:

public partial class PriceList : IValidFromTo { }
public partial class Product   : IValidFromTo { }

To directly answer your last bullet point, Your Func's type signature can look like Func<...IType, bool> to construct predicates, with the IType as an interface that can accept multiple different types. The problem is you'll have to go back in your model and add that interface to any entities you'd want to query.