Column with same name in multiple tables causing problem in SubSonic Select.

Question

Hi folks, there are other question (at least 2 I've seen them) similar to this but I'm not able to solve this using them.

Now the problem: I've 3 table from which I need to select 4 columns only. I'm using InnerJoin and it is working perfectly. Problem starts when I add a Where to this Select. I've a column named "Name" in two tables. If I add simply the

.Where("Name").Like("A%")

It says "... ambiguous column name.."

If I use fully qualified column name (with table prefixed to column name) it says must declare parameter @TABLE_NAME

  SqlQuery sq = new Select(Tables.TableOne + "." + TableOne.Columns.MemberId + 
  " AS MemberId",
  Tables.TableTwo + "." + TableTwo.Columns.Name + " AS MemberName",
  Tables.TableOne + "." + TableOne.Columns.ExpiryOn + " AS MembershipExpiresOn",
  Tables.TableFour + "." + TableFour.Columns.Name + " AS Country")
  .From(DAL.Tables.TableOne)
  .InnerJoin(Tables.TableTwo)
  .InnerJoin(Tables.TableThree)
  .InnerJoin(Tables.TableFour, TableFour.Columns.CountryCode,
  Tables.TableThree, TableThree.Columns.CountryOfBirth).
  sq.Where(Tables.TableTwo + "." + TableTwo.Columns.Name).Like("A%");

I've tried to pass hard-coded string also but nothing works!

Answer 1

I haven't used it ever,

but have your tried to change your last line to:

sq.WhereExpression(Tables.TableTwo + "." + TableTwo.Columns.Name + " LIKE 'A%');

Answer 2

Does the following query work, I'm assuming you're using 2.2:

SqlQuery sq = new Select(TableOne.Columns.MemberId + " AS MemberId",
    TableTwo.Columns.Name + " AS MemberName",
    TableOne.Columns.ExpiryOn + " AS MembershipExpiresOn",
    TableFour.Columns.Name + " AS Country")
  .From(TableOne.Schema)
  .InnerJoin(TableTwo.Schema)
  .InnerJoin(TableThree.Schema)
  .InnerJoin(TableFour.Schema)
  .Where(TableTwo.Columns.Name).Like("A%");

Answer 3

If you pass in the column object to the Where statement SubSonic will use it's fully qualified name instead of building a string. You can find the column on the object as a static property, so in this case if you have an object called "TableOne" you can use "TableOne.NameColumn" and pass that into the Where():

...
sq.Where(TableTwo.NameColumn).Like("A%");

Answer 4

I have the same problem.

When I tried to use: sq.Where("TableTwo.Name").Like("A%")
I get an Error which says: 'Incorrect syntax near '.'. Must declare the scalar variable "@TableTwo" '

The reason it does not work seems to be the parameter Subsonic creates in the SQLquery constraint is named as '@TableTwo.Name' and SQL errors if the period is included in a variable name. I tried to change the parameter name but it cannot be changed. Could any help how to solve this problem.