10
自研ORM Include拆分查询(递归算法 支持无限层级) 性能优化探讨 - China-Mr-zhong
source link: https://www.cnblogs.com/China-Mr-zhong/p/17025500.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
自研ORM Include拆分查询(递归算法 支持无限层级) 性能优化探讨
最近我在优化 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 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 /// <summary>
39 /// 构造方法
40 /// </summary>
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 /// <summary>
61 /// 初始化
62 /// </summary>
63 /// <param name="dbType">数据库类型</param>
64 /// <param name="includeInfo">包括信息</param>
65 /// <param name="isMultipleResult">是否多结果</param>
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 /// <summary>
103 /// Include数据绑定
104 /// </summary>
105 /// /// <param name="queryBuilder">查询建造</param>
106 /// <param name="ado">Ado</param>
107 /// <param name="obj">对象</param>
108 /// <returns></returns>
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 List<object>;
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 }
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK