entity framework 批量更新,批量删除,分页 的扩展函数

   在前面的博客就分别提到了要实现批量更新和删除的函数,今天我也终于实现了.现在拿出来跟大家分享一下吧.

  我们先来说批量删除吧.请看代码

 

        public static int Delete<T>(this ObjectSet<T> ent, Expression<Func<T, bool>> where) where T : class
        {
            var query = ent.Where(where);
            ObjectQuery objQuery = query as ObjectQuery;
           string sql=objQuery.ToTraceString();
           sql = "delete " + sql.Substring(sql.IndexOf("from", StringComparison.OrdinalIgnoreCase));
           sql = sql.Replace("[Extent1].", "").Replace("AS [Extent1]", "").Replace("__linq__", "");
           List<object> objs = new List<object>();
           foreach (var para in objQuery.Parameters)
           {
               objs.Add(para.Value);
           }
          int index= ent.Context.ExecuteStoreCommand(sql, objs.ToArray());
          return index;

        }

删除其实也并不难,主要是通过查询语句,作相应的修改就行了.

我们再看,修改语句.

 public static int Update<T>(this ObjectSet<T> ent, Expression<Func<T, bool>> where, Expression<Func<T>> updater) where T : class
        {
            //where 语句
            var query = ent.Where(where);
            ObjectQuery objQuery = query as ObjectQuery;
            List<object> objParams = new List<object>();            
            string sql = objQuery.ToTraceString();
            sql = sql.Substring(sql.IndexOf("from", StringComparison.OrdinalIgnoreCase)).Replace("__linq__", "");
            int paramindex = objQuery.Parameters.Count;
            foreach (var para in objQuery.Parameters)
            {
                objParams.Add(para.Value);
            }
            //获取Update的赋值语句
            var valueObj = updater.Compile().Invoke();
            MemberInitExpression updateMemberExpr = (MemberInitExpression)updater.Body;
            StringBuilder updateBuilder = new StringBuilder();          
            Type valueType = typeof(T);
            foreach (var bind in updateMemberExpr.Bindings.Cast<MemberAssignment>())
            {
                string name = bind.Member.Name;
                updateBuilder.AppendFormat("{0}=@p{1},", name, paramindex++);                
                var value = valueType.GetProperty(name).GetValue(valueObj);           
                objParams.Add(value);
            }
            if (updateBuilder.Length == 0)
            {
                throw new Exception("请填写要更新的值");
            }
            else
            {
                sql = " update [Extent1] set " + updateBuilder.Remove(updateBuilder.Length - 1, 1).ToString() + " " + sql;
            }
            int index = ent.Context.ExecuteStoreCommand(sql, objParams.ToArray());
            return index;

        }

  修改,折腾了我不少时间,关键就是那个赋值语句,折腾了不少时间,一开始都把时间花费在表达树上,这也是网上找到的.但看网上的那个修改语句,都是有问题,主要是在修改时,对于参数化的赋值,做不了.只能修改 常量的值,这对我们的使用是有非常大的限制.

  后来,突发灵感.想到了,先用表达树,计算出Expression<Func<T>> updater的值,然后再把那得出来的值,弄成参数,传进去.就这样,解决了参数化传值的问题.

  关于这方法,曾经有网友反对过.说直接用sql,就行了.比较简单. 在这里,我想解释一下,我坚持要写,处于2个方面考虑. 一是,方便开发人员开发.因为这个的写法就是典型的lingq写法. 二是,我觉的,作为一个框架, 我们要把能统一的东西,统一起来,这样以后有什么变动,维护起来也比较方便.如果我们直接用sql语句,就会比较乱,不便于后期的维护.

   下面我们就看看批量删除和修改,调用的方便性吧.

        ec.testEnt.Update(ent =>ent.MonthlyDataID==new Guid("95134D1D-2647-4F84-B82A-DB84B0BC382E") ,
                      ()=> new BMW_MonthlyDataDetail2() { CreationUser = "2012-12-1",CreationDate=DateTime.Parse("2012-10-2"),ModificaitonUser=value });



        ec.testEnt.Delete(ent => ent.MonthlyDataID == new Guid("95134D1D-2647-4F84-B82A-DB84B0BC382E"));

看我们删除和修改是否都很方便啊.?

  当然,我这个批量删除和修改是不支持多表的.因为那个批量删除和修改多表,用的地方不多,而且实现起来很麻烦.所以就没有去实现了.

下面我们再看分页的函数吧.

        //分页
        public static IQueryable<T> Page<T, TResult>(this IQueryable<T> query, int pageIndex, int pageSize, Expression<Func<T, TResult>> orderByProperty, bool isAscendingOrder, out int rowsCount)
        {
            if (pageSize <= 0) pageSize = 20;

            rowsCount = query.Count();

            if (rowsCount <= pageSize || pageIndex <= 0) pageIndex = 1;

            int excludedRows = (pageIndex - 1) * pageSize;

            if (orderByProperty != null)
            {
                if (isAscendingOrder)
                    query = query.OrderBy(orderByProperty);
                else
                    query = query.OrderByDescending(orderByProperty);
            }         
            if (pageIndex == 1)
                return query.Take(pageSize);
            else
                return query.Skip(excludedRows).Take(pageSize);
        }

