AppBox升級進行時 - 關聯表查詢與更新（Entity Framework）

AppBox 是基於 FineUI 的通用許可權管理框架，包括使用者管理、職稱管理、部門管理、角色管理、角色許可權管理等模組。

 

關聯表的查詢操作

使用 Include 方法，我們可以在一次資料庫查詢中將關聯表的資料一併取出。

比如查詢線上使用者列表頁面，需要在前端顯示關聯的使用者資訊，如下所示：

<x:Grid ID="Grid1" runat="server" BoxFlex="1" ShowBorder="true" ShowHeader="false"
	EnableCheckBoxSelect="false" EnableRowNumber="true" DataKeyNames="ID" AllowSorting="true"
	OnSort="Grid1_Sort" SortColumnIndex="0" SortDirection="DESC" AllowPaging="true"
	IsDatabasePaging="true" OnPageIndexChange="Grid1_PageIndexChange">
	<Columns>
		<x:BoundField DataField="UpdateTime" SortField="UpdateTime" DataFormatString="{0:yyyy-MM-dd HH:mm}"
			Width="120px" HeaderText="最後操作時間" />
		<x:BoundField DataField="LoginTime" SortField="LoginTime" Width="120px" DataFormatString="{0:yyyy-MM-dd HH:mm}"
			HeaderText="登入時間" />
		<x:BoundField DataField="User.Name" SortField="User.Name" Width="100px" HeaderText="使用者名稱" />
		<x:BoundField DataField="User.ChineseName" SortField="User.ChineseName" Width="100px" HeaderText="中文名" />
		<x:BoundField DataField="IPAdddress" ExpandUnusedSpace="true" HeaderText="IP地址" />
		<x:WindowField Text="檢視" Title="檢視" WindowID="Window1" DataIFrameUrlFields="User.ID"
			DataIFrameUrlFormatString="~/admin/user_view.aspx?id={0}" Width="50px" />
	</Columns>
</x:Grid>

其中“使用者名稱”和“中文名”以及“檢視”按鈕，都用到了導航屬性User（Navigation Property），所以我們在查詢時也使用Include來避免延遲載入：

IQueryable<Online> q = DB.Onlines.Include(o => o.User);

// 在使用者名稱中搜尋
string searchText = ttbSearchMessage.Text.Trim();
if (!String.IsNullOrEmpty(searchText))
{
	q = q.Where(o => o.User.Name.Contains(searchText));
}

DateTime lastD = DateTime.Now.AddHours(-2);
q = q.Where(o => o.UpdateTime > lastD);

// 在新增條件之後，排序和分頁之前獲取總記錄數
Grid1.RecordCount = q.Count();

// 排列和資料庫分頁
q = SortAndPage<Online>(q, Grid1);

Grid1.DataSource = q;
Grid1.DataBind();

　　

為了更形象的說明 Include 的作用，我們使用工具ExpressProfile來監視對資料庫的操作：http://expressprofiler.codeplex.com/　　

首先看下頁面顯示效果：

 

 

使用 Include 情況下，看下對資料庫的操作：

可以看到有兩次資料庫查詢（獲取總記錄數查詢和分頁資料查詢），相應的查詢語句分別為：

獲取總記錄數的SQL查詢語句：

exec sp_executesql N'SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
	COUNT(1) AS [A1]
	FROM [dbo].[Onlines] AS [Extent1]
	WHERE [Extent1].[UpdateTime] > @p__linq__0
)  AS [GroupBy1]',N'@p__linq__0 datetime2(7)',@p__linq__0='2013-08-23 15:35:08.2573160'
go

分頁資料查詢的SQL語句：

