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