最近我在优化 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 }
翻译
搜索
复制
