自研ORM框架實現類似EF Core Include 拆分查詢支援自定義條件、排序、選擇

Baozi, I'm Mr.Zhong I like to brush TikTok, I know that anchors like to call it that, haha!
Recently, I haven't been so busy, and it took almost a day to add some APIs to the self-developed ORM framework to perfectly implement the Include query

寶子們，我是 Mr.Zhong 喜歡刷抖音都知道主播都喜歡這麼叫，哈哈！
最近沒那麼忙了，抽空給自研的ORM 框架新增一些API 花了將近一天的時間完美實現 Include 查詢

一、介面定義

 1     /// <summary>
 2     /// 包括介面類
 3     /// </summary>
 4     /// <typeparam name="T"></typeparam>
 5     /// <typeparam name="TProperty"></typeparam>
 6     public interface IInclude<T, TProperty> : IQuery<T> where TProperty : class, new()
 7     {
 8         /// <summary>
 9         /// Ado
10         /// </summary>
11         IAdo Ado { get; }
12     }

二、介面實現

 1     /// <summary>
 2     /// 包括實現類
 3     /// </summary>
 4     /// <typeparam name="T"></typeparam>
 5     public class IncludeProvider<T, TProperty> : QueryProvider<T>, IInclude<T, TProperty> where TProperty : class, new()
 6     {
 7         /// <summary>
 8         /// Ado
 9         /// </summary>
10         public IAdo Ado { get; }
11 
12         /// <summary>
13         /// 構造方法
14         /// </summary>
15         /// <param name="ado"></param>
16         /// <param name="queryBuilder">查詢構建</param>
17         public IncludeProvider(IAdo ado, IQueryBuilder queryBuilder) : base(ado, queryBuilder)
18         {
19             this.Ado = ado;
20         }
21     }

