自研ORM Include拆分查询(递归算法 支持无限层级) 性能优化探讨

 1                 Console.WriteLine($"总记录数:{db.Query().Count()}");
 2 
 3                 var stopwatch1 = new Stopwatch();
 4                 stopwatch1.Start();
 5                 var data1 = db.Query().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().ToList();
15 
16                 foreach (var item in data2)
17                 {
18                     item.Products = db.Query().Where(w => w.CategoryId == item.CategoryId).ToList();
19                 }
20                 stopwatch2.Stop();
21 
22                 Console.WriteLine($"循环查询 耗时:{stopwatch2.ElapsedMilliseconds} ms {stopwatch2.ElapsedMilliseconds / 1000.00}s");

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

 1         /// 

 2         /// 包括
 3         /// 
 4         /// 
 5         /// 表达式
 6         /// 
 7         public IInclude Include(Expression> 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(ado, QueryBuilder, includeInfo);
51         }

 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     /// 

13     /// 包括信息
14     /// 
15     public class IncludeInfo
16     {
17         /// 

18         /// 属性类型
19         /// 
20         public Type PropertyType { get; set; }
21 
22         /// 

23         /// 类型
24         /// 
25         public Type Type { get; set; }
26 
27         /// 

28         /// 属性名称
29         /// 
30         public string PropertyName { get; set; }
31 
32         /// 

33         /// 实体数据库映射
34         /// 
35         public EntityDbMapping EntityDbMapping { get; set; }
36 
37         /// 

38         /// 条件列
39         /// 
40         public string WhereColumn { get; set; }
41 
42         /// 

43         /// 查询建造
44         /// 
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     /// 

 18     /// 查询建造扩展类
 19     /// 
 20     public static class QueryBuilderExtensions
 21     {
 22 
 23         private static readonly MethodInfo fristBuildMethod;
 24 
 25         private static readonly MethodInfo listBuildMethod;
 26         private static readonly MethodInfo ofTypeMethod;
 27 
 28         private static readonly MethodInfo ofObjTypeMethod;
 29         private static readonly MethodInfo ofObjTypeGenericMethod;
 30 
 31         private static readonly MethodInfo toArrayMethod;
 32 
 33         private static readonly MethodInfo toListMethod;
 34 
 35         private static readonly MethodInfo toObjListMethod;
 36         private static readonly MethodInfo toObjListGenericMethod;
 37 
 38         /// 

 39         /// 构造方法
 40         /// 
 41         static QueryBuilderExtensions()
 42         {
 43             fristBuildMethod = typeof(DbDataReaderExtensions).GetMethod("FristBuild", new Type[] { typeof(DbDataReader) });
 44 
 45             listBuildMethod = typeof(DbDataReaderExtensions).GetMethod("ListBuild", new Type[] { typeof(DbDataReader) });
 46 
 47             ofTypeMethod = typeof(Enumerable).GetMethod("OfType");
 48 
 49             ofObjTypeMethod = typeof(Enumerable).GetMethod("OfType");
 50             ofObjTypeGenericMethod = ofObjTypeMethod.MakeGenericMethod(typeof(object));
 51 
 52             toArrayMethod = typeof(Enumerable).GetMethod("ToArray");
 53 
 54             toListMethod = typeof(Enumerable).GetMethod("ToList");
 55 
 56             toObjListMethod = typeof(Enumerable).GetMethod("ToList");
 57             toObjListGenericMethod = toObjListMethod.MakeGenericMethod(typeof(object));
 58         }
 59 
 60         /// 

 61         /// 初始化
 62         /// 
 63         /// 数据库类型
 64         /// 包括信息
 65         /// 是否多结果
 66         private static void Init(Models.DbType dbType, IncludeInfo includeInfo, bool isMultipleResult)
 67         {
 68             var identifier = dbType.MappingIdentifier();
 69             var parameterSymbol = dbType.MappingParameterSymbol();
 70 
 71             //条件列
 72             if (string.IsNullOrWhiteSpace(includeInfo.WhereColumn))
 73             {
 74                 var whereColumn = includeInfo.QueryBuilder.EntityDbMapping.ColumnsInfos.FirstOrDefault(f => f.IsPrimaryKey || f.ColumnName.ToUpper().EndsWith("ID"));
 75                 includeInfo.WhereColumn = whereColumn.ColumnName;
 76             }
 77 
 78             //排序列
 79             var orderByColumn = includeInfo.EntityDbMapping.ColumnsInfos.FirstOrDefault(f => f.IsPrimaryKey || f.ColumnName.ToUpper().EndsWith("ID"));
 80             if (orderByColumn != null)
 81             {
 82                 if (includeInfo.QueryBuilder.OrderBy.Count == 0)
 83                 {
 84                     includeInfo.QueryBuilder.OrderBy.Add($"{identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, orderByColumn.ColumnName)}");
 85                 }
 86             }
 87 
 88             if (!isMultipleResult)
 89             {
 90                 includeInfo.QueryBuilder.Where.Add($"{identifier.Insert(1, includeInfo.EntityDbMapping.Alias)}.{identifier.Insert(1, includeInfo.WhereColumn)} = {parameterSymbol}{includeInfo.WhereColumn}");
 91             }
 92 
 93             var joinInfo = new JoinInfo();
 94             joinInfo.IsInclude = true;
 95             joinInfo.JoinType = JoinType.Inner;
 96             joinInfo.EntityDbMapping = includeInfo.EntityDbMapping;
 97             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)}";
 98 
 99             includeInfo.QueryBuilder.Join.Add(joinInfo);
100         }
101 
102         /// 

103         /// Include数据绑定
104         /// 
105         /// /// 查询建造
106         /// Ado
107         /// 对象
108         /// 
109         public static void IncludeDataBind(this QueryBuilder queryBuilder, IAdo ado, object obj)
110         {
111             if (queryBuilder.IncludeInfos.Count > 0 && obj != null)
112             {
113                 var type = obj.GetType();
114 
115                 var isMultipleResult = false;
116 
117                 if (type.IsArray)
118                 {
119                     isMultipleResult = true;
120                     type = type.GetElementType();
121                 }
122                 else if (type.IsGenericType)
123                 {
124                     isMultipleResult = true;
125                     type = type.GenericTypeArguments[0];
126                 }
127 
128                 foreach (var includeInfo in queryBuilder.IncludeInfos)
129                 {
130                     Init(ado.DbOptions.DbType, includeInfo, isMultipleResult);
131 
132                     var propertyInfo = type.GetProperty(includeInfo.PropertyName);
133 
134                     object data = null;
135 
136                     if (!isMultipleResult)
137                     {
138                         var parameterValue = type.GetProperty(includeInfo.WhereColumn).GetValue(obj);
139                         includeInfo.QueryBuilder.DbParameters.Add(new DbParameterEx(includeInfo.WhereColumn, parameterValue));
140                     }
141 
142                     var sql = includeInfo.QueryBuilder.ToSqlString();
143                     var reader = ado.ExecuteReader(CommandType.Text, sql, ado.CreateParameter(includeInfo.QueryBuilder.DbParameters));
144 
145                     var fristBuildGenericMethod = fristBuildMethod.MakeGenericMethod(includeInfo.Type);
146 
147                     var listBuildGenericMethod = listBuildMethod.MakeGenericMethod(includeInfo.Type);
148 
149                     var ofTypeGenericMethod = ofTypeMethod.MakeGenericMethod(includeInfo.Type);
150 
151                     var toArrayGenericMethod = toArrayMethod.MakeGenericMethod(includeInfo.Type);
152 
153                     var toListGenericMethod = toListMethod.MakeGenericMethod(includeInfo.Type);
154 
155                     if (isMultipleResult)
156                     {
157                         data = listBuildGenericMethod.Invoke(null, new object[] { reader });
158 
159                         var list = ofObjTypeGenericMethod.Invoke(null, new object[] { data });
160 
161                         list = toObjListGenericMethod.Invoke(null, new object[] { data });
162 
163                         var objList = list as Listobject>;
164 
165                         if (objList.Any())
166                         {
167                             var whereColumnProInfo = objList.FirstOrDefault()?.GetType().GetProperty(includeInfo.WhereColumn);
168                             if (whereColumnProInfo != null)
169                             {
170                                 foreach (var item in obj as IList)
171                                 {
172                                     var parameterValue = type.GetProperty(includeInfo.WhereColumn).GetValue(item);
173 
174                                     object value = null;
175 
176                                     if (includeInfo.PropertyType.IsArray || includeInfo.PropertyType.IsGenericType)
177                                     {
178                                         value = objList.Where(w => Convert.ToString(whereColumnProInfo.GetValue(w)) == Convert.ToString(parameterValue)).ToList();
179 
180                                         value = ofTypeGenericMethod.Invoke(null, new object[] { value });
181 
182                                         if (includeInfo.PropertyType.IsArray)
183                                         {
184                                             value = toArrayGenericMethod.Invoke(null, new object[] { value });
185                                         }
186                                         else if (includeInfo.PropertyType.IsGenericType)
187                                         {
188                                             value = toListGenericMethod.Invoke(null, new object[] { value });
189                                         }
190                                     }
191                                     else
192                                     {
193                                         value = objList.FirstOrDefault(w => Convert.ToString(whereColumnProInfo.GetValue(w)) == Convert.ToString(parameterValue)).ChanageType(includeInfo.PropertyType);
194                                     }
195 
196                                     propertyInfo.SetValue(item, value);
197                                 }
198                             }
199                         }
200                     }
201                     else
202                     {
203                         if (includeInfo.PropertyType.IsArray || includeInfo.PropertyType.IsGenericType)
204                         {
205                             data = listBuildGenericMethod.Invoke(null, new object[] { reader });
206 
207                             if (includeInfo.PropertyType.IsArray)
208                             {
209                                 data = toArrayGenericMethod.Invoke(null, new object[] { data });
210                             }
211                         }
212                         else
213                         {
214                             data = fristBuildGenericMethod.Invoke(null, new object[] { reader });
215                         }
216                         propertyInfo.SetValue(obj, data);
217                     }
218 
219                     if (includeInfo.QueryBuilder.IncludeInfos.Count > 0)
220                     {
221                         includeInfo.QueryBuilder.IncludeDataBind(ado, data);
222                     }
223 
224                 }
225             }
226         }
227 
228     }
229 }