接口：
using Common; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; namespace Service { public interface IRepository<T> where T : class { #region 执行sql语句返回datatable object SqlQueryForScalar(string sql, DbParameter[] parameters); DataTable SqlQueryForDataTatable(string sql, DbParameter[] parameters); #endregion #region 获取单条记录 /// <summary> /// 通过lambda表达式获取一条记录p=>p.id==id /// </summary> T Get(Expression<Func<T, bool>> predicate); #endregion #region 单模型 CRUD 操作 /// <summary> /// 增加一条记录 /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool Save(T entity, bool IsCommit = true); /// <summary> /// 增加一条记录（异步方式） /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> SaveAsync(T entity, bool IsCommit = true); /// <summary> /// 更新一条记录 /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool Update(T entity, bool IsCommit = true); /// <summary> /// 更新一条记录（异步方式） /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> UpdateAsync(T entity, bool IsCommit = true); /// <summary> /// 增加或更新一条记录 /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsSave">是否增加</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool SaveOrUpdate(T entity, bool IsSave, bool IsCommit = true); /// <summary> /// 增加或更新一条记录（异步方式） /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsSave">是否增加</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> SaveOrUpdateAsync(T entity, bool IsSave, bool IsCommit = true); #endregion #region 多模型操作 /// <summary> /// 增加多条记录，同一模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool SaveList(List<T> T1, bool IsCommit = true); /// <summary> /// 增加多条记录，同一模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> SaveListAsync(List<T> T1, bool IsCommit = true); /// <summary> /// 增加多条记录，独立模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool SaveList<T1>(List<T1> T, bool IsCommit = true) where T1 : class; /// <summary> /// 增加多条记录，独立模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> SaveListAsync<T1>(List<T1> T, bool IsCommit = true) where T1 : class; /// <summary> /// 更新多条记录，同一模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool UpdateList(List<T> T1, bool IsCommit = true); /// <summary> /// 更新多条记录，同一模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> UpdateListAsync(List<T> T1, bool IsCommit = true); /// <summary> /// 更新多条记录，独立模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool UpdateList<T1>(List<T1> T, bool IsCommit = true) where T1 : class; /// <summary> /// 更新多条记录，独立模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> UpdateListAsync<T1>(List<T1> T, bool IsCommit = true) where T1 : class; /// <summary> /// 删除多条记录，同一模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool DeleteList(List<T> T1, bool IsCommit = true); /// <summary> /// 删除多条记录，同一模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> DeleteListAsync(List<T> T1, bool IsCommit = true); /// <summary> /// 删除多条记录，独立模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> bool DeleteList<T1>(List<T1> T, bool IsCommit = true) where T1 : class; /// <summary> /// 删除多条记录，独立模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> Task<bool> DeleteListAsync<T1>(List<T1> T, bool IsCommit = true) where T1 : class; /// <summary> /// 通过Lamda表达式，删除一条或多条记录 /// </summary> /// <param name="predicate"></param> /// <param name="IsCommit"></param> /// <returns></returns> bool Delete(Expression<Func<T, bool>> predicate, bool IsCommit = true); /// <summary> /// 通过Lamda表达式，删除一条或多条记录（异步方式） /// </summary> /// <param name="predicate"></param> /// <param name="IsCommit"></param> /// <returns></returns> Task<bool> DeleteAsync(Expression<Func<T, bool>> predicate, bool IsCommit = true); /// <summary> /// 执行SQL删除 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> int DeleteBySql(string sql, params DbParameter[] para); /// <summary> /// 执行SQL删除（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> Task<int> DeleteBySqlAsync(string sql, params DbParameter[] para); #endregion #region 存储过程操作 /// <summary> /// 执行增删改存储过程 /// </summary> object ExecuteProc(string procname, params DbParameter[] parameter); /// <summary> /// 执行查询的存储过程 /// </summary> object ExecuteQueryProc(string procname, params DbParameter[] parameter); #endregion #region 获取多条数据操作 /// <summary> /// 返回IQueryable集合，延时加载数据 /// </summary> /// <param name="predicate"></param> /// <returns></returns> IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate); /// <summary> /// 返回IQueryable集合，延时加载数据（异步方式） /// </summary> /// <param name="predicate"></param> /// <returns></returns> Task<IQueryable<T>> LoadAllAsync(Expression<Func<T, bool>> predicate); /// <summary> /// 返回List<T>集合,不采用延时加载 /// </summary> /// <param name="predicate"></param> /// <returns></returns> List<T> LoadListAll(Expression<Func<T, bool>> predicate); /// <summary> /// 返回List<T>集合,不采用延时加载（异步方式） /// </summary> /// <param name="predicate"></param> /// <returns></returns> Task<List<T>> LoadListAllAsync(Expression<Func<T, bool>> predicate); /// <summary> /// 获取DbQuery的列表 /// </summary> /// <param name="predicate"></param> /// <returns></returns> DbQuery<T> LoadQueryAll(Expression<Func<T, bool>> predicate); /// <summary> /// 获取DbQuery的列表（异步方式） /// </summary> /// <param name="predicate"></param> /// <returns></returns> Task<DbQuery<T>> LoadQueryAllAsync(Expression<Func<T, bool>> predicate); /// <summary> /// 获取IEnumerable列表 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> IEnumerable<T> LoadEnumerableAll(string sql, params DbParameter[] para); /// <summary> /// 获取IEnumerable列表（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> Task<IEnumerable<T>> LoadEnumerableAllAsync(string sql, params DbParameter[] para); /// <summary> /// 获取数据动态集合 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> IEnumerable LoadEnumerable(string sql, params DbParameter[] para); /// <summary> /// 获取数据动态集合（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> Task<IEnumerable> LoadEnumerableAsync(string sql, params DbParameter[] para); /// <summary> /// 采用SQL进行数据的查询，返回IList集合 /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> List<T> SelectBySql(string sql, params DbParameter[] para); /// <summary> /// 采用SQL进行数据的查询，返回IList集合（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> Task<List<T>> SelectBySqlAsync(string sql, params DbParameter[] para); /// <summary> /// 采用SQL进行数据的查询，指定泛型，返回IList集合 /// </summary> /// <typeparam name="T1"></typeparam> /// <param name="sql"></param> /// <param name="para"></param> /// <returns></returns> List<T1> SelectBySql<T1>(string sql, params DbParameter[] para); /// <summary> /// 采用SQL进行数据的查询，指定泛型，返回IList集合 /// </summary> /// <typeparam name="T1"></typeparam> /// <param name="sql"></param> /// <param name="para"></param> /// <returns></returns> Task<List<T1>> SelectBySqlAsync<T1>(string sql, params DbParameter[] para); /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回实体对象集合 /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <typeparam name="TResult">数据结果，与TEntity一致</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>实体集合</returns> List<TResult> QueryEntity<TEntity, TOrderBy, TResult>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity, TResult>> selector, bool IsAsc) where TEntity : class where TResult : class; /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回实体对象集合（异步方式） /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <typeparam name="TResult">数据结果，与TEntity一致</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>实体集合</returns> Task<List<TResult>> QueryEntityAsync<TEntity, TOrderBy, TResult>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity, TResult>> selector, bool IsAsc) where TEntity : class where TResult : class; /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回Object对象集合 /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>自定义实体集合</returns> List<object> QueryObject<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class; /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回Object对象集合（异步方式） /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>自定义实体集合</returns> Task<List<object>> QueryObjectAsync<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class; /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回动态类对象集合 /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>动态类</returns> dynamic QueryDynamic<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class; /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回动态类对象集合（异步方式） /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>动态类</returns> Task<dynamic> QueryDynamicAsync<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class; #endregion #region 分页查询 Common.PageInfo<object> FindAll<TEntity>(int PageIndex, int PageSize, Expression<Func<TEntity, bool>> condition, String orderByExpression, bool IsDESC) where TEntity : class; /// <summary> /// 通用EF分页，默认显示20条记录 /// </summary> /// <typeparam name="TEntity">实体模型</typeparam> /// <typeparam name="TOrderBy">排序类型</typeparam> /// <param name="index">当前页</param> /// <param name="pageSize">显示条数</param> /// <param name="where">过滤条件</param> /// <param name="orderby">排序字段</param> /// <param name="selector">结果集合</param> /// <param name="isAsc">排序方向true正序 false倒序</param> /// <returns>自定义实体集合</returns> PageInfo<object> Query<TEntity, TOrderBy> (int index, int pageSize, Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class; /// <summary> /// 对IQueryable对象进行分页逻辑处理，过滤、查询项、排序对IQueryable操作 /// </summary> /// <param name="t">Iqueryable</param> /// <param name="index">当前页</param> /// <param name="PageSize">每页显示多少条</param> /// <returns>当前IQueryable to List的对象</returns> Common.PageInfo<T> Query(IQueryable<T> query, int index, int PageSize); /// <summary> /// 普通SQL查询分页方法 /// </summary> /// <param name="index">当前页</param> /// <param name="pageSize">显示行数</param> /// <param name="tableName">表名/视图</param> /// <param name="field">获取项</param> /// <param name="filter">过滤条件</param> /// <param name="orderby">排序字段+排序方向</param> /// <param name="group">分组字段</param> /// <returns>结果集</returns> Common.PageInfo Query(int index, int pageSize, string tableName, string field, string filter, string orderby, string group, params DbParameter[] para); /// <summary> /// 简单的Sql查询分页 /// </summary> /// <param name="index"></param> /// <param name="pageSize"></param> /// <param name="sql"></param> /// <returns></returns> Common.PageInfo Query(int index, int pageSize, string sql, string orderby, params DbParameter[] para); /// <summary> /// 多表联合分页算法 /// </summary> PageInfo Query(IQueryable query, int index, int pagesize); #endregion #region ADO.NET增删改查方法 /// <summary> /// 执行增删改方法,含事务处理 /// </summary> object ExecuteSqlCommand(string sql, params DbParameter[] para); /// <summary> /// 执行多条SQL，增删改方法,含事务处理 /// </summary> object ExecuteSqlCommand(Dictionary<string, object> sqllist); /// <summary> /// 执行查询方法,返回动态类，接收使用var，遍历时使用dynamic类型 /// </summary> object ExecuteSqlQuery(string sql, params DbParameter[] para); #endregion #region 更新操作 /// <summary> /// 更新字段 /// </summary> /// <param name="table">表名</param> /// <param name="dic">被解析的字段</param> /// <param name="where">条件</param> /// <returns></returns> //bool Modify(string table, Dictionary<string, object> dic, string where); #endregion #region 验证是否存在 /// <summary> /// 验证当前条件是否存在相同项 /// </summary> bool IsExist(Expression<Func<T, bool>> predicate); /// <summary> /// 验证当前条件是否存在相同项（异步方式） /// </summary> Task<bool> IsExistAsync(Expression<Func<T, bool>> predicate); /// <summary> /// 根据SQL验证实体对象是否存在 /// </summary> bool IsExist(string sql, params DbParameter[] para); /// <summary> /// 根据SQL验证实体对象是否存在（异步方式） /// </summary> Task<bool> IsExistAsync(string sql, params DbParameter[] para); #endregion } }
实现：
using Domain; using System; using System.Collections; using System.Collections.Generic; using System.Data; using System.Data.Common; using System.Data.Entity; using System.Data.Entity.Core.Objects; using System.Data.Entity.Infrastructure; using System.Data.SqlClient; using System.Linq; using System.Linq.Expressions; using System.Text; using System.Threading.Tasks; namespace Service { public abstract class RepositoryBase<T> : IRepository<T> where T : class { #region 执行sql语句返回datatable/object /// <summary> /// 执行查询并返回首行首列的值 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public object SqlQueryForScalar(string sql, DbParameter[] parameters) { string connStr = this.Context.Database.Connection.ConnectionString; using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) { if (conn.State != ConnectionState.Open) { conn.Open(); } SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (parameters != null && parameters.Length > 0) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } object obj = cmd.ExecuteScalar(); conn.Close();//连接需要关闭 conn.Dispose(); return obj; } } /// <summary> /// 执行查询并返回DataTable /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public DataTable SqlQueryForDataTatable(string sql, DbParameter[] parameters) { string connStr = this.Context.Database.Connection.ConnectionString; using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) { if (conn.State != ConnectionState.Open) { conn.Open(); } SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (parameters != null && parameters.Length > 0) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); return table; } } #endregion #region 获取单条记录 /// <summary> /// 通过lambda表达式获取一条记录p=>p.id==id /// </summary> public virtual T Get(Expression<Func<T, bool>> predicate) { try { return dbSet.AsNoTracking().SingleOrDefault(predicate); } catch (Exception e) { throw e; } } #endregion #region 单模型 CRUD 操作 /// <summary> /// 增加一条记录 /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool Save(T entity, bool IsCommit = true) { try { int row = 0; var entry = this.Context.Entry<T>(entity); entry.State = System.Data.Entity.EntityState.Added; if (IsCommit) { row = Context.SaveChanges(); entry.State = System.Data.Entity.EntityState.Detached; } return row > 0; } catch (Exception e) { throw e; } } /// <summary> /// 增加一条记录（异步方式） /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> SaveAsync(T entity, bool IsCommit = true) { try { int row = 0; var entry = this.Context.Entry<T>(entity); entry.State = System.Data.Entity.EntityState.Added; if (IsCommit) { row = Context.SaveChanges(); entry.State = System.Data.Entity.EntityState.Detached; } return Task.FromResult<bool>(row > 0); } catch (Exception e) { throw e; } } /// <summary> /// 更新一条记录 /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool Update(T entity, bool IsCommit = true) { try { int rows = 0; var entry = this.Context.Entry(entity); entry.State = System.Data.Entity.EntityState.Modified; if (IsCommit) { rows = this.Context.SaveChanges(); entry.State = System.Data.Entity.EntityState.Detached; } return rows > 0; } catch (Exception e) { throw e; } } /// <summary> /// 更新一条记录（异步方式） /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> UpdateAsync(T entity, bool IsCommit = true) { try { int rows = 0; var entry = this.Context.Entry(entity); entry.State = System.Data.Entity.EntityState.Modified; if (IsCommit) { rows = this.Context.SaveChanges(); entry.State = System.Data.Entity.EntityState.Detached; } return Task.FromResult<bool>(rows > 0); } catch (Exception e) { throw e; } } /// <summary> /// 增加或更新一条记录 /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsSave">是否增加</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool SaveOrUpdate(T entity, bool IsSave, bool IsCommit = true) { try { return IsSave ? Save(entity, IsCommit) : Update(entity, IsCommit); } catch (Exception e) { throw e; } } /// <summary> /// 增加或更新一条记录（异步方式） /// </summary> /// <param name="entity">实体模型</param> /// <param name="IsSave">是否增加</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> SaveOrUpdateAsync(T entity, bool IsSave, bool IsCommit = true) { try { return IsSave ? SaveAsync(entity, IsCommit) : UpdateAsync(entity, IsCommit); } catch (Exception e) { throw e; } } /// <summary> /// 更新字段 /// </summary> /// <param name="table">表名</param> /// <param name="dic">被解析的字段</param> /// <param name="where">条件</param> /// <returns></returns> //public virtual bool Modify(string table, Dictionary<string, object> dic, string where) //{ // try // { // string sql = "update "+table+" "; // return this.Context.Database.ExecuteSqlCommand(sql, para); // } // catch (Exception e) // { // throw e; // } //} #endregion #region 多模型操作 /// <summary> /// 增加多模型数据，指定独立模型集合 /// </summary> public virtual int SaveList<T1>(List<T1> t) where T1 : class { try { if (t == null || t.Count == 0) return 0; this.Context.Set<T1>().Local.Clear(); foreach (var item in t) { this.Context.Set<T1>().Add(item); } return this.Context.SaveChanges(); } catch (Exception e) { throw e; } } /// <summary> /// 增加多模型数据，与当前模型一致 /// </summary> public virtual int SaveList(List<T> t) { try { this.dbSet.Local.Clear(); foreach (var item in t) { this.dbSet.Add(item); } return this.Context.SaveChanges(); } catch (Exception e) { throw e; } } /// <summary> /// 增加多条记录，同一模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool SaveList(List<T> T1, bool IsCommit = true) { try { if (T1 == null || T1.Count == 0) return false; this.Context.Set<T>().Local.Clear(); foreach (var item in T1) { this.Context.Set<T>().Add(item); } if (IsCommit) { return this.Context.SaveChanges() > 0; } return false; } catch (Exception e) { throw e; } } /// <summary> /// 增加多条记录，同一模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> SaveListAsync(List<T> T1, bool IsCommit = true) { try { if (T1 == null || T1.Count == 0) return Task.FromResult<bool>(false); this.Context.Set<T>().Local.Clear(); foreach (var item in T1) { this.Context.Set<T>().Add(item); } if (IsCommit) { return Task.FromResult<bool>(this.Context.SaveChanges() > 0); } return Task.FromResult<bool>(false); } catch (Exception e) { throw e; } } /// <summary> /// 增加多条记录，独立模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool SaveList<T1>(List<T1> T, bool IsCommit = true) where T1 : class { try { if (T == null || T.Count == 0) return false; this.Context.Set<T1>().Local.Clear(); foreach (var item in T) { this.Context.Set<T1>().Add(item); } if (IsCommit) { return this.Context.SaveChanges() > 0; } return false; } catch (Exception e) { throw e; } } /// <summary> /// 增加多条记录，独立模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> SaveListAsync<T1>(List<T1> T, bool IsCommit = true) where T1 : class { try { if (T == null || T.Count == 0) return Task.FromResult<bool>(false); this.Context.Set<T1>().Local.Clear(); foreach (var item in T) { this.Context.Set<T1>().Add(item); } if (IsCommit) { return Task.FromResult<bool>(this.Context.SaveChanges() > 0); } return Task.FromResult<bool>(false); } catch (Exception e) { throw e; } } /// <summary> /// 更新多模型，指定独立模型集合 /// </summary> public virtual int UpdateList<T1>(List<T1> t) where T1 : class { if (t.Count <= 0) return 0; try { foreach (var item in t) { this.Context.Entry<T1>(item).State = System.Data.Entity.EntityState.Modified; } return this.Context.SaveChanges(); } catch (Exception e) { throw e; } } /// <summary> /// 更新多模型，与当前模型一致 /// </summary> public virtual int UpdateList(List<T> t) { if (t.Count <= 0) return 0; try { foreach (var item in t) { this.Context.Entry(item).State = System.Data.Entity.EntityState.Modified; } return this.Context.SaveChanges(); } catch (Exception e) { throw e; } } /// <summary> /// 更新多条记录，同一模型 /// </summary> /// <param name="t">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool UpdateList(List<T> t, bool IsCommit = true) { if (t.Count <= 0) return false; try { foreach (var item in t) { this.Context.Entry<T>(item).State = System.Data.Entity.EntityState.Modified; } if (IsCommit) { return this.Context.SaveChanges() > 0; } return false; } catch (Exception e) { throw e; } } /// <summary> /// 更新多条记录，同一模型（异步方式） /// </summary> /// <param name="t">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> UpdateListAsync(List<T> t, bool IsCommit = true) { if (t.Count <= 0) return Task.FromResult<bool>(false); try { foreach (var item in t) { this.Context.Entry<T>(item).State = System.Data.Entity.EntityState.Modified; } if (IsCommit) { return Task.FromResult<bool>(this.Context.SaveChanges() > 0); } return Task.FromResult<bool>(false); } catch (Exception e) { throw e; } } /// <summary> /// 更新多条记录，独立模型 /// </summary> /// <param name="t">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool UpdateList<T1>(List<T1> t, bool IsCommit = true) where T1 : class { if (t.Count <= 0) return false; try { foreach (var item in t) { this.Context.Entry<T1>(item).State = System.Data.Entity.EntityState.Modified; } if (IsCommit) { return this.Context.SaveChanges() > 0; } return false; } catch (Exception e) { throw e; } } /// <summary> /// 更新多条记录，独立模型（异步方式） /// </summary> /// <param name="t">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> UpdateListAsync<T1>(List<T1> t, bool IsCommit = true) where T1 : class { if (t.Count <= 0) return Task.FromResult<bool>(false); try { foreach (var item in t) { this.Context.Entry<T1>(item).State = System.Data.Entity.EntityState.Modified; } if (IsCommit) { return Task.FromResult<bool>(this.Context.SaveChanges() > 0); } return Task.FromResult<bool>(false); } catch (Exception e) { throw e; } } /// <summary> /// 批量删除数据，当前模型 /// </summary> public virtual int DeleteList(List<T> t) { if (t == null || t.Count == 0) return 0; foreach (var item in t) { this.dbSet.Remove(item); } return this.Context.SaveChanges(); } /// <summary> /// 批量删除数据，自定义模型 /// </summary> public virtual int DeleteList<T1>(List<T1> t) where T1 : class { try { if (t == null || t.Count == 0) return 0; foreach (var item in t) { this.Context.Set<T1>().Remove(item); } return this.Context.SaveChanges(); } catch (Exception e) { throw e; } } /// <summary> /// 删除多条记录，同一模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool DeleteList(List<T> T1, bool IsCommit = true) { if (T1 == null || T1.Count == 0) return false; foreach (var item in T1) { this.dbSet.Remove(item); } if (IsCommit) { return this.Context.SaveChanges() > 0; } return false; } /// <summary> /// 删除多条记录，同一模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> DeleteListAsync(List<T> T1, bool IsCommit = true) { if (T1 == null || T1.Count == 0) return Task.FromResult<bool>(false); foreach (var item in T1) { this.dbSet.Remove(item); } if (IsCommit) { return Task.FromResult<bool>(this.Context.SaveChanges() > 0); } return Task.FromResult<bool>(false); } /// <summary> /// 删除多条记录，独立模型 /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual bool DeleteList<T1>(List<T1> t, bool IsCommit = true) where T1 : class { if (t == null || t.Count == 0) return false; foreach (var item in t) { this.Context.Set<T1>().Remove(item); } if (IsCommit) { return this.Context.SaveChanges() > 0; } return false; } /// <summary> /// 删除多条记录，独立模型（异步方式） /// </summary> /// <param name="T1">实体模型集合</param> /// <param name="IsCommit">是否提交（默认提交）</param> /// <returns></returns> public virtual Task<bool> DeleteListAsync<T1>(List<T1> T, bool IsCommit = true) where T1 : class { if (T == null || T.Count == 0) return Task.FromResult<bool>(false); foreach (var item in T) { this.Context.Set<T1>().Remove(item); } if (IsCommit) { return Task.FromResult<bool>(this.Context.SaveChanges() > 0); } return Task.FromResult<bool>(false); } /// <summary> /// 通过Lamda表达式，删除一条或多条记录 /// </summary> /// <param name="predicate"></param> /// <param name="IsCommit"></param> /// <returns></returns> public virtual bool Delete(Expression<Func<T, bool>> predicate, bool IsCommit = true) { var data = this.dbSet.Where(predicate); foreach (var item in data) { this.dbSet.Remove(item); } if (IsCommit) { return this.Context.SaveChanges() > 0; } return false; } /// <summary> /// 通过Lamda表达式，删除一条或多条记录（异步方式） /// </summary> /// <param name="predicate"></param> /// <param name="IsCommit"></param> /// <returns></returns> public virtual Task<bool> DeleteAsync(Expression<Func<T, bool>> predicate, bool IsCommit = true) { var data = this.dbSet.Where(predicate); foreach (var item in data) { this.dbSet.Remove(item); } if (IsCommit) { return Task.FromResult<bool>(this.Context.SaveChanges() > 0); } return Task.FromResult<bool>(false); } #endregion #region 固定公用帮助，含事务 private DbContext context = new MyConfig().db; /// <summary> /// 数据上下文--->根据Domain实体模型名称进行更改 /// </summary> public DbContext Context { get { context.Configuration.ValidateOnSaveEnabled = false; return context; } } /// <summary> /// 数据上下文--->拓展属性 /// </summary> public MyConfig Config { get { return new MyConfig(); } } /// <summary> /// 公用泛型处理属性 /// 注:所有泛型操作的基础 /// </summary> public DbSet<T> dbSet { get { return this.Context.Set<T>(); } } /// <summary> /// 事务 /// </summary> private DbContextTransaction _transaction = null; /// <summary> /// 开始事务 /// </summary> public DbContextTransaction Transaction { get { if (this._transaction == null) { this._transaction = this.Context.Database.BeginTransaction(); } return this._transaction; } set { this._transaction = value; } } /// <summary> /// 事务状态 /// </summary> public bool Committed { get; set; } /// <summary> /// 异步锁定 /// </summary> private readonly object sync = new object(); /// <summary> /// 提交事务 /// </summary> public void Commit() { if (!Committed) { lock (sync) { if (this._transaction != null) _transaction.Commit(); } Committed = true; } } /// <summary> /// 回滚事务 /// </summary> public void Rollback() { Committed = false; if (this._transaction != null) this._transaction.Rollback(); } #endregion #region 增删改操作 /// <summary> /// 添加一条模型记录，自动提交更改 /// </summary> public virtual bool Save(T entity) { try { int row = 0; var entry = this.Context.Entry<T>(entity); entry.State = System.Data.Entity.EntityState.Added; row = Context.SaveChanges(); entry.State = System.Data.Entity.EntityState.Detached; return row > 0; } catch (Exception e) { throw e; } } /// <summary> /// 更新一条模型记录，自动提交更改 /// </summary> public virtual bool Update(T entity) { try { int rows = 0; var entry = this.Context.Entry(entity); entry.State = System.Data.Entity.EntityState.Modified; rows = this.Context.SaveChanges(); entry.State = System.Data.Entity.EntityState.Detached; return rows > 0; } catch (Exception e) { throw e; } } /// <summary> /// 更新模型记录，如不存在进行添加操作 /// </summary> public virtual bool SaveOrUpdate(T entity, bool isEdit) { try { return isEdit ? Update(entity) : Save(entity); } catch (Exception e) { throw e; } } /// <summary> /// 删除一条或多条模型记录，含事务 /// </summary> public virtual int Delete(Expression<Func<T, bool>> predicate = null) { try { int rows = 0; IQueryable<T> entry = (predicate == null) ? this.dbSet.AsQueryable() : this.dbSet.Where(predicate); List<T> list = entry.ToList(); if (list.Count > 0) { for (int i = 0; i < list.Count; i++) { this.dbSet.Remove(list[i]); } rows = this.Context.SaveChanges(); } return rows; } catch (Exception e) { throw e; } } /// <summary> /// 使用原始SQL语句,含事务处理 /// </summary> public virtual int DeleteBySql(string sql, params DbParameter[] para) { try { return this.Context.Database.ExecuteSqlCommand(sql, para); } catch (Exception e) { throw e; } } /// <summary> /// 执行SQL删除（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> public virtual Task<int> DeleteBySqlAsync(string sql, params DbParameter[] para) { return Task.FromResult<int>(DeleteBySql(sql, para)); } #endregion #region 存储过程操作 /// <summary> /// 执行返回影响行数的存储过程 /// </summary> /// <param name="procname">过程名称</param> /// <param name="parameter">参数对象</param> /// <returns></returns> public virtual object ExecuteProc(string procname, params DbParameter[] parameter) { try { return ExecuteSqlCommand(procname, parameter); } catch (Exception e) { throw e; } } /// <summary> /// 执行返回结果集的存储过程 /// </summary> /// <param name="procname">过程名称</param> /// <param name="parameter">参数对象</param> /// <returns></returns> public virtual object ExecuteQueryProc(string procname, params DbParameter[] parameter) { try { return Context.Database.SqlFunctionForDynamic(procname, parameter); } catch (Exception e) { throw e; } } #endregion #region 存在验证操作 /// <summary> /// 验证当前条件是否存在相同项 /// </summary> public virtual bool IsExist(Expression<Func<T, bool>> predicate) { var entry = this.dbSet.Where(predicate); return (entry.Any()); } /// <summary> /// 根据SQL验证实体对象是否存在 /// </summary> public virtual bool IsExist(string sql, params DbParameter[] para) { IEnumerable result = this.Context.Database.SqlQuery(typeof(int), sql, para); if (result.GetEnumerator().Current == null || result.GetEnumerator().Current.ToString() == "0") return false; return true; } /// <summary> /// 验证当前条件是否存在相同项（异步方式） /// </summary> public virtual Task<bool> IsExistAsync(Expression<Func<T, bool>> predicate) { return Task.FromResult<bool>(IsExist(predicate)); } /// <summary> /// 根据SQL验证实体对象是否存在（异步方式） /// </summary> public virtual Task<bool> IsExistAsync(string sql, params DbParameter[] para) { return Task.FromResult<bool>(IsExist(sql, para)); } #endregion #region 获取多条数据操作 /// <summary> /// 返回IQueryable集合，延时加载数据 /// </summary> public virtual IQueryable<T> LoadTop(Expression<Func<T, bool>> predicate, int TopNum) { try { if (predicate != null) { return this.dbSet.Where(predicate).Take(TopNum).AsNoTracking<T>(); } return this.dbSet.AsQueryable<T>().AsNoTracking<T>(); } catch (Exception e) { throw e; } } /// <summary> /// 返回IQueryable集合，延时加载数据 /// </summary> public virtual IQueryable<T> LoadAll(Expression<Func<T, bool>> predicate) { try { if (predicate != null) { return this.dbSet.Where(predicate).AsNoTracking<T>(); } return this.dbSet.AsQueryable<T>().AsNoTracking<T>(); } catch (Exception e) { throw e; } } /// <summary> /// 返回IQueryable集合，延时加载数据（异步方式） /// </summary> /// <param name="predicate"></param> /// <returns></returns> public virtual Task<IQueryable<T>> LoadAllAsync(Expression<Func<T, bool>> predicate) { try { return Task.FromResult<IQueryable<T>>(LoadAll(predicate)); } catch (Exception e) { throw e; } } /// <summary> /// 返回DbQuery集合，延时加载数据 /// </summary> public virtual DbQuery<T> LoadQueryAll(Expression<Func<T, bool>> predicate) { try { if (predicate != null) { return this.dbSet.Where(predicate) as DbQuery<T>; } return this.dbSet; } catch (Exception e) { throw e; } } /// <summary> /// 获取DbQuery的列表（异步方式） /// </summary> /// <param name="predicate"></param> /// <returns></returns> public virtual Task<DbQuery<T>> LoadQueryAllAsync(Expression<Func<T, bool>> predicate) { try { return Task.FromResult<DbQuery<T>>(LoadQueryAll(predicate)); } catch (Exception e) { throw e; } } /// <summary> /// 返回List集合,不采用延时加载 /// </summary> public virtual List<T> LoadListAll(Expression<Func<T, bool>> predicate) { try { if (predicate != null) { return this.dbSet.Where(predicate).AsNoTracking().ToList(); } return this.dbSet.AsQueryable<T>().AsNoTracking().ToList(); } catch (Exception e) { throw e; } } /// <summary> /// 返回List<T>集合,不采用延时加载（异步方式） /// </summary> /// <param name="predicate"></param> /// <returns></returns> public virtual Task<List<T>> LoadListAllAsync(Expression<Func<T, bool>> predicate) { try { return Task.FromResult<List<T>>(LoadListAll(predicate)); } catch (Exception e) { throw e; } } /// <summary> /// 返回IEnumerable集合，采用原始T-Sql方式 /// </summary> public virtual IEnumerable<T> LoadEnumerableAll(string sql, params DbParameter[] para) { try { return this.Context.Database.SqlQuery<T>(sql, para); } catch (Exception e) { throw e; } } /// <summary> /// 获取IEnumerable列表（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> public virtual Task<IEnumerable<T>> LoadEnumerableAllAsync(string sql, params DbParameter[] para) { try { return Task.FromResult<IEnumerable<T>>(LoadEnumerableAll(sql, para)); } catch (Exception e) { throw e; } } /// <summary> /// 返回IEnumerable动态集合，采用原始T-Sql方式 /// </summary> public virtual System.Collections.IEnumerable LoadEnumerable(string sql, params DbParameter[] para) { try { return this.Context.Database.SqlQueryForDynamic(sql, para); } catch (Exception e) { throw e; } } /// <summary> /// 获取数据动态集合（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> public virtual Task<IEnumerable> LoadEnumerableAsync(string sql, params DbParameter[] para) { try { return Task.FromResult<IEnumerable>(LoadEnumerable(sql, para)); } catch (Exception e) { throw e; } } /// <summary> /// 返回IList集合，采用原始T-Sql方式 /// </summary> public virtual List<T> SelectBySql(string sql, params DbParameter[] para) { try { return this.Context.Database.SqlQuery(typeof(T), sql, para).Cast<T>().ToList(); } catch (Exception e) { throw e; } } /// <summary> /// 采用SQL进行数据的查询，返回IList集合（异步方式） /// </summary> /// <param name="sql">SQL语句</param> /// <param name="para">Parameters参数</param> /// <returns></returns> public virtual Task<List<T>> SelectBySqlAsync(string sql, params DbParameter[] para) { try { return Task.FromResult<List<T>>(SelectBySql<T>(sql, para)); } catch (Exception e) { throw e; } } /// <summary> /// 采用SQL进行数据的查询，指定泛型，返回IList集合 /// </summary> /// <typeparam name="T1"></typeparam> /// <param name="sql"></param> /// <param name="para"></param> /// <returns></returns> public virtual Task<List<T1>> SelectBySqlAsync<T1>(string sql, params DbParameter[] para) { try { return Task.FromResult<List<T1>>(SelectBySql<T1>(sql, para)); } catch (Exception e) { throw e; } } /// <summary> /// 指定泛型，返回IList集合，采用原始T-Sql方式 /// </summary> public virtual List<T1> SelectBySql<T1>(string sql, params DbParameter[] para) { try { return this.Context.Database.SqlQuery<T1>(sql, para).ToList(); } catch (Exception e) { throw e; } } /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回实体对象 /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <typeparam name="TResult">数据结果，与TEntity一致</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>实体集合</returns> public virtual List<TResult> QueryEntity<TEntity, TOrderBy, TResult> (Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity, TResult>> selector, bool IsAsc) where TEntity : class where TResult : class { IQueryable<TEntity> query = this.Context.Set<TEntity>(); if (where != null) { query = query.Where(where); } if (orderby != null) { query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby); } if (selector == null) { return query.Cast<TResult>().AsNoTracking().ToList(); } return query.Select(selector).AsNoTracking().ToList(); } /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回实体对象集合（异步方式） /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <typeparam name="TResult">数据结果，与TEntity一致</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>实体集合</returns> public virtual Task<List<TResult>> QueryEntityAsync<TEntity, TOrderBy, TResult>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Expression<Func<TEntity, TResult>> selector, bool IsAsc) where TEntity : class where TResult : class { return Task.FromResult<List<TResult>>(QueryEntity(where, orderby, selector, IsAsc)); } /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回Object对象 /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>自定义实体集合</returns> public virtual List<object> QueryObject<TEntity, TOrderBy> (Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class { IQueryable<TEntity> query = this.Context.Set<TEntity>(); if (where != null) { query = query.Where(where); } if (orderby != null) { query = IsAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby); } if (selector == null) { return query.AsNoTracking().ToList<object>(); } return selector(query); } /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回Object对象集合（异步方式） /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>自定义实体集合</returns> public virtual Task<List<object>> QueryObjectAsync<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class { return Task.FromResult<List<object>>(QueryObject<TEntity, TOrderBy>(where, orderby, selector, IsAsc)); } /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回动态类对象 /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>动态类</returns> public virtual dynamic QueryDynamic<TEntity, TOrderBy> (Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class { List<object> list = QueryObject<TEntity, TOrderBy> (where, orderby, selector, IsAsc); return Common.JsonConverter.JsonClass(list); } /// <summary> /// 可指定返回结果、排序、查询条件的通用查询方法，返回动态类对象集合（异步方式） /// </summary> /// <typeparam name="TEntity">实体对象</typeparam> /// <typeparam name="TOrderBy">排序字段类型</typeparam> /// <param name="where">过滤条件，需要用到类型转换的需要提前处理与数据表一致的</param> /// <param name="orderby">排序字段</param> /// <param name="selector">返回结果（必须是模型中存在的字段）</param> /// <param name="IsAsc">排序方向，true为正序false为倒序</param> /// <returns>动态类</returns> public virtual Task<dynamic> QueryDynamicAsync<TEntity, TOrderBy>(Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool IsAsc) where TEntity : class { return Task.FromResult<dynamic>(QueryDynamic<TEntity, TOrderBy>(where, orderby, selector, IsAsc)); } #endregion #region 分页操作 /// <summary> /// 待自定义分页函数，使用必须重写，指定数据模型 /// </summary> public virtual IList<T1> PageByListSql<T1>(string sql, IList<DbParameter> parameters, Common.PageCollection page) { return null; } /// <summary> /// 待自定义分页函数，使用必须重写， /// </summary> public virtual IList<T> PageByListSql(string sql, IList<DbParameter> parameters, Common.PageCollection page) { return null; } /// <summary> /// 对IQueryable对象进行分页逻辑处理，过滤、查询项、排序对IQueryable操作 /// </summary> /// <param name="t">Iqueryable</param> /// <param name="index">当前页</param> /// <param name="PageSize">每页显示多少条</param> /// <returns>当前IQueryable to List的对象</returns> public virtual Common.PageInfo<T> Query(IQueryable<T> query, int index, int PageSize) { if (index < 1) { index = 1; } if (PageSize <= 0) { PageSize = 20; } int count = query.Count(); int maxpage = count / PageSize; if (count % PageSize > 0) { maxpage++; } if (index > maxpage) { index = maxpage; } if (count > 0) query = query.Skip((index - 1) * PageSize).Take(PageSize); return new Common.PageInfo<T>(index, PageSize, count, query.ToList()); } public virtual Common.PageInfo<object> FindAll<TEntity>(int PageIndex, int PageSize, Expression<Func<TEntity, bool>> condition, String orderByExpression, bool IsDESC) where TEntity : class { var property = typeof(TEntity).GetProperty(orderByExpression); var parameter = Expression.Parameter(typeof(TEntity), "p"); var propertyAccess = Expression.MakeMemberAccess(parameter, property); var orderByExp = Expression.Lambda(propertyAccess, parameter); IQueryable<TEntity> query = this.Context.Set<TEntity>(); if (condition != null) { query = query.Where(condition); } int count = query.Count(); string methodName = IsDESC ? "OrderByDescending" : "OrderBy"; MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName, new Type[] { typeof(TEntity), property.PropertyType }, query.Expression, Expression.Quote(orderByExp)); query = query.Provider.CreateQuery<TEntity>(resultExp); return new Common.PageInfo<object>(PageIndex, PageSize, count, query.ToList<object>()); } /// <summary> /// 通用EF分页，默认显示20条记录 /// </summary> /// <typeparam name="TEntity">实体模型</typeparam> /// <typeparam name="TOrderBy">排序类型</typeparam> /// <param name="index">当前页</param> /// <param name="pageSize">显示条数</param> /// <param name="where">过滤条件</param> /// <param name="orderby">排序字段</param> /// <param name="selector">结果集合</param> /// <param name="isAsc">排序方向true正序 false倒序</param> /// <returns>自定义实体集合</returns> public virtual Common.PageInfo<object> Query<TEntity, TOrderBy> (int index, int pageSize, Expression<Func<TEntity, bool>> where, Expression<Func<TEntity, TOrderBy>> orderby, Func<IQueryable<TEntity>, List<object>> selector, bool isAsc) where TEntity : class { if (index < 1) { index = 1; } if (pageSize <= 0) { pageSize = 20; } IQueryable<TEntity> query = this.Context.Set<TEntity>(); if (where != null) { query = query.Where(where); } int count = query.Count(); int maxpage = count / pageSize; if (count % pageSize > 0) { maxpage++; } if (index > maxpage) { index = maxpage; } if (orderby != null) { query = isAsc ? query.OrderBy(orderby) : query.OrderByDescending(orderby); } if (count > 0) query = query.Skip((index - 1) * pageSize).Take(pageSize); //返回结果为null，返回所有字段 if (selector == null) return new Common.PageInfo<object>(index, pageSize, count, query.ToList<object>()); return new Common.PageInfo<object>(index, pageSize, count, selector(query).ToList()); } /// <summary> /// 普通SQL查询分页方法 /// </summary> /// <param name="index">当前页</param> /// <param name="pageSize">显示行数</param> /// <param name="tableName">表名/视图</param> /// <param name="field">获取项</param> /// <param name="filter">过滤条件</param> /// <param name="orderby">排序字段+排序方向</param> /// <param name="group">分组字段</param> /// <returns>结果集</returns> public virtual Common.PageInfo Query(int index, int pageSize, string tableName, string field, string filter, string orderby, string group, params DbParameter[] para) { //执行分页算法 if (index <= 0) index = 1; int start = (index - 1) * pageSize; if (start > 0) start -= 1; else start = 0; int end = index * pageSize; #region 查询逻辑 string logicSql = "SELECT"; //查询项 if (!string.IsNullOrEmpty(field)) { logicSql += " " + field; } else { logicSql += " *"; } logicSql += " FROM (" + tableName + " ) where"; //过滤条件 if (!string.IsNullOrEmpty(filter)) { logicSql += " " + filter; } else { filter = " 1=1"; logicSql += " 1=1"; } //分组 if (!string.IsNullOrEmpty(group)) { logicSql += " group by " + group; } #endregion //获取当前条件下数据总条数 int count = this.Context.Database.SqlQuery(typeof(int), "select count(*) from (" + tableName + ") where " + filter, para).Cast<int>().FirstOrDefault(); string sql = "SELECT T.* FROM ( SELECT B.* FROM ( SELECT A.*,ROW_NUMBER() OVER(ORDER BY getdate()) as RN" + logicSql + ") A ) B WHERE B.RN<=" + end + ") T WHERE T.RN>" + start; //排序 if (!string.IsNullOrEmpty(orderby)) { sql += " order by " + orderby; } var list = ExecuteSqlQuery(sql, para) as IEnumerable; if (list != null) return new Common.PageInfo(index, pageSize, count, list.Cast<object>().ToList()); return new Common.PageInfo(index, pageSize, count, new { }); } /// <summary> /// 最简单的SQL分页 /// </summary> /// <param name="index">页码</param> /// <param name="pageSize">显示行数</param> /// <param name="sql">纯SQL语句</param> /// <param name="orderby">排序字段与方向</param> /// <returns></returns> public virtual Common.PageInfo Query(int index, int pageSize, string sql, string orderby, params DbParameter[] para) { return this.Query(index, pageSize, sql, null, null, orderby, null, para); } /// <summary> /// 多表联合分页算法 /// </summary> public virtual Common.PageInfo Query(IQueryable query, int index, int PageSize) { var enumerable = (query as System.Collections.IEnumerable).Cast<object>(); if (index < 1) { index = 1; } if (PageSize <= 0) { PageSize = 20; } int count = enumerable.Count(); int maxpage = count / PageSize; if (count % PageSize > 0) { maxpage++; } if (index > maxpage) { index = maxpage; } if (count > 0) enumerable = enumerable.Skip((index - 1) * PageSize).Take(PageSize); return new Common.PageInfo(index, PageSize, count, enumerable.ToList()); } #endregion #region ADO.NET增删改查方法 /// <summary> /// 执行增删改方法,含事务处理 /// </summary> public virtual object ExecuteSqlCommand(string sql, params DbParameter[] para) { try { return this.Context.Database.ExecuteSqlCommand(sql, para); } catch (Exception e) { throw e; } } /// <summary> /// 执行多条SQL，增删改方法,含事务处理 /// </summary> public virtual object ExecuteSqlCommand(Dictionary<string, object> sqllist) { try { int rows = 0; IEnumerator<KeyValuePair<string, object>> enumerator = sqllist.GetEnumerator(); using (Transaction) { while (enumerator.MoveNext()) { rows += this.Context.Database.ExecuteSqlCommand(enumerator.Current.Key, enumerator.Current.Value); } Commit(); } return rows; } catch (Exception e) { Rollback(); throw e; } } /// <summary> /// 执行查询方法,返回动态类，接收使用var，遍历时使用dynamic类型 /// </summary> public virtual object ExecuteSqlQuery(string sql, params DbParameter[] para) { try { return this.Context.Database.SqlQueryForDynamic(sql, para); } catch (Exception e) { throw e; } } #endregion } }
context：
namespace Service { public interface IUnitOfWork { /// <summary> /// 提交 /// </summary> /// <returns></returns> bool Commit(); } }
using Domain; using System; using System.Collections.Generic; using System.Data.Entity; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Service { public class UnitOfWork : IUnitOfWork, IDisposable { #region 数据上下文 private DbContext context = new MyConfig().db; /// <summary> /// 数据上下文 /// </summary> public DbContext _Context { get { context.Configuration.ValidateOnSaveEnabled = false; return context; } } #endregion /// <summary> /// 提交 /// </summary> /// <returns></returns> public bool Commit() { return _Context.SaveChanges() > 0; } public void Dispose() { if (_Context != null) { _Context.Dispose(); } GC.SuppressFinalize(this); } } }
扩展：
using System; using System.Collections; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.Entity; using System.Linq; using System.Reflection; using System.Reflection.Emit; using System.Text; namespace Service { /// <summary> /// 查询动态类 /// add yuangang by 2016-05-10 /// </summary> public static class DatabaseExtensions { /// <summary> /// 自定义Connection对象 /// </summary> private static IDbConnection DefaultConnection { get { return Domain.MyConfig.DefaultConnection; } } /// <summary> /// 自定义数据库连接字符串，与EF连接模式一致 /// </summary> private static string DefaultConnectionString { get { return Domain.MyConfig.DefaultConnectionString; } } /// <summary> /// 动态查询主方法 /// </summary> /// <returns></returns> public static IEnumerable SqlQueryForDynamic(this Database db, string sql, params object[] parameters) { IDbConnection defaultConn = DefaultConnection; //ADO.NET数据库连接字符串 db.Connection.ConnectionString = DefaultConnectionString; return SqlQueryForDynamicOtherDB(db, sql, defaultConn, parameters); } private static IEnumerable SqlQueryForDynamicOtherDB(this Database db, string sql, IDbConnection conn, params object[] parameters) { conn.ConnectionString = db.Connection.ConnectionString; if (conn.State != ConnectionState.Open) { conn.Open(); } IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; if (parameters != null) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } using (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++) { Type t = dataReader.GetFieldType(i); switch (t.Name.ToLower()) { case "decimal": t = typeof(Decimal?); break; case "double": t = typeof(Double?); break; case "datetime": t = typeof(DateTime?); break; case "single": t = typeof(float?); break; case "int16": t = typeof(int?); break; case "int32": t = typeof(int?); break; case "int64": t = typeof(int?); break; default: break; } DatabaseExtensions.CreateAutoImplementedProperty( builder, dataReader.GetName(i), t); } #endregion cmd.Parameters.Clear(); 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); } } } private 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; } private 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); } public static dynamic SqlFunctionForDynamic(this Database db, string sql, params object[] parameters) { IDbConnection conn = DefaultConnection; //ADO.NET数据库连接字符串 conn.ConnectionString = DefaultConnectionString; if (conn.State != ConnectionState.Open) { conn.Open(); } IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = sql; cmd.CommandType = CommandType.StoredProcedure; if (parameters != null) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } //1、DataReader查询数据 using (IDataReader dataReader = cmd.ExecuteReader()) { if (!dataReader.Read()) { return null; } //2、DataReader转换Json string jsonstr = Common.JsonConverter.ToJson(dataReader); dataReader.Close(); dataReader.Dispose(); cmd.Dispose(); conn.Close(); conn.Dispose(); //3、Json转换动态类 dynamic dyna = Common.JsonConverter.ConvertJson(jsonstr); return dyna; } } /// <summary> /// 对可空类型进行判断转换(*要不然会报错) /// </summary> /// <param name="value">DataReader字段的值</param> /// <param name="conversionType">该字段的类型</param> /// <returns></returns> private static object CheckType(object value, Type conversionType) { if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>))) { if (value == null) return null; System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType); conversionType = nullableConverter.UnderlyingType; } return Convert.ChangeType(value, conversionType); } /// <summary> /// 判断指定对象是否是有效值 /// </summary> /// <param name="obj"></param> /// <returns></returns> private static bool IsNullOrDBNull(object obj) { return (obj == null || (obj is DBNull)) ? true : false; } } }
在RepositoryBase中又增加了两个备用方法：
接口：
#region 执行sql语句返回datatable object SqlQueryForScalar(string sql, DbParameter[] parameters); DataTable SqlQueryForDataTatable(string sql, DbParameter[] parameters); #endregion
实现：
#region 执行sql语句返回datatable/object /// <summary> /// 执行查询并返回首行首列的值 /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public object SqlQueryForScalar(string sql, DbParameter[] parameters) { string connStr = this.Context.Database.Connection.ConnectionString; using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) { if (conn.State != ConnectionState.Open) { conn.Open(); } SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (parameters != null && parameters.Length > 0) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } object obj = cmd.ExecuteScalar(); conn.Close();//连接需要关闭 conn.Dispose(); return obj; } } /// <summary> /// 执行查询并返回DataTable /// </summary> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public DataTable SqlQueryForDataTatable(string sql, DbParameter[] parameters) { string connStr = this.Context.Database.Connection.ConnectionString; using (SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr)) { if (conn.State != ConnectionState.Open) { conn.Open(); } SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; if (parameters != null && parameters.Length > 0) { foreach (var item in parameters) { cmd.Parameters.Add(item); } } SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable table = new DataTable(); adapter.Fill(table); return table; } } #endregion
