其中ORM支持此 [英] Which ORM Supports this

问题描述

我有查询的可选部分需要被在特定条件执行。下面是示例代码：

I have an optional part of query that needs to be executed on a certain condition. Here is the example code:

int cat = 1;
int UserID = 12;
string qry = "select * from articles";
if(cat > 0)
     qry += " where categoryID = " + cat;
if(UserID > 0)
     qry += " AND userid = " + UserID;  //The AND may be a WHERE if first condition is false

正如你可以看到我有一个，如果语句中的查询。我目前使用实体框架，它不支持这种情景。是否有一个ORM在那里支持此？

As you can see I have an if statement in the query. i am currently using Entity Framework and it does not support this kind of scenario. Is there an ORM out there that support this?

修改
我试图假人下来查询。但我有大约20IF语句和querys很长

Edit I tried to dummy down the query. But I have about 20 "IF" statements and the querys are very long.

我看是的奥姆斯：

• NHibernate的


• LLBLGEN


• 亚音速

我愿意接受任何的ORM。谢谢

I am open to any ORM. Thanks

推荐答案

因为它已经讲到这里，LINQ可以通过简单地添加更多的标准，它扩展任何查询。

As it was already mentioned here, LINQ allows to extend any query by simply adding more criteria to it.

var query = 
  from x in xs 
  where x==1
  select x;

if (mustAddCriteria1)
  query = 
    from x in query 
    where ... // criteria 1
    select x;

if (mustAddCriteria2)
  query = 
    from x in query 
    where ... // criteria 2
    select x;

等。这种方法的工作方式完美。但可能的是，你知道，LINQ查询的编译是相当昂贵的：例如实体框架可以编译只有每秒约500相对简单的查询（例如参见 ORMBattle.NET ）。

在另一方面，许多ORM工具支持编译查询：

On the other hand, many ORM tools support compiled queries:

• 您传递的IQueryable 实例有些编译方法，并获得了代表允许更快以后执行它，因为没有重新编译可能发生在这种情况下。

• You pass an IQueryable instance to some Compile method, and get a delegate allowing to execute it much faster later, because no recompilation would occur in this case.

但是，如果我们想在这里尝试使用这种方法，我们立即注意到我们的查询实际上是动态：的IQueryable 我们执行每一次可能与以前的不同。查询有部分是由外部参数的值确定存在。

But if we'd try to use this approach here, we immediately notice that our query is actually dynamic: IQueryable we execute each time might differ from the previous one. Presence of query parts there is determined by values of external parameters.

所以，我们可以执行这样的查询，因为没有如编译显式缓存？

So can we execute such queries as compiled without e.g. explicit caching?

DataObjects.Net 4支持所谓的布尔分支功能。这意味着查询编译过程中的任何常量布尔表达式的值与实际值被注入到SQL查询作为真正的布尔常量（即不作为参数值或利用参数的表达式）。

DataObjects.Net 4 support so-called "boolean branching" feature. It implies any constant boolean expression is evaluated during query compilation and its actual value is injected into SQL query as true boolean constant (i.e. not as parameter value or as an expression utilizing parameters).

这个功能允许生成不同的查询依赖性计划与减轻这种布尔表达式的值。例如。这个代码：

This feature allows to generate different query plans dependently on values of such boolean expressions with ease. E.g. this code:

  int all = new Random().Next(2);
  var query = 
    from c in Query<Customer>.All
    where all!=0 || c.Id=="ALFKI"
    select c;

将使用两种不同的SQL查询，从而执行 - 两个不同的查询计划：

will be executed using two different SQL queries, and thus - two different query plans:

• 基于索引的查询计划寻求（非常快），如果0


• 基于索引的所有==查询计划扫描（相当慢），如果所有= 0

情况下，当所有的== NULL，SQL查询：

Case when all==null, SQL query:

SELECT
  [a].[CustomerId],
  111 AS [TypeId] ,
  [a].[CompanyName]
