sql - Nhibernate CreateSQLQuery 错误 - ',' 附近的 SetParameterList 语法不正确

Question

错误:

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

代码:

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

生成的查询:

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

这里的重点是= 符号，它不能用于更多的参数。我们需要 IN 运算符

resluting sql包含

AND ANR.RegionId = @p1, @p2

并且在错误中报告了这种昏迷。我们需要:

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

if部分

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

而且在其他部分我们使用了未传递的参数

else部分:

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但没有通过