三、擴充套件方法實現還可以擴充套件很多功能只有你想不到，沒有...

  1     /// <summary>
  2     /// 包括擴充套件類
  3     /// </summary>
  4     public static class IncludeExtensions
  5     {
  6         /// <summary>
  7         /// 然後包括
  8         /// </summary>
  9         /// <typeparam name="T"></typeparam>
 10         /// <typeparam name="TPreviousProperty"></typeparam>
 11         /// <param name="include">包括</param>
 12         /// <returns></returns>
 13         public static IInclude<T, TPreviousProperty> Visit<T, TPreviousProperty>(this IInclude<T, List<TPreviousProperty>> include) where TPreviousProperty : class, new()
 14         {
 15             return new IncludeProvider<T, TPreviousProperty>(include.Ado, include.QueryBuilder);
 16         }
 17 
 18         /// <summary>
 19         /// 然後包括
 20         /// </summary>
 21         /// <typeparam name="T"></typeparam>
 22         /// <typeparam name="TProperty"></typeparam>
 23         /// <param name="include">包括</param>
 24         /// <param name="expression">表示式</param>
 25         /// <returns></returns>
 26         public static IInclude<T, TProperty> ThenInclude<T, TPreviousProperty, TProperty>(this IInclude<T, List<TPreviousProperty>> include, Expression<Func<TPreviousProperty, TProperty>> expression) where TProperty : class, new()
 27         {
 28             return new IncludeProvider<T, TProperty>(include.Ado, include.QueryBuilder);
 29         }
 30 
 31         /// <summary>
 32         /// 條件
 33         /// </summary>
 34         /// <typeparam name="T"></typeparam>
 35         /// <typeparam name="TProperty"></typeparam>
 36         /// <param name="include">包括</param>
 37         /// <param name="expression">表示式</param>
 38         /// <returns></returns>
 39         public static IInclude<T, TProperty> Where<T, TProperty>(this IInclude<T, TProperty> include, Expression<Func<T, TProperty, bool>> expression) where TProperty : class, new()
 40         {
 41             var queryBuilder = include.QueryBuilder.IncludeInfos.Last().QueryBuilder;
 42 
 43             queryBuilder.EntityDbMapping.Alias = expression.Parameters[0]?.Name;
 44             include.QueryBuilder.IncludeInfos.Last().EntityDbMapping.Alias = expression.Parameters[1]?.Name;
 45 
 46             var result = expression.ResolveSql(new ResolveSqlOptions()
 47             {
 48                 DbType = include.Ado.DbOptions.DbType,
 49                 ResolveSqlType = ResolveSqlType.Where,
 50                 ParameterIndex = queryBuilder.DbParameters.Count + 1
 51             });
 52 
 53             queryBuilder.Where.Add(result.SqlString);
 54             queryBuilder.DbParameters.AddRange(result.DbParameters);
 55             return include;
 56         }
 57 
 58         /// <summary>
 59         /// 排序
 60         /// </summary>
 61         /// <typeparam name="T"></typeparam>
 62         /// <typeparam name="TProperty"></typeparam>
 63         /// <param name="include">包括</param>
 64         /// <param name="orderFields">排序欄位</param>
 65         /// <param name="orderByType">排序型別</param>
 66         /// <returns></returns>
 67         public static IInclude<T, TProperty> OrderBy<T, TProperty>(this IInclude<T, TProperty> include, List<string> orderFields, OrderByType orderByType = OrderByType.ASC) where TProperty : class, new()
 68         {
 69             var queryBuilder = include.QueryBuilder.IncludeInfos.Last().QueryBuilder;
 70 
 71             queryBuilder.OrderBy.Add($"{string.Join(",", orderFields)} {orderByType}");
 72             return include;
 73         }
 74 
 75         /// <summary>
 76         /// 排序
 77         /// </summary>
 78         /// <typeparam name="T"></typeparam>
 79         /// <typeparam name="TProperty"></typeparam>
 80         /// <param name="include">包括</param>
 81         /// <param name="expression">表示式</param>
 82         /// <param name="orderByType">排序型別</param>
 83         /// <returns></returns>
 84         public static IInclude<T, TProperty> OrderBy<T, TProperty>(this IInclude<T, TProperty> include, Expression<Func<T, TProperty, object>> expression, OrderByType orderByType = OrderByType.ASC) where TProperty : class, new()
 85         {
 86             var queryBuilder = include.QueryBuilder.IncludeInfos.Last().QueryBuilder;
 87 
 88             queryBuilder.EntityDbMapping.Alias = expression.Parameters[0]?.Name;
 89             include.QueryBuilder.IncludeInfos.Last().EntityDbMapping.Alias = expression.Parameters[1]?.Name;
 90 
 91             var result = expression.ResolveSql(new ResolveSqlOptions()
 92             {
 93                 DbType = include.Ado.DbOptions.DbType,
 94                 ResolveSqlType = ResolveSqlType.OrderBy
 95             });
 96 
 97             queryBuilder.OrderBy.Add($"{result.SqlString} {orderByType}");
 98             return include;
 99         }
100 
101         /// <summary>
102         /// 選擇
103         /// </summary>
104         /// <typeparam name="T"></typeparam>
105         /// <typeparam name="TProperty"></typeparam>
106         /// <param name="include">包括</param>
107         /// <param name="expression">表示式</param>
108         /// <returns></returns>
109         public static IInclude<T, TProperty> Select<T, TProperty>(this IInclude<T, TProperty> include, Expression<Func<T, TProperty, object>> expression) where TProperty : class, new()
110         {
111             var queryBuilder = include.QueryBuilder.IncludeInfos.Last().QueryBuilder;
112 
113             queryBuilder.EntityDbMapping.Alias = expression.Parameters[0]?.Name;
114             include.QueryBuilder.IncludeInfos.Last().EntityDbMapping.Alias = expression.Parameters[1]?.Name;
115 
116             var result = expression.ResolveSql(new ResolveSqlOptions()
117             {
118                 DbType = include.Ado.DbOptions.DbType,
119                 ResolveSqlType = ResolveSqlType.NewAs
120             });
121 
122             queryBuilder.Columns = result.SqlString;
123             queryBuilder.DbParameters.AddRange(result.DbParameters);
124             return include;
125         }
126 
127     }
128 }

四、使用範例

1 var data = await db.Query<Category>().Include(a => a.Products).Visit().OrderBy((a1, a2) => a2.CreateTime, OrderByType.DESC).ToListAsync();

五、生成的SQL語句

SELECT a1.`CategoryId`,a1.`CategoryName`,a2.`ProductId`,a2.`CategoryId`,a2.`ProductCode`,a2.`ProductName`,a2.`DeleteMark`,a2.`CreateTime`,a2.`ModifyTime`,a2.`Custom1`,a2.`Custom2`,a2.`Custom3`,a2.`Custom4`,a2.`Custom5`,a2.`Custom6`,a2.`Custom7`,a2.`Custom8`,a2.`Custom9`,a2.`Custom10`,a2.`Custom11`,a2.`Custom12` FROM `Category` `a1`
INNER JOIN `Product` `a2` ON `a1`.`CategoryId` = `a2`.`CategoryId`
WHERE `a1`.`CategoryId` = @CategoryId
ORDER BY `a2`.`CreateTime` DESC

自研ORM框架 實現類似EF Core Include 拆分查詢 支援自定義條件、排序、選擇

自研ORM框架實現類似EF Core Include 拆分查詢支援自定義條件、排序、選擇