ef增删改查 mysql_关于EF 通用增删改查的封装

1 usingSystem;2 usingSystem.Collections.Generic;3 usingSystem.Linq;4 usingSystem.Text;5 usingSystem.Transactions;6 usingSystem.Data.Entity;7 usingSystem.Linq.Expressions;8 usingSystem.Data;9 usingSystem.Linq.Dynamic;10 usingEntityFramework.Extensions;11 usingSystem.Reflection;12 usingSystem.Data.Entity.Infrastructure;13 usingMySql.Data.MySqlClient;14 /************************************************15 ◇作者： LowKeyC 需要引用这个程序集:EntityFramework.Extended.6.1.0.16816 ◇说明： 实现EF通用的CRUD通用的接口17 ◇版本号：V1.018 ◇创建日期：2017年6月22日 星期四19 *****************************************************/

20 namespaceEFCommon.SqlHelp21 {22 public classRepository : IRepository, IDisposable23 {24

25 private readonly static DbContext _DbContextHandle =new ahavadbEntities();//此处进行调用EF的DBContent 的实体类或者通过工厂化模式来进行调用。

26

27 ///

28 ///添加一个对象29 ///

30 ///

31 ///

32 ///

33 public bool Add(T Entity) where T : class

34 {35 using (TransactionScope Ts = newTransactionScope(TransactionScopeOption.Required))36 {37 _DbContextHandle.Set().Add(Entity);38 int Count =_DbContextHandle.SaveChanges();39 Ts.Complete();40 return Count > 0;41 }42 }43

44 ///

45 ///批量的插入数据46 ///

47 ///

48 ///

49 ///

50 public bool AddRange(List Entity) where T : class

51 {52 using (TransactionScope Ts = newTransactionScope(TransactionScopeOption.Required))53 {54 _DbContextHandle.Set().AddRange(Entity);55 int Count =_DbContextHandle.SaveChanges();56 Ts.Complete();57 return Count > 0;58 }59 }60

61 ///

62 ///根据查询条件进行删除对象63 ///

64 ///

65 /// 查询条件

66 ///

67 public bool Delete(Expression> whereLambda) where T : class

68 {69 using (TransactionScope Ts = newTransactionScope(TransactionScopeOption.Required))70 {71 var EntityModel = _DbContextHandle.Set().Where(whereLambda).FirstOrDefault();72 if (EntityModel != null)73 {74 _DbContextHandle.Set().Remove(EntityModel);75 int Count =_DbContextHandle.SaveChanges();76 Ts.Complete();77 return Count > 0;78 }79 return false;80 }81 }82

83

84 ///

85 ///删除单个对象的实体86 ///

87 ///

88 /// 实体对象

89 ///

90 public bool Delete(T Entity) where T : class

91 {92 using (TransactionScope Ts = newTransactionScope(TransactionScopeOption.Required))93 {94 _DbContextHandle.Set().Attach(Entity);95 _DbContextHandle.Set().Remove(Entity);96 int Count =_DbContextHandle.SaveChanges();97 Ts.Complete();98 return Count > 0;99 }100 }101

102 ///

103 ///批量的进行更新数据104 ///

105 ///

106 ///

107 ///

108 public bool Update(List Entity) where T : class

109 {110 int Count = 0;111 using (TransactionScope Ts = newTransactionScope(TransactionScopeOption.Required))112 {113 if (Entity != null)114 {115 foreach (var items inEntity)116 {117 var EntityModel =_DbContextHandle.Entry(Entity);118 _DbContextHandle.Set().Attach(items);119 EntityModel.State =EntityState.Modified;120 }121

122 }123 Count =_DbContextHandle.SaveChanges();124 Ts.Complete();125 }126

127 return Count > 0;128 }129

130

131 ///

132 ///进行修改单个实体对象133 ///

134 ///

135 /// 实体对象

136 ///

137 public bool Update(T Entity) where T : class

138 {139 using (TransactionScope Ts = newTransactionScope())140 {141 var EntityModel = _DbContextHandle.Entry(Entity);142 _DbContextHandle.Set().Attach(Entity);143 EntityModel.State =EntityState.Modified;144 int Count =_DbContextHandle.SaveChanges();145 Ts.Complete();146 return Count > 0;147 }148 }149

150 ///

151 ///批量的修改152 ///

153 ///

154 ///

155 ///

156 ///

157 public bool Update(Expression> WhereLambda, Expression> UpdateLambda) where T : class

158 {159 _DbContextHandle.Set().Where(WhereLambda).Update(UpdateLambda);160 return _DbContextHandle.SaveChanges() > 0;161 }162

163

164 ///

165 ///查询条件进行修改166 ///

167 ///

168 ///

169 ///

170 ///

171 ///

172 public bool Update(T model, Expression> WhereLambda, params string[] ModifiedProNames) where T : class

173 {174 //查询要修改的数据

175 List ListModifing = _DbContextHandle.Set().Where(WhereLambda).ToList();176 Type t = typeof(T);177 List ProInfos = t.GetProperties(BindingFlags.Instance |BindingFlags.Public).ToList();178 Dictionary DitProList = new Dictionary();179 ProInfos.ForEach(p =>

180 {181 if(ModifiedProNames.Contains(p.Name))182 {183 DitProList.Add(p.Name, p);184 }185 });186

187 if (DitProList.Count <= 0)188 {189 throw new Exception("指定修改的字段名称有误或为空");190 }191 foreach (var item inDitProList)192 {193 PropertyInfo proInfo =item.Value;194 object newValue = proInfo.GetValue(model, null);195 //批量进行修改相互对应的属性

196 foreach (T oModel inListModifing)197 {198 proInfo.SetValue(oModel, newValue, null);//设置其中新的值

199 }200 }201

202 return _DbContextHandle.SaveChanges() > 0;203 }204 ///

205 ///释放缓存206 ///

207 public voidDispose()208 {209 _DbContextHandle.Dispose();210 }211

212 ///

213 ///查询单个对象214 ///

215 ///

216 /// 主键ID

217 ///

218 public T FindByID(dynamic ID) where T : class

219 {220 return _DbContextHandle.Set().Find(ID) ?? null;221 }222

223

224 ///

225 ///获取全部数据的列表226 ///

227 ///

228 /// 排序

229 ///

230 public List GetAll(string Order = null) where T : class

231 {232 return Order != null ? _DbContextHandle.Set().OrderBy(Order).ToList() ?? null : _DbContextHandle.Set().ToList() ?? null;233 }234

235 ///

236 ///根据查询条件进行查询列表237 ///

238 ///

239 ///

240 ///

241 public List GetAllQuery(Expression> WhereLambda = null) where T : class

242 {243 return WhereLambda != null ? _DbContextHandle.Set().Where(WhereLambda).ToList() ?? null : _DbContextHandle.Set().ToList() ?? null;244 }245

246 ///

247 ///判断对象是否存在248 ///

249 ///

250 ///

251 ///

252 public bool GetAny(Expression> WhereLambda = null) where T : class

253 {254 return WhereLambda != null ? _DbContextHandle.Set().Where(WhereLambda).Any() : _DbContextHandle.Set().Any();255 }256

257 ///

258 ///获取查询条件的记录数259 ///

260 ///

261 ///

262 ///

263 public int GetCount(Expression> WhereLambda = null) where T : class

264 {265 return WhereLambda != null ? _DbContextHandle.Set().Where(WhereLambda).Count() : _DbContextHandle.Set().Count();266 }267

268

269 ///

270 ///获取单条的记录271 ///

272 ///

273 ///

274 ///

275 public T GetFristDefault(Expression> WhereLambda = null) where T : class

276 {277 return WhereLambda != null ? _DbContextHandle.Set().Where(WhereLambda).FirstOrDefault() ?? null : _DbContextHandle.Set().FirstOrDefault() ?? null;278 }279

280

281 ///

282 ///查询对象的转化283 ///

284 ///

285 ///

286 ///

287 public List GetSelect(Expression> WhereLambda) where T : class

288 {289 return _DbContextHandle.Set().Where(WhereLambda).ToList() ?? null;290 }291

292 ///

293 ///根据查询条件进行分页294 ///

295 ///

296 /// 当前页

297 /// 每页的大小

298 /// 总记录数

299 /// 排序条件

300 /// 查询条件

301 ///

302 public List Pagination(int PageIndex, int PageSize, out int TotalCount, string Ordering, Expression> WhereLambda = null) where T : class

303 {304 //分页的时候一定要注意 Order 一定在Skip 之前

305 var QueryList = _DbContextHandle.Set().OrderBy(Ordering);306 if (WhereLambda != null)307 {308 QueryList =QueryList.Where(WhereLambda);309 }310

311 TotalCount =QueryList.Count();312 return QueryList.Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList() ?? null;313 }314

315 ///

316 ///根据查询条件进行分页317 ///

318 ///

319 /// 当前页

320 /// 每页的大小

321 /// 总记录数

322 /// 排序条件

323 /// 查询的条件

324 ///

325 ///

326 public List Pagination(int PageIndex, int PageSize, out int TotalCount, Expression> OrderBy, Expression> WhereLambda = null, bool IsOrder = true) where T : class

327 {328 //分页的时候一定要注意 Order一定在Skip 之前

329 IQueryable QueryList = IsOrder == true ? _DbContextHandle.Set().OrderBy(OrderBy) : _DbContextHandle.Set().OrderByDescending(OrderBy);330

331 if (WhereLambda != null)332 {333 QueryList =QueryList.Where(WhereLambda);334 }335

336 TotalCount =QueryList.Count();337 return QueryList.Skip(PageSize * (PageIndex - 1)).Take(PageSize).ToList() ?? null;338 }339

340

341 ///

342 ///执行存储过程的SQL 语句343 ///

344 ///

345 /// 执行的SQL语句

346 /// SQL 语句的参数

347 ///

348 ///

349 public List QueryPro(string Sql, List Parms, CommandType CmdType = CommandType.Text) where T : class

350 {351 //进行执行存储过程

352 if (CmdType ==CommandType.StoredProcedure)353 {354 StringBuilder paraNames = newStringBuilder();355 foreach (var item inParms)356 {357 paraNames.Append($"@{item},");358 }359 Sql = paraNames.Length > 0 ? $"exec {Sql} {paraNames.ToString().Trim(',')}" : $"exec {Sql}";360 }361 return _DbContextHandle.Set().SqlQuery(Sql, Parms.ToArray()).ToList();362 }363

364

365 ///

366 ///进行回滚367 ///

368 ///

369 public void RollBackChanges() where T : class

370 {371 var Query =_DbContextHandle.ChangeTracker.Entries().ToList();372

373 Query.ForEach(p => p.State =EntityState.Unchanged);374 }375

376 }377 }