How to convert an expression tree to a partial SQL query?
When EF or LINQ to SQL runs a query, it:
- Builds an expression tree from the code,
- Converts the expression tree into an SQL query,
- Executes the query, gets the raw results from the database and converts them to the result to be used by the application.
Looking at the stack trace, I can't figure out where the second part happens.
In general, is it possible to use an existent part of EF or (preferably) LINQ to SQL to convert an Expression object to a partial SQL query (using Transact-SQL syntax), or I have to reinvent the wheel?
Update: a comment asks to provide an example of what I'm trying to do.
Actually, the answer by Ryan Wright below illustrates perfectly what I want to achieve as a result, except the fact that my question is specifically about how can I do it by using existent mechanisms of .NET Framework actually used by EF and LINQ to SQL, instead of having to reinvent the wheel and write thousands of lines of not-so-tested code myself to do the similar thing.
Here is also an example. Again, note that there is no ORM-generated code.
private class Product
{
[DatabaseMapping("ProductId")]
public int Id { get; set; }
[DatabaseMapping("Price")]
public int PriceInCents { get; set; }
}
private string Convert(Expression expression)
{
// Some magic calls to .NET Framework code happen here.
// [...]
}
private void TestConvert()
{
Expression<Func<Product, int, int, bool>> inPriceRange =
(Product product, int from, int to) =>
product.PriceInCents >= from && product.PriceInCents <= to;
string actualQueryPart = this.Convert(inPriceRange);
Assert.AreEqual("[Price] between @from and @to", actualQueryPart);
}
Where does the name Price come from in the expected query?
The name can be obtained through reflection by querying the custom DatabaseMapping attribute of Price property of Product class.
Where do names @from and @to come from in the expected query?
Those names are the actual names of the parameters of the expression.
Where does between … and come from in the expected query?
This is a possible result of a binary expression. Maybe EF or LINQ to SQL would, instead of between … and statement, stick with [Price] >= @from and [Price] <= @to instead. It's ok too, it doesn't really matter since the result is logically the same (I'm not mentioning performance).
Why there is no where in the expected query?
Because nothing indicates in the Expression that there must be a where keyword. Maybe the actual expression is just one of the expressions which would be combined later with binary operators to build a larger query to prepend with a where.
Yes it is possible, you can parse a LINQ expression tree using the visitor pattern. You would need to construct a query translator by subclassing ExpressionVisitor like below. By hooking into the correct points you can use the translator to construct your SQL string from your LINQ expression. Note that the code below only deals with basic where/orderby/skip/take clauses, but you can fill it out with more as needed. Hopefully it serves as a good first step.
public class MyQueryTranslator : ExpressionVisitor
{
private StringBuilder sb;
private string _orderBy = string.Empty;
private int? _skip = null;
private int? _take = null;
private string _whereClause = string.Empty;
public int? Skip
{
get
{
return _skip;
}
}
public int? Take
{
get
{
return _take;
}
}
public string OrderBy
{
get
{
return _orderBy;
}
}
public string WhereClause
{
get
{
return _whereClause;
}
}
public MyQueryTranslator()
{
}
public string Translate(Expression expression)
{
this.sb = new StringBuilder();
this.Visit(expression);
_whereClause = this.sb.ToString();
return _whereClause;
}
private static Expression StripQuotes(Expression e)
{
while (e.NodeType == ExpressionType.Quote)
{
e = ((UnaryExpression)e).Operand;
}
return e;
}
protected override Expression VisitMethodCall(MethodCallExpression m)
{
if (m.Method.DeclaringType == typeof(Queryable) && m.Method.Name == "Where")
{
this.Visit(m.Arguments[0]);
LambdaExpression lambda = (LambdaExpression)StripQuotes(m.Arguments[1]);
this.Visit(lambda.Body);
return m;
}
else if (m.Method.Name == "Take")
{
if (this.ParseTakeExpression(m))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
else if (m.Method.Name == "Skip")
{
if (this.ParseSkipExpression(m))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
else if (m.Method.Name == "OrderBy")
{
if (this.ParseOrderByExpression(m, "ASC"))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
else if (m.Method.Name == "OrderByDescending")
{
if (this.ParseOrderByExpression(m, "DESC"))
{
Expression nextExpression = m.Arguments[0];
return this.Visit(nextExpression);
}
}
throw new NotSupportedException(string.Format("The method '{0}' is not supported", m.Method.Name));
}
protected override Expression VisitUnary(UnaryExpression u)
{
switch (u.NodeType)
{
case ExpressionType.Not:
sb.Append(" NOT ");
this.Visit(u.Operand);
break;
case ExpressionType.Convert:
this.Visit(u.Operand);
break;
default:
throw new NotSupportedException(string.Format("The unary operator '{0}' is not supported", u.NodeType));
}
return u;
}
/// <summary>
///
/// </summary>
/// <param name="b"></param>
/// <returns></returns>
protected override Expression VisitBinary(BinaryExpression b)
{
sb.Append("(");
this.Visit(b.Left);
switch (b.NodeType)
{
case ExpressionType.And:
sb.Append(" AND ");
break;
case ExpressionType.AndAlso:
sb.Append(" AND ");
break;
case ExpressionType.Or:
sb.Append(" OR ");
break;
case ExpressionType.OrElse:
sb.Append(" OR ");
break;
case ExpressionType.Equal:
if (IsNullConstant(b.Right))
{
sb.Append(" IS ");
}
else
{
sb.Append(" = ");
}
break;
case ExpressionType.NotEqual:
if (IsNullConstant(b.Right))
{
sb.Append(" IS NOT ");
}
else
{
sb.Append(" <> ");
}
break;
case ExpressionType.LessThan:
sb.Append(" < ");
break;
case ExpressionType.LessThanOrEqual:
sb.Append(" <= ");
break;
case ExpressionType.GreaterThan:
sb.Append(" > ");
break;
case ExpressionType.GreaterThanOrEqual:
sb.Append(" >= ");
break;
default:
throw new NotSupportedException(string.Format("The binary operator '{0}' is not supported", b.NodeType));
}
this.Visit(b.Right);
sb.Append(")");
return b;
}
protected override Expression VisitConstant(ConstantExpression c)
{
IQueryable q = c.Value as IQueryable;
if (q == null && c.Value == null)
{
sb.Append("NULL");
}
else if (q == null)
{
switch (Type.GetTypeCode(c.Value.GetType()))
{
case TypeCode.Boolean:
sb.Append(((bool)c.Value) ? 1 : 0);
break;
case TypeCode.String:
sb.Append("'");
sb.Append(c.Value);
sb.Append("'");
break;
case TypeCode.DateTime:
sb.Append("'");
sb.Append(c.Value);
sb.Append("'");
break;
case TypeCode.Object:
throw new NotSupportedException(string.Format("The constant for '{0}' is not supported", c.Value));
default:
sb.Append(c.Value);
break;
}
}
return c;
}
protected override Expression VisitMember(MemberExpression m)
{
if (m.Expression != null && m.Expression.NodeType == ExpressionType.Parameter)
{
sb.Append(m.Member.Name);
return m;
}
throw new NotSupportedException(string.Format("The member '{0}' is not supported", m.Member.Name));
}
protected bool IsNullConstant(Expression exp)
{
return (exp.NodeType == ExpressionType.Constant && ((ConstantExpression)exp).Value == null);
}
private bool ParseOrderByExpression(MethodCallExpression expression, string order)
{
UnaryExpression unary = (UnaryExpression)expression.Arguments[1];
LambdaExpression lambdaExpression = (LambdaExpression)unary.Operand;
lambdaExpression = (LambdaExpression)Evaluator.PartialEval(lambdaExpression);
MemberExpression body = lambdaExpression.Body as MemberExpression;
if (body != null)
{
if (string.IsNullOrEmpty(_orderBy))
{
_orderBy = string.Format("{0} {1}", body.Member.Name, order);
}
else
{
_orderBy = string.Format("{0}, {1} {2}", _orderBy, body.Member.Name, order);
}
return true;
}
return false;
}
private bool ParseTakeExpression(MethodCallExpression expression)
{
ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];
int size;
if (int.TryParse(sizeExpression.Value.ToString(), out size))
{
_take = size;
return true;
}
return false;
}
private bool ParseSkipExpression(MethodCallExpression expression)
{
ConstantExpression sizeExpression = (ConstantExpression)expression.Arguments[1];
int size;
if (int.TryParse(sizeExpression.Value.ToString(), out size))
{
_skip = size;
return true;
}
return false;
}
}
Then visit the expression by calling:
var translator = new MyQueryTranslator();
string whereClause = translator.Translate(expression);
The short answer seems to be that you cannot use a part of EF or LINQ to SQL as a shortcut to translation. You need at least a subclass of ObjectContext to get at the internal protected QueryProvider property, and that means all the overhead of creating the context, including all the metadata and so on.
Assuming you are ok with that, to get a partial SQL query, for example, just the WHERE clause you're basically going to need the query provider and call IQueryProvider.CreateQuery() just as LINQ does in its implementation of Queryable.Where. To get a more complete query you can use ObjectQuery.ToTraceString().
As to where this happens, LINQ provider basics states generally that
IQueryProvider returns a reference to IQueryable with the constructed expression-tree passed by the LINQ framework, which is used for further calls. In general terms, each query block is converted to a bunch of method calls. For each method call, there are some expressions involved. While creating our provider - in the method IQueryProvider.CreateQuery - we run through the expressions and fill up a filter object, which is used in the IQueryProvider.Execute method to run a query against the data store
and that
the query can be executed in two ways, either by implementing the GetEnumerator method (defined in the IEnumerable interface) in the Query class, (which inherits from IQueryable); or it can be executed by the LINQ runtime directly
Checking EF under the debugger it's the former.
If you don't want to completely re-invent the wheel and neither EF nor LINQ to SQL are options, perhaps this series of articles would help:
- How to: LINQ to SQL Translation
- How to: LINQ to SQL Translation - Part II
- How to: LINQ to SQL Translation - Part III
Here are some sources for creating a query provider that probably involve much more heavy lifting on your part to implement what you want:
- LINQ: Building an IQueryable provider series
- Creating custom LINQ provider using LinqExtender