亚音速3 ActiveRecord的嵌套查询的NotIn错误? [英] Subsonic 3 ActiveRecord nested select for NotIn bug?
问题描述
我有以下的亚音速3.0的查询,其中包含嵌套NotIn查询:
I have the following Subsonic 3.0 query, which contains a nested NotIn query:
public List<Order> GetRandomOrdersForNoReason(int shopId, int typeId)
{
// build query
var q = new SubSonic.Query.Select().Top("1")
.From("Order")
.Where("ShopId")
.IsEqualTo(shopId)
.And(OrderTable.CustomerId).NotIn(
new Subsonic.Query.Select("CustomerId")
.From("Customer")
.Where("TypeId")
.IsNotEqualTo(typeId))
.OrderDesc("NewId()");
// Output query
Debug.WriteLine(q.ToString());
// returned typed list
return q.ExecuteTypedList<Order>();
}
内部查询似乎是不正确的:
The internal query appears to be incorrect:
SELECT TOP 1 *
FROM [Order]
WHERE ShopId = @0 AND CustomerId NOT IN (SELECT CustomerId
FROM [Customer]
WHERE TypeId = @0)
ORDER BY NewId() ASC
您会发现,这两个参数是 @ 0 。我假设参数是枚举(从零开始),为每一个新的选择查询。然而,在这种情况下,两个选择查询是嵌套的,我本来期望的输出有两个参数,一个名为 @ 0 和 @ 1
You'll notice that both parameters are @0. I'm assuming that the parameters are enumerated (starting at zero), for each "new" Select query. However, in this case where the two Select queries are nested, I would have expected the output to have two parameters named @0 and @1.
我的查询基于的 之一是罗布科纳介绍了他的博客作为一个preVIEW,成为亚音速Pakala查询工具3.他的例子是:
My query is based on one that Rob Conery gave on his blog as a preview of the "Pakala" query tool that became Subsonic 3. His example was:
int records = new Select(Northwind.Product.Schema)
.Where("productid")
.In(
new Select("productid").From(Northwind.Product.Schema)
.Where("categoryid").IsEqualTo(5)
)
.GetRecordCount();
有其他人看到这种行为?它是一个bug,或者这是一个错误,或者我的一部分?因为我是新来的亚音速我猜测,这可能是程序员的错误在我的部分,但我想确认如果可能的话。
Has anyone else seen this behavior? Is it a bug, or is this an error or my part? Since I'm new to Subsonic I'm guessing that this probably programmer error on my part but I'd like confirmation if possible.
推荐答案
刚碰到这个完全一样的问题,在最新发布的,所以显然它尚未确定。我尝试切换的条件的顺序(把NotIn条件第一),并且没有的伎俩。下面是新的code样子,它生产参数@ 0和@ 1,而不是@ 0和@ 0:
Just came across this exact same issue in the latest release, so apparently it hasn't been fixed yet. I tried switching the order of the conditions (putting the NotIn condition first) and that did the trick. Here's what the new code looks like, which produced parameters @0 and @1 instead of @0 and @0:
var q = new SubSonic.Query.Select().Top("1")
.From("Order")
.Where(OrderTable.CustomerId).NotIn(
new Subsonic.Query.Select("CustomerId")
.From("Customer")
.Where("TypeId")
.IsNotEqualTo(typeId)
)
.And("ShopId")
.IsEqualTo(shopId)
.OrderDesc("NewId()");
这篇关于亚音速3 ActiveRecord的嵌套查询的NotIn错误?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!