解决 EF 分层查询的一个性能问题[转]

前两年帮朋友 做了个网吧管理软件，采用动软的三层架构 sql语句生成的。最近因功能变更 要改动，而我这段正在做asp.net mvc +ef+autofac的一个电商网站。索性 就把原来的底层全重新了套用了我现在的架构 EF6.0+autofac+三层架构，上层的asp.net没有变。改完后发现交班页面打开巨慢。

   

跟踪EF生成的sql语句   发现生成的sql 有问题，查找的全表，而全表有近10万条的数据。

继续跟踪数据库的耗时查询 发现确实是这条语句占时间

为什么会这样呢，我在查询里做条件搜索了，为啥 结果不对呢？

贴出 BaseRepository.cs 的代码

class="code_img_closed" src="/Upload/Images/2014080923/0015B68B3C38AA5B.gif" alt="" />logs_code_hide('90546e12-ce1e-45ff-857c-4fbf39441633',event)" src="/Upload/Images/2014080923/2B1B950FA3DF188F.gif" alt="" />

 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 = db.Set<T>().Where<T>(whereLambda); 46 47 total = tempData.Count(); 48 49 //排序获取当前页的数据 50 if (isAsc) 51  { 52 tempData = tempData.OrderBy<T, S>(orderByLambda). 53 Skip<T>(pageSize * (pageIndex - 1)). 54 Take<T>(pageSize).AsQueryable(); 55  } 56 else 57  { 58 tempData = tempData.OrderByDescending<T, S>(orderByLambda). 59 Skip<T>(pageSize * (pageIndex - 1)). 60 Take<T>(pageSize).AsQueryable(); 61  } 62 return tempData.AsQueryable(); 63  } 64 }

View Code

调用代码 

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 = this.Entities.Where<T>(whereLambda); 120 121 total = tempData.Count(); 122 123 //排序获取当前页的数据 124 if (isAsc) 125 { 126 tempData = tempData.OrderBy<T, S>(orderByLambda). 127 Skip<T>(pageSize * (pageIndex - 1)). 128 Take<T>(pageSize).AsQueryable(); 129 } 130 else 131 { 132 tempData = tempData.OrderByDescending<T, S>(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> Entities 143 { 144 get 145 { 146 if (_entities == null) 147 _entities = _context.Set<T>(); 148 return _entities; 149 } 150 } 151 152 153 public virtual IQueryable<T> Table 154 { 155 get { return this.Entities; } 156 } 157 158 159 }

View Code

同时修改调用代码 为

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<>方法 没问题。但跨了几层传递后 就有问题。并没有生成我想要的查询语句。