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

.InnerJoin("Partner p")..Where("p.PartnerID") ?

I have never seen this before, so i'm really just guessing. So ignore me if i'm just being stupid :p

Answer 2

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

Answer 3

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.