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 (CompareType
s 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:
- There is A LOT of repeated code
String
,DateTime
andbool
are special in that you can't just do ==, >, and < on themString
requiresstring.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
inExpression<Func<T, string, bool>> expression = (ex, value) => value.Trim() == cond.Item3;
is only used in the next lineMemberExpression newSelector = Expression.Property(expression.Parameters[0], cond.Item1);
, which in turn is only used to determine the type (as inif (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 likeExpression<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.