最近我在优化 Include 拆分查询，贴出源码供大家交流探讨是否还有优化空间。
测试代码
1 Console.WriteLine($"总记录数:{db.Query<Category>().Count()}"); 2 3 var stopwatch1 = new Stopwatch(); 4 stopwatch1.Start(); 5 var data1 = db.Query<Category>().Include(i => i.Products).ToList(); 6 stopwatch1.Stop(); 7 8 Console.WriteLine($"Include查询 耗时:{stopwatch1.ElapsedMilliseconds} ms {stopwatch1.ElapsedMilliseconds / 1000.00}s"); 9 10 //Console.WriteLine(Json.Serialize(data1[0].Products[0])); 11 12 var stopwatch2 = new Stopwatch(); 13 stopwatch2.Start(); 14 var data2 = db.Query<Category>().ToList(); 15 16 foreach (var item in data2) 17 { 18 item.Products = db.Query<Product>().Where(w => w.CategoryId == item.CategoryId).ToList(); 19 } 20 stopwatch2.Stop(); 21 22 Console.WriteLine($"循环查询 耗时:{stopwatch2.ElapsedMilliseconds} ms {stopwatch2.ElapsedMilliseconds / 1000.00}s");
测试结果
Include 生成的Sql语句
SELECT `CategoryId`,`CategoryName` FROM `Category` -------------------------- -------------------------- SELECT a.`CategoryId`,a.`CategoryName`,b.`ProductId`,b.`CategoryId`,b.`ProductCode`,b.`ProductName`,b.`DeleteMark`,b.`CreateTime`,b.`Custom1`,b.`Custom2`,b.`Custom3`,b.`Custom4`,b.`Custom5`,b.`Custom6`,b.`Custom7`,b.`Custom8`,b.`Custom9`,b.`Custom10`,b.`Custom11`,b.`Custom12` FROM `Category` `a` INNER JOIN `Product` `b` ON `a`.`CategoryId` = `b`.`CategoryId` ORDER BY `b`.`ProductId`
Include 方法实现
1 /// <summary> 2 /// 包括 3 /// </summary> 4 /// <typeparam name="TProperty"></typeparam> 5 /// <param name="expression">表达式</param> 6 /// <returns></returns> 7 public IInclude<T, TProperty> Include<TProperty>(Expression<Func<T, TProperty>> expression) where TProperty : class 8 { 9 var result = expression.ResolveSql(new ResolveSqlOptions() 10 { 11 DbType = ado.DbOptions.DbType, 12 ResolveSqlType = ResolveSqlType.NewColumn, 13 IgnoreParameter = true, 14 IgnoreIdentifier = true 15 }); 16 17 var propertyType = typeof(TProperty); 18 19 if (QueryBuilder.IncludeInfos.Any(a => a.PropertyType.FullName == propertyType.FullName)) 20 { 21 throw new Exception($"属性名称:{result.SqlString} 不能重复使用Include方法."); 22 } 23 24 var type = propertyType; 25 26 if (type.IsArray) 27 { 28 type = type.GetElementType(); 29 } 30 else if (type.IsGenericType) 31 { 32 type = type.GenericTypeArguments[0]; 33 } 34 35 var queryBuilder = SqlBuilderFactory.CreateQueryBuilder(ado.DbOptions.DbType); 36 queryBuilder.EntityDbMapping = typeof(T).GetEntityDbMapping(); 37 queryBuilder.EntityDbMapping.Alias = "a"; 38 39 var includeInfo = new IncludeInfo(); 40 includeInfo.EntityDbMapping = type.GetEntityDbMapping(); 41 includeInfo.EntityDbMapping.Alias = "b"; 42 43 includeInfo.PropertyName = result.SqlString; 44 includeInfo.PropertyType = propertyType; 45 includeInfo.Type = type; 46 includeInfo.QueryBuilder = queryBuilder; 47 48 QueryBuilder.IncludeInfos.Add(includeInfo); 49 50 return new IncludeProvider<T, TProperty>(ado, QueryBuilder, includeInfo); 51 }
IncludeInfo 实体结构1 using Fast.Framework.Abstract; 2 using Fast.Framework.Interfaces; 3 using System; 4 using System.Collections.Generic; 5 using System.Linq; 6 using System.Text; 7 using System.Threading.Tasks; 8 9 namespace Fast.Framework.Models 10 { 11 12 /// <summary> 13 /// 包括信息 14 /// </summary> 15 public class IncludeInfo 16 { 17 /// <summary> 18 /// 属性类型 19 /// </summary> 20 public Type PropertyType { get; set; } 21 22 /// <summary> 23 /// 类型 24 /// </summary> 25 public Type Type { get; set; } 26 27 /// <summary> 28 /// 属性名称 29 /// </summary> 30 public string PropertyName { get; set; } 31 32 /// <summary> 33 /// 实体数据库映射 34 /// </summary> 35 public EntityDbMapping EntityDbMapping { get; set; } 36 37 /// <summary> 38 /// 条件列 39 /// </summary> 40 public string WhereColumn { get; set; } 41 42 /// <summary> 43 /// 查询建造 44 /// </summary> 45 public QueryBuilder QueryBuilder { get; set; } 46 47 } 48 }
数据绑定核心类
1 using System; 2 using System.Reflection; 3 using System.Collections; 4 using System.Collections.Generic; 5 using System.Data.Common; 6 using System.Data; 7 using System.Linq; 8 using System.Text; 9 using System.Threading.Tasks; 10 using Fast.Framework.Abstract; 11 using Fast.Framework.Interfaces; 12 using Fast.Framework.Models; 13 14 namespace Fast.Framework.Extensions 15 { 16 17 /// <summary> 18 /// 查询建造扩展类 19 /// </summary> 20 public static class QueryBuilderExtensions 21 { 22 23 private static readonly MethodInfo fristBuildMethod; 24 25 private static readonly MethodInfo listBuildMethod; 26 27 private static readonly MethodInfo ofTypeMethod; 28 29 private static readonly MethodInfo ofObjTypeMethod; 30 private static readonly MethodInfo ofObjTypeGenericMethod; 31 32 private static readonly MethodInfo toArrayMethod; 33 34 private static readonly MethodInfo toListMethod; 35 36 private static readonly MethodInfo toObjListMethod; 37 private static readonly MethodInfo toObjListGenericMethod; 38 39 /// <summary> 40 /// 构造方法 41 /// </summary> 42 static QueryBuilderExtensions() 43 { 44 fristBuildMethod = typeof(DbDataReaderExtensions).GetMethod("FristBuild", new Type[] { typeof(DbDataReader) }); 45 46 listBuildMethod = typeof(DbDataReaderExtensions).GetMethod("ListBuild", new Type[] { typeof(DbDataReader) }); 47 48 ofTypeMethod = typeof(Enumerable).GetMethod("OfType"); 49 50 ofObjTypeMethod = typeof(Enumerable).GetMethod("OfType"); 51 ofObjTypeGenericMethod = ofObjTypeMethod.MakeGenericMethod(typeof(object)); 52 53 toArrayMethod = typeof(Enumerable).GetMethod("ToArray"); 54 55 toListMethod = typeof(Enumerable).GetMethod("ToList"); 56 57 toObjListMethod = typeof(Enumerable).GetMethod("ToList"); 58 toObjListGenericMethod = toObjListMethod.MakeGenericMethod(typeof(object)); 59 } 60 61 /// <summary> 62 /// 初始化 63 /// </summary> 64 /// <param name="dbType">数据库类型</param> 65 /// <param name="includeInfo">包括信息</param> 66 /// <param name="isList">是否列表</param> 67 private static void Init(Models.DbType dbType, IncludeInfo includeInfo, bool isList) 68 { 69 var identifier = dbType.MappingIdentifier(); 70 var parameterSymbol = dbType.MappingParameterSymbol(); 71 72 //条件列 73 if (string.IsNullOrWhiteSpace(includeInfo.WhereColumn)) 74 { 75 var whereColumn = includeInfo.QueryBuilder.EntityDbMapping.ColumnsInfos.FirstOrDefault(f => f.IsPrimaryKey || f.ColumnName.ToUpper().EndsWith("ID")); 76 includeInfo.WhereColumn = whereColumn.ColumnName; 77 } 78 79 //排序列 80 var orderByColumn = includeInfo.EntityDbMapping.ColumnsInfos.FirstOrDefault(f => f.IsPrimaryKey || f.ColumnName.ToUpper().EndsWith("ID")); 81 if (orderByColumn != null) 82 { 83 if (includeInfo.QueryBuilder.OrderBy.Count == 0) 84 { 85 includeInfo.QueryBuilder.OrderBy.Add($"{identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, orderByColumn.ColumnName)}"); 86 } 87 } 88 89 if (!isList) 90 { 91 includeInfo.QueryBuilder.Where.Add($"{identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, includeInfo.WhereColumn)} = {parameterSymbol}{includeInfo.WhereColumn}"); 92 } 93 94 var joinInfo = new JoinInfo(); 95 joinInfo.IsInclude = true; 96 joinInfo.JoinType = JoinType.Inner; 97 joinInfo.EntityDbMapping = includeInfo.EntityDbMapping; 98 joinInfo.Where = $"{identifier.Insert(1, includeInfo.QueryBuilder.EntityDbMapping.Alias)}.{identifier.Insert(1, includeInfo.WhereColumn)} = {identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, includeInfo.WhereColumn)}"; 99 100 includeInfo.QueryBuilder.Join.Add(joinInfo); 101 } 102 103 /// <summary> 104 /// Include数据绑定 105 /// </summary> 106 /// /// <param name="queryBuilder">查询建造</param> 107 /// <param name="ado">Ado</param> 108 /// <param name="obj">对象</param> 109 /// <returns></returns> 110 public static void IncludeDataBind(this QueryBuilder queryBuilder, IAdo ado, object obj) 111 { 112 if (queryBuilder.IncludeInfos.Count > 0 && obj != null) 113 { 114 var type = obj.GetType(); 115 116 var isMultipleResult = false; 117 118 if (type.IsArray) 119 { 120 isMultipleResult = true; 121 type = type.GetElementType(); 122 } 123 else if (type.IsGenericType) 124 { 125 isMultipleResult = true; 126 type = type.GenericTypeArguments[0]; 127 } 128 129 foreach (var includeInfo in queryBuilder.IncludeInfos) 130 { 131 Init(ado.DbOptions.DbType, includeInfo, isMultipleResult); 132 133 var propertyInfo = type.GetProperty(includeInfo.PropertyName); 134 135 object data = null; 136 137 if (!isMultipleResult) 138 { 139 var parameterValue = type.GetProperty(includeInfo.WhereColumn).GetValue(obj); 140 includeInfo.QueryBuilder.DbParameters.Add(new DbParameterEx(includeInfo.WhereColumn, parameterValue)); 141 } 142 143 var sql = includeInfo.QueryBuilder.ToSqlString(); 144 var reader = ado.ExecuteReader(CommandType.Text, sql, ado.CreateParameter(includeInfo.QueryBuilder.DbParameters)); 145 146 var fristBuildGenericMethod = fristBuildMethod.MakeGenericMethod(includeInfo.Type); 147 148 var listBuildGenericMethod = listBuildMethod.MakeGenericMethod(includeInfo.Type); 149 150 var ofTypeGenericMethod = ofTypeMethod.MakeGenericMethod(includeInfo.Type); 151 152 var toArrayGenericMethod = toArrayMethod.MakeGenericMethod(includeInfo.Type); 153 154 var toListGenericMethod = toListMethod.MakeGenericMethod(includeInfo.Type); 155 156 if (isMultipleResult) 157 { 158 data = listBuildGenericMethod.Invoke(null, new object[] { reader }); 159 160 data = ofObjTypeGenericMethod.Invoke(null, new object[] { data }); 161 162 data = toObjListGenericMethod.Invoke(null, new object[] { data }); 163 164 var list = data as List<object>; 165 166 if (list.Any()) 167 { 168 var whereColumnProInfo = list.FirstOrDefault()?.GetType().GetProperty(includeInfo.WhereColumn); 169 if (whereColumnProInfo != null) 170 { 171 foreach (var item in obj as IList) 172 { 173 var parameterValue = type.GetProperty(includeInfo.WhereColumn).GetValue(item); 174 175 object value = null; 176 177 if (includeInfo.PropertyType.IsArray || includeInfo.PropertyType.IsGenericType) 178 { 179 value = list.Where(w => Convert.ToString(whereColumnProInfo.GetValue(w)) == Convert.ToString(parameterValue)); 180 181 value = ofTypeGenericMethod.Invoke(null, new object[] { value }); 182 183 if (includeInfo.PropertyType.IsArray) 184 { 185 value = toArrayGenericMethod.Invoke(null, new object[] { value }); 186 } 187 else if (includeInfo.PropertyType.IsGenericType) 188 { 189 value = toListGenericMethod.Invoke(null, new object[] { value }); 190 } 191 } 192 else 193 { 194 value = list.FirstOrDefault(w => Convert.ToString(whereColumnProInfo.GetValue(w)) == Convert.ToString(parameterValue)).ChanageType(includeInfo.PropertyType); 195 } 196 197 propertyInfo.SetValue(item, value); 198 } 199 } 200 } 201 } 202 else 203 { 204 if (includeInfo.PropertyType.IsArray || includeInfo.PropertyType.IsGenericType) 205 { 206 data = listBuildGenericMethod.Invoke(null, new object[] { reader }); 207 208 if (includeInfo.PropertyType.IsArray) 209 { 210 data = toArrayGenericMethod.Invoke(null, new object[] { data }); 211 } 212 } 213 else 214 { 215 data = fristBuildGenericMethod.Invoke(null, new object[] { reader }); 216 } 217 propertyInfo.SetValue(obj, data); 218 } 219 220 if (includeInfo.QueryBuilder.IncludeInfos.Count > 0) 221 { 222 includeInfo.QueryBuilder.IncludeDataBind(ado, data); 223 } 224 225 } 226 } 227 } 228 229 } 230 }
翻译
搜索
复制