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
.
Best Answer
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.
Then visit the expression by calling: