AppBox升級進行時 - Any與All的用法（Entity Framework）

三生石上(FineUI控制元件)發表於2013-09-18
APPFramework

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

　　

注意，這個NULL<>1的結果是FALSE，NULL=1的結果也是FALSE，所以最終的結果才是NULL。

再來看一個簡單的SELECT查詢：

　　

 

下載或捐贈AppBox

1. AppBox v2.1 是免費軟體，免費提供下載：http://fineui.com/bbs/forum.php?mod=viewthread&tid=3788 

2. AppBox v3.0 是捐贈軟體，你可以透過捐贈作者來獲取AppBox v3.0的全部原始碼（http://fineui.com/donate/）。

 

 

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

 

 

 

相關文章