Subsonic：相同的列名称不同的表

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?

我有一个查询,我需要在两个不同的表中为两个不同的列做一个“Where”子句,但是subsonic为两者创建了相同的参数化参数名称,这导致了一个问题。我怎样才能解决这个问题?

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?

请注意,在最后两行中,它使用@Sid作为Table1和Table3。怎么去,所以它使用@ Sid0和@ Sid1?

Any help would be appreciated. Thanks

任何帮助,将不胜感激。谢谢

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

感谢您的回复,我真的很感激。我正在使用2.1我已经在使用TableColumn。下面是c#亚音速代码......

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

which creates the following sql when viewed in sql profiler

在sql profiler中查看时会创建以下sql

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"

你能否告诉我如何用你建议的方式替换这些线?我真的不想使用文字“Table2.Sid = 2”

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

ranmore,问题与变量或常量相同。

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.

在这种情况下我终于获得了不同的参数化变量,但现在SQL Server抱怨因为它不喜欢。在参数化的var名称中。

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

我不知道如何使用2个不同表的2个子句执行连接!

3 个解决方案

#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).

你用的是什么版本?在2.2中您可以使用TableColumn对象来解决这个问题(对于2.1也可能是相同的。因此,您可以使用该对象(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.

如果push推进 - 你可以使用字符串覆盖所有内容 - 所以在你的情况下你可以在Where()方法中使用“Table1.Sid”和“Table2.Sid”。

#2

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.

我记得在使用带有文字值的多个.In()子句时遇到问题,但不确定这是否适用于您的问题。

#3

I'm not sure what version of the code I have but your query produces numbered parameters for me.

我不确定我的代码版本是什么,但您的查询为我生成了编号参数。

If you look at Line 255 of ANSISqlGenerator.cs https://github.com/subsonic/SubSonic-2.0/blob/master/SubSonic/SqlQuery/SqlGenerators/ANSISqlGenerator.cs

如果你看一下ANSISqlGenerator.cs的第255行https://github.com/subsonic/SubSonic-2.0/blob/master/SubSonic/SqlQuery/SqlGenerators/ANSISqlGenerator.cs

c.ParameterName = String.Concat(col.ParameterName, query.Constraints.IndexOf(c));

The where parameters really should have numbers appended to them... maybe pull the latest version?

where参数确实应该附加数字......也许拉最新版本?

#1