总结EF通用数据层封装类实例详解

 public class DbBase
    {protected DbContext Db = new DbContextFactory().GetDbContext();        #region 自定义其他方法/// 
/// 执行存储过程或自定义sql语句--返回集合(自定义返回类型)/// 
/// /// /// /// public List Query(string sql, List parms, CommandType cmdType = CommandType.Text)
        {//存储过程（exec getActionUrlId @name,@ID）if (cmdType == CommandType.StoredProcedure)
            {
                StringBuilder paraNames = new StringBuilder();foreach (var sqlPara in parms)
                {
                    paraNames.Append($" @{sqlPara},");
                }
                sql = paraNames.Length > 0 ? $"exec {sql} {paraNames.ToString().Trim(',')}" : $"exec {sql} ";
            }var list = Db.Database.SqlQuery(sql, parms.ToArray());var enityList = list.ToList();return enityList;
        }/// 
/// 自定义语句和存储过程的增删改--返回影响的行数/// 
/// /// /// /// public int Execute(string sql, List parms, CommandType cmdType = CommandType.Text)
        {//存储过程（exec getActionUrlId @name,@ID）if (cmdType == CommandType.StoredProcedure)
            {
                StringBuilder paraNames = new StringBuilder();foreach (var sqlPara in parms)
                {
                    paraNames.Append($" @{sqlPara},");
                }
                sql = paraNames.Length > 0 ?$"exec {sql} {paraNames.ToString().Trim(',')}" :
                    $"exec {sql} ";
            }int ret = Db.Database.ExecuteSqlCommand(sql, parms.ToArray());return ret;
        }#endregion 自定义其他方法}/// 
/// mssql数据库 数据层 父类/// 
/// public class DbBase : DbBase where T : class, new()
    {#region INSERT/// 
/// 新增 实体/// 
/// /// public void Insert(T model)
        {
            Db.Set().Add(model);

        }/// 
/// 普通批量插入/// 
/// public void InsertRange(List datas)
        {
            Db.Set().AddRange(datas);
        }#endregion INSERT#region DELETE/// 
/// 根据模型删除/// 
/// 包含要删除id的对象/// public void Delete(T model)
        {
            Db.Set().Attach(model);
            Db.Set().Remove(model);
        }/// 
/// 删除/// 
/// public void Delete(Expression> whereLambda)
        {
            Db.Set().Where(whereLambda).Delete();
        }#endregion DELETE#region UPDATE/// 
/// 单个对象指定列修改/// 
/// 要修改的实体对象/// 要修改的 属性 名称/// /// public void Update(T model, List proNames, bool isProUpdate = true)
        {//将 对象 添加到 EF中Db.Set().Attach(model);var setEntry = ((IObjectContextAdapter)Db).ObjectContext.ObjectStateManager.GetObjectStateEntry(model);//指定列修改if (isProUpdate)
            {foreach (string proName in proNames)
                {
                    setEntry.SetModifiedProperty(proName);
                }
            }//忽略类修改else{
                Type t = typeof(T);
                List proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();foreach (var item in proInfos)
                {string proName = item.Name;if (proNames.Contains(proName))
                    {continue;
                    }
                    setEntry.SetModifiedProperty(proName);
                }
            }
        }/// 
/// 单个对象修改/// 
/// /// public void Update(T model)
        {
            DbEntityEntry entry = Db.Entry(model);
            Db.Set().Attach(model);
            entry.State = EntityState.Modified;

        }/// 
/// 批量修改/// 
/// /// public void Update(Expression> whereLambda, Expression> updateExpression)
        {
            Db.Set().Where(whereLambda).Update(updateExpression);
        }/// 
/// 批量修改/// 
/// /// public void UpdateAll(List models)
        {foreach (var model in models)
            {
                DbEntityEntry entry = Db.Entry(model);
                entry.State = EntityState.Modified;
            }


        }/// 
/// 批量统一修改/// 
/// 要修改的实体对象/// 查询条件/// /// public void Update(T model, Expression> whereLambda, params string[] modifiedProNames)
        {//查询要修改的数据List listModifing = Db.Set().Where(whereLambda).ToList();
            Type t = typeof(T);
            List proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            Dictionary dictPros = new Dictionary();
            proInfos.ForEach(p =>{if (modifiedProNames.Contains(p.Name))
                {
                    dictPros.Add(p.Name, p);
                }
            });if (dictPros.Count <= 0)
            {throw new Exception("指定修改的字段名称有误或为空");
            }foreach (var item in dictPros)
            {
                PropertyInfo proInfo = item.Value;//取出 要修改的值object newValue = proInfo.GetValue(model, null);//批量设置 要修改 对象的 属性foreach (T oModel in listModifing)
                {//为 要修改的对象 的 要修改的属性 设置新的值proInfo.SetValue(oModel, newValue, null);
                }
            }

        }#endregion UPDATE#region SELECT/// 
/// 根据主键查询/// 
/// /// public T FindById(dynamic id)
        {return Db.Set().Find(id);
        }/// 
/// 获取默认一条数据，没有则为NULL/// 
/// /// public T FirstOrDefault(Expression> whereLambda = null)
        {if (whereLambda == null)
            {return Db.Set().FirstOrDefault();
            }return Db.Set().FirstOrDefault(whereLambda);
        }/// 
/// 获取全部数据/// 
/// public List GetAll(string ordering = null)
        {return ordering == null? Db.Set().ToList()
                : Db.Set().OrderBy(ordering).ToList();
        }/// 
/// 带条件查询获取数据/// 
/// /// /// public List GetAll(Expression> whereLambda, string ordering = null)
        {var iQueryable = Db.Set().Where(whereLambda);return ordering == null? iQueryable.ToList()
                : iQueryable.OrderBy(ordering).ToList();
        }/// 
/// 带条件查询获取数据/// 
/// /// public IQueryable GetAllIQueryable(Expression> whereLambda = null)
        {return whereLambda == null ? Db.Set() : Db.Set().Where(whereLambda);
        }/// 
/// 获取数量/// 
/// /// public int GetCount(Expression> whereLambd = null)
        {return whereLambd == null ? Db.Set().Count() : Db.Set().Where(whereLambd).Count();
        }/// 
/// 判断对象是否存在/// 
/// /// public bool Any(Expression> whereLambd)
        {return Db.Set().Where(whereLambd).Any();
        }/// 
/// 分页查询/// 
/// 当前页码/// 每页大小/// 总条数/// 排序条件（一定要有）/// 查询添加（可有，可无）/// 是否是Order排序/// public List Page(int pageIndex, int pageSize, out int rows, Expression> orderBy, Expression> whereLambda = null, bool isOrder = true)
        {
            IQueryable data = isOrder ?Db.Set().OrderBy(orderBy) :
                Db.Set().OrderByDescending(orderBy);if (whereLambda != null)
            {
                data = data.Where(whereLambda);
            }
            rows = data.Count();return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
        }/// 
/// 分页查询/// 
/// 当前页码/// 每页大小/// 总条数/// 排序条件（一定要有）/// 查询添加（可有，可无）/// public List Page(int pageIndex, int pageSize, out int rows, string ordering, Expression> whereLambda = null)
        {// 分页 一定注意： Skip 之前一定要 OrderByvar data = Db.Set().OrderBy(ordering);if (whereLambda != null)
            {
                data = data.Where(whereLambda);
            }
            rows = data.Count();return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
        }/// 
/// 查询转换/// 
/// /// /// public List Select(Expression> whereLambda)
        {return Db.Set().Where(whereLambda).Select().ToList();
        }#endregion SELECT#region ORTHER/// 
/// 执行存储过程或自定义sql语句--返回集合/// 
/// /// /// /// public List Query(string sql, List parms, CommandType cmdType = CommandType.Text)
        {return Query(sql, parms, cmdType);
        }/// 
/// 提交保存/// 
/// public int SaveChanges()
        {return Db.SaveChanges();
        }/// 
/// 回滚/// 
public void RollBackChanges()
        {var items = Db.ChangeTracker.Entries().ToList();
            items.ForEach(o => o.State = EntityState.Unchanged);
        }#endregion ORTHER}

public class DbBase
    {//是否读写分离(可以配置在配置文件中)private static readonly bool IsReadWriteSeparation = true;#region EF上下文对象(主库)protected DbContext MasterDb => _masterDb.Value;private readonly Lazy _masterDb = new Lazy(() => new DbContextFactory().GetWriteDbContext());#endregion EF上下文对象(主库)#region EF上下文对象(从库)protected DbContext SlaveDb => IsReadWriteSeparation ? _slaveDb.Value : _masterDb.Value;private readonly Lazy _slaveDb = new Lazy(() => new DbContextFactory().GetReadDbContext());#endregion EF上下文对象(从库)#region 自定义其他方法/// 
/// 执行存储过程或自定义sql语句--返回集合(自定义返回类型)/// 
/// /// /// /// public List Query(string sql, List parms, CommandType cmdType = CommandType.Text)
        {//存储过程（exec getActionUrlId @name,@ID）if (cmdType == CommandType.StoredProcedure)
            {
                StringBuilder paraNames = new StringBuilder();foreach (var sqlPara in parms)
                {
                    paraNames.Append($" @{sqlPara},");
                }
                sql = paraNames.Length > 0 ? $"exec {sql} {paraNames.ToString().Trim(',')}" : $"exec {sql} ";
            }var list = SlaveDb.Database.SqlQuery(sql, parms.ToArray());var enityList = list.ToList();return enityList;
        }/// 
/// 自定义语句和存储过程的增删改--返回影响的行数/// 
/// /// /// /// public int Execute(string sql, List parms, CommandType cmdType = CommandType.Text)
        {//存储过程（exec getActionUrlId @name,@ID）if (cmdType == CommandType.StoredProcedure)
            {
                StringBuilder paraNames = new StringBuilder();foreach (var sqlPara in parms)
                {
                    paraNames.Append($" @{sqlPara},");
                }
                sql = paraNames.Length > 0 ?$"exec {sql} {paraNames.ToString().Trim(',')}" :
                    $"exec {sql} ";
            }int ret = MasterDb.Database.ExecuteSqlCommand(sql, parms.ToArray());return ret;
        }#endregion 自定义其他方法}/// 
/// mssql数据库 数据层 父类/// 
/// public class DbBase : DbBase where T : class, new()
    {#region INSERT/// 
/// 新增 实体/// 
/// /// public void Insert(T model)
        {
            MasterDb.Set().Add(model);
        }/// 
/// 普通批量插入/// 
/// public void InsertRange(List datas)
        {
            MasterDb.Set().AddRange(datas);
        }#endregion INSERT#region DELETE/// 
/// 根据模型删除/// 
/// 包含要删除id的对象/// public void Delete(T model)
        {
            MasterDb.Set().Attach(model);
            MasterDb.Set().Remove(model);
        }/// 
/// 删除/// 
/// public void Delete(Expression> whereLambda)
        {
            MasterDb.Set().Where(whereLambda).Delete();
        }#endregion DELETE#region UPDATE/// 
/// 单个对象指定列修改/// 
/// 要修改的实体对象/// 要修改的 属性 名称/// /// public void Update(T model, List proNames, bool isProUpdate = true)
        {//将 对象 添加到 EF中MasterDb.Set().Attach(model);var setEntry = ((IObjectContextAdapter)MasterDb).ObjectContext.ObjectStateManager.GetObjectStateEntry(model);//指定列修改if (isProUpdate)
            {foreach (string proName in proNames)
                {
                    setEntry.SetModifiedProperty(proName);
                }
            }//忽略类修改else{
                Type t = typeof(T);
                List proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();foreach (var item in proInfos)
                {string proName = item.Name;if (proNames.Contains(proName))
                    {continue;
                    }
                    setEntry.SetModifiedProperty(proName);
                }
            }
        }/// 
/// 单个对象修改/// 
/// /// public void Update(T model)
        {
            DbEntityEntry entry = MasterDb.Entry(model);
            MasterDb.Set().Attach(model);
            entry.State = EntityState.Modified;
        }/// 
/// 批量修改/// 
/// /// public void Update(Expression> whereLambda, Expression> updateExpression)
        {
            MasterDb.Set().Where(whereLambda).Update(updateExpression);
        }/// 
/// 批量修改/// 
/// /// public void UpdateAll(List models)
        {foreach (var model in models)
            {
                DbEntityEntry entry = MasterDb.Entry(model);
                entry.State = EntityState.Modified;
            }
        }/// 
/// 批量统一修改/// 
/// 要修改的实体对象/// 查询条件/// /// public void Update(T model, Expression> whereLambda, params string[] modifiedProNames)
        {//查询要修改的数据List listModifing = MasterDb.Set().Where(whereLambda).ToList();
            Type t = typeof(T);
            List proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            Dictionary dictPros = new Dictionary();
            proInfos.ForEach(p =>{if (modifiedProNames.Contains(p.Name))
                {
                    dictPros.Add(p.Name, p);
                }
            });if (dictPros.Count <= 0)
            {throw new Exception("指定修改的字段名称有误或为空");
            }foreach (var item in dictPros)
            {
                PropertyInfo proInfo = item.Value;//取出 要修改的值object newValue = proInfo.GetValue(model, null);//批量设置 要修改 对象的 属性foreach (T oModel in listModifing)
                {//为 要修改的对象 的 要修改的属性 设置新的值proInfo.SetValue(oModel, newValue, null);
                }
            }
        }#endregion UPDATE#region SELECT/// 
/// 根据主键查询/// 
/// /// public T FindById(dynamic id)
        {return SlaveDb.Set().Find(id);
        }/// 
/// 获取默认一条数据，没有则为NULL/// 
/// /// public T FirstOrDefault(Expression> whereLambda = null)
        {if (whereLambda == null)
            {return SlaveDb.Set().FirstOrDefault();
            }return SlaveDb.Set().FirstOrDefault(whereLambda);
        }/// 
/// 获取全部数据/// 
/// public List GetAll(string ordering = null)
        {return ordering == null? SlaveDb.Set().ToList()
                : SlaveDb.Set().OrderBy(ordering).ToList();
        }/// 
/// 带条件查询获取数据/// 
/// /// /// public List GetAll(Expression> whereLambda, string ordering = null)
        {var iQueryable = SlaveDb.Set().Where(whereLambda);return ordering == null? iQueryable.ToList()
                : iQueryable.OrderBy(ordering).ToList();
        }/// 
/// 带条件查询获取数据/// 
/// /// public IQueryable GetAllIQueryable(Expression> whereLambda = null)
        {return whereLambda == null ? SlaveDb.Set() : SlaveDb.Set().Where(whereLambda);
        }/// 
/// 获取数量/// 
/// /// public int GetCount(Expression> whereLambd = null)
        {return whereLambd == null ? SlaveDb.Set().Count() : SlaveDb.Set().Where(whereLambd).Count();
        }/// 
/// 判断对象是否存在/// 
/// /// public bool Any(Expression> whereLambd)
        {return SlaveDb.Set().Where(whereLambd).Any();
        }/// 
/// 分页查询/// 
/// 当前页码/// 每页大小/// 总条数/// 排序条件（一定要有）/// 查询添加（可有，可无）/// 是否是Order排序/// public List Page(int pageIndex, int pageSize, out int rows, Expression> orderBy, Expression> whereLambda = null, bool isOrder = true)
        {
            IQueryable data = isOrder ?SlaveDb.Set().OrderBy(orderBy) :
                SlaveDb.Set().OrderByDescending(orderBy);if (whereLambda != null)
            {
                data = data.Where(whereLambda);
            }
            rows = data.Count();return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
        }/// 
/// 分页查询/// 
/// 当前页码/// 每页大小/// 总条数/// 排序条件（一定要有）/// 查询添加（可有，可无）/// public List Page(int pageIndex, int pageSize, out int rows, string ordering, Expression> whereLambda = null)
        {// 分页 一定注意： Skip 之前一定要 OrderByvar data = SlaveDb.Set().OrderBy(ordering);if (whereLambda != null)
            {
                data = data.Where(whereLambda);
            }
            rows = data.Count();return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
        }/// 
/// 查询转换/// 
/// /// /// public List Select(Expression> whereLambda)
        {return SlaveDb.Set().Where(whereLambda).Select().ToList();
        }#endregion SELECT#region ORTHER/// 
/// 执行存储过程或自定义sql语句--返回集合/// 
/// /// /// /// public List Query(string sql, List parms, CommandType cmdType = CommandType.Text)
        {return Query(sql, parms, cmdType);
        }/// 
/// 提交保存/// 
/// public int SaveChanges()
        {return MasterDb.SaveChanges();
        }/// 
/// 回滚/// 
public void RollBackChanges()
        {var items = MasterDb.ChangeTracker.Entries().ToList();
            items.ForEach(o => o.State = EntityState.Unchanged);
        }#endregion ORTHER}