SubSonic.SqlQuery incorrectly generated when using Where()

subsonic

I am using version 2.1 of SubSonic. I am trying to build to build a relatively simple query where I get a list of Roles for a User using UserId as a parameter. (UserId is a string…)

SubSonic.SqlQuery sel = new SubSonic.Select().From(Data.Tables.Role).InnerJoin(Data.Tables.UserRole, Data.UserRole.Columns.RoleId, Data.Tables.Role, Data.Role.Columns.Id).InnerJoin(Data.Tables.User, Data.User.Columns.Id, Data.Tables.UserRole, Data.UserRole.Columns.UserId).Where("[dbo].[User].[Id]").IsEqualTo(userId);

this generates the query

SELECT [dbo].[Role].[Id], [dbo].[Role].[PID], [dbo].[Role].[Name]
FROM [dbo].[Role]
INNER JOIN [dbo].[UserRole] ON [dbo].[Role].[Id] = [dbo].[UserRole].[RoleId]
INNER JOIN [dbo].[User] ON [dbo].[UserRole].[UserId] = [dbo].[User].[Id]
WHERE [dbo].[User].[Id] = @[dbo].[User].[Id]0

which fails. If I replace the Where with .Where(Data.User.Columns.Id) this generates the query

SELECT [dbo].[Role].[Id], [dbo].[Role].[PID], [dbo].[Role].[Name]
FROM [dbo].[Role]
INNER JOIN [dbo].[UserRole] ON [dbo].[Role].[Id] = [dbo].[UserRole].[RoleId]
INNER JOIN [dbo].[User] ON [dbo].[UserRole].[UserId] = [dbo].[User].[Id]
WHERE [dbo].[Role].[Id] = @Id0

which uses the Role table in the Where clause instead of the User table.

Is this a bug, or am I doing something incorrectly? What would be the correct way to do this? Thanks.

Best Answer

This is fixed in version 2.2 - I would suggest upgrading.