基于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> { }