exec sp_executesql N'SELECT TOP (20) 
[Project1].[ID] AS [ID], 
[Project1].[IPAdddress] AS [IPAdddress], 
[Project1].[LoginTime] AS [LoginTime], 
[Project1].[UpdateTime] AS [UpdateTime], 
[Project1].[ID1] AS [ID1], 
[Project1].[Name] AS [Name], 
[Project1].[Email] AS [Email], 
[Project1].[Password] AS [Password], 
[Project1].[Enabled] AS [Enabled], 
[Project1].[Gender] AS [Gender], 
[Project1].[ChineseName] AS [ChineseName], 
[Project1].[EnglishName] AS [EnglishName], 
[Project1].[Photo] AS [Photo], 
[Project1].[QQ] AS [QQ], 
[Project1].[CompanyEmail] AS [CompanyEmail], 
[Project1].[OfficePhone] AS [OfficePhone], 
[Project1].[OfficePhoneExt] AS [OfficePhoneExt], 
[Project1].[HomePhone] AS [HomePhone], 
[Project1].[CellPhone] AS [CellPhone], 
[Project1].[Address] AS [Address], 
[Project1].[Remark] AS [Remark], 
[Project1].[IdentityCard] AS [IdentityCard], 
[Project1].[Birthday] AS [Birthday], 
[Project1].[TakeOfficeTime] AS [TakeOfficeTime], 
[Project1].[LastLoginTime] AS [LastLoginTime], 
[Project1].[CreateTime] AS [CreateTime], 
[Project1].[DeptID] AS [DeptID]
FROM ( SELECT [Project1].[ID] AS [ID], [Project1].[IPAdddress] AS [IPAdddress], [Project1].[LoginTime] AS [LoginTime], [Project1].[UpdateTime] AS [UpdateTime], [Project1].[ID1] AS [ID1], [Project1].[Name] AS [Name], [Project1].[Email] AS [Email], [Project1].[Password] AS [Password], [Project1].[Enabled] AS [Enabled], [Project1].[Gender] AS [Gender], [Project1].[ChineseName] AS [ChineseName], [Project1].[EnglishName] AS [EnglishName], [Project1].[Photo] AS [Photo], [Project1].[QQ] AS [QQ], [Project1].[CompanyEmail] AS [CompanyEmail], [Project1].[OfficePhone] AS [OfficePhone], [Project1].[OfficePhoneExt] AS [OfficePhoneExt], [Project1].[HomePhone] AS [HomePhone], [Project1].[CellPhone] AS [CellPhone], [Project1].[Address] AS [Address], [Project1].[Remark] AS [Remark], [Project1].[IdentityCard] AS [IdentityCard], [Project1].[Birthday] AS [Birthday], [Project1].[TakeOfficeTime] AS [TakeOfficeTime], [Project1].[LastLoginTime] AS [LastLoginTime], [Project1].[CreateTime] AS [CreateTime], [Project1].[DeptID] AS [DeptID], row_number() OVER (ORDER BY [Project1].[UpdateTime] DESC) AS [row_number]
	FROM ( SELECT 
		[Extent1].[ID] AS [ID], 
		[Extent1].[IPAdddress] AS [IPAdddress], 
		[Extent1].[LoginTime] AS [LoginTime], 
		[Extent1].[UpdateTime] AS [UpdateTime], 
		[Extent2].[ID] AS [ID1], 
		[Extent2].[Name] AS [Name], 
		[Extent2].[Email] AS [Email], 
		[Extent2].[Password] AS [Password], 
		[Extent2].[Enabled] AS [Enabled], 
		[Extent2].[Gender] AS [Gender], 
		[Extent2].[ChineseName] AS [ChineseName], 
		[Extent2].[EnglishName] AS [EnglishName], 
		[Extent2].[Photo] AS [Photo], 
		[Extent2].[QQ] AS [QQ], 
		[Extent2].[CompanyEmail] AS [CompanyEmail], 
		[Extent2].[OfficePhone] AS [OfficePhone], 
		[Extent2].[OfficePhoneExt] AS [OfficePhoneExt], 
		[Extent2].[HomePhone] AS [HomePhone], 
		[Extent2].[CellPhone] AS [CellPhone], 
		[Extent2].[Address] AS [Address], 
		[Extent2].[Remark] AS [Remark], 
		[Extent2].[IdentityCard] AS [IdentityCard], 
		[Extent2].[Birthday] AS [Birthday], 
		[Extent2].[TakeOfficeTime] AS [TakeOfficeTime], 
		[Extent2].[LastLoginTime] AS [LastLoginTime], 
		[Extent2].[CreateTime] AS [CreateTime], 
		[Extent2].[DeptID] AS [DeptID]
		FROM  [dbo].[Onlines] AS [Extent1]
		INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[ID]
		WHERE [Extent1].[UpdateTime] > @p__linq__0
	)  AS [Project1]
)  AS [Project1]
WHERE [Project1].[row_number] > 0
ORDER BY [Project1].[UpdateTime] DESC',N'@p__linq__0 datetime2(7)',@p__linq__0='2013-08-23 15:35:08.2573160'
go

