首页 > 代码库 > 解决 EF 分层查询的一个性能问题
解决 EF 分层查询的一个性能问题
前两年帮朋友 做了个网吧管理软件,采用动软的三层架构 sql语句生成的。最近因功能变更 要改动,而我这段正在做asp.net mvc +ef+autofac的一个电商网站。索性 就把原来的底层全重新了套用了我现在的架构 EF6.0+autofac+三层架构,上层的asp.net没有变。改完后发现交班页面打开巨慢。
跟踪EF生成的sql语句 发现生成的sql 有问题,查找的全表,而全表有近10万条的数据。
继续跟踪数据库的耗时查询 发现确实是这条语句占时间
为什么会这样呢,我在查询里做条件搜索了,为啥 结果不对呢?
贴出 BaseRepository.cs 的代码
1 public class BaseRepository<T> :IDBbase<T> where T : class 2 { 3 //实例化EF框架 4 protected skdbContext db = new skdbContext(); 5 6 //添加 7 public T AddEntities(T entity) 8 { 9 db.Entry<T>(entity).State = EntityState.Added;10 db.SaveChanges();11 return entity;12 }13 14 //修改15 public bool UpdateEntity(T entity)16 {17 db.Set<T>().Attach(entity);18 db.Entry<T>(entity).State = EntityState.Modified;19 return db.SaveChanges() > 0;20 }21 22 //修改23 public bool DeleteEntities(T entity)24 {25 db.Set<T>().Attach(entity);26 db.Entry<T>(entity).State = EntityState.Deleted;27 return db.SaveChanges() > 0;28 }29 30 //查询31 public IQueryable<T> LoadEntities(Func<T, bool> wherelambda)32 {33 return db.Set<T>().Where<T>(wherelambda).AsQueryable();34 }35 //查询单个36 public T LoadEntitie(Func<T, bool> wherelambda)37 {38 return db.Set<T>().FirstOrDefault<T>(wherelambda);39 }40 41 //分页42 public IQueryable<T> LoadPagerEntities<S>(int pageSize, int pageIndex, out int total,43 Func<T, bool> whereLambda, bool isAsc, Func<T, S> orderByLambda)44 {45 var tempData = http://www.mamicode.com/db.Set().Where (whereLambda);46 47 total = tempData.Count();48 49 //排序获取当前页的数据50 if (isAsc)51 {52 tempData = http://www.mamicode.com/tempData.OrderBy (orderByLambda).53 Skip<T>(pageSize * (pageIndex - 1)).54 Take<T>(pageSize).AsQueryable();55 }56 else57 {58 tempData = http://www.mamicode.com/tempData.OrderByDescending (orderByLambda).59 Skip<T>(pageSize * (pageIndex - 1)).60 Take<T>(pageSize).AsQueryable();61 }62 return tempData.AsQueryable();63 }64 }
调用代码
return jiaobanitem.LoadEntities(t => t.JiaoBanID == jiaobanID && t.GoodsID == GoodsID).FirstOrDefault();
参考 nopCommerce 修改baserepository
1 public class EFRepository<T> : IRepository<T> where T : class 2 { 3 //实例化EF框架 4 //protected YaFeiNetContext db = new YaFeiNetContext(); 5 private DbContext _context; 6 private IDbSet<T> _entities; 7 8 public EFRepository(DbContext context) 9 { 10 this._context = context; 11 } 12 13 14 //添加 15 public virtual T AddEntities(T entity) 16 { 17 try 18 { 19 if(entity==null) 20 throw new ArgumentNullException("entity"); 21 22 this.Entities.Add(entity); 23 this._context.SaveChanges(); 24 return entity; 25 } 26 catch(DbEntityValidationException dbEx) 27 { 28 var msg = string.Empty; 29 foreach(var validationErrors in dbEx.EntityValidationErrors) 30 foreach (var validationError in validationErrors.ValidationErrors) 31 msg += string.Format("Property:{0} Error:{1}", validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine ; 32 33 var fail = new Exception(msg,dbEx); 34 throw fail; 35 } 36 } 37 38 //修改 39 public virtual bool UpdateEntities(T entity) 40 { 41 try 42 { 43 if (entity == null) 44 throw new ArgumentNullException("entity"); 45 46 47 // this.Entities.Attach(entity); 48 // _context.Entry<T>(entity).State = EntityState.Modified; 49 return this._context.SaveChanges() > 0; 50 } 51 catch (DbEntityValidationException dbEx) 52 { 53 var msg = string.Empty; 54 foreach (var validationErrors in dbEx.EntityValidationErrors) 55 foreach (var validationError in validationErrors.ValidationErrors) 56 msg += string.Format("Property:{0} Error:{1}", validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine; 57 58 var fail = new Exception(msg, dbEx); 59 throw fail; 60 } 61 62 } 63 64 //修改 65 public virtual bool DeleteEntities(T entity) 66 { 67 try 68 { 69 if (entity == null) 70 throw new ArgumentNullException("entity"); 71 72 //db2.Set<T>().Attach(entity); 73 //db2.Entry<T>(entity).State = EntityState.Deleted; 74 this.Entities.Remove(entity); 75 return this._context.SaveChanges() > 0; 76 } 77 catch (DbEntityValidationException dbEx) 78 { 79 var msg = string.Empty; 80 foreach (var validationErrors in dbEx.EntityValidationErrors) 81 foreach (var validationError in validationErrors.ValidationErrors) 82 msg += string.Format("Property:{0} Error:{1}", validationError.PropertyName, validationError.ErrorMessage) + Environment.NewLine; 83 84 var fail = new Exception(msg, dbEx); 85 throw fail; 86 } 87 88 89 } 90 91 //查询 92 public virtual IQueryable<T> LoadEntities(Func<T, bool> wherelambda) 93 { 94 return this.Entities.Where<T>(wherelambda).AsQueryable(); 95 } 96 //查询单个 97 public virtual T LoadEntitie(Func<T, bool> wherelambda) 98 { 99 return this.Table.Where(wherelambda).FirstOrDefault();100 }101 /// <summary>102 /// 根据主键查找103 /// </summary>104 /// <param name="id"></param>105 /// <returns></returns>106 public virtual T GetById(object id)107 {108 return this.Entities.Find(id);109 }110 111 112 113 114 //分页115 public virtual IQueryable<T> LoadPagerEntities<S>(int pageSize, int pageIndex, out int total,116 Func<T, bool> whereLambda, bool isAsc, Func<T, S> orderByLambda)117 {118 119 var tempData = http://www.mamicode.com/this.Entities.Where<T>(whereLambda);120 121 total = tempData.Count();122 123 //排序获取当前页的数据124 if (isAsc)125 {126 tempData = http://www.mamicode.com/tempData.OrderBy(orderByLambda).127 Skip<T>(pageSize * (pageIndex - 1)).128 Take<T>(pageSize).AsQueryable();129 }130 else131 {132 tempData = http://www.mamicode.com/tempData.OrderByDescending (orderByLambda).133 Skip<T>(pageSize * (pageIndex - 1)).134 Take<T>(pageSize).AsQueryable();135 }136 return tempData.AsQueryable();137 138 139 140 }141 142 protected virtual IDbSet<T> Entities143 {144 get145 {146 if (_entities == null)147 _entities = _context.Set<T>();148 return _entities;149 }150 }151 152 153 public virtual IQueryable<T> Table154 {155 get { return this.Entities; }156 }157 158 159 }
同时修改调用代码 为
return jiaobanitem.Table.Where(t=>t.JiaoBanID ==jiaobanID && t.GoodsID ==GoodsID).FirstOrDefault();
问题解决 页面响应不到100ms 同时调试中 生成的sql语句已经有 查询条件了
问题出在
//查询
public IQueryable<T> LoadEntities(Func<T, bool> wherelambda)
{
return db.Set<T>().Where<T>(wherelambda).AsQueryable();
}
为了验证,我在前台直接调用
return this.context.Set<tb_e_jiaoBanItem>().Where(t => t.JiaoBanID == jiaobanID && t.GoodsID == GoodsID).AsQueryable().FirstOrDefault();
页面响应也是 100ms左右,性能没问题。直接调用 dbcontext的set<>方法 没问题。但跨了几层传递后 就有问题。并没有生成我想要的查询语句。