1.项目中经常要用到 EF，有时候大多数的增删改查都是重复性的东西，本次封装就是为了快速开发，期间没有考虑到架构上的各种思想，就感觉到欠缺点什么东西所以这次将这些拉出来，有存在问题的话还请各位多多指导。例如这样调用：
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_read).Get(p => p.UserId == 1111);//查询一个账户
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_write).AddEntity(“一个实体”);//新增
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_write).DeleteAll(p=>p.Sex=1);//删除所有
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_write).DeleteAll(List<实体> tList);//删除所有
//详细的就不列举了自己看 Bll 基本日常开发方法都包含在其中。
/**分页*****/
DataBLL<Account> accountBLL = new DataBLL<Account>();
return accountBLL.GetPage(out totalCount, query.Expression, pageIndex, pageSize, k => k.OrderByDescending(_ => _.CreateTime)).ToList();
2.封装后从压力和并发上也没有去测试，有兴趣的小伙伴还望给看下。好了不废话了直接上了。
a.先看下大概结构如下 
按照顺序介绍 a.1: 实体就是一般大家手动写的实体
a.2: DALContext.cs 代码如下：
namespace Test.Web.Site.DAL{ public class DALContext<T> : DbContext where T : class { public DALContext(string con) : base(con){} public DALContext(){} public DbSet<T> TV { get; set; } }}a.3 BaseDAL.cs 主要的增删改方法
namespace Test.Web.Site.DAL{ /// <summary> /// DAL父类 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <typeparam name="TContext"></typeparam> public class BaseDAL<TEntity> where TEntity : class,new() { private static readonly object s_lock = new object(); private string constr = "xxxConn";//默认一个字符串 public BaseDAL(string con = "") { if (con != constr && con.Length > 0) { lock (s_lock) { constr = con; } } } public BaseDAL() { } #region Query 方法 /// <summary> /// /// </summary> /// <param name="col">条件</param> /// <returns></returns> public virtual TEntity Get(object col) { using (var db = new DALContext<TEntity>(constr)) { return db.Set<TEntity>().Find(col); } } public virtual int GetCount(Expression<Func<TEntity, bool>> filter) { using (var db = new DALContext<TEntity>(constr)) { IQueryable<TEntity> query = db.Set<TEntity>(); if (filter != null) { query = query.Where(filter); } return query.Count(); //return query.AsNoTracking().FirstOrDefault(); } } /// <summary> /// 根据条件获取实体 /// </summary> /// <param name="predicate"></param> /// <returns></returns> public virtual TEntity Get(Expression<Func<TEntity, bool>> filter, string includeProperties = "") { using (var db = new DALContext<TEntity>(constr)) { IQueryable<TEntity> query = db.Set<TEntity>(); if (filter != null) { query = query.Where(filter); } if (!string.IsNullOrWhiteSpace(includeProperties) || !string.IsNullOrEmpty(includeProperties)) { foreach (var includePropertie in includeProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { query = query.Include(includePropertie); } } return query.AsNoTracking().AsEnumerable().FirstOrDefault(); } } /// <summary> /// 获取相应字段 /// </summary> /// <typeparam name="TEntity"></typeparam> /// <param name="predicate"></param> /// <param name="dynamicPredicate"></param> /// <param name="dynamicFunc"></param> /// <returns></returns> public TEntity GetField(Expression<Func<TEntity, bool>> predicate, Expression<Func<TEntity, dynamic>> dynamicPredicate, Func<dynamic, TEntity> dynamicFunc) { using (var db = new DALContext<TEntity>(constr)) { db.Set<TEntity>().Where(predicate).AsNoTracking().Select(dynamicPredicate).AsEnumerable() .Select(dynamicFunc).FirstOrDefault(); } return null; } /// <summary> /// 获取字段值(获取部分字段) /// </summary> /// <param name="dynamicPredicate"></param> /// <param name="dynamicFunc"></param> /// <param name="fiter"></param> /// <param name="orderby"></param> /// <param name="includeProperties"></param> /// <returns></returns> public virtual IEnumerable<TEntity> GetAll(Expression<Func<TEntity, dynamic>> dynamicPredicate, Func<dynamic, TEntity> dynamicFunc, Expression<Func<TEntity, bool>> filter = null, Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderby = null, string includeProperties = "") { using (var db = new DALContext<TEntity>(constr)) { IQueryable<TEntity> query = db.Set<TEntity>(); if (filter != null) { query = query.Where(filter); } if (!string.IsNullOrWhiteSpace(includeProperties) || !string.IsNullOrEmpty(includeProperties)) { foreach (var includePropertie in includeProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { query = query.Include(includePropertie); } } if (orderby != null) { query = orderby(query); } if (dynamicPredicate != null && dynamicFunc != null) { return query.Select(dynamicPredicate).AsEnumerable().Select(dynamicFunc); } return query.AsNoTracking().AsEnumerable(); } } /// <summary> /// 获取数据 /// </summary> /// <param name="fiter">条件</param> /// <param name="orderby">排序条件</param> /// <param name="includeProperties">需要加载哪一个导航属性</param> /// <returns></returns> public virtual IEnumerable<TEntity> GetAll(Expression<Func<TEntity, bool>> filter = null, Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderby = null, string includeProperties = "", int count = 0) { using (var db = new DALContext<TEntity>(constr)) { IQueryable<TEntity> query = db.Set<TEntity>(); if (filter != null) { query = query.Where(filter); } if (!string.IsNullOrWhiteSpace(includeProperties) || !string.IsNullOrEmpty(includeProperties)) { foreach (var includePropertie in includeProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { query = query.Include(includePropertie); } } if (orderby != null) { query = orderby(query).AsNoTracking(); } if (count != 0) { query = query.Take(count); } return query.AsNoTracking().AsEnumerable().ToList(); } } /// <summary> /// 获取分页 /// </summary> /// <param name="totalcount"></param> /// <param name="fiter"></param> /// <param name="pi"></param> /// <param name="pz"></param> /// <param name="orderby"></param> /// <param name="includeProperties"></param> /// <returns></returns> public virtual IEnumerable<TEntity> GetPage(out int totalcount, Expression<Func<TEntity, bool>> filter = null, int pi = 1, int pz = 0, Func<IQueryable<TEntity>, IOrderedQueryable<TEntity>> orderby = null, string includeProperties = "") { using (var db = new DALContext<TEntity>(constr)) { IQueryable<TEntity> query = db.Set<TEntity>(); if (filter != null) { query = query.Where(filter); } foreach (var includePropertie in includeProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { query = query.Include(includePropertie); } if (orderby != null) { query = orderby(query).AsNoTracking(); } else { query = query.AsNoTracking(); } totalcount = query.Count(); if (pz != 0) { query = query.Skip((pi - 1) * pz).Take(pz); } return query.AsNoTracking().AsEnumerable().ToList(); } } /// <summary> /// 根据sql获取数据 /// </summary> /// <param name="query"></param> /// <param name="parameters"></param> /// <returns></returns> public virtual IEnumerable<TEntity> GetWithDbSetSql(string query, params object[] parameters) { using (var db = new DALContext<TEntity>(constr)) { var dbSet = db.Set<TEntity>(); return dbSet.SqlQuery(query, parameters).AsNoTracking().AsEnumerable<TEntity>(); } } public virtual IEnumerable<object> GetBySql(string query, bool otherdb = true) { using (var db = new DALContext<TEntity>(constr)) { IEnumerable data = null; var Connection = db.Database.Connection; if (otherdb) { data = db.Database.SqlQueryForDynamicOtherDB(query, Connection, null); } else { data = db.Database.SqlQueryForDynamic(query, Connection, null); } var list = new List<object>(); if (data != null) { foreach (var item in data) { list.Add(item); } } return list; } } public virtual IEnumerable GetBySqls(string query) { using (var db = new DALContext<TEntity>(constr)) { var data = db.Database.SqlQuery(typeof(string), query, null); return data; } } /// <summary> /// 根据sql获取数据 /// </summary> /// <param name="query"></param> /// <param name="parameters"></param> /// <returns></returns> public virtual IEnumerable<T> GetWithDbContextSql<T>(string query, params object[] parameters) { using (var db = new DALContext<TEntity>(constr)) { return db.Database.SqlQuery<T>(query, parameters); } } /// <summary> /// 根据sql获取数据 /// </summary> /// <param name="query"></param> /// <param name="parameters"></param> /// <returns></returns> public virtual IEnumerable<T> GetListBySql<T>(string query, params object[] parameters) { using (var db = new DALContext<TEntity>(constr)) { return db.Database.SqlQuery<T>(query, parameters); } } /// <summary> /// 根据sql获取分页数据 /// </summary> /// <param name="query"></param> /// <param name="parameters"></param> /// <returns>yp</returns> public virtual IEnumerable<T> GetListBySql<T>(out int totalCount, string query, int pageIndex, int pageSize) { using (var db = new DbContext(constr)) { totalCount = db.Database.SqlQuery<T>(query).Count(); return db.Database.SqlQuery<T>(query).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList(); } } #endregion #region Modify and Delete /// <summary> /// 插入 /// </summary> /// <param name="entity"></param> public virtual bool Insert(TEntity entity) { using (var db = new DALContext<TEntity>(constr)) { db.Set<TEntity>().Add(entity); return db.SaveChanges() > 0; } } /// <summary> /// 删除 /// </summary> /// <param name="id"></param> public virtual bool Delete(object col) { using (var db = new DALContext<TEntity>(constr)) { TEntity entityToDelete = db.Set<TEntity>().Find(col); if (entityToDelete != null) { return Delete(entityToDelete); } else { return false; } } } /// <summary> /// 删除 /// </summary> /// <param name="predicate"></param> public virtual bool Delete(Expression<Func<TEntity, bool>> predicate) { TEntity entityToDelete = Get(predicate); if (entityToDelete != null) { return Delete(entityToDelete); } else { return false; } } /// <summary> /// 删除 /// </summary> /// <param name="entityToDelete">删除的实体对象</param> public virtual bool Delete(TEntity entityToDelete) { using (var db = new DALContext<TEntity>(constr)) { var dbSet = db.Set<TEntity>(); if (db.Entry(entityToDelete).State == System.Data.EntityState.Detached) { dbSet.Attach(entityToDelete); } dbSet.Remove(entityToDelete); return db.SaveChanges() > 0; } } /// <summary> /// 批量删除。删除失败的以 IEnumerable<T> 形式返回 /// </summary> /// <param name="predicate"></param> /// <returns>IEnumerable<T>：失败数据</returns> public virtual IEnumerable<TEntity> DeleteAll(Expression<Func<TEntity, bool>> predicate) { using (var db = new DALContext<TEntity>(constr)) { var dbSet = db.Set<TEntity>(); List<TEntity> tList = new List<TEntity>(); var collect = dbSet.Where(predicate); try { foreach (var item in collect) { Delete(item); tList.Add(item); } db.SaveChanges(); return null; } catch (Exception) { db.SaveChanges(); return collect.ToList().Except(tList); } } } /// <summary> /// 批量插入 /// </summary> /// <param name="entity"></param> public virtual IEnumerable<TEntity> InsertAll(List<TEntity> list) { using (var db = new DALContext<TEntity>(constr)) { var dbSet = db.Set<TEntity>(); List<TEntity> tList = new List<TEntity>(); foreach (var item in list) { try { db.Set<TEntity>().Add(item); } catch (Exception) { tList.Add(item); throw; } } db.SaveChanges(); return tList; } } /// <summary> /// 批量更新（list 中实体必须为 数据库中查找到的实体） /// </summary> /// <param name="list"></param> /// <returns></returns> public virtual IEnumerable<TEntity> UpdateAll(List<TEntity> list) { using (var db = new DALContext<TEntity>(constr)) { var dbSet = db.Set<TEntity>(); List<TEntity> tList = new List<TEntity>(); foreach (var item in list) { try { var entity = dbSet.Attach(item); db.Entry(item).State = System.Data.EntityState.Modified; } catch (Exception) { tList.Add(item); throw; } } db.SaveChanges(); return tList; } } /// <summary> /// 全部实体更新 /// </summary> /// <param name="entityToUpdate">更新的实体(必须为数据库中查找到的实体)</param> public virtual bool Update(TEntity entityToUpdate) { using (var db = new DALContext<TEntity>(constr)) { var dbSet = db.Set<TEntity>(); var entity = dbSet.Attach(entityToUpdate); db.Entry(entityToUpdate).State = System.Data.EntityState.Modified; return db.SaveChanges() > 0; } } #endregion }}a.4 DatabaseExtensions.cs 一个EF 扩展类 用于 1.连接其他数据库比如 mysql（参考的网络资源）, 2.执行sql语句查询视图 如下：
namespace Test.Web.Site.DAL{ /// <summary> /// ef扩展类 /// </summary> public static class DatabaseExtensions { /// <summary> /// 查询视图 /// </summary> /// <param name="db"></param> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public static IEnumerable SqlQueryForDynamic(this Database db, string sql, params object[] parameters) { // MySql.Data.MySqlClient IDbConnection defaultConn =new System.Data.SqlClient.SqlConnection(); return SqlQueryForDynamicOtherDB(db, sql, defaultConn, parameters); } /// <summary> /// 其他数据库查询视图 /// </summary> /// <param name="db"></param> /// <param name="sql"></param> /// <param name="conn"></param> /// <param name="parameters"></param> /// <returns></returns> public static IEnumerable SqlQueryForDynamicOtherDB(this Database db, string sql, IDbConnection conn, params object[] parameters) { db = new DbContext(db.Connection.ConnectionString).Database; conn.ConnectionString = db.Connection.ConnectionString; if (conn.State != ConnectionState.Open) { conn.Open(); } IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; IDataReader dataReader = cmd.ExecuteReader(); if (!dataReader.Read()) { return null; //无结果返回Null } #region 构建动态字段 TypeBuilder builder = DatabaseExtensions.CreateTypeBuilder( "EF_DynamicModelAssembly", "DynamicModule", "DynamicType"); int fieldCount = dataReader.FieldCount; for (int i = 0; i < fieldCount; i++) { //dic.Add(i, dataReader.GetName(i)); //Type type = dataReader.GetFieldType(i); DatabaseExtensions.CreateAutoImplementedProperty( builder, dataReader.GetName(i), dataReader.GetFieldType(i)); } #endregion dataReader.Close(); dataReader.Dispose(); cmd.Dispose(); conn.Close(); conn.Dispose(); Type returnType = builder.CreateType(); if (parameters != null) { return db.SqlQuery(returnType, sql, parameters); } else { return db.SqlQuery(returnType, sql); } } /// <summary> /// /// </summary> /// <param name="assemblyName"></param> /// <param name="moduleName"></param> /// <param name="typeName"></param> /// <returns></returns> public static TypeBuilder CreateTypeBuilder(string assemblyName, string moduleName, string typeName) { TypeBuilder typeBuilder = AppDomain.CurrentDomain.DefineDynamicAssembly( new AssemblyName(assemblyName), AssemblyBuilderAccess.Run).DefineDynamicModule(moduleName).DefineType(typeName, TypeAttributes.Public); typeBuilder.DefineDefaultConstructor(MethodAttributes.Public); return typeBuilder; } /// <summary> /// 创建属性 /// </summary> /// <param name="builder"></param> /// <param name="propertyName"></param> /// <param name="propertyType"></param> public static void CreateAutoImplementedProperty( TypeBuilder builder, string propertyName, Type propertyType) { const string PrivateFieldPrefix = "m_"; const string GetterPrefix = "get_"; const string SetterPrefix = "set_"; // Generate the field. FieldBuilder fieldBuilder = builder.DefineField( string.Concat( PrivateFieldPrefix, propertyName), propertyType, FieldAttributes.Private); // Generate the property PropertyBuilder propertyBuilder = builder.DefineProperty( propertyName, System.Reflection.PropertyAttributes.HasDefault, propertyType, null); // Property getter and setter attributes. MethodAttributes propertyMethodAttributes = MethodAttributes.Public | MethodAttributes.SpecialName | MethodAttributes.HideBySig; // Define the getter method. MethodBuilder getterMethod = builder.DefineMethod( string.Concat( GetterPrefix, propertyName), propertyMethodAttributes, propertyType, Type.EmptyTypes); // Emit the IL code. // ldarg.0 // ldfld,_field // ret ILGenerator getterILCode = getterMethod.GetILGenerator(); getterILCode.Emit(OpCodes.Ldarg_0); getterILCode.Emit(OpCodes.Ldfld, fieldBuilder); getterILCode.Emit(OpCodes.Ret); // Define the setter method. MethodBuilder setterMethod = builder.DefineMethod( string.Concat(SetterPrefix, propertyName), propertyMethodAttributes, null, new Type[] { propertyType }); // Emit the IL code. // ldarg.0 // ldarg.1 // stfld,_field // ret ILGenerator setterILCode = setterMethod.GetILGenerator(); setterILCode.Emit(OpCodes.Ldarg_0); setterILCode.Emit(OpCodes.Ldarg_1); setterILCode.Emit(OpCodes.Stfld, fieldBuilder); setterILCode.Emit(OpCodes.Ret); propertyBuilder.SetGetMethod(getterMethod); propertyBuilder.SetSetMethod(setterMethod); } }}a.5 : Test.Web.Site.BLL 对Control 开放的一系列方法
namespace Test.Web.Site.BLL{ public class DataBLL<T> where T : class,new() { /// <summary> /// 连接字符串 /// </summary> /// <param name="sd"></param> public DataBLL(string constr = "") { SingletonBase<BaseDAL<T>>.Initialize(new BaseDAL<T>(constr)); } [Obsolete("请采用表达式方式")] public T Get(object col) { return SingletonBase<BaseDAL<T>>.Instance.Get(col); } public int GetCount(Expression<Func<T, bool>> filter) { return SingletonBase<BaseDAL<T>>.Instance.GetCount(filter); } public T Get(Expression<Func<T, bool>> filter, string includeProperties = "") { return SingletonBase<BaseDAL<T>>.Instance.Get(filter, includeProperties); } public IEnumerable<T> GetList(Expression<Func<T, dynamic>> dynamicPredicate, Func<dynamic, T> dynamicFunc, Expression<Func<T, bool>> fiter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderby = null, string includeProperties = "") { return SingletonBase<BaseDAL<T>>.Instance.GetAll(dynamicPredicate, dynamicFunc, fiter, orderby, includeProperties); } /// <summary> /// 获取列表 /// </summary> /// <param name="fiter"></param> /// <param name="orderby"></param> /// <param name="includeProperties"></param> /// <returns></returns> public IEnumerable<T> GetList(Expression<Func<T, bool>> fiter = null, Func<IQueryable<T>, IOrderedQueryable<T>> orderby = null, string includeProperties = "", int count = 0) { return SingletonBase<BaseDAL<T>>.Instance.GetAll(fiter, orderby, includeProperties, count); } /// <summary> /// 获取分页 /// </summary> /// <param name="fiter"></param> /// <param name="pi"></param> /// <param name="pz"></param> /// <param name="orderby"></param> /// <returns></returns> public IEnumerable<T> GetPage(out int totalcount, Expression<Func<T, bool>> fiter = null, int pi = 1, int pz = 0, Func<IQueryable<T>, IOrderedQueryable<T>> orderby = null, string includeProperties = "") { return SingletonBase<BaseDAL<T>>.Instance.GetPage(out totalcount, fiter, pi, pz, orderby, includeProperties); } /// <summary> /// 插入一个实体 /// </summary> /// <param name="t_entity"></param> /// <returns></returns> public bool AddEntity(T t_entity) { return SingletonBase<BaseDAL<T>>.Instance.Insert(t_entity); } /// <summary> /// 跟新实体 /// </summary> /// <param name="t_enttiy"></param> /// <returns></returns> public bool Update(T t_enttiy) { return SingletonBase<BaseDAL<T>>.Instance.Update(t_enttiy); } /// <summary> /// 依据sql查询 list /// </summary> /// <param name="query">sql</param> /// <param name="parameters">参数</param> /// <returns></returns> public IEnumerable<object> GetListBySql(string query,bool otherdb = true) { return SingletonBase<BaseDAL<T>>.Instance.GetBySql(query, otherdb); } /// <summary> /// 依据sql按分页查询 list /// </summary> /// <param name="query">sql</param> /// <param name="parameters">参数</param> /// <returns>新增</returns> public IEnumerable<T> GetDataListBySql(out int totalCount, string query, int pageIndex, int pageSize) { return SingletonBase<BaseDAL<T>>.Instance.GetListBySql<T>(out totalCount, query, pageIndex, pageSize); } /// <summary> /// 删除满足条件的所有数据 /// </summary> /// <param name="predicate">删除条件</param> /// <returns>返回删除失败的集合</returns> public IEnumerable<T> DeleteAll(Expression<Func<T, bool>> predicate) { return SingletonBase<BaseDAL<T>>.Instance.DeleteAll(predicate); } /// <summary> /// 批量更新 list 中实体必须为 数据库中查找到的实体 /// </summary> /// <param name="predicate">删除条件</param> /// <returns>返回删除失败的集合</returns> public IEnumerable<T> UpdateAll(List<T> list) { return SingletonBase<BaseDAL<T>>.Instance.UpdateAll(list); } /// <summary> /// 批量添加 /// </summary> /// <param name="predicate">删除条件</param> /// <returns>返回删除失败的集合</returns> public IEnumerable<T> InsertAll(List<T> list) { return SingletonBase<BaseDAL<T>>.Instance.InsertAll(list); } }}
3.Control 调用只需要类似： ConfigHelper.ConStr 这个作用适合做读写分离 代表着 读或者写的数据库连接字符串标识，最好是在AddEntity 时候去实例化 而不是 在BLL 或者 Control
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_read).Get(p => p.UserId == 1111);//查询一个账户
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_write).AddEntity(“一个实体”);//新增
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_write).DeleteAll(p=>p.Sex=1);//删除所有
Account currAccount = new DataBLL<Account>(ConfigHelper.Constr_write).DeleteAll(List<实体> tList);//删除所有
//详细的就不列举了自己看 Bll 基本日常开发方法都包含在其中。
/**分页*****/
DataBLL<Account> accountBLL = new DataBLL<Account>();
return accountBLL.GetPage(out totalCount, query.Expression, pageIndex, pageSize, k => k.OrderByDescending(_ => _.CreateTime)).ToList();
总结：到此就结束了。有发现问题的小伙伴敬请抛砖。小弟不胜感激。
原标题：MVC,EF 小小封装
关键词：mvc
我知道了 