Nhibernate CreateSQLQuery error - SetParameterList Incorrect syntax near ','

Question

Error:

System.Data.SqlClient.SqlException: Incorrect syntax near ','

Code:

IQuery permissionTypes;

if (regionIds != null && regionIds.Count > 0)
{
    permissionTypes =  session.CreateSQLQuery(
       @"SELECT DISTINCT PT.PermissionType FROM AspNetUsers AU 
       INNER JOIN AspNetUserRoles AR ON AU.Id= AR.UserId
       INNER JOIN dbo.AspNetRoles ANR ON ANR.Id = AR.RoleId
       INNER JOIN [dbo].[tbl_RolePermissionTypeAssoc] RPT ON RPT.FK_RoleId  = AR.RoleId
       INNER JOIN tbl_PermissionType PT ON RPT.FK_permissionTypeId=PT.Id 
       WHERE AU.ID=:userId AND ANR.RegionId=:regionId")
       .SetParameter("userId", userId).SetParameterList("regionId", regionIds);
}
else
{
    permissionTypes =  session.CreateSQLQuery(
       @"SELECT DISTINCT PT.PermissionType FROM AspNetUsers AU 
       INNER JOIN AspNetUserRoles AR ON AU.Id= AR.UserId
       INNER JOIN dbo.AspNetRoles ANR ON ANR.Id = AR.RoleId
       INNER JOIN [dbo].[tbl_RolePermissionTypeAssoc] RPT ON RPT.FK_RoleId  = AR.RoleId
       INNER JOIN tbl_PermissionType PT ON RPT.FK_permissionTypeId=PT.Id 
       WHERE AU.ID=:userId AND ANR.RegionId=:regionId")
       .SetParameter("userId", userId);
}

return permissionTypes.List<string>();

Generated query :

SELECT DISTINCT PT.PermissionType 
FROM AspNetUsers AU 
INNER JOIN AspNetUserRoles AR ON AU.Id = AR.UserId
INNER JOIN dbo.AspNetRoles ANR ON ANR.Id = AR.RoleId
INNER JOIN [dbo].[tbl_RolePermissionTypeAssoc] RPT ON RPT.FK_RoleId  = AR.RoleId
INNER JOIN tbl_PermissionType PT ON RPT.FK_permissionTypeId = PT.Id 
WHERE AU.ID = @p0 AND ANR.RegionId = @p1, @p2

Answer 1

The point here is the = sign, it cannot be used for more arguments. We need IN operator

resluting sql contains

AND ANR.RegionId = @p1, @p2

and this coma is reported in the error. We need:

AND ANR.RegionId IN (@p1, @p2)

if part

session.CreateSQLQuery(
    @"SELECT DISTINCT PT.PermissionType FROM AspNetUsers AU 
    INNER JOIN AspNetUserRoles AR ON AU.Id= AR.UserId
    INNER JOIN dbo.AspNetRoles ANR ON ANR.Id = AR.RoleId
    INNER JOIN [dbo].[tbl_RolePermissionTypeAssoc] RPT 
            ON RPT.FK_RoleId  = AR.RoleId
    INNER JOIN tbl_PermissionType PT ON RPT.FK_permissionTypeId=PT.Id 
    // this is wrong 
    // WHERE AU.ID=:userId AND ANR.RegionId=:regionId 
    // we need IN 
    WHERE AU.ID=:userId AND ANR.RegionId IN (:regionId) 
    ")
    .SetParameter("userId", userId)
    .SetParameterList("regionId", regionIds);

And also in the else part we are using params which are not passed

else part:

session.CreateSQLQuery(
   @"SELECT DISTINCT PT.PermissionType FROM AspNetUsers AU 
   INNER JOIN AspNetUserRoles AR ON AU.Id= AR.UserId
   INNER JOIN dbo.AspNetRoles ANR ON ANR.Id = AR.RoleId
   INNER JOIN [dbo].[tbl_RolePermissionTypeAssoc] RPT ON RPT.FK_RoleId  = AR.RoleId
   INNER JOIN tbl_PermissionType PT ON RPT.FK_permissionTypeId=PT.Id

   -- here is again RegionId, but that is not passed
   WHERE AU.ID=:userId AND ANR.RegionId=:regionId")
   .SetParameter("userId", userId);

RegionId is expected again but not passed