Subsonic : Same column name different tables

Question

Hi,

I have a query where I need to do a "Where" clause for two different columns in two different tables but subsonic creates the same parametrized parameter name for both which is causing an issue. How can I fix this?

string _RawSql = new Select()
  .From(Tables.Table1)
  .InnerJoin(Tables.Table2)
  .InnerJoin(Table3.SidColumn, Table2.Table3SidColumn)
  .Where(Table1.SidColumn).IsEqualTo(2)
  .And(Table3.SidColumn).IsEqualTo(1)
  .BuildSqlStatement();

The query this is creating is

SELECT ....
FROM [dbo].[Table1]
INNER JOIN [dbo].[Table2] ON [dbo].[Table1].[Table2Sid] = [dbo].[Table2].[Sid]
INNER JOIN [dbo].[Table3] ON [dbo].[Table2].[Table3Sid] = [dbo].[Table3].[Sid]
WHERE [dbo].[Table1].[Sid] = @Sid
AND [dbo].[Table3].[Sid] = @Sid

Note that in the last two lines its using @Sid for both Table1 and Table3. How go I do it so it uses @Sid0 and @Sid1?

Any help would be appreciated. Thanks

Answer 1

What version are you using? In 2.2 You can use the TableColumn object to get around this (it may be the same for 2.1 as well. So instead of using the struct, as you're doing, you can use the object (Table2.SidColumn).

If push comes to shove - you can override everything with a string - so in your case you could use "Table1.Sid" and "Table2.Sid" right in the Where() method.

Answer 2

Thanks for the response, I really appreciate it. I am using 2.1 I am already using TableColumn. Below is the c# subsonic code...

.Where(Table1.SidColumn).IsEqualTo(2)  
.And(Table3.SidColumn).IsEqualTo(1)

which creates the following sql when viewed in sql profiler

WHERE [dbo].[Table1].[Sid] = @Sid
AND [dbo].[Table3].[Sid] = @Sid

Could you please show me how can I replace these lines with the way you are suggesting? I would really rather not use literal "Table2.Sid = 2"

Answer 3

I haven't been able to confirm this, but perhaps the problem only happens with literals? (not sure if your sample code is like that for brevity's sake)

int table1SidColumnValue = 2;
int table3SidColumnValue = 1;

.Where(Table1.SidColumn).IsEqualTo(table1SidColumnValue)
.And(Table3.SidColumn).IsEqualTo(table2SidColumnValue)

I remember seeing a problem with this when using multiple .In() clauses with literal values, not sure if that applies to your problem though.

Answer 4

ranmore, the issue is same with variables or with constants.

I have even tried

.Where("Table1.Sid").IsEqualTo(2)  
.And("Table3.Sid").IsEqualTo(1)

This creates the query as

WHERE Table1.Sid = @Table1.Sid0
AND Table3.Sid = @Table3.Sid1

I finally get different parametrized vars in this case but now SQL Server complains because it does not like . in the parametrized var names.

I have no clue how to perform a join with 2 where clauses for 2 different tables!