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);
        }

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

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

using System;
using System.Collections.Generic;
using System.Data.Objects;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace System.Linq
{
    public static class LinqExt
    {
        //分页
        public static IQueryable<T> Page<T>(this IQueryable<T> query, int pageIndex, int pageSize)
        {
            int total;
           return  Page<T>(query, pageIndex, pageSize);
        }

        //分页
        public static IQueryable<T> Page<T>(this IQueryable<T> query, int pageIndex, int pageSize, out int total)
        {
            Expression<Func<T, string>> order = null;
            return Page(query, pageIndex, pageSize, order, false, out total);
        }

        //分页
        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);
        }

      

        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;

        }

    }
}

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