net core 使用 SqlSugar

来源：cnblogs　　作者：調調　　时间：2018/11/8 9:55:36　　对本文有异议

 1  /// <summary>
 2     ///  SqlSugar 注入Service的扩展方法
 3     /// </summary>
 4     public static class SqlSugarServiceCollectionExtensions
 5     {
 6         /// <summary>
 7         /// SqlSugar上下文注入
 8         /// </summary>
 9         /// <typeparam name="TSugarContext">要注册的上下文的类型</typeparam>
10         /// <param name="serviceCollection"></param>
11         /// <param name="configAction"></param>
12         /// <param name="lifetime">用于在容器中注册TSugarClient服务的生命周期</param>
13         /// <returns></returns>
14         public static IServiceCollection AddSqlSugarClient<TSugarContext>(this IServiceCollection serviceCollection, Action<IServiceProvider, ConnectionConfig> configAction, ServiceLifetime lifetime = ServiceLifetime.Singleton)
15             where TSugarContext : IDbFactory
16         {
17             serviceCollection.AddMemoryCache().AddLogging();
18             serviceCollection.TryAdd(new ServiceDescriptor(typeof(ConnectionConfig), p => ConnectionConfigFactory(p, configAction), lifetime));
19             serviceCollection.Add(new ServiceDescriptor(typeof(ConnectionConfig), p => ConnectionConfigFactory(p, configAction), lifetime));
20             serviceCollection.TryAdd(new ServiceDescriptor(typeof(TSugarContext), typeof(TSugarContext), lifetime));
21             return serviceCollection;
22         }
23 
24         private static ConnectionConfig ConnectionConfigFactory(IServiceProvider applicationServiceProvider, Action<IServiceProvider, ConnectionConfig> configAction)
25         {
26             var config = new ConnectionConfig();
27             configAction.Invoke(applicationServiceProvider, config);
28             return config;
29         }
30     }

注入扩展

1 public interface IDbFactory
2     {
3         SqlSugarClient GetDbContext(Action<Exception> onErrorEvent);
4         SqlSugarClient GetDbContext(Action<string, SugarParameter[]> onExecutedEvent);
5         SqlSugarClient GetDbContext(Func<string, SugarParameter[], KeyValuePair<string, SugarParameter[]>> onExecutingChangeSqlEvent);
6         SqlSugarClient GetDbContext(Action<string, SugarParameter[]> onExecutedEvent = null, Func<string, SugarParameter[], KeyValuePair<string, SugarParameter[]>> onExecutingChangeSqlEvent = null, Action<Exception> onErrorEvent = null);
7     }

IDbFactory

 1 public class DbFactory : IDbFactory
 2     {
 3         private readonly ILogger _logger;
 4         private readonly ConnectionConfig _config;
 5 
 6         public DbFactory(ConnectionConfig config, ILogger<DbFactory> logger)
 7         {
 8             this._logger = logger;
 9             this._config = config;
10         }
11 
12         public SqlSugarClient GetDbContext(Action<Exception> onErrorEvent) => GetDbContext(null, null, onErrorEvent);
13         public SqlSugarClient GetDbContext(Action<string, SugarParameter[]> onExecutedEvent) => GetDbContext(onExecutedEvent);
14         public SqlSugarClient GetDbContext(Func<string, SugarParameter[], KeyValuePair<string, SugarParameter[]>> onExecutingChangeSqlEvent) => GetDbContext(null, onExecutingChangeSqlEvent);
15         public SqlSugarClient GetDbContext(Action<string, SugarParameter[]> onExecutedEvent = null, Func<string, SugarParameter[], KeyValuePair<string, SugarParameter[]>> onExecutingChangeSqlEvent = null, Action<Exception> onErrorEvent = null)
16         {
17             SqlSugarClient db = new SqlSugarClient(_config)
18             {
19                 Aop =
20                  {
21                         OnExecutingChangeSql = onExecutingChangeSqlEvent,
22                         OnError = onErrorEvent ?? ((Exception ex) => { this._logger.LogError(ex, "ExecuteSql Error"); }),
23                         OnLogExecuted =onExecutedEvent?? ((string sql, SugarParameter[] pars) =>
24                         {
25                             var keyDic = new KeyValuePair<string, SugarParameter[]>(sql, pars);
26                             this._logger.LogInformation($"ExecuteSql：【{keyDic.ToJson()}】");
27                         })
28                  }
29             };
30             return db;
31         }
32     }

