最近我在优化 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 }
翻译
搜索
复制
查看更多关于自研ORM Include拆分查询(递归算法 支持无限层级) 性能优化探讨的详细内容...
声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did238175