I'm proud to introduce "Filter By Expressions". As I mentioned in my previous post about LINQ and IQueryable<T>, I got several ideas about implementing some sort of data(base) filter mechanism just until LINQ will be stable enough. I have 3 alternatives: (1) Implementing some sort of HQL language (like NHibernate) or query parser, (2) use an existing infrastructure and (3) Implement my own mechanism. Considering the options with my managers' hat on, options (1) and (2) are irrelevant at this stage as the learning curve and the adjustments I'll have to make is simply too much for me at the moment. In addition, LINQ will be here shortly so I don't want to invest more than 2 days of work. This leaves me with option (3).
After some good thinking(nothing more) during the weekend and 15 minutes of exchanging ideas with Moran, the [1]API took form. Here is a quick example (from one of our .aspx files):
IFilterObject filter = new Filter();
filter.PageResults = true;
filter.CurrentPage = 1;
filter.PageSize = 10;
// Here is the gold !
filter.Where(
Expression.EqualTo(Order.Field.PurchaseDate, DateTime.Today),
Expression.Operator(OperatorType.And),
Expression.EqualTo(Order.Field.IsValid, true)
);
ordersGrid.DataSource = OrdersGateway.Instance.Get(filter);
Any idea what kind of "Where clause" it will generate behind the scenes ?
I bet you do so here it goes(SqlServer syntax):
WHERE Orders.PurchaseDate = @p0 AND Orders.IsValid = @p1
@p0 and @p1 will be filled with DateTime.Today and true respectively.
Here is another example, this time with "Inner condition":
filter.Where(
Expression.Block(
Expression.GreaterThan(User.Field.Age, 18),
Expression.Operator(OperatorType.And),
Expression.NotEqualTo(User.Field.Email, "")
)
Expression.Operator(OperatorType.Or),
Expression.EqualTo(User.Field.IsGoldMember, true)
);
This will generate the following query(SqlServer syntax):
WHERE (Users.Age>@p0 AND Users.Email != @p1) Or Users.IsGoldMember = @p2
@p0, @p1 and @p2 will be 18, "" and true respectively.
But what about complex scenarios ? My answer is "keep it simple". With this in mind I think that we covered about 98% of every-day queries, which is good enough for me at the moment.
At the moment, our Query Generator Engine only filter the Where clause so you can't do some sort of magic like LINQ or other infrastructures out there allow you to do. Still, this infrastructure is small enough to be easily integrated with almost any existing infrastructure and yet powerful enough to be extremely useful.
Oh, by the way - at current stage, our engine support queries for SqlServer & Oracle databases.
Enough about high-level API. I'll try to explain the Architecture before I'll start showing you the way I decided to sew this engine. Our first (static)class is Expression, which is kind of Factory for expressions:
Factory of expression ah ? what kind of concrete objects I've got there ?
Well, here are few samples: EqualExpression, NotEqualExpression and OperatorExpression
IDataExpression inherit from IExpression and overload GenerateQuery method with two additional parameters: DatabaseMapper and IDataParameterCollection.
The former one(DatabaseMapper) will simply "tell" the Query Engine how to convert User.Field.Age enum into "Users.Age" string and the latter(IDataParameterCollection) will be an empty collection of parameters which every expression can fill according to its' needs.
Eventually, we've got an Engine that receives the IExpression(s) and generates code from them:
Here is the implementation of IQueryCreatorEngine.GenerateWhereClause():
string IQueryCreatorEngine.GenerateWhereClause(IExpression[] expressions, IDataParameterCollection parameters)
{
IDataExpression wrapper = (IDataExpression)Expression.Block(expressions);
return wrapper.GenerateQuery(this.Mapper, parameters);
}
Pretty straight forward: wrap the expressions with DataBlockExpression and let him take care of our business. Let's look at our DataBlockExpression.GenerateQuery method:
protected override string GenerateQueryCore(DatabaseMapper mapper, System.Data.IDataParameterCollection parameters)
{
StringBuilder whereClause = new StringBuilder(this.Expressions.Length * 150);
whereClause.Append("( ");
foreach (IExpression exp in this.Expressions)
{
IDataExpression dataExpression = exp as IDataExpression;
if (dataExpression != null)
whereClause.Append(dataExpression.GenerateQuery(mapper, parameters));
else
whereClause.Append(exp.GenerateQuery());
}
whereClause.Append(" )");
return whereClause.ToString();
}
The only thing we have to do now is to supply some sort of method which implement DatabaseMapper (delegate) signature when we initialize the QueryCreatorEngine. Here is a quick example:
QueryCreatorEngine engine = new QueryCreatorEngine(
delegate(object field)
{
User.Field typedObj = field as User.Field;
if (typedObj != null)
{
switch(typedObj)
{
case User.Field.Age: return "Users.Age";
case User.Field.IsGoldMember: return "Users.IsGoldMember";
case User.Field.Email: return "Users.Email";
default:
throw new ArgumentException("field not supprted"); //whatever
}
}
else
throw new
ArgumentException("field != User.Field type"); //whatever
});
That's it.
Our mapper is generated by tool (our lovely Code-Agent) and our Filter By Expressions infrastructure is ready to go !
If you are still here, leave me a comment and I'll send you a T-Shirt of "Code don't make me sleepy!".
I'll upload our Data Access infrastructure in a few days so be patient and let me know if this post made you happy.
[1] We did a quick API exam in our department and the results were good.