Converting Linq Expressions to T-SQL

In my post about a Batch Update command for Linq To SQL, the code I presented uses a method ToMSSqlString() which has not yet been defined.

The method converts a Linq Expression to a T-SQL expression by evaluating the current node of the expression tree and evaluation the node’s children:

public static class ExpressionExtensions
{
  public static string ToMSSqlString(this Expression expression)
  {
    switch (expression.NodeType)
    {
      case ExpressionType.Add:
        var add = expression as BinaryExpression;
        return add.Left.ToMSSqlString() + " + " + add.Right.ToMSSqlString();
      case ExpressionType.Constant:
        var constant = expression as ConstantExpression;
        if (constant.Type == typeof(string))
          return "N'" + constant.Value.ToString().Replace("'", "''") + "'";
        return constant.Value.ToString();
      case ExpressionType.Equal:
        var equal = expression as BinaryExpression;
        return equal.Left.ToMSSqlString() + " = " +
               equal.Right.ToMSSqlString();
      case ExpressionType.Lambda:
        var l = expression as LambdaExpression;
        return l.Body.ToMSSqlString();
      case ExpressionType.MemberAccess:
        var memberaccess = expression as MemberExpression;
        // todo: if column aliases are used, look up ColumnAttribute.Name
        return "[" + memberaccess.Member.Name + "]";
    }

    throw new NotImplementedException(
      expression.GetType().ToString() + " " + 
      expression.NodeType.ToString());
  }
}

This only implements selected NodeTypes to sketch the mechanism.

As an alternative, you can also use the ExpressionVisitor class to iterate the expression tree, as illustrated in other blogs.

Whichever method you use, the result of such a method is a T-SQL string representation of the Linq expression which can be used to build the final T-SQL command.