一. 简介
该章节,可以说是一个简单轻松的章节,只要你对Expression表达式树、EF的基本使用、泛型有所了解,那么本章节实质上就是一个非常简单的封装章节,便于我们快捷开发。
PS:在该章节对于EF的上下文怎么处理,怎么来的,不做介绍,在后续的框架篇将详细介绍,下面的EF上下文,将直接使用db代替。
如果你对Expression、EF的增删改查、泛型生疏的话,可以先阅读以下章节:
(1). Expression表达式目录树:http://www.cnblogs.com/yaopengfei/p/7486870.html
(2). EF的基本增删改查:http://www.cnblogs.com/yaopengfei/p/7674715.html
(3). 泛型的使用:http://www.cnblogs.com/yaopengfei/p/6880629.html
二. 代码封装分享
下面的代码封装,主要就是围绕EF的增删改查进行封装以及各自对应的扩展,其中包括事务一体的封装、事务分离的封装、集成 Z.EntityFramework.Extensions 插件的封装、以及EF调用SQL语句的封装。
1. EF调用SQL语句:
1 /// <summary>
2 /// 执行增加,删除,修改操作(或调用存储过程)
3 /// </summary>
4 /// <param name="sql"></param>
5 /// <param name="pars"></param>
6 /// <returns></returns>
7 public int ExecuteSql(string sql, params SqlParameter[] pars)
8 {
9 return db.Database.ExecuteSqlCommand(sql, pars);
10 }
11
12 /// <summary>
13 /// 执行查询操作
14 /// </summary>
15 /// <typeparam name="T"></typeparam>
16 /// <param name="sql"></param>
17 /// <param name="pars"></param>
18 /// <returns></returns>
19 public List<T> ExecuteQuery<T>(string sql, params SqlParameter[] pars)
20 {
21 return db.Database.SqlQuery<T>(sql, pars).ToList();
22 }
2. EF增删改查封装(事务一体)
(1). 新增
1 public int Add(T model)
2 {
3 DbSet<T> dst = db.Set<T>();
4 dst.Add(model);
5 return db.SaveChanges();
6 }
(2). 删除
1 /// <summary>
2 /// 删除(适用于先查询后删除的单个实体)
3 /// </summary>
4 /// <param name="model">需要删除的实体</param>
5 /// <returns></returns>
6 public int Del(T model)
7 {
8 db.Set<T>().Attach(model);
9 db.Set<T>().Remove(model);
10 return db.SaveChanges();
11 }
12 /// <summary>
13 /// 根据条件删除(支持批量删除)
14 /// </summary>
15 /// <param name="delWhere">传入Lambda表达式(生成表达式目录树)</param>
16 /// <returns></returns>
17 public int DelBy(Expression<Func<T, bool>> delWhere)
18 {
19 List<T> listDels = db.Set<T>().Where(delWhere).ToList();
20 listDels.ForEach(d =>
21 {
22 db.Set<T>().Attach(d);
23 db.Set<T>().Remove(d);
24 });
25 return db.SaveChanges();
26 }
(3). 查询
1 /// <summary>
2 /// 根据条件查询
3 /// </summary>
4 /// <param name="whereLambda">查询条件(lambda表达式的形式生成表达式目录树)</param>
5 /// <returns></returns>
6 public List<T> GetListBy(Expression<Func<T, bool>> whereLambda)
7 {
8 return db.Set<T>().Where(whereLambda).ToList();
9 }
10 /// <summary>
11 /// 根据条件排序和查询
12 /// </summary>
13 /// <typeparam name="Tkey">排序字段类型</typeparam>
14 /// <param name="whereLambda">查询条件</param>
15 /// <param name="orderLambda">排序条件</param>
16 /// <param name="isAsc">升序or降序</param>
17 /// <returns></returns>
18 public List<T> GetListBy<Tkey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true)
19 {
20 List<T> list = null;
21 if (isAsc)
22 {
23 list = db.Set<T>().Where(whereLambda).OrderBy(orderLambda).ToList();
24 }
25 else
26 {
27 list = db.Set<T>().Where(whereLambda).OrderByDescending(orderLambda).ToList();
28 }
29 return list;
30 }
31 /// <summary>
32 /// 分页查询
33 /// </summary>
34 /// <typeparam name="Tkey">排序字段类型</typeparam>
35 /// <param name="pageIndex">页码</param>
36 /// <param name="pageSize">页容量</param>
37 /// <param name="whereLambda">查询条件</param>
38 /// <param name="orderLambda">排序条件</param>
39 /// <param name="isAsc">升序or降序</param>
40 /// <returns></returns>
41 public List<T> GetPageList<Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true)
42 {
43
44 List<T> list = null;
45 if (isAsc)
46 {
47 list = db.Set<T>().Where(whereLambda).OrderBy(orderLambda)
48 .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
49 }
50 else
51 {
52 list = db.Set<T>().Where(whereLambda).OrderByDescending(orderLambda)
53 .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
54 }
55 return list;
56 }
57 /// <summary>
58 /// 分页查询输出总行数
59 /// </summary>
60 /// <typeparam name="Tkey">排序字段类型</typeparam>
61 /// <param name="pageIndex">页码</param>
62 /// <param name="pageSize">页容量</param>
63 /// <param name="whereLambda">查询条件</param>
64 /// <param name="orderLambda">排序条件</param>
65 /// <param name="isAsc">升序or降序</param>
66 /// <returns></returns>
67 public List<T> GetPageList<Tkey>(int pageIndex, int pageSize, ref int rowCount, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true)
68 {
69 int count = 0;
70 List<T> list = null;
71 count = db.Set<T>().Where(whereLambda).Count();
72 if (isAsc)
73 {
74 var iQueryList = db.Set<T>().Where(whereLambda).OrderBy(orderLambda)
75 .Skip((pageIndex - 1) * pageSize).Take(pageSize);
76
77 list = iQueryList.ToList();
78 }
79 else
80 {
81 var iQueryList = db.Set<T>().Where(whereLambda).OrderByDescending(orderLambda)
82 .Skip((pageIndex - 1) * pageSize).Take(pageSize);
83 list = iQueryList.ToList();
84 }
85 rowCount = count;
86 return list;
87 }
(4). 修改
1 /// <summary>
2 /// 修改
3 /// </summary>
4 /// <param name="model">修改后的实体</param>
5 /// <returns></returns>
6 public int Modify(T model)
7 {
8 db.Entry(model).State = EntityState.Modified;
9 return db.SaveChanges();
10 }
11
12 /// <summary>
13 /// 单实体扩展修改(把不需要修改的列用LAMBDA数组表示出来)
14 /// </summary>
15 /// <param name="model">要修改的实体对象</param>
16 /// <param name="ignoreProperties">不须要修改的相关字段</param>
17 /// <returns>受影响的行数</returns>
18 public int Modify(T model, params Expression<Func<T, object>>[] ignoreProperties)
19 {
20 using (DbContext db = new DBContextFactory().GetDbContext())
21 {
22 db.Set<T>().Attach(model);
23
24 DbEntityEntry entry = db.Entry<T>(model);
25 entry.State = EntityState.Unchanged;
26
27 Type t = typeof(T);
28 List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
29
30 Dictionary<string, PropertyInfo> dicPros = new Dictionary<string, PropertyInfo>();
31 proInfos.ForEach(
32 p => dicPros.Add(p.Name, p)
33 );
34
35 if (ignoreProperties != null)
36 {
37 foreach (var ignorePropertyExpression in ignoreProperties)
38 {
39 //根据表达式得到对应的字段信息
40 var ignorePropertyName = new PropertyExpressionParser<T>(ignorePropertyExpression).Name;
41 dicPros.Remove(ignorePropertyName);
42 }
43 }
44
45 foreach (string proName in dicPros.Keys)
46 {
47 entry.Property(proName).IsModified = true;
48 }
49 return db.SaveChanges();
50 }
51 }
52
53 /// <summary>
54 /// 批量修改(非lambda)
55 /// </summary>
56 /// <param name="model">要修改实体中 修改后的属性 </param>
57 /// <param name="whereLambda">查询实体的条件</param>
58 /// <param name="proNames">lambda的形式表示要修改的实体属性名</param>
59 /// <returns></returns>
60 public int ModifyBy(T model, Expression<Func<T, bool>> whereLambda, params string[] proNames)
61 {
62 List<T> listModifes = db.Set<T>().Where(whereLambda).ToList();
63 Type t = typeof(T);
64 List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
65 Dictionary<string, PropertyInfo> dicPros = new Dictionary<string, PropertyInfo>();
66 proInfos.ForEach(p =>
67 {
68 if (proNames.Contains(p.Name))
69 {
70 dicPros.Add(p.Name, p);
71 }
72 });
73 foreach (string proName in proNames)
74 {
75 if (dicPros.ContainsKey(proName))
76 {
77 PropertyInfo proInfo = dicPros[proName];
78 object newValue = proInfo.GetValue(model, null);
79 foreach (T m in listModifes)
80 {
81 proInfo.SetValue(m, newValue, null);
82 }
83 }
84 }
85 return db.SaveChanges();
86 }
87
88 /// <summary>
89 /// 批量修改(支持lambda)
90 /// </summary>
91 /// <param name="model">要修改实体中 修改后的属性 </param>
92 /// <param name="whereLambda">查询实体的条件</param>
93 /// <param name="proNames">lambda的形式表示要修改的实体属性名</param>
94 /// <returns></returns>
95 public int ModifyBy(T model, Expression<Func<T, bool>> whereLambda, params Expression<Func<T, object>>[] proNames)
96 {
97 List<T> listModifes = db.Set<T>().Where(whereLambda).ToList();
98 Type t = typeof(T);
99 List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
100 Dictionary<string, PropertyInfo> dicPros = new Dictionary<string, PropertyInfo>();
101 if (proNames != null)
102 {
103 foreach (var myProperyExp in proNames)
104 {
105 var my_ProName = new PropertyExpressionParser<T>(myProperyExp).Name;
106 proInfos.ForEach(p =>
107 {
108 if (p.Name.Equals(my_ProName))
109 {
110 dicPros.Add(p.Name, p);
111 }
112 });
113 if (dicPros.ContainsKey(my_ProName))
114 {
115 PropertyInfo proInfo = dicPros[my_ProName];
116 object newValue = proInfo.GetValue(model, null);
117 foreach (T m in listModifes)
118 {
119 proInfo.SetValue(m, newValue, null);
120 }
121 }
122 }
123 }
124 return db.SaveChanges();
125 }
126
3. EF增删改封装(事务分离)
(1). 事务批量处理
1 /// <summary>
2 /// 事务批量处理
3 /// </summary>
4 /// <returns></returns>
5 public int SaveChange()
6 {
7 return db.SaveChanges();
8 }
(2). 新增
1 /// <summary>
2 /// 新增
3 /// </summary>
4 /// <param name="model">需要新增的实体</param>
5 public void AddNo(T model)
6 {
7 db.Set<T>().Add(model);
8 }
(3). 修改
1 /// <summary>
2 /// 修改
3 /// </summary>
4 /// <param name="model">修改后的实体</param>
5 public void ModifyNo(T model)
6 {
7 db.Entry(model).State = EntityState.Modified;
8 }
(4). 删除
/// <summary>
/// 删除
/// </summary>
/// <param name="model">需要删除的实体</param>
public void DelNo(T model)
{
db.Entry(model).State = EntityState.Deleted;
}
/// <summary>
/// 条件删除
/// </summary>
/// <param name="delWhere">需要删除的条件</param>
public void DelByNo(Expression<Func<T, bool>> delWhere)
{
List<T> listDels = db.Set<T>().Where(delWhere).ToList();
listDels.ForEach(d =>
{
db.Set<T>().Attach(d);
db.Set<T>().Remove(d);
});
}
4. Z.EntityFramework.Extensions 插件封装
方案一:在使用EF事务分离的方法的前提下,单独调用提交方法
1 /// <summary>
2 /// 事务提交,速度约为saveChange的10倍-15倍
3 /// </summary>
4 public void BulkSaveChanges()
5 {
6 db.BulkSaveChanges();
7 }
方案二:插件特有的增删改方法
/// <summary>
/// 新增
/// </summary>
/// <param name="model">新增的实体集合</param>
public void BulkInsert(List<T> model)
{
db.BulkInsert<T>(model);
}
/// <summary>
/// 删除
/// </summary>
/// <param name="model">需要删除的实体集合</param>
public void BulkDelete(List<T> model)
{
db.BulkDelete<T>(model);
}
/// <summary>
/// 根据条件删除
/// </summary>
/// <param name="delWhere">删除条件</param>
public void BulkDeleteBy(Expression<Func<T, bool>> delWhere)
{
List<T> listDels = db.Set<T>().Where(delWhere).ToList();
db.BulkDelete<T>(listDels);
}
/// <summary>
/// 需要修改的实体集合
/// </summary>
/// <param name="model"></param>
public void BulkUpdate(List<T> model)
{
db.BulkUpdate<T>(model);
}
未完,后续会不断更新。