亚音速2连接在多个列上 [英] subsonic 2 join on multiple columns

问题描述

我想将以下语句转移到SubSonic 2.2

I want to transfer the following statement to SubSonic 2.2

    SELECT b.*
    FROM tableA a
      INNER JOIN tableB b
        ON (a.year = b.year AND a.month = b.monath AND a.userid = b.userid);

我的问题是SubSonic的SqlQuery.LeftInnerJoin()命令没有重载，该重载占用了不止一列.

My problem is that SubSonic's SqlQuery.LeftInnerJoin() command has no overload which takes more than one column.

由于只能使用where子句重写任何联接，因此我在sql中执行了以下操作:

Since any join can be rewritten only using where clauses, I did the following in my sql:

    SELECT b.*
    FROM tableA a, tableB b
    WHERE a.year = b.year
      AND a.month = b.month
      AND a.userid = b.userid

应该提供相同的结果(实际上，至少对于mysql而言，这些语句在逻辑上绝对没有区别).

which should deliver the same result (in fact, at least for mysql, there is logically absolutely no difference between these statements).

但是我也因为将"IsEqualTo(...)"成员非常聪明地发现我的参数是一个字符串并将其放在引号中而不得不将其转移到亚音速.

But I also got stuck transfering this to subsonic because the "IsEqualTo(...)" member is smart enough to figure out that my parameter is a string and puts it into quotes.

DB.Select("TableB.*")
  .From<TableA>()
  .From<TableB>()
  .Where(TableA.YearColumn).IsEqualTo("TableB.Year")
  .And(TableA.MonthColumn).IsEqualTo("TableB.Month")
  .And(TableA.UseridColumn).IsEqualTo("TableB.UserId")

(我在设置IsEqualTo参数时尝试了不同的方法)

(I tried different ways in setting the IsEqualTo parameter)

IsEqualTo(TableB.YearColumn)
IsEqualTo(TableB.YearColumn.QualifiedName)

该参数都解释为

TableA.Year = 'TableB.Year'

或者我收到SqlQueryException.

or I get a SqlQueryException.

有人可以告诉我如何使用亚音速(第一个-JOIN或第二个)进行此查询吗?谢谢

Can somebody tell me how to do this query with subsonic (Either the first - with JOIN or the second one)? Thanks

推荐答案

开箱即用SubSonic 2，您将无法做到.

With SubSonic 2 out of the box you can't.

这就是说，您有以下选择:

This said, you have the following alternatives:

扩展SubSonic

如果您已经熟悉SubSonic，则可以考虑向SubSonic本身添加多列联接.

If you're already familiar with SubSonic, you may consider to add multi-column joins to SubSonic itself.

使用视图，存储过程，表函数

如果不想弄乱SubSonics代码，请在sql server中使用视图，存储过程和/或表函数. SubSonic使得从视图和存储过程访问数据变得很容易.

If you do not want to mess with SubSonics code, use views, stored procedures and/or table functions within sql server. SubSonic makes it easy to access data from views and stored procedures.

使用InlineQuery

InlineQuery 允许您执行任何sql-如果可以选择在其中包含裸露的sql代码.

InlineQuery allows you to execute any sql - if it is an option to have bare sql in your code.

使用InlineQuery的变通办法

如果您绝对要使用SubSonic创建查询，则可以尝试以下操作:

If you absolutely want to create your query with SubSonic, you can try this:

SqlQuery q = DB.Select()
  .From<TableA>()
  .CrossJoin<TableB>()
  .Where(TableA.YearColumn).IsEqualTo(0)
  .And(TableA.MonthColumn).IsEqualTo(0)
  .And(TableA.UseridColumn).IsEqualTo(0);

构建SQL语句，并替换参数名称:

Build the SQL statement, and replace the parameter names:

string s = q.BuildSqlStatement();
s = s.Replace(q.Constraints[0].ParameterName, TableB.YearColumn.QualifiedName);
s = s.Replace(q.Constraints[1].ParameterName, TableB.MonthColumn.QualifiedName);
s = s.Replace(q.Constraints[2].ParameterName, TableB.UserIdColumn.QualifiedName);

然后将s与InlineQuery一起使用.

Then use s with an InlineQuery.

这篇关于亚音速2连接在多个列上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！