我们把分页,也用一个函数统一起来,这样开发人员分页的时候,就不会每个人都有自己的一套了.

下面我再把这个类的整体代码弄出来吧.

View Code 
  1 using System;
  2 using System.Collections.Generic;
  3 using System.Data.Objects;
  4 using System.Linq;
  5 using System.Linq.Expressions;
  6 using System.Text;
  7 using System.Threading.Tasks;
  8 
  9 namespace System.Linq
 10 {
 11     public static class LinqExt
 12     {
 13         //分页
 14         public static IQueryable<T> Page<T>(this IQueryable<T> query, int pageIndex, int pageSize)
 15         {
 16             int total;
 17            return  Page<T>(query, pageIndex, pageSize);
 18         }
 19 
 20         //分页
 21         public static IQueryable<T> Page<T>(this IQueryable<T> query, int pageIndex, int pageSize, out int total)
 22         {
 23             Expression<Func<T, string>> order = null;
 24             return Page(query, pageIndex, pageSize, order, false, out total);
 25         }
 26 
 27         //分页
 28         public static IQueryable<T> Page<T, TResult>(this IQueryable<T> query, int pageIndex, int pageSize, Expression<Func<T, TResult>> orderByProperty, bool isAscendingOrder, out int rowsCount)
 29         {
 30             if (pageSize <= 0) pageSize = 20;
 31 
 32             rowsCount = query.Count();
 33 
 34             if (rowsCount <= pageSize || pageIndex <= 0) pageIndex = 1;
 35 
 36             int excludedRows = (pageIndex - 1) * pageSize;
 37 
 38             if (orderByProperty != null)
 39             {
 40                 if (isAscendingOrder)
 41                     query = query.OrderBy(orderByProperty);
 42                 else
 43                     query = query.OrderByDescending(orderByProperty);
 44             }         
 45             if (pageIndex == 1)
 46                 return query.Take(pageSize);
 47             else
 48                 return query.Skip(excludedRows).Take(pageSize);
 49         }
 50 
 51       
 52 
 53         public static int Delete<T>(this ObjectSet<T> ent, Expression<Func<T, bool>> where) where T : class
 54         {
 55             var query = ent.Where(where);
 56             ObjectQuery objQuery = query as ObjectQuery;
 57            string sql=objQuery.ToTraceString();
 58            sql = "delete " + sql.Substring(sql.IndexOf("from", StringComparison.OrdinalIgnoreCase));
 59            sql = sql.Replace("[Extent1].", "").Replace("AS [Extent1]", "").Replace("__linq__", "");
 60            List<object> objs = new List<object>();
 61            foreach (var para in objQuery.Parameters)
 62            {
 63                objs.Add(para.Value);
 64            }
 65           int index= ent.Context.ExecuteStoreCommand(sql, objs.ToArray());
 66           return index;
 67 
 68         }
 69 
 70         public static int Update<T>(this ObjectSet<T> ent, Expression<Func<T, bool>> where, Expression<Func<T>> updater) where T : class
 71         {
 72             //where 语句
 73             var query = ent.Where(where);
 74             ObjectQuery objQuery = query as ObjectQuery;
 75             List<object> objParams = new List<object>();            
 76             string sql = objQuery.ToTraceString();
 77             sql = sql.Substring(sql.IndexOf("from", StringComparison.OrdinalIgnoreCase)).Replace("__linq__", "");
 78             int paramindex = objQuery.Parameters.Count;
 79             foreach (var para in objQuery.Parameters)
 80             {
 81                 objParams.Add(para.Value);
 82             }
 83             //获取Update的赋值语句
 84             var valueObj = updater.Compile().Invoke();
 85             MemberInitExpression updateMemberExpr = (MemberInitExpression)updater.Body;
 86             StringBuilder updateBuilder = new StringBuilder();          
 87             Type valueType = typeof(T);
 88             foreach (var bind in updateMemberExpr.Bindings.Cast<MemberAssignment>())
 89             {
 90                 string name = bind.Member.Name;
 91                 updateBuilder.AppendFormat("{0}=@p{1},", name, paramindex++);                
 92                 var value = valueType.GetProperty(name).GetValue(valueObj);           
 93                 objParams.Add(value);
 94             }
 95             if (updateBuilder.Length == 0)
 96             {
 97                 throw new Exception("请填写要更新的值");
 98             }
 99             else
100             {
101                 sql = " update [Extent1] set " + updateBuilder.Remove(updateBuilder.Length - 1, 1).ToString() + " " + sql;
102             }
103             int index = ent.Context.ExecuteStoreCommand(sql, objParams.ToArray());
104             return index;
105 
106         }
107 
108     }
109 }

希望我这个类,对大家有帮助,同时也希望各位网友,提出你们宝贵的意见,让我们共同进步吧.

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/jake1/p/3047965.html