Table specification in the where clause in SubSonic

I'm having a bit of a hassle with something that should be rather straigt forward, so either I'm getting crosseyed (that's actually not unlikely) or there is something strange going on.

I'm joining two tables and filtering on the join table, however SubSonic ignores my table specification in the Where statement.

If I switch the tables (join Users to UserConnections) things works right, but I will be adding more tables to the join, so I need it in this order.

This is the SQL query I'm trying to reproduce in SubSonic:

select u.Username
from [Users] u
Inner Join UserConnections uc on uc.TargetID = u.UserID
where uc.UserID = 55

This is the SubSonic query:

var qry = new Select(UsersTable.UsernameColumn)
 .From<User>()
 .InnerJoin<UserConnection>("UserID", "TargetID")
 .Where( UserConnectionsTable.UserIDColumn )
 .IsEqualTo(55);

And this is query generated by SubSonic:

SELECT [Users].[Username]
FROM [Users]
INNER JOIN [UserConnections] ON [Users].[UserID] = [UserConnections].[TargetID]
WHERE [Users].[UserID] = @0

Note that in the where clause, 'Users' table is specified, not the 'UserConnectionsTable'

Any help would be appreciated. Thanks

ps. I'm using v.3

ansaurus

tags:

views:

answers:

Table specification in the where clause in SubSonic

related questions