[原创]修正SubSonic v2.2.1的一处BUG，以及如何使用SubSonic进行多表查询、子查询以及数据库分页

相信很多同学都用过SubSonic，在07 - 10年ORM兴起的时代，SubSonic可以说是DotNet开发人员的救星。虽说现在 EntityFramework大有一统江湖的趋势，不过在DotNet2.0框架下，SubSonic依然是为数不多的选择。

最近在维护基于 ExtAspNet 的通用权限管理项目 AppBox ，在使用SubSonic进行多表查询和数据库分页时遇到了点问题，下面我会详细分享这一经过，以及如何通过修改SubSonic的源代码来修正这一问题。

我要实现如下的功能

我要实现的功能非常简单：用户管理，角色管理，角色用户管理（一个用户可以属于多个角色）。相信很多同学闭着眼睛就能把数据库给构造出来，不是吗？

1. 用户表

2. 角色表

3. 角色用户表

其中用户管理和角色管理都很简单，我要实现的角色用户管理界面如下所示：

1. 查看角色下的所有用户

2. 向角色添加现有用户

数据库查询时遇到问题

在查看角色下的所有用户页面，需要进行表关联，相关的SubSonic代码如下所示：

// 查询 X_User 表
SqlQuery q = new Select().From<XUser>();
q.Where("1").IsEqualTo("1");

// 在用户名称中搜索
string searchText = ttbSearchUser.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
    q.And(XUser.NameColumn).ContainsString(searchText);
}

// 过滤选中角色下的所有用户
object[] values = Grid1.DataKeys[Grid1.SelectedRowIndexArray[0]];
int roleId = Convert.ToInt32(values[0]);
SqlQuery subQ = new Select(XRoleUser.UserIdColumn).From<XRoleUser>().Where(XRoleUser.RoleIdColumn).IsEqualTo(roleId);

q.And(XUser.IdColumn).In(subQ);


// 在查询添加之后，排序和分页之前获取总记录数
// Grid1总共有多少条记录
Grid2.RecordCount = q.GetRecordCount();

// 排列
q.OrderBys.Add(GetSortExpression(Grid2, XUser.Schema));

// 数据库分页
q.Paged(Grid2.PageIndex + 1, Grid2.PageSize);
items = q.ExecuteAsCollection<XUserCollection>();

令人不解的时，居然报如下错误：

很明显，SubSonic生成的SQL脚本不对，经过调试发现生成的脚本如下所示：

DECLARE @Page int
DECLARE @PageSize int

SET @Page = 1
SET @PageSize = 20

SET NOCOUNT ON

-- create a temp table to hold order ids
DECLARE @TempTable TABLE (IndexId int identity, _keyID Int)

-- insert the table ids and row numbers into the memory table
INSERT INTO @TempTable
(
    _keyID
)
SELECT [dbo].[X_User].[Id]
FROM [dbo].[X_User]
WHERE 1 = @10
AND [dbo].[X_User].[Id] IN (SELECT [dbo].[X_RoleUser].[UserId]
    FROM [dbo].[X_RoleUser]
    WHERE [dbo].[X_RoleUser].[RoleId] = @RoleId0
    )

AND 1 = @10
AND [dbo].[X_User].[Id] IN (SELECT [dbo].[X_RoleUser].[UserId]
    FROM [dbo].[X_RoleUser]
    AND [dbo].[X_RoleUser].[RoleId] = @RoleId0
    )

 ORDER BY Name DESC

-- select only those rows belonging to the proper page
SELECT [dbo].[X_User].[Id], [dbo].[X_User].[Name], [dbo].[X_User].[Password], [dbo].[X_User].[Enabled], [dbo].[X_User].[Email], [dbo].[X_User].[Gender], [dbo].[X_User].[RealName], [dbo].[X_User].[QQ], [dbo].[X_User].[MSN], [dbo].[X_User].[CellPhone], [dbo].[X_User].[OfficePhone], [dbo].[X_User].[HomePhone], [dbo].[X_User].[Remark], [dbo].[X_User].[DeptId], [dbo].[X_User].[RoleId], [dbo].[X_User].[CreateTime]

