InnerJoin 与Where 表达式问题

发布于 2024-08-11 16:27:47 字数 658 浏览 4 评论 0原文

我正在使用 Subsonic 2.1，我需要进行内连接并使用 where 表达式，但我不断收到错误：

Must declare the scalar variable "@Partner"

这是我的代码：

Dim ds As DataSet = UWP.Model.DB.Select("TOP 30 FirstName, LastName, EmailAddress, CustomerRowID, CompanyName")
 .From("Customer").InnerJoin("Partner")
     .Where("Partner.PartnerID").IsEqualTo("Customer.PartnerID")
 .WhereExpression("FirstName").Like("%" & SearchTerm & "%")
     .Or("LastName").Like("%" & SearchTerm & "%")
     .Or("EmailAddress").Like("%" & SearchTerm & "%")
 .CloseExpression()
 .ExecuteDataSet()

我尝试过以 10 种不同的方式重新排列这个东西，但似乎无法得到它正确的。

原文

I am using Subsonic 2.1 and I need to do an innerjoin and use a where expression and I keep getting the error:

Must declare the scalar variable "@Partner"

Here is my code:

Dim ds As DataSet = UWP.Model.DB.Select("TOP 30 FirstName, LastName, EmailAddress, CustomerRowID, CompanyName")
 .From("Customer").InnerJoin("Partner")
     .Where("Partner.PartnerID").IsEqualTo("Customer.PartnerID")
 .WhereExpression("FirstName").Like("%" & SearchTerm & "%")
     .Or("LastName").Like("%" & SearchTerm & "%")
     .Or("EmailAddress").Like("%" & SearchTerm & "%")
 .CloseExpression()
 .ExecuteDataSet()

I have tried re-arranging this thing about 10 different ways and just can't seem to get it right.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

倾听心声的旋律 2024-08-18 16:27:47

您在 where 子句中而不是在联接本身中指定联接条件是否有原因？

我重写了您的查询以利用强类型列名，您应该尽可能这样做，因为您可以在编译时而不是运行时捕获问题。另外，使用 .ContainsString() 而不是串联的繁文缛节，以获得更好的可读性。

Dim ds As DataSet = UWP.Model.DB.Select("TOP 30 FirstName, LastName, EmailAddress, CustomerRowID, CompanyName")
 .From(Customer.Schema)
  .InnerJoin(Partner.PartnerIDColumn, Customer.PartnerIDColumn)
  .Where(Customer.FirstNameColumn).ContainsString(SearchTerm)
  .Or(Customer.LastNameColumn).ContainsString(SearchTerm)
  .Or(Customer.EmailAddressColumn).ContainsString(SearchTerm)
 .ExecuteDataSet()

或者要使用原始代码，只需使用内部联接的四个字符串重载并指定其中的列。我认为当你并不真正需要时，你会因为尝试在 where 子句中进行连接而被绊倒。

Dim ds As DataSet = UWP.Model.DB.Select("TOP 30 FirstName, LastName, EmailAddress, CustomerRowID, CompanyName")
 .From("Customer")
 .InnerJoin("Partner","PartnerID","Customer","PartnerID")
 .Where("FirstName").Like("%" & SearchTerm & "%")
     .Or("LastName").Like("%" & SearchTerm & "%")
     .Or("EmailAddress").Like("%" & SearchTerm & "%")
 .ExecuteDataSet()

Is there a reason you specify the join criteria in the where clause instead of in the join itself?

I re-wrote your query to take advantage of strongly typed column names, which you should do whenever possible because you can catch problems at compile time instead of run time. Also, use .ContainsString() instead of that concatenated gobblydegook for better readability.

Dim ds As DataSet = UWP.Model.DB.Select("TOP 30 FirstName, LastName, EmailAddress, CustomerRowID, CompanyName")
 .From(Customer.Schema)
  .InnerJoin(Partner.PartnerIDColumn, Customer.PartnerIDColumn)
  .Where(Customer.FirstNameColumn).ContainsString(SearchTerm)
  .Or(Customer.LastNameColumn).ContainsString(SearchTerm)
  .Or(Customer.EmailAddressColumn).ContainsString(SearchTerm)
 .ExecuteDataSet()

Or to use your original code, just use the four string overload of inner join and specify the columns there. I think you're getting tripped up by trying to do the join in the where clause when you don't really need to.

Dim ds As DataSet = UWP.Model.DB.Select("TOP 30 FirstName, LastName, EmailAddress, CustomerRowID, CompanyName")
 .From("Customer")
 .InnerJoin("Partner","PartnerID","Customer","PartnerID")
 .Where("FirstName").Like("%" & SearchTerm & "%")
     .Or("LastName").Like("%" & SearchTerm & "%")
     .Or("EmailAddress").Like("%" & SearchTerm & "%")
 .ExecuteDataSet()

回复收藏 0 原文

甲如呢乙后呢 2024-08-18 16:27:47

.InnerJoin("合作伙伴 p")..Where("p.PartnerID") ？

我以前从未见过这个，所以我真的只是猜测。所以如果我只是愚蠢的话请忽略我:p

回复收藏 0 原文

内心激荡 2024-08-18 16:27:47

有两种方法可以进行亚音速内连接。第一种方法是指定要链接的列，例如：

.InnerJoin(Partner.PartnerIDColumn, Customer.PartnerIDColumn)

如果已在 SubSonic 模型中设置关系，则只需指定表，而无需指定列。看起来这就是您想要做的，但您的语法不太正确。您必须使用泛型，如下所示：

.InnerJoin<Partner>()

第二种方法是更好的选择，因为它更具可读性。但是，它仅在尝试连接您在 From() 函数中指定的表时才有效。在这种情况下它应该对你有用。

There are 2 ways to do an inner join with subsonic. The first way is to specify the columns to link on, such as:

.InnerJoin(Partner.PartnerIDColumn, Customer.PartnerIDColumn)

If the relationships is already setup in your SubSonic model, you can just specify the table, without having to specify the columns. It looks like this is what you were trying to do, but you didn't have the syntax quite right. You must use generics, like this:

.InnerJoin<Partner>()

The second way is preferrable because it is more readable. However, it only works when trying to join with the table that you specify in the From() function. It should work for you in this situation.

回复收藏 0 原文

~没有更多了~