DbFactory

    public interface IRepository
    {

    }

IRepository

 1  public class Repository<TFactory, TIRepository> : IRepository where TFactory : IDbFactory where TIRepository : IRepository
 2     {
 3         protected readonly ILogger Log;
 4         protected readonly TFactory Factory;
 5         protected readonly TIRepository DbRepository;
 6         protected SqlSugarClient DbContext => this.Factory.GetDbContext();
 7 
 8         public Repository(TFactory factory) => Factory = factory;
 9         public Repository(TFactory factory, ILogger logger) : this(factory) => Log = logger;
10         public Repository(TFactory factory, TIRepository repository) : this(factory) => DbRepository = repository;
11         public Repository(TFactory factory, TIRepository repository, ILogger logger) : this(factory, repository) => Log = logger;
12     }
13 
14     public class Repository<TFactory> : IRepository where TFactory : IDbFactory
15     {
16         protected readonly ILogger Log;
17         protected readonly TFactory Factory;
18         protected SqlSugarClient DbContext => this.Factory.GetDbContext();
19 
20         public Repository(TFactory factory) => Factory = factory;
21         public Repository(TFactory factory, ILogger logger) : this(factory) => Log = logger;
22     }

Repository

  1   public static class SugarFactoryExtensions
  2     {
  3 
  4         #region 根据主键获取实体对象
  5 
  6         /// <summary>
  7         /// 根据主键获取实体对象
  8         /// </summary>
  9         /// <typeparam name="TSource">数据源类型</typeparam>
 10         /// <param name="db"></param>
 11         /// <param name="id"></param>
 12         /// <returns></returns>
 13         public static TSource GetById<TSource>(this SqlSugarClient db, dynamic id) where TSource : EntityBase, new()
 14         {
 15             return db.Queryable<TSource>().InSingle(id);
 16         }
 17 
 18         /// <summary>
 19         /// 根据主键获取实体对象
 20         /// </summary>
 21         /// <typeparam name="TSource">数据源类型</typeparam>
 22         /// <typeparam name="TMap">数据源映射类型</typeparam>
 23         /// <param name="db"></param>
 24         /// <param name="id"></param>
 25         /// <returns></returns>
 26         public static TMap GetById<TSource, TMap>(this SqlSugarClient db, dynamic id) where TSource : EntityBase, new()
 27         {
 28             TSource model = db.Queryable<TSource>().InSingle(id);
 29             return model.Map<TSource, TMap>();
 30         }
 31 
 32         #endregion
 33 
 34         #region 根据Linq表达式条件获取单个实体对象
 35 
 36         /// <summary>
 37         /// 根据条件获取单个实体对象
 38         /// </summary>
 39         /// <typeparam name="TSource">数据源类型</typeparam>
 40         /// <param name="db"></param>
 41         /// <param name="whereExp"></param>
 42         /// <returns></returns>
 43         public static TSource Get<TSource>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
 44         {
 45             return db.Queryable<TSource>().Where(whereExp).Single();
 46         }
 47 
 48         /// <summary>
 49         /// 根据条件获取单个实体对象
 50         /// </summary>
 51         /// <typeparam name="TSource">数据源类型</typeparam>
 52         /// <typeparam name="TMap">数据源映射类型</typeparam>
 53         /// <param name="db"></param>
 54         /// <param name="whereExp">条件表达式</param>
 55         /// <returns></returns>
 56         public static TMap Get<TSource, TMap>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
 57         {
 58             TSource model = db.Queryable<TSource>().Where(whereExp).Single();
 59             return model.Map<TSource, TMap>();
 60         }
 61 
 62         #endregion
 63 
 64         #region 获取所有实体列表
 65 
 66         /// <summary>
 67         /// 获取所有实体列表
 68         /// </summary>
 69         /// <typeparam name="TSource">数据源类型</typeparam>
 70         /// <param name="db"></param>
 71         /// <returns></returns>
 72         public static List<TSource> GetList<TSource>(this SqlSugarClient db) where TSource : EntityBase, new()
 73         {
 74             return db.Queryable<TSource>().ToList();
 75         }
 76 
 77         /// <summary>
 78         /// 获取实体列表
 79         /// </summary>
 80         /// <typeparam name="TSource">数据源类型</typeparam>
 81         /// <typeparam name="TMap">数据源映射类型</typeparam>
 82         /// <param name="db"></param>
 83         /// <returns></returns>
 84         public static List<TMap> GetList<TSource, TMap>(this SqlSugarClient db) where TSource : EntityBase, new()
 85         {
 86             var result = db.Queryable<TSource>().ToList();
 87             return result.Map<List<TSource>, List<TMap>>();
 88         }
 89 
 90         #endregion
 91 
 92         #region 根据Linq表达式条件获取列表
 93 
 94         /// <summary>
 95         /// 根据条件获取实体列表
 96         /// </summary>
 97         /// <typeparam name="TSource">数据源类型</typeparam>
 98         /// <param name="db"></param>
 99         /// <param name="whereExp">条件表达式</param>
100         /// <returns></returns>
101         public static List<TSource> GetList<TSource>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
102         {
103             return db.Queryable<TSource>().Where(whereExp).ToList();
104         }
105 
106         /// <summary>
107         /// 根据条件获取实体列表
108         /// </summary>
109         /// <typeparam name="TSource">数据源类型</typeparam>
110         /// <typeparam name="TMap">数据源映射类型</typeparam>
111         /// <param name="db"></param>
112         /// <param name="whereExp">条件表达式</param>
113         /// <returns></returns>
114         public static List<TMap> GetList<TSource, TMap>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
115         {
116             var result = db.Queryable<TSource>().Where(whereExp).ToList();
117             return result.Map<List<TSource>, List<TMap>>();
118         }
119 
120         #endregion
121 
122         #region 根据Sugar条件获取列表
123 
124         /// <summary>
125         /// 根据条件获取实体列表
126         /// </summary>
127         /// <typeparam name="TSource"></typeparam>
128         /// <param name="db"></param>
129         /// <param name="conditionals">Sugar调价表达式集合</param>
130         /// <returns></returns>
131         public static List<TSource> GetList<TSource>(this SqlSugarClient db, List<IConditionalModel> conditionals) where TSource : EntityBase, new()
132         {
133             return db.Queryable<TSource>().Where(conditionals).ToList();
134         }
135 
136         /// <summary>
137         /// 根据条件获取实体列表
138         /// </summary>
139         /// <typeparam name="TSource">数据源类型</typeparam>
140         /// <typeparam name="TMap">数据源映射类型</typeparam>
141         /// <param name="db"></param>
142         /// <param name="conditionals">Sugar调价表达式集合</param>
143         /// <returns></returns>
144         public static List<TMap> GetList<TSource, TMap>(this SqlSugarClient db, List<IConditionalModel> conditionals) where TSource : EntityBase, new()
145         {
146             var result = db.Queryable<TSource>().Where(conditionals).ToList();
147             return result.Map<List<TSource>, List<TMap>>();
148         }
149 
150         #endregion
151 
152         #region 是否包含某个元素
153         /// <summary>
154         /// 是否包含某个元素
155         /// </summary>
156         /// <typeparam name="TSource"></typeparam>
157         /// <param name="db"></param>
158         /// <param name="whereExp">条件表达式</param>
159         /// <returns></returns>
160         public static bool Exist<TSource>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
161         {
162             return db.Queryable<TSource>().Where(whereExp).Any();
163         }
164         #endregion
165 
166         #region 新增实体对象
167         /// <summary>
168         /// 新增实体对象
169         /// </summary>
170         /// <typeparam name="TSource"></typeparam>
171         /// <param name="db"></param>
172         /// <param name="insertObj"></param>
173         /// <returns></returns>
174         public static bool Insert<TSource>(this SqlSugarClient db, TSource insertObj) where TSource : EntityBase, new()
175         {
176             return db.Insertable(insertObj).ExecuteCommand() > 0;
177         }
178 
179         /// <summary>
180         /// 新增实体对象
181         /// </summary>
182         /// <typeparam name="TSource"></typeparam>
183         /// <typeparam name="TMap"></typeparam>
184         /// <param name="db"></param>
185         /// <param name="insertDto"></param>
186         /// <returns></returns>
187         public static bool Insert<TSource, TMap>(this SqlSugarClient db, TSource insertDto) where TMap : EntityBase, new()
188         {
189             var entity = insertDto.Map<TSource, TMap>();
190             return db.Insertable(entity).ExecuteCommand() > 0;
191         }
192         #endregion
193 
194         #region 批量新增实体对象
195         /// <summary>
196         /// 批量新增实体对象
197         /// </summary>
198         /// <typeparam name="TSource"></typeparam>
199         /// <param name="db"></param>
200         /// <param name="insertObjs"></param>
201         /// <returns></returns>
202         public static bool InsertRange<TSource>(this SqlSugarClient db, List<TSource> insertObjs) where TSource : EntityBase, new()
203         {
204             return db.Insertable(insertObjs).ExecuteCommand() > 0;
205         }
206 
207         /// <summary>
208         /// 批量新增实体对象
209         /// </summary>
210         /// <typeparam name="TSource"></typeparam>
211         /// <typeparam name="TMap"></typeparam>
212         /// <param name="db"></param>
213         /// <param name="insertObjs"></param>
214         /// <returns></returns>
215         public static bool InsertRange<TSource, TMap>(this SqlSugarClient db, List<TSource> insertObjs) where TMap : EntityBase, new()
216         {
217             var entitys = insertObjs.Map<List<TSource>, List<TMap>>();
218             return db.Insertable(entitys).ExecuteCommand() > 0;
219         }
220         #endregion
221 
222         #region 更新单个实体对象
223         /// <summary>
224         /// 更新单个实体对象
225         /// </summary>
226         /// <typeparam name="TSource"></typeparam>
227         /// <param name="db"></param>
228         /// <param name="updateObj"></param>
229         /// <returns></returns>
230         public static bool Update<TSource>(this SqlSugarClient db, TSource updateObj) where TSource : EntityBase, new()
231         {
232             return db.Updateable(updateObj).ExecuteCommand() > 0;
233         }
234         #endregion
235 
236         #region 根据条件批量更新实体指定列
237         /// <summary>
238         /// 根据条件批量更新实体指定列
239         /// </summary>
240         /// <typeparam name="TSource"></typeparam>
241         /// <param name="db"></param>
242         /// <param name="columns">需要更新的列</param>
243         /// <param name="whereExp">条件表达式</param>
244         /// <returns></returns>
245         public static bool Update<TSource>(this SqlSugarClient db, Expression<Func<TSource, TSource>> columns, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
246         {
247             return db.Updateable<TSource>().UpdateColumns(columns).Where(whereExp).ExecuteCommand() > 0;
248         }
249         #endregion
250 
251         #region 物理删除实体对象
252 
253         /// <summary>
254         /// 物理删除实体对象
255         /// </summary>
256         /// <typeparam name="TSource"></typeparam>
257         /// <param name="db"></param>
258         /// <param name="deleteObj"></param>
259         /// <returns></returns>
260         public static bool Delete<TSource>(this SqlSugarClient db, TSource deleteObj) where TSource : EntityBase, new()
261         {
262             return db.Deleteable<TSource>().Where(deleteObj).ExecuteCommand() > 0;
263         }
264 
265         /// <summary>
266         /// 物理删除实体对象
267         /// </summary>
268         /// <typeparam name="TSource"></typeparam>
269         /// <param name="db"></param>
270         /// <param name="whereExp">条件表达式</param>
271         /// <returns></returns>
272         public static bool Delete<TSource>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
273         {
274             return db.Deleteable<TSource>().Where(whereExp).ExecuteCommand() > 0;
275         }
276 
277         /// <summary>
278         /// 根据主键物理删除实体对象
279         /// </summary>
280         /// <typeparam name="TSource"></typeparam>
281         /// <param name="db"></param>
282         /// <param name="id"></param>
283         /// <returns></returns>
284         public static bool DeleteById<TSource>(this SqlSugarClient db, dynamic id) where TSource : EntityBase, new()
285         {
286             return db.Deleteable<TSource>().In(id).ExecuteCommand() > 0;
287         }
288 
289         /// <summary>
290         /// 根据主键批量物理删除实体集合
291         /// </summary>
292         /// <typeparam name="TSource"></typeparam>
293         /// <param name="db"></param>
294         /// <param name="ids"></param>
295         /// <returns></returns>
296         public static bool DeleteByIds<TSource>(this SqlSugarClient db, dynamic[] ids) where TSource : EntityBase, new()
297         {
298             return db.Deleteable<TSource>().In(ids).ExecuteCommand() > 0;
299         }
300 
301         #endregion
302 
303         #region 分页查询
304 
305         /// <summary>
306         /// 获取分页列表【页码，每页条数】
307         /// </summary>
308         /// <typeparam name="TSource">数据源类型</typeparam>
309         /// <param name="db"></param>
310         /// <param name="pageIndex">页码（从0开始）</param>
311         /// <param name="pageSize">每页条数</param>
312         /// <returns></returns>
313         public static IPagedList<TSource> GetPageList<TSource>(this SqlSugarClient db, int pageIndex, int pageSize) where TSource : EntityBase, new()
314         {
315             int count = 0;
316             var result = db.Queryable<TSource>().ToPageList(pageIndex, pageSize, ref count);
317             return new PagedList<TSource>(result, pageIndex, pageSize, count);
318         }
319 
320         /// <summary>
321         /// 获取分页列表【页码，每页条数】
322         /// </summary>
323         /// <typeparam name="TSource">数据源类型</typeparam>
324         /// <typeparam name="TMap">数据源映射类型</typeparam>
325         /// <param name="db"></param>
326         /// <param name="pageIndex">页码（从0开始）</param>
327         /// <param name="pageSize">每页条数</param>
328         /// <returns></returns>
329         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, int pageIndex, int pageSize) where TSource : EntityBase, new()
330         {
331             int count = 0;
332             var result = db.Queryable<TSource>().ToPageList(pageIndex, pageSize, ref count);
333             var pageResult = new PagedList<TSource>(result, pageIndex, pageSize, count);
334             return pageResult.Map<TSource, TMap>();
335         }
336 
337         #endregion
338 
339         #region 分页查询（排序）
340 
341         /// <summary>
342         /// 获取分页列表【排序，页码，每页条数】
343         /// </summary>
344         /// <typeparam name="TSource">数据源类型</typeparam>
345         /// <param name="db"></param>
346         /// <param name="orderExp">排序表达式</param>
347         /// <param name="orderType">排序类型</param>
348         /// <param name="pageIndex">页码（从0开始）</param>
349         /// <param name="pageSize">每页条数</param>
350         /// <returns></returns>
351         public static IPagedList<TSource> GetPageList<TSource>(this SqlSugarClient db, Expression<Func<TSource, object>> orderExp, OrderByType orderType, int pageIndex, int pageSize) where TSource : EntityBase, new()
352         {
353             int count = 0;
354             var result = db.Queryable<TSource>().OrderBy(orderExp, orderType).ToPageList(pageIndex, pageSize, ref count);
355             return new PagedList<TSource>(result, pageIndex, pageSize, count);
356         }
357 
358         /// <summary>
359         /// 获取分页列表【排序，页码，每页条数】
360         /// </summary>
361         /// <typeparam name="TSource">数据源类型</typeparam>
362         /// <typeparam name="TMap">数据源映射类型</typeparam>
363         /// <param name="db"></param>
364         /// <param name="orderExp">排序表达式</param>
365         /// <param name="orderType">排序类型</param>
366         /// <param name="pageIndex">页码（从0开始）</param>
367         /// <param name="pageSize">每页条数</param>
368         /// <returns></returns>
369         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, Expression<Func<TSource, object>> orderExp, OrderByType orderType, int pageIndex, int pageSize) where TSource : EntityBase, new()
370         {
371             int count = 0;
372             var result = db.Queryable<TSource>().OrderBy(orderExp, orderType).ToPageList(pageIndex, pageSize, ref count);
373             var pageResult = new PagedList<TSource>(result, pageIndex, pageSize, count);
374             return pageResult.Map<TSource, TMap>();
375         }
376 
377         #endregion
378 
379         #region 分页查询（Linq表达式条件）
380 
381         /// <summary>
382         /// 获取分页列表【Linq表达式条件，页码，每页条数】
383         /// </summary>
384         /// <typeparam name="TSource">数据源类型</typeparam>
385         /// <param name="db"></param>
386         /// <param name="whereExp">Linq表达式条件</param>
387         /// <param name="pageIndex">页码（从0开始）</param>
388         /// <param name="pageSize">每页条数</param>
389         /// <returns></returns>
390         public static IPagedList<TSource> GetPageList<TSource>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp, int pageIndex, int pageSize) where TSource : EntityBase, new()
391         {
392             int count = 0;
393             var result = db.Queryable<TSource>().Where(whereExp).ToPageList(pageIndex, pageSize, ref count);
394             return new PagedList<TSource>(result, pageIndex, pageSize, count);
395         }
396 
397         /// <summary>
398         /// 获取分页列表【Linq表达式条件，页码，每页条数】
399         /// </summary>
400         /// <typeparam name="TSource">数据源类型</typeparam>
401         /// <typeparam name="TMap">数据源映射类型</typeparam>
402         /// <param name="db"></param>
403         /// <param name="whereExp">Linq表达式条件</param>
404         /// <param name="pageIndex">页码（从0开始）</param>
405         /// <param name="pageSize">每页条数</param>
406         /// <returns></returns>
407         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp, int pageIndex, int pageSize) where TSource : EntityBase, new()
408         {
409             int count = 0;
410             var result = db.Queryable<TSource>().Where(whereExp).ToPageList(pageIndex, pageSize, ref count);
411             var pageResult = new PagedList<TSource>(result, pageIndex, pageSize, count);
412             return pageResult.Map<TSource, TMap>();
413         }
414 
415         #endregion
416 
417         #region 分页查询（Linq表达式条件，排序）
418 
419         /// <summary>
420         /// 获取分页列表【Linq表达式条件，排序，页码，每页条数】
421         /// </summary>
422         /// <typeparam name="TSource">数据源类型</typeparam>
423         /// <param name="db"></param>
424         /// <param name="whereExp">Linq表达式条件</param>
425         /// <param name="orderExp">排序表达式</param>
426         /// <param name="orderType">排序类型</param>
427         /// <param name="pageIndex">页码（从0开始）</param>
428         /// <param name="pageSize">每页条数</param>
429         /// <returns></returns>
430         public static IPagedList<TSource> GetPageList<TSource>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp, Expression<Func<TSource, object>> orderExp, OrderByType orderType, int pageIndex, int pageSize) where TSource : EntityBase, new()
431         {
432             int count = 0;
433             var result = db.Queryable<TSource>().Where(whereExp).OrderBy(orderExp, orderType).ToPageList(pageIndex, pageSize, ref count);
434             return new PagedList<TSource>(result, pageIndex, pageSize, count);
435         }
436 
437         /// <summary>
438         ///  获取分页列表【Linq表达式条件，排序，页码，每页条数】
439         /// </summary>
440         /// <typeparam name="TSource">数据源类型</typeparam>
441         /// <typeparam name="TMap">数据源映射类型</typeparam>
442         /// <param name="db"></param>
443         /// <param name="whereExp">Linq表达式条件</param>
444         /// <param name="orderExp">排序表达式</param>
445         /// <param name="orderType">排序类型</param>
446         /// <param name="pageIndex">页码（从0开始）</param>
447         /// <param name="pageSize">每页条数</param>
448         /// <returns></returns>
449         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, Expression<Func<TSource, bool>> whereExp, Expression<Func<TSource, object>> orderExp, OrderByType orderType, int pageIndex, int pageSize) where TSource : EntityBase, new()
450         {
451             int count = 0;
452             var result = db.Queryable<TSource>().Where(whereExp).OrderBy(orderExp, orderType).ToPageList(pageIndex, pageSize, ref count);
453             var pageResult = new PagedList<TSource>(result, pageIndex, pageSize, count);
454             return pageResult.Map<TSource, TMap>();
455         }
456 
457         #endregion
458 
459         #region 分页查询（Sugar条件）
460 
461         /// <summary>
462         /// 获取分页列表【Sugar表达式条件，页码，每页条数】
463         /// </summary>
464         /// <typeparam name="TSource">数据源类型</typeparam>
465         /// <param name="db"></param>
466         /// <param name="conditionals">Sugar条件表达式集合</param>
467         /// <param name="pageIndex">页码（从0开始）</param>
468         /// <param name="pageSize">每页条数</param>
469         /// <returns></returns>
470         public static IPagedList<TSource> GetPageList<TSource>(this SqlSugarClient db, List<IConditionalModel> conditionals, int pageIndex, int pageSize) where TSource : EntityBase, new()
471         {
472             int count = 0;
473             var result = db.Queryable<TSource>().Where(conditionals).ToPageList(pageIndex, pageSize, ref count);
474             return new PagedList<TSource>(result, pageIndex, pageSize, count);
475         }
476 
477         /// <summary>
478         /// 获取分页列表【Sugar表达式条件，页码，每页条数】
479         /// </summary>
480         /// <typeparam name="TSource">数据源类型</typeparam>
481         /// <typeparam name="TMap">数据源映射类型</typeparam>
482         /// <param name="db"></param>
483         /// <param name="conditionals">Sugar条件表达式集合</param>
484         /// <param name="pageIndex">页码（从0开始）</param>
485         /// <param name="pageSize">每页条数</param>
486         /// <returns></returns>
487         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, List<IConditionalModel> conditionals, int pageIndex, int pageSize) where TSource : EntityBase, new()
488         {
489             int count = 0;
490             var result = db.Queryable<TSource>().Where(conditionals).ToPageList(pageIndex, pageSize, ref count);
491             var pageResult = new PagedList<TSource>(result, pageIndex, pageSize, count);
492             return pageResult.Map<TSource, TMap>();
493         }
494 
495         #endregion
496 
497         #region 分页查询（Sugar条件，排序）
498 
499         /// <summary>
500         ///  获取分页列表【Sugar表达式条件，排序，页码，每页条数】
501         /// </summary>
502         /// <typeparam name="TSource"></typeparam>
503         /// <param name="db"></param>
504         /// <param name="conditionals">Sugar条件表达式集合</param>
505         /// <param name="orderExp">排序表达式</param>
506         /// <param name="orderType">排序类型</param>
507         /// <param name="pageIndex">页码（从0开始）</param>
508         /// <param name="pageSize">每页条数</param>
509         /// <returns></returns>
510         public static IPagedList<TSource> GetPageList<TSource>(this SqlSugarClient db, List<IConditionalModel> conditionals, Expression<Func<TSource, object>> orderExp, OrderByType orderType, int pageIndex, int pageSize) where TSource : EntityBase, new()
511         {
512             int count = 0;
513             var result = db.Queryable<TSource>().Where(conditionals).OrderBy(orderExp, orderType).ToPageList(pageIndex, pageSize, ref count);
514             return new PagedList<TSource>(result, pageIndex, pageSize, count);
515         }
516 
517         /// <summary>
518         ///  获取分页列表【Sugar表达式条件，排序，页码，每页条数】
519         /// </summary>
520         /// <typeparam name="TSource"></typeparam>
521         /// <param name="db"></param>
522         /// <param name="conditionals">Sugar条件表达式集合</param>
523         /// <param name="orderExp">排序表达式</param>
524         /// <param name="orderType">排序类型</param>
525         /// <param name="pageIndex">页码（从0开始）</param>
526         /// <param name="pageSize">每页条数</param>
527         /// <returns></returns>
528         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, List<IConditionalModel> conditionals, Expression<Func<TSource, object>> orderExp, OrderByType orderType, int pageIndex, int pageSize) where TSource : EntityBase, new()
529         {
530             int count = 0;
531             var result = db.Queryable<TSource>().Where(conditionals).OrderBy(orderExp, orderType).ToPageList(pageIndex, pageSize, ref count);
532             var pageResult = new PagedList<TSource>(result, pageIndex, pageSize, count);
533             return pageResult.Map<TSource, TMap>();
534         }
535 
536         #endregion
537 
538         #region 分页查询 （扩展条件构造实体，默认排序列，默认排序方式）
539         /// <summary>
540         /// 分页查询 （扩展条件构造实体，默认排序列，默认排序方式）
541         /// </summary>
542         /// <typeparam name="TSource"></typeparam>
543         /// <typeparam name="TMap"></typeparam>
544         /// <param name="db"></param>
545         /// <param name="query"></param>
546         /// <param name="defaultSort"></param>
547         /// <param name="defaultSortType"></param>
548         /// <returns></returns>
549         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, QueryCollection query, Expression<Func<TSource, object>> defaultSort, OrderByType defaultSortType) where TSource : EntityBase, new()
550         {
551             int count = 0;
552             List<IConditionalModel> conditionals = query.ConditionItems.ExamineConditional<TSource>();
553             Expression<Func<TSource, object>> sort = query.SortLambda<TSource, object>(defaultSort, defaultSortType, out var sortType);
554             var result = db.Queryable<TSource>().Where(conditionals).OrderBy(sort, sortType).ToPageList(query.PageIndex, query.PageSize, ref count);
555             var pageResult = new PagedList<TSource>(result, query.PageIndex, query.PageSize, count);
556             return pageResult.Map<TSource, TMap>();
557         }
558         #endregion
559 
560         #region 分页查询 （扩展条件构造实体，默认排序列，默认排序方式，Linq表达式条件）
561         /// <summary>
562         /// 分页查询 （扩展条件构造实体，默认排序列，默认排序方式，Linq表达式条件）
563         /// </summary>
564         /// <typeparam name="TSource"></typeparam>
565         /// <typeparam name="TMap"></typeparam>
566         /// <param name="db"></param>
567         /// <param name="query"></param>
568         /// <param name="defaultSort"></param>
569         /// <param name="defaultSortType"></param>
570         /// <param name="whereExp"></param>
571         /// <returns></returns>
572         public static IPagedList<TMap> GetPageList<TSource, TMap>(this SqlSugarClient db, QueryCollection query, Expression<Func<TSource, object>> defaultSort, OrderByType defaultSortType, Expression<Func<TSource, bool>> whereExp) where TSource : EntityBase, new()
573         {
574             int count = 0;
575             List<IConditionalModel> conditionals = query.ConditionItems.ExamineConditional<TSource>();
576             Expression<Func<TSource, object>> sort = query.SortLambda<TSource, object>(defaultSort, defaultSortType, out var sortType);
577             var result = db.Queryable<TSource>().Where(whereExp).Where(conditionals).OrderBy(sort, sortType).ToPageList(query.PageIndex, query.PageSize, ref count);
578             var pageResult = new PagedList<TSource>(result, query.PageIndex, query.PageSize, count);
579             return pageResult.Map<TSource, TMap>();
580         }
581         #endregion
582     }