FROM
  [dbo].[Customers] [a]
WHERE(( CAST( 0 AS bit ) <> 0 ) OR( [a].[CustomerId] = 'ALFKI' ) );

情况下，当所有的== NULL，查询计划：

Case when all==null, query plan:

|--Compute Scalar(DEFINE:([Expr1002]=(111)))
   |--Clustered Index Seek(OBJECT:([DO40-Tests].[dbo].[Customers].[PK_Customer] AS [a]), SEEK:([a].[CustomerId]=N'ALFKI') ORDERED FORWARD)

第二种情况（！当所有= NULL），SQL查询：

Second case (when all!=null), SQL query:

SELECT
  [a].[CustomerId],
  111 AS [TypeId] ,
  [a].[CompanyName]
FROM
  [dbo].[Customers] [a]
WHERE(( CAST( 1 AS bit ) <> 0 ) OR( [a].[CustomerId] = 'ALFKI' ) );
-- Notice the ^ value is changed!

第二种情况（！当所有= NULL），查询计划：

Second case (when all!=null), query plan:

|--Compute Scalar(DEFINE:([Expr1002]=(111)))
   |--Clustered Index Scan(OBJECT:([DO40-Tests].[dbo].[Customers].[PK_Customer] AS [a]))
-- There is index scan instead of index seek!

请注意，几乎任何其他的ORM将汇编这一个查询使用整型参数：

Note that almost any other ORM would compile this to a query utilizing integer parameter:

SELECT
  [a].[CustomerId],
  111 AS [TypeId] ,
  [a].[CompanyName]
FROM
  [dbo].[Customers] [a]
WHERE(( @p <> 0 ) OR ( [a].[CustomerId] = 'ALFKI' ) );
--      ^^ parameter is used here

由于SQL服务器（以及大部分的数据库）生成查询计划的一个版本一个特定的查询，但在这种情况下，唯一的选择 - 生成索引扫描一个计划：

Since SQL Server (as well as most of databases) generates a single version of query plan for a particular query, it has the only option in this case - generate a plan with index scan:

|--Compute Scalar(DEFINE:([Expr1002]=(111)))
   |--Clustered Index Scan(OBJECT:([DO40-Tests].[dbo].[Customers].[PK_Customer] AS [a]), WHERE:(CONVERT(bit,[@p],0)<>(0) OR [DO40-Tests].[dbo].[Customers].[CustomerId] as [a].[CustomerId]=N'ALFKI'))

好吧，这是一个快此功能的有用性的解释。让我们回到现在回到你的情况。

Ok, that was a "quick" explanation of usefulness of this feature. Let's return back to your case now.

布尔分支可以实现它非常简单的方式：

Boolean branching allows to implement it in very simple fashion:

var categoryId = 1;
var userId = 1;

var query = 
  from product in Query<Product>.All
  let skipCategoryCriteria = !(categoryId > 0)
  let skipUserCriteria = !(userId > 0)
  where skipCategoryCriteria ? true : product.Category.Id==categoryId
  where skipUserCriteria ? true : 
  (
    from order in Query<Order>.All
    from detail in order.OrderDetails
    where detail.Product==product
    select true
  ).Any()
  select product;

这个例子跟你不同，但它说明了想法。我用不同的模式主要是为了能够测试这个（我的例子是基于OM罗斯文模型）

The example differs from yours, but it illustrates the idea. I used different model mainly to be able to test this (my example is based om Northwind model).

这查询是：

• 不是一个动态查询，所以你可以放心地将其传递给 Query.Execute（...）方法得到它执行作为编译的查询。


• 不过它的每一个执行会导致同样的结果，如果这将与追加做才能的IQueryable

• Not a dynamic query, so you can safely pass it to Query.Execute(...) method to get it executed as compiled query.
• Nevertheless each its execution will lead to the same result as if this would be done with "appending" to IQueryable.

这篇关于其中ORM支持此的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！