Entity Framework生成的SQL語句包含了子查詢和關聯查詢，還是蠻複雜的。

 

我們把這兩條SQL語句分別在VS2012的伺服器資源管理器中執行一下，就能看到和頁面上相同的結果了：　　

　　

 

 

作為對比，來看下不使用 Include 的資料庫SQL查詢語句：

 

此時，除了獲取總記錄數的SQL查詢和獲取分頁資料的SQL查詢，還多了 3 條獲取使用者資訊的SQL查詢，其中一個獲取使用者資訊的SQL語句為：

exec sp_executesql N'SELECT 
[Extent2].[ID] AS [ID], 
[Extent2].[Name] AS [Name], 
[Extent2].[Email] AS [Email], 
[Extent2].[Password] AS [Password], 
[Extent2].[Enabled] AS [Enabled], 
[Extent2].[Gender] AS [Gender], 
[Extent2].[ChineseName] AS [ChineseName], 
[Extent2].[EnglishName] AS [EnglishName], 
[Extent2].[Photo] AS [Photo], 
[Extent2].[QQ] AS [QQ], 
[Extent2].[CompanyEmail] AS [CompanyEmail], 
[Extent2].[OfficePhone] AS [OfficePhone], 
[Extent2].[OfficePhoneExt] AS [OfficePhoneExt], 
[Extent2].[HomePhone] AS [HomePhone], 
[Extent2].[CellPhone] AS [CellPhone], 
[Extent2].[Address] AS [Address], 
[Extent2].[Remark] AS [Remark], 
[Extent2].[IdentityCard] AS [IdentityCard], 
[Extent2].[Birthday] AS [Birthday], 
[Extent2].[TakeOfficeTime] AS [TakeOfficeTime], 
[Extent2].[LastLoginTime] AS [LastLoginTime], 
[Extent2].[CreateTime] AS [CreateTime], 
[Extent2].[DeptID] AS [DeptID]
FROM  [dbo].[Onlines] AS [Extent1]
INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[ID]
WHERE [Extent1].[ID] = @EntityKeyValue1',N'@EntityKeyValue1 int',@EntityKeyValue1=2
go

其在VS2012中的執行結果為：

　　

 

 

關聯表的新增操作

在前面的文章中，我們已經看到如何新增一個使用者，併為使用者新增角色列表，如下所示：

User item = new User();
item.Name = tbxName.Text.Trim();
item.Password = PasswordUtil.CreateDbPassword(tbxPassword.Text.Trim());
item.ChineseName = tbxRealName.Text.Trim();
item.Gender = ddlGender.SelectedValue;
item.CompanyEmail = tbxCompanyEmail.Text.Trim();
item.Email = tbxEmail.Text.Trim();
item.OfficePhone = tbxOfficePhone.Text.Trim();
item.OfficePhoneExt = tbxOfficePhoneExt.Text.Trim();
item.HomePhone = tbxHomePhone.Text.Trim();
item.CellPhone = tbxCellPhone.Text.Trim();
item.Remark = tbxRemark.Text.Trim();
item.Enabled = cbxEnabled.Checked;
item.CreateTime = DateTime.Now;
 
