AppBox升级进行时 - Any与All的用法（Entity Framework）

怼烎@ 2022-03-31 03:16 133阅读 0赞

> AppBox 是基于 FineUI 的通用权限管理框架，包括用户管理、职称管理、部门管理、角色管理、角色权限管理等模块。

## 属于某个角色的用户列表（Any的用法） ##

使用Subsonic，我们有两种方法获取属于某个角色的用户列表，分别是表关联和子查询。

Subsonic的表关联实现：

// 查询 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);
    XUserCollection items = q.ExecuteAsCollection<XUserCollection>();
    
    Grid2.DataSource = items;
    Grid2.DataBind();

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);
    XUserCollection items = q.ExecuteAsCollection<XUserCollection>();
    
    Grid2.DataSource = items;
    Grid2.DataBind();

使用Entity Framework就不能从数据库的角度思考问题，而应该从实体类之间的关系考虑问题，具体的实现：

IQueryable<User> q = DB.Users;
     
    // 在用户名称中搜索
    string searchText = ttbSearchUser.Text.Trim();
    if (!String.IsNullOrEmpty(searchText))
    {
    	q = q.Where(u => u.Name.Contains(searchText));
    }
    
    // 过滤选中角色下的所有用户
    object[] values = Grid1.DataKeys[Grid1.SelectedRowIndexArray[0]];
    int roleId = Convert.ToInt32(values[0]);
    q = q.Where(u => u.Roles.Any(r => r.ID == roleId));
    
    // 在查询添加之后，排序和分页之前获取总记录数
    Grid2.RecordCount = q.Count();
    
    // 排列和分页
    q = SortAndPage<User>(q, Grid2);
    
    Grid2.DataSource = q;
    Grid2.DataBind();

这里用到了 Any 方法，可以这么理解：检索一些用户，只要用户的任意一个角色是roleId就可以。

来看下生成的SQL语句：

