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
