FROM [dbo].[Employee]
WHERE [dbo].[Employee].[ID] IN (SELECT [dbo].[Employee_Role].[EmployeeID]
FROM [dbo].[Employee_Role]
WHERE [dbo].[Employee_Role].[RoleID] = @RoleID0
)
AND [dbo].[Employee].[ID] IN (SELECT [dbo].[Employee_Role].[EmployeeID]
FROM [dbo].[Employee_Role]
WHERE [dbo].[Employee_Role].[RoleID] = @RoleID0
)
}
Notice how both subselects get the same parameter reference, @RoleID0, instead of @RoleID0 and @RoleID1.
Consider this example that references two tables, Employee and Employee_Role (linking table)
SqlQuery q2 = new Select() .From(Employee.Schema) .Where(Employee.IDColumn).In( new Select(Employee_Role.EmployeeIDColumn) .From(Employee_Role.Schema) .Where(Employee_Role.RoleIDColumn).IsEqualTo(3)) .And(Employee.IDColumn).In( new Select(Employee_Role.EmployeeIDColumn) .From(Employee_Role.Schema) .Where(Employee_Role.RoleIDColumn).IsEqualTo(1));
The resulting SQL looks like this:
{SELECT [dbo].[Employee].[ID], [dbo].[Employee].[LastName], [dbo].[Employee].[FirstName], [dbo].[Employee].[MidName], [dbo].[Employee].[CommonName], [dbo].[Employee].[DepartmentNumber], [dbo].[Employee].[EmployeeNumber], [dbo].[Employee].[ManagerEmployeeNumber], [dbo].[Employee].[NetworkName], [dbo].[Employee].[Email], [dbo].[Employee].[HireDate], [dbo].[Employee].[EmploymentStatusID]
FROM [dbo].[Employee] WHERE [dbo].[Employee].[ID] IN (SELECT [dbo].[Employee_Role].[EmployeeID] FROM [dbo].[Employee_Role] WHERE [dbo].[Employee_Role].[RoleID] = @RoleID0 ) AND [dbo].[Employee].[ID] IN (SELECT [dbo].[Employee_Role].[EmployeeID] FROM [dbo].[Employee_Role] WHERE [dbo].[Employee_Role].[RoleID] = @RoleID0 ) }
Notice how both subselects get the same parameter reference, @RoleID0, instead of @RoleID0 and @RoleID1.