自研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 拆分查询 支持自定义条件、排序、选择

最新文章

热门文章

自研ORM框架实现类似EF Core Include 拆分查询支持自定义条件、排序、选择