exec sp_executesql N'SELECT TOP (20) 
    [Project2].[ID] AS [ID], 
    [Project2].[Name] AS [Name], 
    [Project2].[Email] AS [Email], 
    [Project2].[Password] AS [Password], 
    [Project2].[Enabled] AS [Enabled], 
    [Project2].[Gender] AS [Gender], 
    [Project2].[ChineseName] AS [ChineseName], 
    [Project2].[EnglishName] AS [EnglishName], 
    [Project2].[Photo] AS [Photo], 
    [Project2].[QQ] AS [QQ], 
    [Project2].[CompanyEmail] AS [CompanyEmail], 
    [Project2].[OfficePhone] AS [OfficePhone], 
    [Project2].[OfficePhoneExt] AS [OfficePhoneExt], 
    [Project2].[HomePhone] AS [HomePhone], 
    [Project2].[CellPhone] AS [CellPhone], 
    [Project2].[Address] AS [Address], 
    [Project2].[Remark] AS [Remark], 
    [Project2].[IdentityCard] AS [IdentityCard], 
    [Project2].[Birthday] AS [Birthday], 
    [Project2].[TakeOfficeTime] AS [TakeOfficeTime], 
    [Project2].[LastLoginTime] AS [LastLoginTime], 
    [Project2].[CreateTime] AS [CreateTime], 
    [Project2].[DeptID] AS [DeptID]
    FROM ( SELECT [Project2].[ID] AS [ID], [Project2].[Name] AS [Name], [Project2].[Email] AS [Email], [Project2].[Password] AS [Password], [Project2].[Enabled] AS [Enabled], [Project2].[Gender] AS [Gender], [Project2].[ChineseName] AS [ChineseName], [Project2].[EnglishName] AS [EnglishName], [Project2].[Photo] AS [Photo], [Project2].[QQ] AS [QQ], [Project2].[CompanyEmail] AS [CompanyEmail], [Project2].[OfficePhone] AS [OfficePhone], [Project2].[OfficePhoneExt] AS [OfficePhoneExt], [Project2].[HomePhone] AS [HomePhone], [Project2].[CellPhone] AS [CellPhone], [Project2].[Address] AS [Address], [Project2].[Remark] AS [Remark], [Project2].[IdentityCard] AS [IdentityCard], [Project2].[Birthday] AS [Birthday], [Project2].[TakeOfficeTime] AS [TakeOfficeTime], [Project2].[LastLoginTime] AS [LastLoginTime], [Project2].[CreateTime] AS [CreateTime], [Project2].[DeptID] AS [DeptID], row_number() OVER (ORDER BY [Project2].[Name] DESC) AS [row_number]
    	FROM ( SELECT 
    		[Extent1].[ID] AS [ID], 
    		[Extent1].[Name] AS [Name], 
    		[Extent1].[Email] AS [Email], 
    		[Extent1].[Password] AS [Password], 
    		[Extent1].[Enabled] AS [Enabled], 
    		[Extent1].[Gender] AS [Gender], 
    		[Extent1].[ChineseName] AS [ChineseName], 
    		[Extent1].[EnglishName] AS [EnglishName], 
    		[Extent1].[Photo] AS [Photo], 
    		[Extent1].[QQ] AS [QQ], 
    		[Extent1].[CompanyEmail] AS [CompanyEmail], 
    		[Extent1].[OfficePhone] AS [OfficePhone], 
    		[Extent1].[OfficePhoneExt] AS [OfficePhoneExt], 
    		[Extent1].[HomePhone] AS [HomePhone], 
    		[Extent1].[CellPhone] AS [CellPhone], 
    		[Extent1].[Address] AS [Address], 
    		[Extent1].[Remark] AS [Remark], 
    		[Extent1].[IdentityCard] AS [IdentityCard], 
    		[Extent1].[Birthday] AS [Birthday], 
    		[Extent1].[TakeOfficeTime] AS [TakeOfficeTime], 
    		[Extent1].[LastLoginTime] AS [LastLoginTime], 
    		[Extent1].[CreateTime] AS [CreateTime], 
    		[Extent1].[DeptID] AS [DeptID]
    		FROM [dbo].[Users] AS [Extent1]
    		WHERE (N''admin'' <> [Extent1].[Name]) AND ( EXISTS (SELECT 
    			1 AS [C1]
    			FROM [dbo].[RoleUsers] AS [Extent2]
    			WHERE ([Extent1].[ID] = [Extent2].[UserID]) AND ([Extent2].[RoleID] = @p__linq__0)
    		))
    	)  AS [Project2]
    )  AS [Project2]
    WHERE [Project2].[row_number] > 0
    ORDER BY [Project2].[Name] DESC',N'@p__linq__0 int',@p__linq__0=1
    go

EF生成的SQL语句是很复杂，我们来稍微简化一下（为了看清本质，去掉了排序，过滤以及返回字段的个数等）：

exec sp_executesql N'SELECT [Project2].[ID] AS [ID], [Project2].[Name] AS [Name], [Project2].[Email] AS [Email], [Project2].[Password] AS [Password]
    	FROM ( SELECT 
    		[Extent1].[ID] AS [ID], 
    		[Extent1].[Name] AS [Name], 
    		[Extent1].[Email] AS [Email], 
    		[Extent1].[Password] AS [Password]
    		FROM [dbo].[Users] AS [Extent1]
    		WHERE (N''admin'' <> [Extent1].[Name]) AND ( EXISTS (SELECT 
    			1 AS [C1]
    			FROM [dbo].[RoleUsers] AS [Extent2]
    			WHERE ([Extent1].[ID] = [Extent2].[UserID]) AND ([Extent2].[RoleID] = @p__linq__0)
    		))
    	)  AS [Project2]',N'@p__linq__0 int',@p__linq__0=1
    go

进一步简化：

exec sp_executesql N' SELECT 
    		[Extent1].[ID] AS [ID], 
    		[Extent1].[Name] AS [Name], 
    		[Extent1].[Email] AS [Email], 
    		[Extent1].[Password] AS [Password]
    		FROM [dbo].[Users] AS [Extent1]
    		WHERE (N''admin'' <> [Extent1].[Name]) AND ( EXISTS (SELECT 
    			1 AS [C1]
    			FROM [dbo].[RoleUsers] AS [Extent2]
    			WHERE ([Extent1].[ID] = [Extent2].[UserID]) AND ([Extent2].[RoleID] = @p__linq__0)
    		))
    	',N'@p__linq__0 int',@p__linq__0=1
    go

