InnerJoin with Where Expression trouble

Question

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.

Answer 1

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