框架搭建与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 = null; 353 354 if (condition != null) 355 { 356 cacheKey = cacheKey+ ".Condition." + condition.ToString(); 357 } 358 359 data = _cacheManager.Get<List<T>>(cacheKey); 360 361 if(data != null) 362 { 363 if (sort != null) 364 { 365 foreach (var item in sort) 366 { 367 data = data.AsQueryable().OrderBy(item.Key, item.Value).ToList(); 368 } 369 } 370 else 371 { 372 data = data.AsQueryable().OrderByDescending(x => x.CreateTime).ToList(); 373 } 374 } 375 else 376 { 377 if(condition!=null) 378 { 379 data = this.Entities.Where(condition).ToList(); 380 } 381 else 382 { 383 data = this.Entities.ToList(); 384 } 385 _cacheManager.Set(cacheKey, data, 60); 386 387 data = 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 = _cacheManager.Get<List<T>>(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下篇我们讲到缓存管理的时候再来讲，包括依赖注入也放到下篇，今天就先到这里了！