FROM [dbo].[X_User]
INNER JOIN [dbo].[X_RoleUser] ON [dbo].[X_User].[Id] = [dbo].[X_RoleUser].[UserId]

INNER JOIN @TempTable t ON [dbo].[X_User].[Id] = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

这里面有两处错误：

1. 首先，25 – 29 行的Where子句重复了，相信这个问题一直存在于SubSonic2.2中，只不过大家都没发现而已

2. 其次，重复的子查询中Where被替换成了AND，导致这个子查询没有Where子句，从而报错！

如果撇去分页的SQL脚本不管，正确的SQL脚本应该是这样的：

SELECT [dbo].[X_User].[Id]
FROM [dbo].[X_User]
WHERE 1 = @10
AND [dbo].[X_User].[Id] IN (SELECT [dbo].[X_RoleUser].[UserId]
    FROM [dbo].[X_RoleUser]
    WHERE [dbo].[X_RoleUser].[RoleId] = @RoleId0
    )

 ORDER BYName DESC

很明显，SubSonic在生成带子查询的分页SQL脚本时除了问题。

修改SubSonic的源代码

从Github下载SubSonic2.0的源代码：https://nodeload.github.com/subsonic/SubSonic-2.0/zip/master

其实下载下来的是SubSonic2.2.1，找到其中的 SqlQuery\SqlGenerators\ANSISqlGenerator.cs 文件：

public virtual string BuildPagedSelectStatement()
{
    // 省略的代码...

    string wheres = GenerateWhere();

    //have to doctor the wheres, since we're using a WHERE in the paging
    //bits. So change all "WHERE" to "AND"
    string tweakedWheres = wheres.Replace("WHERE", "AND");

    // 省略的代码...

    string sql = string.Format(PAGING_SQL, idColumn, String.Concat(fromLine, joins, wheres), String.Concat(tweakedWheres, orderby, havings),
        String.Concat(select, fromLine, joins), query.CurrentPage, query.PageSize, sqlType);
    return sql;
}

其中 tweakedWheres 是关键，作者还特别指出要把其中 WHERE 替换成 AND，殊不知这样做对子查询是破坏性操作，而且下面连接SQL脚本时重复添加了WHERE子句。

修改后的代码：

public virtual string BuildPagedSelectStatement()
{
    // 省略的代码...

    string wheres = GenerateWhere();

    //have to doctor the wheres, since we're using a WHERE in the paging
    //bits. So change all "WHERE" to "AND"
    //string tweakedWheres = wheres.Replace("WHERE", "AND");

    // 省略的代码...

    string sql = string.Format(PAGING_SQL, idColumn, String.Concat(fromLine, joins, wheres), String.Concat(orderby, havings),
        String.Concat(select, fromLine, joins), query.CurrentPage, query.PageSize, sqlType);
    return sql;
}

搞定!

子查询与表关联查询（查看角色下所有用户）

在上面的例子中，我们使用的是子查询，对于“查看角色下所有用户”这个案例，我们还有如下另一种解决办法（效果完全一样）：

// 查询 X_User 表
SqlQuery q = new Select().From<XUser>().InnerJoin(XRoleUser.UserIdColumn, XUser.IdColumn);
q.Where("1").IsEqualTo("1");

// 在用户名称中搜索
string searchText = ttbSearchUser.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
    q.And(XUser.NameColumn).ContainsString(searchText);
}

// 过滤选中角色下的所有用户
object[] values = Grid1.DataKeys[Grid1.SelectedRowIndexArray[0]];
int roleId = Convert.ToInt32(values[0]);
q.And(XRoleUser.RoleIdColumn).IsEqualTo(roleId);


// 在查询添加之后，排序和分页之前获取总记录数
// Grid1总共有多少条记录
Grid2.RecordCount = q.GetRecordCount();