进一步简化：

SELECT 
    		[Users].[ID] AS [ID], 
    		[Users].[Name] AS [Name], 
    		[Users].[Email] AS [Email], 
    		[Users].[Password] AS [Password]
    		FROM [dbo].[Users] AS [Users]
    		WHERE EXISTS (SELECT 
    			1 AS [C1]
    			FROM [dbo].[RoleUsers] AS [RoleUsers]
    			WHERE ([Users].[ID] = [RoleUsers].[UserID]) AND ([RoleUsers].[RoleID] = 1)
    		)

进一步简化：

SELECT 
    		ID, 
    		Name, 
    		Email, 
    		Password
    		FROM Users
    		WHERE EXISTS (SELECT 
    			1
    			FROM RoleUsers
    			WHERE (Users.ID = RoleUsers.UserID) AND (RoleUsers.RoleID = 1)
    		)

最终，我们看到了Entity Framework使用子查询和 EXISTS 关键字来完成 Any 的操作。

当然，如果是我们自己写SQL，可以使用 IN 关键字来达到相同的效果：

SELECT 
    		ID, 
    		Name, 
    		Email, 
    		Password
    		FROM Users
    		WHERE ID IN (SELECT 
    			UserID
    			FROM RoleUsers
    			WHERE (Users.ID = RoleUsers.UserID) AND (RoleUsers.RoleID = 1)
    		)

也可以使用关联查询达到相同的效果：

SELECT 
    		ID, 
    		Name, 
    		Email, 
    		Password
    		FROM Users 
    		INNER JOIN RoleUsers
    		ON (Users.ID = RoleUsers.UserID) AND (RoleUsers.RoleID = 1)

幸运的是，我么只需要一个 Any 关键字就完成了这个稍微复杂的查询。

## 不属于某个角色的用户列表（All的用法） ##

类似的，查询不属于某个角色的用户列表（用来添加用户到某个角色的UI界面中），使用Subsonic也有两种方法，我们只看下子查询的方式：

SqlQuery q = new Select().From<XUser>(); 
    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);
     
    // 在查询添加之后，排序和分页之前获取总记录数
    Grid1.RecordCount = q.GetRecordCount();
     
    // 排列
    q.OrderBys.Add(GetSortExpression(Grid1, XUser.Schema));
     
    // 数据库分页
    q.Paged(Grid1.PageIndex + 1, Grid1.PageSize);
    XUserCollection items = q.ExecuteAsCollection<XUserCollection>();
     
     
    Grid1.DataSource = items;
    Grid1.DataBind();

使用Entity Framework，我们只需借助 All 关键字就能简单实现：

IQueryable<User> q = DB.Users;
     
    // 在职务名称中搜索
    string searchText = ttbSearchMessage.Text.Trim();
    if (!String.IsNullOrEmpty(searchText))
    {
        q = q.Where(u => u.Name.Contains(searchText));
    }
     
    // 排除已经属于本角色的用户
    int currentRoleId = GetQueryIntValue("id");
    q = q.Where(u => u.Roles.All(r => r.ID != currentRoleId));
     
    // 在查询添加之后，排序和分页之前获取总记录数
    Grid1.RecordCount = q.Count();
     
    // 排列和分页
    q = SortAndPage<User>(q, Grid1);
     
    Grid1.DataSource = q;
    Grid1.DataBind();

可以简单的理解：检索一些用户，要保证这些用户的所有角色没有一个是currentRoleId。

去除分页和排序后，生成的SQL语句为：

