AppBox升级进行时 - 关联表查询与更新（Entity Framework）-白红宇

AppBox升级进行时 - 关联表查询与更新（Entity Framework）

阅读量：5935 次

发布时间：2019-06-19

本文共 12026 字，大约阅读时间需要 40 分钟。

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

关联表的查询操作

使用 Include 方法，我们可以在一次数据库查询中将关联表的数据一并取出。

比如查询在线用户列表页面，需要在前端显示关联的用户信息，如下所示：

其中“用户名”和“中文名”以及“查看”按钮，都用到了导航属性User（Navigation Property），所以我们在查询时也使用Include来避免延迟加载：

IQueryable
     
       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
      
       (q, Grid1);Grid1.DataSource = q;Grid1.DataBind();

为了更形象的说明 Include 的作用，我们使用工具ExpressProfile来监视对数据库的操作：　　

首先看下页面显示效果：

使用 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] > 0ORDER 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=2go

其在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
     
      ();    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
     
       roleIds = GetSelectedDataKeyIDs(Grid1);List
      
        userIds = GetSelectedDataKeyIDs(Grid2);new Delete().From
       
        ()	.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 是免费软件，免费提供下载：

2. AppBox v3.0 是捐赠软件，你可以通过捐赠作者来获取AppBox v3.0的全部源代码（）。

转载地址：http://rcctx.baihongyu.com/

你可能感兴趣的文章