SugarFactoryExtensions，自行斟酌，喜欢使用sugar原生的，可跳过

使用：

1             services.AddSqlSugarClient<DbFactory>((sp, op) =>
2             {
3                 op.ConnectionString = sp.GetService<IConfiguration>().GetConnectionString("lucy");
4                 op.DbType = DbType.MySql;
5                 op.IsAutoCloseConnection = true;
6                 op.InitKeyType = InitKeyType.Attribute;
7                 op.IsShardSameThread = true;
8             });

注入

 1     //如果数据操作简单，直接在业务层使用
 2     public class UsersService : Repository<DbFactory, IUsersRepository>, IUsersService
 3     {
 4         public UsersService(DbFactory factory, IUsersRepository) : base(factory)
 5         {
 6 
 7         }
 8 
 9 
10         public async Task TestMethod()
11         {
12             //获取数据库上下文
13             //第一种  
14             DbContext.Insert<Users>(new Users());
15             //第二种
16             using (var db = Factory.GetDbContext())
17             {
18                 db.Insert<Users>(new Users());
19                 db.Update<Users>(new Users());
20             }
21             //数据操作繁琐的放到自定义的IUsersRepository中
22             await DbRepository.TestAddAsync();
23         }
24 
25     }

业务层使用示例

 1  public class UsersRepository : Repository<DbFactory>, IUsersRepository
 2     {
 3         public UsersRepository(DbFactory factory) : base(factory)
 4         {
 5 
 6         }
 7 
 8         public async Task<bool> TestAddAsync()
 9         {
10             //这里获取数据库上下文，与业务层一致
11 
12             DbContext.Insert<Users>(new Users());
13 
14             using (var db = Factory.GetDbContext())
15             {
16                 db.Insert<Users>(new Users());
17                 db.Update<Users>(new Users());
18             }
19             return await Task.FromResult(true);
20         }
21     }