如何将表达式树转换为部分SQL查询？ [英] How to convert an expression tree to a partial SQL query?
问题描述
当EF或LINQ to SQL运行查询时：
When EF or LINQ to SQL runs a query, it:
- 从代码中构建一个表达式树，
- 将表达式树转换为SQL查询
- 执行查询，从数据库获取原始结果并将其转换为应用程序使用的结果。
查看堆栈跟踪，我无法确定第二部分发生的位置。
Looking at the stack trace, I can't figure out where the second part happens.
一般来说，是否可以使用EF或（最好是）LINQ to SQL的现有部分将 Expression 对象转换为部分SQL查询使用Transact-SQL语法），或者我必须重新发明轮？
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.
其实，下面的Ryan Wright的答案说明了我w蚂蚁结果实现，除了我的问题是具体关于我如何通过使用现有的.NET Framework实际使用的.NET Framework框架来实现的事实，而不是重新创建轮子和自己编写了数千条不经过测试的代码来做类似的事情。
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.
这里也是一个例子。再次注意，没有ORM生成的代码。
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);
}
名称价格 来自预期的查询？
可以通过反映获取名称，方法是查询自定义的 DatabaseMapping 属性价格属性产品类。
The name can be obtained through reflection by querying the custom DatabaseMapping attribute of Price property of Product class.
来自预期查询的名称 @from 和 @to / strong>
Where do names @from and @to come from in the expected query?
这些名称是表达式参数的实际名称。
Those names are the actual names of the parameters of the expression.
其中之间的来自预期的查询？
这是一个可能的结果二进制表达式也许EF或LINQ to SQL将代替之间的...和语句，坚持使用 [Price]> = @from和[Price]< = @to 。这也没关系，因为结果在逻辑上是一样的（我没有提到性能）。
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).
为什么没有code>其中在预期的查询中？
Why there is no where in the expected query?
因为在 Expression 必须有一个其中关键字。也许实际的表达式只是其中一个表达式，稍后将与二进制运算符相结合，构建一个较大的查询，使用，其中。
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.
推荐答案
简短的回答似乎是您不能使用EF或LINQ to SQL的部分作为翻译的快捷方式。您至少需要一个 ObjectContext 的子类来获取内部受保护的 QueryProvider 属性，这意味着创建上下文的所有开销，包括所有元数据等等。
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.
假设你可以确定，要获得部分SQL查询，例如，只有 WHERE 子句你基本上需要查询提供者，并调用 IQueryProvider.CreateQuery（）正如LINQ在执行可查询的地方中所做的那样。要获得更完整的查询，您可以使用 ObjectQuery.ToTraceString（）。
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().
至于发生这种情况， LINQ提供商基础知识通常说明
As to where this happens, LINQ provider basics states generally that
IQueryProvider返回对由LINQ框架传递的构造的表达式树的IQueryable引用，该框架用于进一步电话。一般来说，每个查询块都转换成一堆方法调用。对于每个方法调用，都有一些表达式涉及。在创建我们的提供者的时候，在方法IQueryProvider.CreateQuery中，我们运行这些表达式并填写一个过滤器对象，该对象在IQueryProvider.Execute方法中用于针对数据存储运行查询
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
和
查询可以通过两种方式执行，无论是通过实现Query类中的GetEnumerator方法（在IEnumerable接口中定义）（它继承自IQueryable）;或者它可以由LINQ运行时直接执行
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
在调试器下检查EF是前者。
Checking EF under the debugger it's the former.
如果您不想完全重新发明轮子，而且EF和LINQ to SQL都不是选项，也许这一系列的文章将有助于：
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
这篇关于如何将表达式树转换为部分SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
