框架搭建與EF常用基類實作

      前兩篇簡單談了一些.Net Core的優勢以及機構設計的一些思路，這一篇開始，我們將從零開始搭建架構，底層我們將采用EF來訪問資料庫，所以這篇我們將貼一下EF常用操作的基類，

    簡單介紹下一些類別庫將要實作的功能：

           Business：業務實作層

           Domains：物體（Model）

           Service：介面

           Data：資料庫訪問（EF或其他）

           EasyCacheing：開源快取管理

           Tools：工具類別庫

    其他的我們用到的時候再說；

    接著說EF常用操作基類，我們將基類放在Data下，具體目錄結構如下：

   BaseEntity：物體基類，基礎共有的放在這里面：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 
 5 namespace Data
 6 {
 7     /// <summary>
 8     /// Base class for entities
 9     /// </summary>
10     ///[Serializable]
11     public abstract partial class BaseEntity
12     {
13         /// <summary>
14         /// Gets or sets the entity identifier
15         /// </summary>
16         public string Id { get; set; }
17         public virtual Nullable<DateTime> CreateTime { get; set; }
18         public virtual string CreatePerson { get; set; }
19         public virtual Nullable<DateTime> UpdateTime { get; set; }
20         public virtual string UpdatePerson { get; set; }
21 
22         public BaseEntity()
23         {
24             this.Id = GetIdentifier();
25             this.CreateTime = DateTime.Now;
26             this.UpdateTime = DateTime.Now;
27         }
28 
29         private string GetIdentifier()
30         {
31             return Guid.NewGuid().ToString();
32         }
33 
34         public override bool Equals(object obj)
35         {
36             return Equals(obj as BaseEntity);
37         }
38 
39         private static bool IsTransient(BaseEntity obj)
40         {
41             return obj != null && Equals(obj.Id, default(string));
42         }
43 
44         private Type GetUnproxiedType()
45         {
46             return GetType();
47         }
48 
49         public virtual bool Equals(BaseEntity other)
50         {
51             if (other == null)
52                 return false;
53 
54             if (ReferenceEquals(this, other))
55                 return true;
56 
57             if (!IsTransient(this) &&
58                 !IsTransient(other) &&
59                 Equals(Id, other.Id))
60             {
61                 var otherType = other.GetUnproxiedType();
62                 var thisType = GetUnproxiedType();
63                 return thisType.IsAssignableFrom(otherType) ||
64                         otherType.IsAssignableFrom(thisType);
65             }
66 
67             return false;
68         }
69 
70         public static bool operator ==(BaseEntity x, BaseEntity y)
71         {
72             return Equals(x, y);
73         }
74 
75         public static bool operator !=(BaseEntity x, BaseEntity y)
76         {
77             return !(x == y);
78         }
79     }
80 }

       DbContextExtensions：資料庫操作擴展

 1 using Microsoft.EntityFrameworkCore;
 2 using Microsoft.EntityFrameworkCore.Infrastructure;
 3 using System;
 4 using System.Collections.Generic;
 5 using System.Data;
 6 using System.Data.Common;
 7 using System.Data.SqlClient;
 8 using System.Reflection;
 9 using System.Text;
