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 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 !
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”:
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);
foreach (IExpression exp in this.Expressions)
IDataExpression dataExpression = exp as IDataExpression;
if (dataExpression != null)
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(
User.Field typedObj = field as User.Field;
if (typedObj != null)
case User.Field.Age: return “Users.Age”;
case User.Field.IsGoldMember: return “Users.IsGoldMember”;
case User.Field.Email: return “Users.Email”;
throw new ArgumentException(“field not supprted”); //whatever
ArgumentException(“field != User.Field type”); //whatever
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.
 We did a quick API exam in our department and the results were good.
4 thoughts on “Filter By Expressions – One step closer to LINQ. Well, sort of…”
I think it would be much nicer to code things as a fluent interface, so you end up stringing the operations on the end like this:
Thanks for your relpy.
I really liked the idea of fluent interface but I think that you took it to extreme.
Still, the idea is really lovely so I changed the interface so it will be possilbe to do:
Very cool stuff. We are indeed doing a lot of similar work. The one other thing that we’ve found beneficial is to make the User.Field.Age an actual object. In our case it’s a Column[T, int] object. By doing this we made it possible to do things like:
repository.FindAll(User.Fields.Age.IsNotNull() && User.Fields.Type.ToUpper() == "FOO")
IsNotNull() would return an Expression (or Criteria in our case) as would the User.Fields.Type.ToUpper() == "FOO". Anyway, its proven useful in a couple cases for us.
We’ve also made use of the fluent interface which makes the api very nice to work with.
ps – whats this free t-shirt talk about? :)
Thanks for your comment.
I’ve actually changed the interface so we now use a fluent interface – that’s pretty slick.
About turning our columns into actual object (instead of Enum) – after looking at your posts it’s the next things I’m planning to refactor in our SEE (Simple Expression Engine). I hope you don’t mind that I’m borrowing your ideas ;)
Comments are closed.