Generating SQL from expression trees

Is this further down the rabbit hole than IL generation? I’m not sure but I went there.

(Note: This was an experiment. It doesn’t generate safe SQL. I’ll follow up with a better version.)

An expression tree is an abstract representation of code as data. In .NET they are primarily used for LINQ-style code. In C#, lambda expressions can be decomposed into expression trees. Here is an example of a lambda and it’s expression tree:

x => x.PosId == 1 && x.Name == "Main"

ExpressionTree

There are five key types of expression tree nodes.

UnaryExpression: An operation with a single operand such as negation.

BinaryExpression: An operation with two operands such as addition or && or ||.

MemberExpression: Accessing a property, field, or method of an object or a variable. (Variable references in lambda expressions are implemented as fields on a class generated by the compiler.)

ConstantExpression: A node that is a constant value.

ParameterExpression: An input to a lambda function.

The following code recursively walks an expression tree and generates the equivalent where clause in SQL, for sufficiently simple expressions. One of the areas that was a bit tricky is SQL’s handling of NULL. I have to check the right side of a binary expression for NULL so I can generate “x IS NULL” instead of “x = NULL”. I used parentheses liberally so ease composing the expressions. Handling negation was done naively. It could be cleaned up by propagating the negation into the child node.

Input Output
x => x.PosId == 1 ([PosId] = 1)
x => x.IsAborted ([IsAborted] = 1)
x => !x.IsAborted (NOT ([IsAborted] = 1))
x => x.Name == null ([Name] IS NULL)
x => x.PosId == posId (where posId = 2) ([PosId] = 2)
x => x.PosId == 1 && x.Name == “Main” (([PosId] = 1) AND ([Name] = ‘Main’))
x => x.Name.Contains(“Main”) ([Name] LIKE ‘%Main%’)
x => x.Name.StartsWith(“R”) ([Name] LIKE ‘R%’)
x => list.Contains(x.PosId) ([PosId] IN (1, 2, 3))