subsonic 2 join on multiple columns

Question

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

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

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

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

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

(I tried different ways in setting the IsEqualTo parameter)

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

Either the parameter is interpreted as

TableA.Year = 'TableB.Year'

or I get a SqlQueryException.

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

Answer 1

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

This said, you have the following alternatives:

Extend SubSonic

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

Use views, Stored procedures, table functions

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.

Use an InlineQuery

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

Ugly workaround with InlineQuery

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

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

Then use s with an InlineQuery.