// 新增所有角色
if (!String.IsNullOrEmpty(hfSelectedRole.Text))
{
    int[] roleIDs = StringUtil.GetIntArrayFromString(hfSelectedRole.Text);
     
    item.Roles = new List<Role>();
    foreach (int roleID in roleIDs)
    {
        Role role = new Role { ID = roleID };
        DB.Roles.Attach(role);
        item.Roles.Add(role);
    }
}
 
DB.Users.Add(item);
DB.SaveChanges();

　　

關聯表的刪除操作

從角色中刪除多個使用者的操作，用Subsonic來實現的程式碼：

List<int> roleIds = GetSelectedDataKeyIDs(Grid1);
List<int> userIds = GetSelectedDataKeyIDs(Grid2);

new Delete().From<XRoleUser>()
	.Where(XRoleUser.RoleIdColumn).IsEqualTo(roleIds[0])
	.And(XRoleUser.UserIdColumn).In(userIds)
	.Execute();

使用Entity Framework時，由於需要先將需要的資料載入，所以程式碼稍微有點複雜：

Role role = DB.Roles.Include(r => r.Users)
	.Where(r => r.ID == roleID)
	.FirstOrDefault();

foreach (int userID in userIDs)
{
	User user = role.Users.Where(u => u.ID == userID).FirstOrDefault();
	if (user != null)
	{
		role.Users.Remove(user);
	}
}

DB.SaveChanges();

上面程式碼也可以簡化為：

Role role = DB.Roles.Include(r => r.Users)
	.Where(r => r.ID == roleID)
	.FirstOrDefault();

role.Users.Where(u => userIDs.Contains(u.ID)).ToList().ForEach(u => role.Users.Remove(u));

DB.SaveChanges();

　　

 

下面再來看看如果移除屬於某個部門的多個使用者，由於部門和使用者是一對多的關係，所以我們可以先找到這些使用者，再清空這些使用者所屬的部門，如下所示：

DB.Users.Include(u => u.Dept)
	.Where(u => userIDs.Contains(u.ID))
	.ToList()
	.ForEach(u => u.Dept = null);

DB.SaveChanges();

另一種做法，先找到這個部門，然後從部門的使用者列表Users（導航屬性）中刪除這些使用者，如下所示：

Dept dept = DB.Depts.Include(d => d.Users)
	.Where(d => d.ID == deptID)
	.FirstOrDefault();

foreach (int userID in userIDs)
{
	User user = dept.Users.Where(u => u.ID == userID).FirstOrDefault();
	if (user != null)
	{
		dept.Users.Remove(user);
	}
}

DB.SaveChanges();

上面兩種做法的結果是完全一致的。　　

 

關聯表的更新操作

以更新單個使用者資訊為例，一個使用者可以屬於一個部門（Dept） ，可以擁有多個角色（Role），使用Subsonic更新使用者資訊的程式碼如下所示：

