第十九节: 结合【表达式目录树】来封装EF的BaseDal层的方法

一. 简介

　　该章节，可以说是一个简单轻松的章节，只要你对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);
        }

　　未完，后续会不断更新。