exec sp_executesql N'SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Email] AS [Email], 
    [Extent1].[Password] AS [Password], 
    [Extent1].[Enabled] AS [Enabled], 
    [Extent1].[Gender] AS [Gender], 
    [Extent1].[ChineseName] AS [ChineseName], 
    [Extent1].[EnglishName] AS [EnglishName], 
    [Extent1].[Photo] AS [Photo], 
    [Extent1].[QQ] AS [QQ], 
    [Extent1].[CompanyEmail] AS [CompanyEmail], 
    [Extent1].[OfficePhone] AS [OfficePhone], 
    [Extent1].[OfficePhoneExt] AS [OfficePhoneExt], 
    [Extent1].[HomePhone] AS [HomePhone], 
    [Extent1].[CellPhone] AS [CellPhone], 
    [Extent1].[Address] AS [Address], 
    [Extent1].[Remark] AS [Remark], 
    [Extent1].[IdentityCard] AS [IdentityCard], 
    [Extent1].[Birthday] AS [Birthday], 
    [Extent1].[TakeOfficeTime] AS [TakeOfficeTime], 
    [Extent1].[LastLoginTime] AS [LastLoginTime], 
    [Extent1].[CreateTime] AS [CreateTime], 
    [Extent1].[DeptID] AS [DeptID]
    FROM [dbo].[Users] AS [Extent1]
    WHERE  NOT EXISTS (SELECT 
    	1 AS [C1]
    	FROM [dbo].[RoleUsers] AS [Extent2]
    	WHERE ([Extent1].[ID] = [Extent2].[UserID]) AND (([Extent2].[RoleID] = @p__linq__0) OR (CASE WHEN ([Extent2].[RoleID] <> @p__linq__0) THEN cast(1 as bit) WHEN ([Extent2].[RoleID] = @p__linq__0) THEN cast(0 as bit) END IS NULL))
    )',N'@p__linq__0 int',@p__linq__0=1
    go

最终简化为：

SELECT 
    ID, 
    Name, 
    Email, 
    Password
    FROM Users
    WHERE  NOT EXISTS (SELECT 
    	1
    	FROM RoleUsers
    	WHERE (ID = RoleUsers.UserID) AND ((RoleUsers.RoleID = 1) OR (CASE WHEN (RoleUsers.RoleID <> 1) THEN cast(1 as bit) WHEN (RoleUsers.RoleID = 1) THEN cast(0 as bit) END IS NULL))
    )

按照我的理解，其中：

CASE WHEN (RoleUsers.RoleID <> 1) THEN cast(1 as bit) WHEN (RoleUsers.RoleID = 1) THEN cast(0 as bit) END IS NULL

类似于下面的判断：

RoleUsers.RoleID  IS NULL

只是不知道为啥会生成这么令人费解的代码。因为如果 RoleUsers.RoleID为NULL的话，既不会走进第一个WHEN，也不会走进第二个WHEN，自然就是NULL IS NULL为true了。

下面简单写个SELECT来验证我的想法：

select CASE WHEN (null <> 1) 
    					THEN cast(1 as bit) 
    					WHEN (null = 1) 
    					THEN cast(0 as bit) 
    			END

![26180836-f76a3eaf80b542e2b7573c0d1f9246c0.png][]

注意，这个NULL<>1的结果是FALSE，NULL=1的结果也是FALSE，所以最终的结果才是NULL。

再来看一个简单的SELECT查询：

![26181022-ae2d4dfd6a8a41fe95af8baa03078153.png][]

## 下载或捐赠AppBox ##

1. AppBox v2.1 是免费软件，免费提供下载：[http://fineui.com/bbs/forum.php?mod=viewthread&tid=3788][http_fineui.com_bbs_forum.php_mod_viewthread_tid_3788]

2. AppBox v3.0 是捐赠软件，你可以通过捐赠作者来获取AppBox v3.0的全部源代码（[http://fineui.com/donate/][http_fineui.com_donate]）。

**日寇忘我之心不死，同志尚需警惕！纪念九一八。**

[26180836-f76a3eaf80b542e2b7573c0d1f9246c0.png]: /images/20220331/08f515efacae4b69a21cfaa6abe9f37d.png
[26181022-ae2d4dfd6a8a41fe95af8baa03078153.png]: /images/20220331/70bf3cd17d17438f98cb0887cd8686f2.png
[http_fineui.com_bbs_forum.php_mod_viewthread_tid_3788]: http://fineui.com/bbs/forum.php?mod=viewthread&tid=3788
[http_fineui.com_donate]: http://fineui.com/donate/