using (TransactionScope scope = new TransactionScope())
{
    int userId = GetQueryIntValue("id");
    XUser item = XUser.FetchByID(userId);
    //item.Name = tbxName.Text.Trim();
    item.ChineseName = tbxRealName.Text.Trim();
    item.Gender = ddlGender.SelectedValue;
    item.CompanyEmail = tbxEmail.Text.Trim();
    item.PersonalEmail = tbxPersonalEmail.Text.Trim();
    item.CellPhone = tbxCellPhone.Text.Trim();
    item.OfficePhone = tbxOfficePhone.Text.Trim();
    item.OfficePhoneExt = tbxOfficePhoneExt.Text.Trim();
    item.HomePhone = tbxHomePhone.Text.Trim();
    item.Remark = tbxRemark.Text.Trim();
    item.Enabled = cbxEnabled.Checked;
    //item.RoleId = Convert.ToInt32(ddlRole.SelectedValue);
    //item.DeptId = Convert.ToInt32(ddlDept.SelectedValue);
    item.Save();
 
 
    // 刪除使用者所屬的所有角色
    new Delete().From(XRoleUser.Schema)
        .Where(XRoleUser.UserIdColumn).IsEqualTo(userId)
        .Execute();
 
    // 新增所有角色
    if (!String.IsNullOrEmpty(hfSelectedRole.Text))
    {
        XRoleUserCollection roleUsers = new XRoleUserCollection();
        foreach (string roleIdStr in hfSelectedRole.Text.Split(','))
        {
            int id = Convert.ToInt32(roleIdStr);
 
            XRoleUser roleUser = new XRoleUser();
            roleUser.RoleId = id;
            roleUser.UserId = userId;
 
            roleUsers.Add(roleUser);
        }
        roleUsers.SaveAll();
    }
 
 
    // 刪除使用者所屬的所有部門
    new Delete().From(XDeptUser.Schema)
        .Where(XDeptUser.UserIdColumn).IsEqualTo(userId)
        .Execute();
 
    // 新增所有部門
    if (!String.IsNullOrEmpty(hfSelectedDept.Text))
    {
        XDeptUser deptUser = new XDeptUser();
        deptUser.DeptId = Convert.ToInt32(hfSelectedDept.Text);
        deptUser.UserId = userId;
        deptUser.Save();
    }
 
    scope.Complete();
}

這裡的資料庫查詢比較多，分別為：

1. 查詢單個資料的資訊；

2. 刪除使用者所屬的所有角色；

3. 新增使用者的新角色；

4. 刪除使用者所屬的部門；

5. 新增使用者所屬的新部門；（這個地方是資料庫設計也不大合理，其實不需單獨表來儲存部門使用者資訊）　　

 

完成相同的功能，來看下Entity Framework的實現：

int id = GetQueryIntValue("id");
User item = DB.Users
    .Include(u => u.Dept)
    .Include(u => u.Roles)
    .Where(u => u.UserID == id).FirstOrDefault();
//item.Name = tbxName.Text.Trim();
item.ChineseName = tbxRealName.Text.Trim();
item.Gender = ddlGender.SelectedValue;
item.CompanyEmail = tbxCompanyEmail.Text.Trim();
item.Email = tbxEmail.Text.Trim();
item.CellPhone = tbxCellPhone.Text.Trim();
item.OfficePhone = tbxOfficePhone.Text.Trim();
item.OfficePhoneExt = tbxOfficePhoneExt.Text.Trim();
item.HomePhone = tbxHomePhone.Text.Trim();
item.Remark = tbxRemark.Text.Trim();
item.Enabled = cbxEnabled.Checked;
 
if (String.IsNullOrEmpty(hfSelectedDept.Text))
{
    item.Dept = null;
}
else
{
    int newDeptID = Convert.ToInt32(hfSelectedDept.Text);
    if (item.Dept.DeptID != newDeptID)
    {
        Dept newDept = new Dept { DeptID = newDeptID };
        DB.Depts.Attach(newDept);
        item.Dept = newDept;
    }
}
 
if (String.IsNullOrEmpty(hfSelectedRole.Text))
{
    item.Roles = null;
}
else
{
    int[] roleIDs = StringUtil.GetIntArrayFromString(hfSelectedRole.Text);
 
    int[] addRoleIDs = roleIDs.Except(item.Roles.Select(r => r.RoleID)).ToArray();
    int[] removeRoleIDs = item.Roles.Select(r => r.RoleID).Except(roleIDs).ToArray();
 
    foreach (int roleID in addRoleIDs)
    {
        var role = new Role { RoleID = roleID };
        DB.Roles.Attach(role);
        item.Roles.Add(role);
    }
    foreach (int roleID in removeRoleIDs)
    {
        item.Roles.Remove(item.Roles.Single(r => r.RoleID == roleID));
    }
 
}
 
DB.SaveChanges();

　　

這裡，我們又再次用到了 Attach 方法，實際上這樣使用 Attach 是有風險的，下一節我們會重點講解 Attach 的使用。 

 

下載或捐贈AppBox

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

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