亚音速3 ActiveRecord的嵌套查询的NotIn错误？

本文介绍了亚音速3 ActiveRecord的嵌套查询的NotIn错误？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下的亚音速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()");