前两年帮朋友 做了个网吧管理软件，采用动软的三层架构 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 = 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<>方法 没问题。但跨了几层传递后 就有问题。并没有生成我想要的查询语句。
相关文章
