Converting C# Anonymous Functions to SQL Where Clauses using Expression Trees

C# Anonymous functions are awesome, they allow you to abstract your code in a beautiful and clean synax.
But trying to integrate this with reflection and generics might get a little bit hard, I had a problem a few days ago... Generate a SQL WHERE clause based on a function, which takes an object of ta generic ype T, and returns a boolean (f(x: T): Boolean).

The function itself will be pretty simple most of the time, the concrete example of the method I was trying to accomplish is the following: db.Update<MyType>(myObj, (o => o.Id == 1))

So basically, that will update the conveniently named table MyType, with myObj data, WHERE Id = 1

So how take an anonymouse function, parse it, and return a string? I knew it was possible, because LINQ does this for SQL, so there must be a way. The first thing I did was search on StackOverflow, which lead me to this good article on Expression Trees.

After learning a bit about that, I knew I had to use Expression Trees, but I still didn't quite understand how, I wanted to use a function on my method, not a Expression Tree, and there is no way to get an Expression Tree from a function, so I asked on StackOverflow, the answer was pretty simple, just use a function on your method call, and in your Update method use a ExpressionTree as argument type.

public bool Update<T>(T obj, Expression<Func<T, bool>> predicate)

That was I can call my function like this Update<MyType>(obj, (t => t.Id == 1))

That was a huge step, now I could work on the Expression Tree itself, I ended up with a quite nice function to get a string based on a Expression Tree, it assumes the body it's just a list of conditions, for example A && (B || C), which is what you'll most likely have to cope with.

Here is the code I used to get a string from my expression, also note the GetValue method is a helper method, used to get the value of a MemberExpression (Thanks again, StackOverflow!)

	private string parsePredicateExpression(Expression body)
	{
	BinaryExpression binary = (BinaryExpression)body; // cast
	string separator = body.NodeType == ExpressionType.OrElse ? " OR " : " AND ";
	if (body.NodeType == ExpressionType.AndAlso || body.NodeType == ExpressionType.OrElse)
	{ // If we are working with several ANDs or ORs
	return "(" + parsePredicateExpression(binary.Left) + separator + parsePredicateExpression(binary.Right) + ")";
	}
	else
	{
	MemberExpression member = (MemberExpression)binary.Left;
	string output = member.Member.Name;
	switch (body.NodeType)
	{ // Given the node type of the expression, we set the correct string, normally it's equal (x.Id == 1)
	default:
	case ExpressionType.Equal:
	output += " = ";
	break;
	case ExpressionType.NotEqual:
	output += " <> ";
	break;
	case ExpressionType.GreaterThan:
	output += " > ";
	break;
	case ExpressionType.GreaterThanOrEqual:
	output += " >= ";
	break;
	case ExpressionType.LessThan:
	output += " < ";
	break;
	case ExpressionType.LessThanOrEqual:
	output += " <= ";
	break;
	}
	object val = null;
	if (binary.Right.NodeType == ExpressionType.MemberAccess)
	{
	var exp = (MemberExpression)binary.Right;
	val = GetValue(exp);
	}
	else
	{
	ConstantExpression constant = (ConstantExpression)binary.Right;
	val = constant.Value;
	}
	if (val is int) { output += val; }
	else { output += "\"" + val + "\""; }
	return output;
	}
	}
	private object GetValue(MemberExpression member)
	{
	var objectMember = Expression.Convert(member, typeof(object));
	var getterLambda = Expression.Lambda>(objectMember);
	var getter = getterLambda.Compile();
	return getter();
	}

view raw gistfile1.cs hosted with ❤ by GitHub