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

Question

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!

score 4 · Accepted Answer · answered Jun 30 '09 at 19:21

4

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%");

answered Jun 30 '09 at 19:21

thanks for answer Rob. Firstly it is not TableTwo.NameColumn but TableTwo.Columns.NameColumn. Secondly, when I pass sq.Where(TableTwo.NameColumn) it uses TableFour.Name in the generated SQL Script. It is working but wrongly :( [I've moved to SubSonic 2.2] – TheVillageIdiot Jul 01 '09 at 02:34

Adam Cooper · Answer 2 · 2009-06-30T14:25:57.427

1

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%");

edited Jun 30 '09 at 14:25

answered Jun 30 '09 at 08:42

Adam Cooper

8,077
2
33
51

Yes as posted somewhere on this site-itself by Rob Conery the second thing sould work with SubSonic 2.2 but it is not working. Using column Alias is also not working which is strange – TheVillageIdiot Jun 30 '09 at 09:08
@Adam: .Where("MemberName").Like("A%") will propably not work, because SQL Selects cannot use the aliases in a where clause. This does not work in sql: "SELECT sum * quantity as result WHERE result > 5". You have to write "SELECT sum * quantity as result HAVING result > 5" or "SELECT sum * quantity as result WHERE sum * quantity > 5" instead. – Jürgen Steinblock Jun 30 '09 at 12:30
@SchlaWiener: Thanks, I always forget that until sql reminds me. – Adam Cooper Jun 30 '09 at 13:06

score 0 · Answer 3 · answered Jun 30 '09 at 07:25

0

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%');

answered Jun 30 '09 at 07:25

Jürgen Steinblock

30,746
24
119
189

WhereExpression is for combining multiple conditions, it won't have any effect in this case. – Adam Cooper Jun 30 '09 at 08:44

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

3 Answers3

Linked