基于SqlSugar的开发框架循序渐进介绍（2）– 基于中间表的查询处理 – 伍华聪

来源： 基于SqlSugar的开发框架循序渐进介绍（2）– 基于中间表的查询处理 – 伍华聪 – 博客园

在前面介绍的SQLSugar的相关查询处理操作中，我们主要以单表的方式生成相关的实体类，并在查询的时候，对单表的字段进行条件的对比处理，从而返回对应的数据记录。本篇随笔介绍在一些外键或者中间表的处理中，如何遍历查询并获得所需的记录操作。

1、回顾单表的操作查询

我在《基于SqlSugar的开发框架的循序渐进介绍（1）–框架基础类的设计和使用》中介绍过的Customer表信息，就是一个单表的处理。

例如，我们对于一个简单的客户信息表，如下所示。

生成对应的实体对象CustomerInfo外，同时生成 CustomerPagedDto 的分页查询条件对象。

在继承基类后

/// <summary>
/// 应用层服务接口实现
/// </summary>
public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService
{
       ....
}

并重写 CreateFilteredQueryAsync 函数，从而实现了条件的精确查询处理。

    /// <summary>
    /// 应用层服务接口实现
    /// </summary>
    public class CustomerService : MyCrudService<CustomerInfo, string, CustomerPagedDto>, ICustomerService
    {
        /// <summary>
        /// 自定义条件处理
        /// </summary>
        /// <param name="input">查询条件Dto</param>
        /// <returns></returns>
        protected override ISugarQueryable<CustomerInfo> CreateFilteredQueryAsync(CustomerPagedDto input)
        {
            var query = base.CreateFilteredQueryAsync(input);

            query = query
                .WhereIF(!input.ExcludeId.IsNullOrWhiteSpace(), t => t.Id != input.ExcludeId) //不包含排除ID
                .WhereIF(!input.Name.IsNullOrWhiteSpace(), t => t.Name.Contains(input.Name)) //如需要精确匹配则用Equals
                                                                                             //年龄区间查询
                .WhereIF(input.AgeStart.HasValue, s => s.Age >= input.AgeStart.Value)
                .WhereIF(input.AgeEnd.HasValue, s => s.Age <= input.AgeEnd.Value)

                //创建日期区间查询
                .WhereIF(input.CreateTimeStart.HasValue, s => s.CreateTime >= input.CreateTimeStart.Value)
                .WhereIF(input.CreateTimeEnd.HasValue, s => s.CreateTime <= input.CreateTimeEnd.Value)
                ;

            return query;
        }

在表的对应实体信息没有其他表关联的时候，我们直接通过SQLSugar的基础接口返回对象列表即可。

通过 CreateFilteredQueryAsync 的精确条件处理，我们就可以明确实体类的查询条件处理，因此对于CustomerPagedDto来说，就是可以有客户端传入，服务后端的基类进行处理了。

如基类的分页条件查询函数GetListAsync就是根据这个来处理的，它的实现代码如下所示。

        /// <summary>
        /// 根据条件获取列表
        /// </summary>
        /// <param name="input">分页查询条件</param>
        /// <returns></returns>
        public virtual async Task<PagedResultDto<TEntity>> GetListAsync(TGetListInput input)
        {
            var query = CreateFilteredQueryAsync(input);
            var totalCount = await query.CountAsync();

            query = ApplySorting(query, input);
            query = ApplyPaging(query, input);

            var list = await query.ToListAsync();

            return new PagedResultDto<TEntity>(
               totalCount,
               list
           );
        }

也就是说只要继承了 CustomerService ，我们默认调用基类的 GetListAsync 就可以返回对应的列表记录了。

如在Web API的控制器中调用获取记录返回，调用处理的代码如下所示。

        /// <summary>
        /// 获取所有记录
        /// </summary>
        [HttpGet]
        [Route("all")]
        [HttpGet]public virtual async Task<ListResultDto<TEntity>> GetAllAsync()
        {
            //检查用户是否有权限，否则抛出MyDenyAccessException异常
            base.CheckAuthorized(AuthorizeKey.ListKey);

            return await _service.GetAllAsync();
        }

而对于Winform的调用，我们这里首先利用代码生成工具生成对应的界面和代码

查看其调用的界面代码

而其中GetData中的函数部分内容如下所示。

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        private async Task<IPagedResult<CustomerInfo>> GetData()
        {
            CustomerPagedDto pagerDto = null;
            if (advanceCondition != null)
            {
                //如果有高级查询，那么根据输入信息构建查询条件
                pagerDto = new CustomerPagedDto(this.winGridViewPager1.PagerInfo);
                pagerDto = dlg.GetPagedResult(pagerDto);
            }
            else
            {
                //构建分页的条件和查询条件
                pagerDto = new CustomerPagedDto(this.winGridViewPager1.PagerInfo)
                {
                    //添加所需条件
                    Name = this.txtName.Text.Trim(),
                };

                //日期和数值范围定义
                //年龄，需在CustomerPagedDto中添加 int? 类型字段AgeStart和AgeEnd
                var Age = new ValueRange<int?>(this.txtAge1.Text, this.txtAge2.Text); //数值类型
                pagerDto.AgeStart = Age.Start;
                pagerDto.AgeEnd = Age.End;

                //创建时间，需在CustomerPagedDto中添加 DateTime? 类型字段CreationTimeStart和CreationTimeEnd
                var CreationTime = new TimeRange(this.txtCreationTime1.Text, this.txtCreationTime2.Text); //日期类型
                pagerDto.CreateTimeStart = CreationTime.Start;
                pagerDto.CreateTimeEnd = CreationTime.End;
            }

            var result = await BLLFactory<CustomerService>.Instance.GetListAsync(pagerDto);
            return result;
        }

列表界面效果如下所示。

2、基于中间表的查询处理

前面的查询处理，主要就是针对没有任何关系的表实体对象的返回处理，但往往我们开发的时候，会涉及到很多相关的表，单独的表相对来说还是比较少，因此对表的关系遍历处理和中间表的关系转换，就需要在数据操作的时候考虑的了。

例如对于字典大类和字典项目的关系，如下所示。

以及在权限管理系统模块中，用户、角色、机构、权限等存在着很多中间表的关系，如下所示。

如对于字典表关系处理，我们采用Queryable<DictDataInfo, DictTypeInfo>的查询处理方式，可以联合两个表对象实体进行联合查询，如下代码所示。

        /// <summary>
        /// 根据字典类型名称获取所有该类型的字典列表集合(Key为名称，Value为值）
        /// </summary>
        /// <param name="dictTypeName">字典类型名称</param>
        /// <returns></returns>
        public async Task<Dictionary<string, string>> GetDictByDictType(string dictTypeName)
        {
            var query = this.Client.Queryable<DictDataInfo, DictTypeInfo>(
                (d, t) => d.DictType_ID == t.Id && t.Name == dictTypeName)
                .Select(d => d); //联合条件获取对象

            query = query.OrderBy(d => d.DictType_ID).OrderBy(d => d.Seq);//排序
            var list = await query.ToListAsync();//获取列表

            var dict = new Dictionary<string, string>();
            foreach (var info in list)
            {
                if (!dict.ContainsKey(info.Name))
                {
                    dict.Add(info.Name, info.Value);
                }
            }
            return dict;
        }

其中的Client对象是DbContext对象实例的Client属性，如下图所示。

这个对象是在DbContext对象中构建的，如下所示。

            this.Client = new SqlSugarScope(new ConnectionConfig()
            {
                DbType = this.DbType,
                ConnectionString = this.ConnectionString,
                InitKeyType = InitKeyType.Attribute,
                IsAutoCloseConnection = true, //是否自动关闭连接
                AopEvents = new AopEvents
                {
                    OnLogExecuting = (sql, p) =>
                    {
                        //Log.Information(sql);
                        //Log.Information(string.Join(",", p?.Select(it => it.ParameterName + ":" + it.Value)));
                    }
                }
            });

我们查看Queryable，可以看到这个SQLSugar基类函数 Queryable 提供了很多重载函数，也就是它们可以提供更多的表对象进行联合查询的，如下所示。

前面介绍的是外键的一对多的关系查询，通过两个对象之间进行的关系连接，从而实现另一个对象属性的对比查询操作的。

对于中间表的处理，也是类似的情况，我们通过对比中间表的属性，从而实现条件的过滤处理。如下是对于角色中相关关系的中间表查询。

        /// <summary>
        /// 根据用户ID获取对应的角色列表
        /// </summary>
        /// <param name="userID">用户ID</param>
        /// <returns></returns>
        private async Task<List<RoleInfo>> GetByUser(int userID)
        {
            var query = this.Client.Queryable<RoleInfo, User_RoleInfo>(
            (t, m) => t.Id == m.Role_ID && m.User_ID == userID)
            .Select(t => t); //联合条件获取对象

            query = query.OrderBy(t => t.CreateTime);//排序
            var list = await query.ToListAsync();//获取列表
            return list;
        }

        /// <summary>
        /// 根据机构获取对应的角色列表(判断机构角色中间表）
        /// </summary>
        /// <param name="ouID">机构的ID</param>
        /// <returns></returns>
        public async Task<List<RoleInfo>> GetRolesByOu(int ouID)
        {
            var query = this.Client.Queryable<RoleInfo, OU_RoleInfo>(
            (t, m) => t.Id == m.Role_ID && m.Ou_ID == ouID)
            .Select(t => t); //联合条件获取对象

            query = query.OrderBy(t => t.CreateTime);//排序
            var list = await query.ToListAsync();//获取列表
            return list;
        }

通过联合查询中间表对象信息，可以对它的字段属性进行条件联合，从而获得所需的记录。

这里User_RoleInfo和Ou_RoleInfo表也是根据中间表的属性生成的，不过它们在业务层并没有任何关联操作，也不需要生成对应的Service层，因此只需要生成相关的Model类实体即可。

    /// <summary>
    /// 用户角色关联
    /// </summary>
    [SugarTable("T_ACL_User_Role")]
    public class User_RoleInfo
    {         
        /// <summary>
        /// 用户ID
        /// </summary>
        [Required]
        public virtual int User_ID { get; set; }

        /// <summary>
        /// 角色ID
        /// </summary>
        [Required]
        public virtual int Role_ID { get; set; }

    }

    /// <summary>
    /// 机构角色关联
    /// </summary>
    [SugarTable("T_ACL_OU_Role")]
    public class OU_RoleInfo 
    {         
        /// <summary>
        /// 机构ID
        /// </summary>
        [Required]
        public virtual int Ou_ID { get; set; }

        /// <summary>
        /// 角色ID
        /// </summary>
        [Required]
        public virtual int Role_ID { get; set; }
    }

可以看到这两个实体不同于其他实体，它们没有基类继承关系，而一般标准的实体是有的。

    /// <summary>
    /// 角色信息
    /// </summary>
    [SugarTable("T_ACL_Role")]
    public class RoleInfo : Entity<int> {  }


    /// <summary>
    /// 功能菜单
    /// </summary>
    [SugarTable("T_ACL_Menu")]
    public class MenuInfo : Entity<string> { }

所以我们就不需要构建它们的Service层来处理数据，它的存在合理性只是在于能够和其他实体对象进行表的联合查询处理而且。

最后贴上一个整合SqlSugar处理而完成的系统基础框架的Winform端界面，其中包括用户、组织机构、角色管理、权限管理、菜单管理、日志、字典、客户信息等业务表的处理。

以证所言非虚。