// 排列
q.OrderBys.Add(GetSortExpression(Grid2, XUser.Schema));

// 数据库分页
q.Paged(Grid2.PageIndex + 1, Grid2.PageSize);
items = q.ExecuteAsCollection<XUserCollection>();

再来看下这段代码生成的SQL脚本（修正SubSonic2.2.1中的BUG后）：

DECLARE @Page int
DECLARE @PageSize int

SET @Page = 1
SET @PageSize = 20

SET NOCOUNT ON

-- create a temp table to hold order ids
DECLARE @TempTable TABLE (IndexId int identity, _keyID Int)

-- insert the table ids and row numbers into the memory table
INSERT INTO @TempTable
(
    _keyID
)
SELECT [dbo].[X_User].[Id]
FROM [dbo].[X_User]
INNER JOIN [dbo].[X_RoleUser] ON [dbo].[X_User].[Id] = [dbo].[X_RoleUser].[UserId]
    WHERE 1 = @10
    AND [dbo].[X_RoleUser].[RoleId] = @RoleId1

ORDER BY Name DESC

-- select only those rows belonging to the proper page
SELECT [dbo].[X_User].[Id], [dbo].[X_User].[Name], [dbo].[X_User].[Password], [dbo].[X_User].[Enabled], [dbo].[X_User].[Email], [dbo].[X_User].[Gender], [dbo].[X_User].[RealName], [dbo].[X_User].[QQ], [dbo].[X_User].[MSN], [dbo].[X_User].[CellPhone], [dbo].[X_User].[OfficePhone], [dbo].[X_User].[HomePhone], [dbo].[X_User].[Remark], [dbo].[X_User].[DeptId], [dbo].[X_User].[RoleId], [dbo].[X_User].[CreateTime]

FROM [dbo].[X_User]
INNER JOIN [dbo].[X_RoleUser] ON [dbo].[X_User].[Id] = [dbo].[X_RoleUser].[UserId]

INNER JOIN @TempTable t ON [dbo].[X_User].[Id] = t._keyID
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

向当前角色添加现有用户

对于这个情况，我们要注意一点，就是供选择的现有用户不应当包括哪些已经属于当前角色的用户，可以用子查询来实现：

SqlQuery q = new Select().From<XUser>(); //.LeftOuterJoin(XRoleUser.UserIdColumn, XUser.IdColumn);
q.Where("1").IsEqualTo("1");

// 在职务名称中搜索
string searchText = ttbSearchMessage.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
    q.And(XUser.NameColumn).ContainsString(searchText);
}

// 排除已经属于本角色的用户
int currentRoleId = GetQueryIntValue("id");
SqlQuery subQ = new Select(XRoleUser.UserIdColumn).From<XRoleUser>().Where(XRoleUser.RoleIdColumn).IsEqualTo(currentRoleId);

q.And(XUser.IdColumn).NotIn(subQ);
//q.And(XUser.IdColumn).IsNotEqualTo(1);

// 只列出不在当前角色中的用户
//q.AndExpression(XUser.RoleIdColumn.ColumnName).IsNotEqualTo(GetQueryIntValue("id")).Or(XUser.RoleIdColumn).IsNull().CloseExpression();

// 在查询添加之后，排序和分页之前获取总记录数
// Grid1总共有多少条记录
Grid1.RecordCount = q.GetRecordCount();

// 排列
q.OrderBys.Add(GetSortExpression(Grid1, XUser.Schema));

// 数据库分页
q.Paged(Grid1.PageIndex + 1, Grid1.PageSize);
XUserCollection items = q.ExecuteAsCollection<XUserCollection>();

小结

虽然SubSonic2.2的代码已经不更新了，但是在实际应用中，我们可以恰当的修正其源代码来满足需求，这也归功于开源的力量。同时也希望大家能多关注同样是完全开源的ExtAspNet（基于ExtJS的专业ASP.NET2.0控件库）。

注：AppBox是捐赠软件，也就是说你可以通过捐赠作者来获得AppBox源代码。