如何将表达式树转换为部分 SQL 查询? [英] How to convert an expression tree to a partial SQL query?

查看:35
本文介绍了如何将表达式树转换为部分 SQL 查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

当 EF 或 LINQ to SQL 运行查询时,它:

When EF or LINQ to SQL runs a query, it:

  1. 根据代码构建表达式树,
  2. 将表达式树转换为 SQL 查询,
  3. 执行查询,从数据库中获取原始结果并将它们转换为应用程序要使用的结果.

查看堆栈跟踪,我无法弄清楚第二部分发生在哪里.

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.

实际上,下面瑞安赖特的回答完美地说明了我想要实现的结果,除了我的问题是专门关于如何通过使用 EF 和实际使用的 .NET Framework 的现有机制来实现的LINQ to SQL,而不必重新发明轮子,自己编写数千行未经测试的代码来做类似的事情.

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);
}

名称 Price 在预期查询中的来源?

Where does the name Price come from in the expected query?

名称可以通过查询Product类的Price属性的自定义DatabaseMapping属性反射得到.

The name can be obtained through reflection by querying the custom DatabaseMapping attribute of Price property of Product class.

名称 @from@to 在预期查询中来自哪里?

Where do names @from and @to come from in the expected query?

这些名称是表达式参数的实际名称.

Those names are the actual names of the parameters of the expression.

between ... and 在预期查询中来自哪里?

Where does between … and come from in the expected query?

这是二进制表达式的可能结果.也许 EF 或 LINQ to SQL 会坚持使用 [Price] >= @from 和 [Price] <= @to 而不是 between ... and 语句.也没关系,这并不重要,因为结果在逻辑上是相同的(我没有提到性能).

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).

为什么预期查询中没有where?

因为Expression 中没有任何内容表明必须有一个where 关键字.也许实际的表达式只是稍后将与二元运算符组合以构建更大的查询以前置 where 的表达式之一.

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 的子类才能访问 internal protected 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 在 Queryable.Where.要获得更完整的查询,您可以使用 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类(继承自IQueryable)中实现GetEnumerator方法(在IEnumerable接口中定义);或者直接由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:

以下是创建查询提供程序的一些来源,您可能需要承担更多繁重的工作来实现您想要的:

Here are some sources for creating a query provider that probably involve much more heavy lifting on your part to implement what you want:

这篇关于如何将表达式树转换为部分 SQL 查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