基於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端界面，其中包括用戶、組織機構、角色管理、許可權管理、菜單管理、日誌、字典、客戶信息等業務表的處理。

以證所言非虛。

系列文章：

《基於SqlSugar的開發框架的循序漸進介紹（1）--框架基礎類的設計和使用》

《基於SqlSugar的開發框架循序漸進介紹（2）-- 基於中間表的查詢處理》

《基於SqlSugar的開發框架循序漸進介紹（3）-- 實現代碼生成工具Database2Sharp的整合開發》

專註於Winform開發框架/混合式開發框架、Web開發框架、Bootstrap開發框架、微信門戶開發框架的研究及應用。
  轉載請註明出處：
撰寫人：伍華聰  http://www.iqidi.com