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 }
希望我这个类,对大家有帮助,同时也希望各位网友,提出你们宝贵的意见,让我们共同进步吧.