10 
11 namespace Data
12 {
13     public static class DbContextExtensions
14     {
15         private static void CombineParams(ref DbCommand command, params object[] parameters)
16         {
17             if (parameters != null)
18             {
19                 foreach (SqlParameter parameter in parameters)
20                 {
21                     if (!parameter.ParameterName.Contains("@"))
22                         parameter.ParameterName = $"@{parameter.ParameterName}";
23                     command.Parameters.Add(parameter);
24                 }
25             }
26         }
27       
28         
29         private static DbCommand CreateCommand(DatabaseFacade facade, string sql, out DbConnection dbConn, params object[] parameters)
30         {
31             DbConnection conn = facade.GetDbConnection();
32             dbConn = conn;
33             conn.Open();
34             DbCommand cmd = conn.CreateCommand();
35             if (facade.IsSqlServer())
36             {
37                 cmd.CommandText = sql;
38                 CombineParams(ref cmd, parameters);
39             }
40             return cmd;
41         }
42 
43         public static DataTable SqlQuery(this DatabaseFacade facade, string sql, params object[] parameters)
44         {
45             DbCommand cmd = CreateCommand(facade, sql, out DbConnection conn, parameters);
46             DbDataReader reader = cmd.ExecuteReader();
47             DataTable dt = new DataTable();
48             dt.Load(reader);
49             reader.Close();
50             conn.Close();
51             return dt;
52         }
53 
54         public static IEnumerable<T> SqlQuery<T>(this DatabaseFacade facade, string sql, params object[] parameters) where T : class, new()
55         {
56             DataTable dt = SqlQuery(facade, sql, parameters);
57             return dt.ToEnumerable<T>();
58         }
59      
60         public static IEnumerable<T> ToEnumerable<T>(this DataTable dt) where T : class, new()
61         {
62             PropertyInfo[] propertyInfos = typeof(T).GetProperties();
63             T[] ts = new T[dt.Rows.Count];
64             int i = 0;
65             foreach (DataRow row in dt.Rows)
66             {
67                 T t = new T();
68                 foreach (PropertyInfo p in propertyInfos)
69                 {
70                     if (dt.Columns.IndexOf(p.Name) != -1 && row[p.Name] != DBNull.Value)
71                         p.SetValue(t, row[p.Name], null);
72                 }
73                 ts[i] = t;
74                 i++;
75             }
76             return ts;
77         }
78     }
79 }

        IPagedList：分頁介面

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Text;
 4 
 5 namespace Data
 6 {
 7     public interface IPagedList<T> : IList<T>
 8     {
 9         int PageIndex { get; }
10         int PageSize { get; }
11         int TotalCount { get; }
12         int TotalPages { get; }
13         bool HasPreviousPage { get; }
14         bool HasNextPage { get; }
15     }
16 }

       PagedList：分頁介面的實作

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 namespace Data
 7 {
 8     /// <summary>
 9     /// Paged list
10     /// </summary>
11     /// <typeparam name="T">T</typeparam>
12     public class PagedList<T> : List<T>, IPagedList<T>
13     {
14         /// <summary>
15         /// Ctor
16         /// </summary>
17         /// <param name="source">source</param>
18         /// <param name="pageIndex">Page index</param>
19         /// <param name="pageSize">Page size</param>
20         public PagedList(IQueryable<T> source, int pageIndex, int pageSize)
21         {
22             int total = source.Count();
23             this.TotalCount = total;
24             this.TotalPages = total / pageSize;
25 
26             if (total % pageSize > 0)
27                 TotalPages++;
28 
29             this.PageSize = pageSize;
30             this.PageIndex = pageIndex;
31             this.AddRange(source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
32         }
33 
34         /// <summary>
35         /// Ctor
36         /// </summary>
37         /// <param name="source">source</param>
38         /// <param name="pageIndex">Page index</param>
39         /// <param name="pageSize">Page size</param>
40         public PagedList(IList<T> source, int pageIndex, int pageSize)
41         {
42             TotalCount = source.Count();
43             TotalPages = TotalCount / pageSize;
44 
45             if (TotalCount % pageSize > 0)
46                 TotalPages++;
47 
48             this.PageSize = pageSize;
49             this.PageIndex = pageIndex;
50             this.AddRange(source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList());
51         }
52 
53         /// <summary>
54         /// Ctor
55         /// </summary>
56         /// <param name="source">source</param>
57         /// <param name="pageIndex">Page index</param>
58         /// <param name="pageSize">Page size</param>
59         /// <param name="totalCount">Total count</param>
60         public PagedList(IEnumerable<T> source, int pageIndex, int pageSize, int totalCount)
61         {
62             TotalCount = totalCount;
63             TotalPages = TotalCount / pageSize;
64 
65             if (TotalCount % pageSize > 0)
66                 TotalPages++;
67 
68             this.PageSize = pageSize;
69             this.PageIndex = pageIndex;
70             this.AddRange(source);
71         }
72 
73         public int PageIndex { get; private set; }
74         public int PageSize { get; private set; }
75         public int TotalCount { get; private set; }
76         public int TotalPages { get; private set; }
77 
78         public bool HasPreviousPage
79         {
80             get { return (PageIndex > 0); }
81         }
82         public bool HasNextPage
83         {
84             get { return (PageIndex + 1 < TotalPages); }
85         }
86     }
87 }

         IEnumerableExtensions：IEnumerable分頁的擴展

 1 using System.Collections.Generic;
 2 using System.Linq;
 3 
 4 namespace Data
 5 {
 6     public static class IEnumerableExtensions
 7     {
 8         public static PagedList<TSource> ToPageList<TSource>(this IEnumerable<TSource> source, int pageIndex, int pageSize)
 9         {
10             if (pageIndex < 1)
11                 pageIndex = 1;
12             int TotalCount = 0;
13             List<TSource> resultList = new List<TSource>();
14             resultList = source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); ;
15             TotalCount = source.Count();
16             return new PagedList<TSource>(resultList, pageIndex, pageSize, TotalCount);
17         }
18     }
19 }

        IRepository：資料庫倉庫訪問介面

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Linq.Expressions;
  5 
  6 namespace Data
  7 {
  8     public interface IRepository<T> where T : BaseEntity
  9     {
 10         /// <summary>
 11         /// 通過ID獲取單個物體
 12         /// </summary>
 13         /// <param name="id"></param>
 14         /// <returns></returns>
 15         T GetById(object id);
 16         /// <summary>
 17         /// 插入單個物體
 18         /// </summary>
 19         /// <param name="entity">物體</param>
 20         /// <returns></returns>
 21         bool Insert(T entity);
 22         /// <summary>
 23         /// 插入單個物體并回傳ID
 24         /// </summary>
 25         /// <param name="entity"></param>
 26         /// <returns></returns>
 27         object InsertAndGetId(T entity);
 28         /// <summary>
 29         /// 批量插入資料集
 30         /// </summary>
 31         /// <param name="entities">資料集</param>
 32         void Insert(IEnumerable<T> entities);
 33         /// <summary>
 34         /// 更新單個物體
 35         /// </summary>
 36         /// <param name="entity">物體</param>
 37         /// <returns></returns>
 38         bool Update(T entity);
 39         /// <summary>
 40         /// 批量更新資料集
 41         /// </summary>
 42         /// <param name="entities"></param>
 43         void Update(IEnumerable<T> entities);
 44         /// <summary>
 45         /// 洗掉單個物體
 46         /// </summary>
 47         /// <param name="entity"></param>
 48         /// <returns></returns>
 49         bool Delete(T entity);
 50         /// <summary>
 51         /// 批量洗掉
 52         /// </summary>
 53         /// <param name="entities">洗掉的資料集</param>
 54         void Delete(IEnumerable<T> entities);
 55         /// <summary>
 56         /// 通過ID洗掉物體
 57         /// </summary>
 58         /// <param name="id"></param>
 59         /// <returns></returns>
 60         bool DeleteById(object id);
 61         /// <summary>
 62         /// 通過ID（逗號分隔ID）批量洗掉
 63         /// </summary>
 64         /// <param name="ids"></param>
 65         /// <returns></returns>
 66         bool DeleteByIds(object ids);
 67         /// <summary>
 68         /// 通過Id串列批量洗掉
 69         /// </summary>
 70         /// <param name="list"></param>
 71         /// <returns></returns>
 72         bool DeleteByIdList(List<object> list);
 73         /// <summary>
 74         /// 分頁查詢
 75         /// </summary>
 76         /// <param name="pageIndex">當前頁</param>
 77         /// <param name="pageSize">每頁條數</param>
 78         /// <param name="condition">lambda查詢條件where</param>
 79         /// <param name="orderName">排序欄位 默認CreateTime</param>
 80         /// <param name="sortOrder">排序方式 asc desc,默認CreateTime desc</param>
 81         /// <returns></returns>
 82         IPagedList<T> GetListForPaging(int pageIndex, int pageSize, Expression<Func<T, bool>> condition = null, string orderName = null, string sortOrder = null);
 83         /// <summary>
 84         /// Linq連表查詢專用，獲取單表所有資料請使用GetList
 85         /// </summary>
 86         IQueryable<T> Table { get; }
 87         /// <summary>
 88         /// 根據條件查找
 89         /// </summary>
 90         /// <param name="condition">lambda查詢條件where</param>
 91         /// <returns></returns>
 92         T GetEntity(Expression<Func<T, bool>> condition);
 93         /// <summary>
 94         /// 分頁查詢(Linq分頁方式)
 95         /// </summary>
 96         /// <param name="pageIndex">當前頁</param>
 97         /// <param name="pageSize">頁碼</param>
 98         /// <param name="condition">lambda查詢條件where</param>
 99         /// <<param name="sort">排序key:排序欄位，value:bool,true-desc，false-asc 默認：CreateTime desc</param>
100         /// <returns></returns>
101         IPagedList<T> GetListForPaging(int pageIndex, int pageSize, Expression<Func<T, bool>> condition = null, Dictionary<string, bool> sort = null);
102         /// <summary>  
103         /// 執行原始SQL命令  
104         /// </summary>  
105         /// <param name="commandText">SQL命令</param>  
106         /// <param name="parameters">引數</param>  
107         /// <returns>影響的記錄數</returns>  
108         IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters) where TElement : class, new();
109         /// <summary>
110         /// 執行SqlCommand
111         /// </summary>
112         /// <param name="sql">sql</param>
113         /// <param name="parameters">引數</param>
114         /// <returns></returns>
115         int ExecuteSqlCommand(string sql, params object[] parameters);
116         /// <summary>
117         /// 查詢串列,默認回傳整個表資料
118         /// </summary>
119         /// <param name="condition">lambda查詢條件where</param>
120         /// <returns></returns>
121         List<T> GetList(Expression<Func<T, bool>> condition = null);
122     }
123 }

       EFRepository：IEFRepository介面實作

  1 using Microsoft.EntityFrameworkCore;
  2 using System;
  3 using System.Collections.Generic;
  4 using System.Data;
  5 using System.Linq;
  6 using System.Linq.Expressions;
  7 using System.Reflection;
  8 using Tools.Cache;
  9 
 10 namespace Data
 11 {
 12     public class EFRepository<T> : IRepository<T> where T : BaseEntity
 13     {
 14         private DbContext _context;
 15         private DbSet<T> _entities;
 16         private IStaticCacheManager _cacheManager;
 17         public EFRepository(DbContext context, IStaticCacheManager cacheManager)
 18         {
 19             this._context = context;
 20             _cacheManager = cacheManager;
 21         }
 22 
 23         private DbSet<T> Entities
 24         {
 25             get
 26             {
 27                 if (_entities == null)
 28                 {
 29                     _entities = this._context.Set<T>();
 30                 }     
 31                 return _entities;
 32             }
 33         }
 34         /// <summary>
 35         /// Linq連表查詢專用，獲取單表所有資料請使用GetList
 36         /// </summary>
 37         public virtual IQueryable<T> Table
 38         {
 39             get {
 40                 return this.Entities;
 41             }
 42         }
 43         /// <summary>
 44         /// 通過ID獲取單個物體
 45         /// </summary>
 46         /// <param name="id"></param>
 47         /// <returns></returns>
 48         public virtual T GetById(object id)
 49         {
 50             var cacheKey = typeof(T).Name+".GetById."+id;
 51             return _cacheManager.Get(cacheKey, ()=>this.Entities.Find(id));
 52         }
 53         /// <summary>
 54         /// 插入單個物體
 55         /// </summary>
 56         /// <param name="entity">物體</param>
 57         /// <returns></returns>
 58         public virtual bool Insert(T entity)
 59         {
 60             if (entity == null)
 61                 throw new ArgumentNullException(nameof(entity));
 62 
 63             try
 64             {
 65                 Entities.Add(entity);
 66                 _context.SaveChanges();
 67                 var cacheContain = typeof(T).Name;
 68                 _cacheManager.RemoveByContain(cacheContain);
 69             }
 70             catch (DbUpdateException exception)
 71             {
 72                 //ensure that the detailed error text is saved in the Log
 73                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
 74             }
 75             return true;
 76         }
 77         /// <summary>
 78         /// 插入單個物體并回傳ID
 79         /// </summary>
 80         /// <param name="entity"></param>
 81         /// <returns></returns>
 82         public virtual object InsertAndGetId(T entity)
 83         {
 84             if (entity == null)
 85                 throw new ArgumentNullException(nameof(entity));
 86             try
 87             {
 88                 this.Entities.Add(entity);
 89                 this._context.SaveChanges();
 90                 var cacheContain = typeof(T).Name;
 91                 _cacheManager.RemoveByContain(cacheContain);
 92             }
 93             catch (DbUpdateException exception)
 94             {
 95                 //ensure that the detailed error text is saved in the Log
 96                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
 97             }
 98             return entity.Id;
 99         }
100         /// <summary>
101         /// 批量插入資料集
102         /// </summary>
103         /// <param name="entities">資料集</param>
104         public virtual void Insert(IEnumerable<T> entities)
105         {
106             if (entities == null)
107                 throw new ArgumentNullException(nameof(entities));
108 
109             try
110             {
111                 Entities.AddRange(entities);
112                 _context.SaveChanges();
113                 var cacheContain = typeof(T).Name;
114                 _cacheManager.RemoveByContain(cacheContain);
115             }
116             catch (DbUpdateException exception)
117             {
118                 //ensure that the detailed error text is saved in the Log
119                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
120             }
121         }
122         /// <summary>
123         /// 更新單個物體
124         /// </summary>
125         /// <param name="entity">物體</param>
126         /// <returns></returns>
127         public virtual bool Update(T entity)
128         {
129             if (entity == null)
130                 throw new ArgumentNullException(nameof(entity));
131             try
132             {
133                 this._context.Set<T>().Attach(entity);
134                 Type _type = typeof(T);
135                 PropertyInfo[] _properties = _type.GetProperties();
136                 var entry = _context.Entry(entity);
137                 foreach (PropertyInfo item in _properties)
138                 {
139                     if ("Id" == item.Name || "CreateTime" == item.Name || "CreatePerson" == item.Name)
140                     {
141                         continue;
142                     }
143                   
144                     entry.Property(item.Name).IsModified = true;
145                 }
146                 this._context.SaveChanges();
147                 var cacheContain = typeof(T).Name;
148                 _cacheManager.RemoveByContain(cacheContain);
149             }
150             catch (DbUpdateException exception)
151             {
152                 //ensure that the detailed error text is saved in the Log
153                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
154             }
155             return true;
156         }
157         /// <summary>
158         /// 批量更新資料集
159         /// </summary>
160         /// <param name="entities"></param>
161         public virtual void Update(IEnumerable<T> entities)
162         {
163             if (entities == null)
164                 throw new ArgumentNullException(nameof(entities));
165 
166             try
167             {
168                 // Entities.UpdateRange(entities);
169                 this._context.Set<T>().AttachRange(entities);
170                 foreach (var entity in entities)
171                 {
172                     Type _type = typeof(T);
173                     PropertyInfo[] _properties = _type.GetProperties();
174                     var entry = _context.Entry(entity);
175                     foreach (PropertyInfo item in _properties)
176                     {
177                         if ("Id" == item.Name || "CreateTime" == item.Name || "CreatePerson" == item.Name)
178                         {
179                             continue;
180                         }
181 
182                         entry.Property(item.Name).IsModified = true;
183                     }
184                 }
185 
186                 _context.SaveChanges();
187                 var cacheContain = typeof(T).Name;
188                 _cacheManager.RemoveByContain(cacheContain);
189             }
190             catch (DbUpdateException exception)
191             {
192                 //ensure that the detailed error text is saved in the Log
193                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
194             }
195         }
196         /// <summary>
197         /// 洗掉單個物體
198         /// </summary>
199         /// <param name="entity"></param>
200         /// <returns></returns>
201         public virtual bool Delete(T entity)
202         {
203             if (entity == null)
204                 throw new ArgumentNullException(nameof(entity));
205 
206             try
207             {
208                 Entities.Remove(entity);
209                 _context.SaveChanges();
210                 var cacheContain = typeof(T).Name;
211                 _cacheManager.RemoveByContain(cacheContain);
212             }
213             catch (DbUpdateException exception)
214             {
215                 //ensure that the detailed error text is saved in the Log
216                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
217             }
218             return true;
219         }
220         /// <summary>
221         /// 批量洗掉
222         /// </summary>
223         /// <param name="entities">洗掉的資料集</param>
224         public virtual void Delete(IEnumerable<T> entities)
225         {
226             if (entities == null)
227                 throw new ArgumentNullException(nameof(entities));
228 
229             try
230             {
231                 Entities.RemoveRange(entities);
232                 _context.SaveChanges();
233                 var cacheContain = typeof(T).Name;
234                 _cacheManager.RemoveByContain(cacheContain);
235             }
236             catch (DbUpdateException exception)
237             {
238                 //ensure that the detailed error text is saved in the Log
239                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
240             }
241         }
242         /// <summary>
243         /// 通過ID洗掉物體
244         /// </summary>
245         /// <param name="id"></param>
246         /// <returns></returns>
247         public virtual bool DeleteById(object id)
248         {
249             try
250             {
251                 var item = this.Entities.Find(id);
252                 if (item == null)
253                 {
254                     return false;
255                 }
256                 this.Entities.Remove(item);
257                 this._context.SaveChanges();
258                 var cacheContain = typeof(T).Name;
259                 _cacheManager.RemoveByContain(cacheContain);
260             }
261             catch (DbUpdateException exception)
262             {
263                 //ensure that the detailed error text is saved in the Log
264                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
265             }
266             return true;
267         }
268         /// <summary>
269         /// 通過ID（逗號分隔ID）批量洗掉
270         /// </summary>
271         /// <param name="ids"></param>
272         /// <returns></returns>
273         public virtual bool DeleteByIds(object ids)
274         {
275             try
276             {
277                 var idArray = ids.ToString().Split(',');
278                 for (int i = 0; i < idArray.Length; i++)
279                 {
280                     var item = this.Entities.Find(idArray[i]);
281                     if (item == null)
282                     {
283                         continue;
284                     }
285                     this.Entities.Remove(item);
286                 }
287                 this._context.SaveChanges();
288                 var cacheContain = typeof(T).Name;
289                 _cacheManager.RemoveByContain(cacheContain);
290             }
291             catch (DbUpdateException exception)
292             {
293                 //ensure that the detailed error text is saved in the Log
294                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
295             }
296             return true;
297         }
298         /// <summary>
299         /// 通過Id串列批量洗掉
300         /// </summary>
301         /// <param name="list"></param>
302         /// <returns></returns>
303         public virtual bool DeleteByIdList(List<object> list)
304         {
305             try
306             {
307                 for (int i = 0; i < list.Count; i++)
308                 {
309                     var item = this.Entities.Find(list[i]);
310                     if (item == null)
311                     {
312                         continue;
313                     }
314                     this.Entities.Remove(item);
315                 }
316                 this._context.SaveChanges();
317                 var cacheContain = typeof(T).Name;
318                 _cacheManager.RemoveByContain(cacheContain);
319             }
320             catch (DbUpdateException exception)
321             {
322                 //ensure that the detailed error text is saved in the Log
323                 throw new Exception(GetFullErrorTextAndRollbackEntityChanges(exception), exception);
324             }
325             return true;
326         }
327 
328         /// <summary>
329         /// 根據條件查找
330         /// </summary>
331         /// <param name="condition">lambda查詢條件where</param>
332         /// <returns></returns>
333         public virtual T GetEntity(Expression<Func<T, bool>> condition)
334         {
335             var cacheKey = typeof(T) + "GetSingleOrDefault.Condition."+ condition.ToString();
336             return _cacheManager.Get(cacheKey,()=>this.Entities.Where(condition).SingleOrDefault());
337         }
338         /// <summary>
339         /// 分頁查詢(Linq分頁方式)
340         /// </summary>
341         /// <param name="pageIndex">當前頁</param>
342         /// <param name="pageSize">頁碼</param>
343         /// <param name="condition">lambda查詢條件where</param>
344         /// <param name="sort">排序key:排序欄位，value:bool,true-desc，false-asc，默認：CreateTime desc</param>
345         /// <returns></returns>
346         public virtual IPagedList<T> GetListForPaging(int pageIndex, int pageSize, Expression<Func<T, bool>> condition = null, Dictionary<string, bool> sort = null)
347         {
348             if (pageIndex < 1)
349                 pageIndex = 1;
350             var cacheKey = typeof(T).Name;
351             cacheKey = cacheKey + ".GetList";
352             List<T> data = https://www.cnblogs.com/minghon/p/null;
353 
354             if (condition != null)
355             {
356                 cacheKey = cacheKey+ ".Condition." + condition.ToString();
357             }
358 
359             data = https://www.cnblogs.com/minghon/p/_cacheManager.Get>(cacheKey);
360 
361             if(data != null)
362             {
363                 if (sort != null)
364                 {
365                     foreach (var item in sort)
366                     {
367                         data =https://www.cnblogs.com/minghon/p/ data.AsQueryable().OrderBy(item.Key, item.Value).ToList();
368                     }
369                 }
370                 else
371                 {
372                     data = https://www.cnblogs.com/minghon/p/data.AsQueryable().OrderByDescending(x => x.CreateTime).ToList();
373                 }
374             }
375             else
376             {
377                 if(condition!=null)
378                 {
379                     data = https://www.cnblogs.com/minghon/p/this.Entities.Where(condition).ToList();
380                 }
381                 else
382                 {
383                     data = https://www.cnblogs.com/minghon/p/this.Entities.ToList();
384                 }
385                 _cacheManager.Set(cacheKey, data, 60);
386 
387                 data = https://www.cnblogs.com/minghon/p/data.AsQueryable().OrderByDescending(x => x.CreateTime).ToList();//無快取默認回傳排序后資料
388             }
389 
390             return new PagedList<T>(data, pageIndex, pageSize);
391         }
392 
393         /// <summary>  
394         /// 執行原始SQL命令  
395         /// </summary>  
396         /// <param name="commandText">SQL命令</param>  
397         /// <param name="parameters">引數</param>  
398         /// <returns>影響的記錄數</returns>  
399         public virtual IEnumerable<TElement> SqlQuery<TElement>(string sql, params object[] parameters) where TElement : class, new()
400         {
401             DataTable dt = this._context.Database.SqlQuery(sql, parameters);
402             return dt.ToEnumerable<TElement>();
403 
404         }
405         /// <summary>
406         /// 分頁查詢
407         /// </summary>
408         /// <param name="pageIndex">當前頁</param>
409         /// <param name="pageSize">每頁條數</param>
410         /// <param name="condition">lambda查詢條件where</param>
411         /// <param name="orderName">排序欄位 默認CreateTime</param>
412         /// <param name="sortOrder">排序方式 asc desc,默認CreateTime desc</param>
413         /// <returns></returns>
414         public virtual IPagedList<T> GetListForPaging(int pageIndex, int pageSize, Expression<Func<T, bool>> condition = null, string orderName = null, string sortOrder = null)
415         {
416             var cachekey = typeof(T).Name;
417             if (pageIndex < 1)
418                 pageIndex = 1;
419             if (orderName == null)
420             {
421                 orderName = "CreateTime";
422             }
423             bool isDesc = true;
424             if(sortOrder!=null&&sortOrder.ToLower() == "asc")
425             {
426                 isDesc = false;
427             }
428 
429             cachekey = cachekey + ".GetList";
430 
431             if(condition!=null)
432             {
433                 cachekey = cachekey + ".Condition." + condition.ToString();
434             }
435 
436             List<T> resultList = null;
437 
438             var cacheData = https://www.cnblogs.com/minghon/p/_cacheManager.Get>(cachekey);
439             if (cacheData != null)
440             {
441                 resultList = cacheData.AsQueryable().OrderBy(orderName, isDesc).ToList();
442             }
443             else
444             {
445                 if (condition == null)
446                 {
447                     resultList = Entities.ToList();
448                 }
449                 else
450                 {
451                     resultList = Entities.Where(condition).ToList();
452                 }
453                 _cacheManager.Set(cachekey, resultList, 60);
454             }
455          
456             return new PagedList<T>(resultList, pageIndex, pageSize);
457         }
458         /// <summary>
459         /// 執行SqlCommand
460         /// </summary>
461         /// <param name="sql">sql</param>
462         /// <param name="parameters">引數</param>
463         /// <returns></returns>
464         public virtual int ExecuteSqlCommand(string sql, params object[] parameters)
465         {
466             var result = this._context.Database.ExecuteSqlCommand(sql, parameters);
467             return result;
468         }
469         /// <summary>
470         /// 查詢串列,默認回傳整個表資料
471         /// </summary>
472         /// <param name="condition">lambda查詢條件where</param>
473         /// <returns></returns>
474         public virtual List<T> GetList(Expression<Func<T, bool>> condition = null)
475         {
476             var cacheKey = typeof(T).Name+ ".GetList";
477             List<T> entities = null;
478             if (condition != null)
479             {
480                 cacheKey = cacheKey + ".Condition." + condition.ToString();
481                 entities = _cacheManager.Get(cacheKey,()=>this.Entities.Where(condition).ToList());
482             }
483             else
484             {
485                 entities = _cacheManager.Get(cacheKey,()=>this.Entities.ToList());
486             }
487             return entities;
488         }
489         #region Utilities
490 
491         /// <summary>
492         /// Rollback of entity changes and return full error message
493         /// </summary>
494         /// <param name="exception">Exception</param>
495         /// <returns>Error message</returns>
496         protected string GetFullErrorTextAndRollbackEntityChanges(DbUpdateException exception)
497         {
498             //rollback entity changes
499             if (_context is DbContext dbContext)
500             {
501                 var entries = dbContext.ChangeTracker.Entries()
502                     .Where(e => e.State == EntityState.Added || e.State == EntityState.Modified).ToList();
503 
504                 entries.ForEach(entry =>
505                 {
506                     try
507                     {
508                         entry.State = EntityState.Unchanged;
509                     }
510                     catch (InvalidOperationException)
511                     {
512                         // ignored
513                     }
514                 });
515             }
516 
517             try
518             {
519                 _context.SaveChanges();
520                 return exception.ToString();
521             }
522             catch (Exception ex)
523             {
524                 //if after the rollback of changes the context is still not saving,
525                 //return the full text of the exception that occurred when saving
526                 return ex.ToString();
527             }
528         }
529 
530         #endregion
531 
532     }
533 }
534 public static class QueryableExtensions
535 {
536     public static IQueryable<T> OrderBy<T>(this IQueryable<T> queryable, string propertyName)
537     {
538         return QueryableHelper<T>.OrderBy(queryable, propertyName, false);
539     }
540     public static IQueryable<T> OrderBy<T>(this IQueryable<T> queryable, string propertyName, bool desc)
541     {
542         return QueryableHelper<T>.OrderBy(queryable, propertyName, desc);
543     }
544     static class QueryableHelper<T>
545     {
546         private static Dictionary<string, LambdaExpression> cache = new Dictionary<string, LambdaExpression>();
547         public static IQueryable<T> OrderBy(IQueryable<T> queryable, string propertyName, bool desc)
548         {
549             dynamic keySelector = GetLambdaExpression(propertyName);
550             return desc ? Queryable.OrderByDescending(queryable, keySelector) : Queryable.OrderBy(queryable, keySelector);
551         }
552         private static LambdaExpression GetLambdaExpression(string propertyName)
553         {
554             if (cache.ContainsKey(propertyName)) return cache[propertyName];
555             var param = Expression.Parameter(typeof(T));
556             var body = Expression.Property(param, propertyName);
557             var keySelector = Expression.Lambda(body, param);
558             cache[propertyName] = keySelector;
559             return keySelector;
560         }
561     }
562 }

   _cacheManager下篇我們講到快取管理的時候再來講，包括依賴注入也放到下篇，今天就先到這里了！

標籤：.NET Core

上一篇：【原創】C# 計時周期數（Ticks）在不同資料庫上的實作

下一篇：ASP.NET報 System.InvalidOperationException: 閱讀器關閉時嘗試呼叫 Read 無效。錯誤