• EF 通用数据层

    2019-01-03 16:29:40
    EF 通用数据层父类方法小结 转载:http://www.cnblogs.com/yq-Hua/p/4165344.html MSSql 数据库 数据层 父类 增删改查:   using System; using System.Collections.Generic; using System.Data; using...

    EF 通用数据层父类方法小结

    转载:http://www.cnblogs.com/yq-Hua/p/4165344.html

    MSSql 数据库 数据层 父类

    增删改查:

     

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Text;
    namespace DAL
    {
       /// <summary>
        /// MSSql 数据库 数据层 父类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public class BaseDAL<T> where T : class,new()
        {
            /// <summary>
            /// EF 上下文对象
            /// </summary>
            DbContext db = new DBContextFactory().GetDbContext();
    View Code

     

    展开全文
  • 支持多条件查询的扩展 ExpressionExtensions.cs类using System.Collections.Generic; using System.Linq; using System.Linq.Expressions; namespace System { /// &lt;summary&gt;......

    支持多条件查询的扩展 ExpressionExtensions.cs类

    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    namespace System
    {
        /// <summary>
        /// 谓词表达式构建器
        /// </summary>
        public static class ExpressionExtensions
        {
            /// <summary>
            /// 创建一个值恒为 <c>true</c> 的表达式。
            /// </summary>
            /// <typeparam name="T">表达式方法类型</typeparam>
            /// <returns>一个值恒为 <c>true</c> 的表达式。</returns>
            public static Expression<Func<T, bool>> True<T>() { return p => true; }
    
            /// <summary>
            /// 创建一个值恒为 <c>false</c> 的表达式。
            /// </summary>
            /// <typeparam name="T">表达式方法类型</typeparam>
            /// <returns>一个值恒为 <c>false</c> 的表达式。</returns>
            public static Expression<Func<T, bool>> False<T>() { return f => false; }
    
            /// <summary>
            /// 使用 Expression.OrElse 的方式拼接两个 System.Linq.Expression。
            /// </summary>
            /// <typeparam name="T">表达式方法类型</typeparam>
            /// <param name="left">左边的 System.Linq.Expression 。</param>
            /// <param name="right">右边的 System.Linq.Expression。</param>
            /// <returns>拼接完成的 System.Linq.Expression。</returns>
            public static Expression<T> Or<T>(this Expression<T> left, Expression<T> right)
            {
                return MakeBinary(left, right, Expression.OrElse);
            }
    
            /// <summary>
            /// 使用 Expression.AndAlso 的方式拼接两个 System.Linq.Expression。
            /// </summary>
            /// <typeparam name="T">表达式方法类型</typeparam>
            /// <param name="left">左边的 System.Linq.Expression 。</param>
            /// <param name="right">右边的 System.Linq.Expression。</param>
            /// <returns>拼接完成的 System.Linq.Expression。</returns>
            public static Expression<T> And<T>(this Expression<T> left, Expression<T> right)
            {
                return MakeBinary(left, right, Expression.AndAlso);
            }
    
            /// <summary>
            /// 使用自定义的方式拼接两个 System.Linq.Expression。
            /// </summary>
            /// <typeparam name="T">表达式方法类型</typeparam>
            /// <param name="left">左边的 System.Linq.Expression 。</param>
            /// <param name="right">右边的 System.Linq.Expression。</param>
            /// <param name="func"> </param>
            /// <returns>拼接完成的 System.Linq.Expression。</returns>
            private static Expression<T> MakeBinary<T>(this Expression<T> left, Expression<T> right, Func<Expression, Expression, Expression> func)
            {
                //Debug.Assert(func != null, "func != null");
                return MakeBinary((LambdaExpression)left, right, func) as Expression<T>;
            }
    
            /// <summary>
            /// 拼接两个 <paramref>
            /// <name>System.Linq.Expression</name>
            /// </paramref>  ,两个 <paramref>
            /// <name>System.Linq.Expression</name>
            /// </paramref>  的参数必须完全相同。
            /// </summary>
            /// <param name="left">左边的 <paramref>
            /// <name>System.Linq.Expression</name>
            /// </paramref> </param>
            /// <param name="right">右边的 <paramref>
            /// <name>System.Linq.Expression</name>
            /// </paramref> </param>
            /// <param name="func">表达式拼接的具体逻辑</param>
            /// <returns>拼接完成的 <paramref>
            /// <name>System.Linq.Expression</name>
            /// </paramref> </returns>
            private static LambdaExpression MakeBinary(this LambdaExpression left, LambdaExpression right, Func<Expression, Expression, Expression> func)
            {
                var data = Combinate(right.Parameters, left.Parameters).ToArray();
                right = ParameterReplace.Replace(right, data) as LambdaExpression;
                //Debug.Assert(right != null, "right != null");
                return Expression.Lambda(func(left.Body, right.Body), left.Parameters.ToArray());
            }
    
            /// <summary>
            /// 
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="left"></param>
            /// <param name="right"></param>
            /// <returns></returns>
            private static IEnumerable<KeyValuePair<T, T>> Combinate<T>(IEnumerable<T> left, IEnumerable<T> right)
            {
                var a = left.GetEnumerator();
                var b = right.GetEnumerator();
                while (a.MoveNext() && b.MoveNext())
                    yield return new KeyValuePair<T, T>(a.Current, b.Current);
            }
        }
    
        #region class: ParameterReplace
        internal sealed class ParameterReplace : ExpressionVisitor
        {
            public static Expression Replace(Expression e, IEnumerable<KeyValuePair<ParameterExpression, ParameterExpression>> paramList)
            {
                var item = new ParameterReplace(paramList);
                return item.Visit(e);
            }
    
            private readonly Dictionary<ParameterExpression, ParameterExpression> _parameters;
    
            public ParameterReplace(IEnumerable<KeyValuePair<ParameterExpression, ParameterExpression>> paramList)
            {
                _parameters = paramList.ToDictionary(p => p.Key, p => p.Value, new ParameterEquality());
            }
    
            protected override Expression VisitParameter(ParameterExpression p)
            {
                ParameterExpression result;
                if (_parameters.TryGetValue(p, out result))
                    return result;
                return base.VisitParameter(p);
            }
    
            #region class: ParameterEquality
            private class ParameterEquality : IEqualityComparer<ParameterExpression>
            {
                public bool Equals(ParameterExpression x, ParameterExpression y)
                {
                    if (x == null || y == null)
                        return false;
    
                    return x.Type == y.Type;
                }
    
                public int GetHashCode(ParameterExpression obj)
                {
                    if (obj == null)
                        return 0;
    
                    return obj.Type.GetHashCode();
                }
            }
            #endregion
        }
        #endregion
    }
    

    支持多列排序的封装 

    IOrderable.cs 接口

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace ZKHKCMS.IRepository
    {
        // <summary>
        /// 排序规范
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public interface IOrderable<T>
        {
            /// <summary>
            /// 递增
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="keySelector"></param>
            /// <returns></returns>
            IOrderable<T> Asc<TKey>(global::System.Linq.Expressions.Expression<Func<T, TKey>> keySelector);
            /// <summary>
            /// 然后递增
            /// </summary>
            /// <typeparam name="TKey1"></typeparam>
            /// <typeparam name="TKey2"></typeparam>
            /// <param name="keySelector1"></param>
            /// <returns></returns>
            IOrderable<T> ThenAsc<TKey>(Expression<Func<T, TKey>> keySelector);
            /// <summary>
            /// 递减
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="keySelector"></param>
            /// <returns></returns>
            IOrderable<T> Desc<TKey>(global::System.Linq.Expressions.Expression<Func<T, TKey>> keySelector);
            /// <summary>
            /// 然后递减
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="keySelector"></param>
            /// <returns></returns>
            IOrderable<T> ThenDesc<TKey>(Expression<Func<T, TKey>> keySelector);
            /// <summary>
            /// 排序后的结果集
            /// </summary>
            global::System.Linq.IQueryable<T> Queryable { get; }
        }
    }
    

    实现 IOrderable.cs接口的Orderable.cs类

    using System;
    using System.Linq;
    using System.Linq.Expressions;
    using ZKHKCMS.IRepository;
    
    namespace ZKHKCMS.Repository
    {
        /// <summary>
        /// Linq架构里对集合排序实现
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public class Orderable<T> : IOrderable<T>
        {
            private IQueryable<T> _queryable;
    
            /// <summary>
            /// 排序后的结果集
            /// </summary>
            /// <param name="enumerable"></param>
            public Orderable(IQueryable<T> enumerable)
            {
                _queryable = enumerable;
            }
    
            /// <summary>
            /// 排序之后的结果集
            /// </summary>
            public IQueryable<T> Queryable
            {
                get { return _queryable; }
            }
            /// <summary>
            /// 递增
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="keySelector"></param>
            /// <returns></returns>
            public IOrderable<T> Asc<TKey>(Expression<Func<T, TKey>> keySelector)
            {
                _queryable = (_queryable as IOrderedQueryable<T>)
                    .OrderBy(keySelector);
                return this;
            }
            /// <summary>
            /// 然后递增
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="keySelector"></param>
            /// <returns></returns>
            public IOrderable<T> ThenAsc<TKey>(Expression<Func<T, TKey>> keySelector)
            {
                _queryable = (_queryable as IOrderedQueryable<T>)
                    .ThenBy(keySelector);
                return this;
            }
            /// <summary>
            /// 递减
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="keySelector"></param>
            /// <returns></returns>
            public IOrderable<T> Desc<TKey>(Expression<Func<T, TKey>> keySelector)
            {
                _queryable = _queryable
                    .OrderByDescending(keySelector);
                return this;
            }
            /// <summary>
            /// 然后递减
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="keySelector"></param>
            /// <returns></returns>
            public IOrderable<T> ThenDesc<TKey>(Expression<Func<T, TKey>> keySelector)
            {
                _queryable = (_queryable as IOrderedQueryable<T>)
                    .ThenByDescending(keySelector);
                return this;
            }
        }
    }
    

    EF通用查询封装

    IDAL 接口IBaseRepository.cs

    using MySql.Data.MySqlClient;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Linq.Expressions;
    using ZKHKCMS.Common;
    using ZKHKCMS.Models;
    
    namespace ZKHKCMS.IRepository
    {
        public interface IBaseRepository<T> where T : BaseEntity, new()
        {
            /// <summary>  
            /// 新增实体,返回受影响的行数  
            /// </summary>  
            /// <param name="model"></param>  
            /// <returns>返回受影响的行数</returns>  
            int Add(T model);
    
    
            /// <summary>  
            ///新增实体,返回对应的实体对象  
            /// </summary>  
            /// <param name="model"></param>  
            /// <returns>新增的实体对象</returns>  
            T AddReturnModel(T model);
    
    
            /// <summary>  
            /// 批量新增实体  
            /// </summary>  
            /// <typeparam name="T">泛型类型参数</typeparam>  
            /// <param name="entityList">待添加的实体集合</param>  
            /// <returns></returns>  
            int AddRange(List<T> entityList);
    
    
            /// <summary>
            /// 批量的插入数据(带事务)
            /// </summary>
            /// <param name="entityList">待添加的实体集合</param>
            /// <returns>是否成功插入 true:是 false:否</returns>
            bool AddRangeTransaction(List<T> entityList);
    
    
            /// <summary>
            /// 根据id删除数据
            /// </summary>
            /// <param name="id"></param>
            /// <returns>返回受影响的行数</returns>
            int DelById(int id);
    
    
            /// <summary>  
            /// 根据模型删除数据
            /// </summary>  
            /// <param name="model">该模型对象必须包含id值</param>  
            /// <returns>返回受影响的行数</returns>  
            int Delete(T model);
    
    
            /// <summary>  
            /// 根据条件删除数据 (支持多条件查询)
            /// </summary>  
            /// <param name="delWhere"></param>  
            /// <returns>返回受影响的行数</returns>  
            int Delete(Expression<Func<T, bool>> whereLambda);
    
    
            /// <summary>  
            /// 修改实体  
            /// </summary>  
            /// <param name="model">该模型对象必须包含id值</param>  
            /// <returns>返回受影响的行数</returns>  
            int Edit(T model);
    
    
            /// <summary>  
            /// 修改实体,可修改指定属性  
            /// </summary>  
            /// <param name="model">该模型对象必须包含id值</param>  
            /// <param name="propertyName">要修改的属性名称数组</param>  
            /// <returns></returns>  
            int Edit(T model, params string[] propertyNames);
    
    
            /// <summary>  
            /// 批量修改  (支持多条件查询)
            /// </summary>  
            /// <param name="model"></param>  
            /// <param name="whereLambda">条件查询表达式</param>  
            /// <param name="modifiedPropertyNames">要修改的属性名称数组</param>  
            /// <returns></returns>  
            int EditBatch(T model, Expression<Func<T, bool>> whereLambda, params string[] modifiedPropertyNames);
    
    
            /// <summary>
            /// 批量修改
            /// </summary>
            /// <param name="entityList">待修改的实体集合</param>
            /// <returns></returns>
            int EditBatch(List<T> entityList);
    
    
            /// <summary>
            /// 批量的进行更新数据 (带事务)
            /// </summary>
            /// <param name="EntityList">待修改的实体集合</param>
            /// <returns>是否更新成功 true:是 false:否</returns>
            bool EditBatchTransaction(List<T> EntityList);
    
    
            /// <summary>
            /// 根据Id查询单个Model
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            T GetById(int id);
    
    
            /// <summary>  
            /// 根据条件查询单个model (支持多条件查询)
            /// </summary>  
            /// <param name="whereLambda"></param>  
            /// <returns></returns>  
            T Get(Expression<Func<T, bool>> whereLambda);
    
    
            /// <summary>  
            /// 根据条件查询单个model (支持多条件查询,支持多列排序)  
            /// </summary>  
            /// <typeparam name="TKey"></typeparam>  
            /// <param name="whereLambda">查询条件</param>  
            /// <param name="orderLambda">排序条件</param>  
            /// <param name="isAsc"></param>  
            /// <returns></returns>  
            T Get(Expression<Func<T, bool>> whereLambda, Action<IOrderable<T>> orderBy = null);
    
    
            /// <summary>  
            /// 根据条件查询单个model (支持多条件查询,仅支持单列排序)  
            /// </summary>  
            /// <typeparam name="TKey"></typeparam>  
            /// <param name="whereLambda">查询条件</param>  
            /// <param name="orderLambda">排序条件</param>  
            /// <param name="isAsc">是否为升序排序,默认true</param>  
            /// <returns></returns>  
            T Get<TKey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderByLambda, bool isAsc = true);
    
    
            /// <summary>
            /// 查询所有数据
            /// </summary>
            /// <returns></returns>
            IQueryable<T> GetAll();
    
    
            /// <summary>
            /// 获取查询条件的数据总条数 (支持多条件查询)
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            int GetCount(Expression<Func<T, bool>> whereLambda = null);
    
    
            /// <summary>
            /// 判断对象是否存在 (支持多条件查询)
            /// </summary>
            /// <param name="whereLambda">查询条件</param>
            /// <returns>对象存在则返回true,不存在则返回false</returns>
            bool GetAny(Expression<Func<T, bool>> whereLambda = null);
    
    
            /// <summary>  
            /// 获取数据集合 (支持多条件查询) 
            /// </summary>  
            /// <param name="whereLambda"></param>  
            /// <returns></returns>  
            IQueryable<T> GetList(Expression<Func<T, bool>> whereLambda);
    
    
            /// <summary>  
            ///  获取数据集合 (支持多条件查询,仅支持单条件排序)
            /// </summary>  
            /// <typeparam name="TKey"></typeparam>  
            /// <param name="whereLambda"></param>  
            /// <param name="orderLambda"></param>  
            /// <param name="isAsc">是否为升序排序,默认为true</param>  
            /// <returns></returns>  
            IQueryable<T> GetList<TKey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderByLambda, bool isAsc = true);
    
    
            /// <summary>
            /// 获取数据集合 (支持多条件查询,支持多列排序)
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <param name="orderBy"></param>
            /// <returns></returns>
            IQueryable<T> GetList(Expression<Func<T, bool>> whereLambda, Action<IOrderable<T>> orderBy);
    
    
            /// <summary>
            /// 获取数据集合 (支持多条件查询,支持多列排序)
            /// </summary>
            /// <typeparam name="E">目标类型:说明:将查询出来的数据转换成目标类型实体</typeparam>
            /// <param name="orderBy">排序(可选)</param>
            /// <returns></returns>
            IQueryable<E> GetList<E>(string sql, Action<IOrderable<E>> orderBy = null);
    
    
            /// <summary>
            /// 分页查询 (支持多条件查询,仅支持单列排序)
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="pageIndex">当前页</param>
            /// <param name="pageSize">每页显示数据的条数</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="orderByLambda">排序条件</param>
            /// <param name="isAsc">是否为升序排序,默认为true</param>
            /// <returns></returns>
            IQueryable<T> GetPagedList<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderByLambda, bool isAsc = true);
    
    
            /// <summary>
            /// 分页查询 ,带输出数据总条数 (支持多条件查询,仅支持单列排序)
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="pageIndex">当前页</param>
            /// <param name="pageSize">每页显示数据的条数</param>
            /// <param name="totalCount">数据总条数</param>
            /// <param name="orderByLambda">排序条件</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="isAsc">是否为升序排序,默认为true</param>
            /// <returns></returns>
            IQueryable<T> GetPagedList<TKey>(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, TKey>> orderByLambda, Expression<Func<T, bool>> whereLambda = null, bool isAsc = true);
    
    
            /// <summary>
            /// 分页查询,带输出数据总条数  (支持多条件查询,支持多列排序)
            /// </summary>
            /// <param name="pageIndex">当前页</param>
            /// <param name="pageSize">每页显示数据的条数</param>
            /// <param name="totalCount">输出数据:数据总条数</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="orderBy">排序条件</param>
            /// <returns></returns>
            IQueryable<T> GetPagedList(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, bool>> whereLambda = null, Action<IOrderable<T>> orderBy = null);
    
    
            /// <summary>
            /// 执行存储过程的SQL 语句
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="Sql">执行的SQL语句</param>
            /// <param name="Parms">SQL 语句的参数</param>
            /// <param name="CmdType"> SQL命令(默认为Text)</param>
            /// <returns></returns>
            IQueryable<T> ProQuery(string Sql, List<SqlParameter> Parms, CommandType CmdType = CommandType.Text);
    
    
            /// <summary>
            /// 创建一个原始 SQL 查询,该查询将返回给定泛型类型的元素。
            /// </summary>
            /// <typeparam name="T">查询所返回对象的类型</typeparam>
            /// <param name="sql">SQL 查询字符串</param>
            /// <param name="parameters">要应用于 SQL 查询字符串的参数</param>
            /// <returns></returns>
            IQueryable<T> SqlQuery(string sql, params SqlParameter[] parameters);
    
    
            /// <summary>
            /// 创建一个原始 SQL 查询,该查询将返回给定泛型类型的元素 (用于MySql数据库,需要安装MySql.Data插件)
            /// </summary>
            /// <typeparam name="T">查询所返回对象的类型</typeparam>
            /// <param name="sql">SQL 查询字符串</param>
            /// <param name="parameters">要应用于 SQL 查询字符串的参数</param>
            /// <returns></returns>
            IQueryable<T> MySqlQuery(string sql, params MySqlParameter[] parameters);
    
    
            /// <summary>
            /// 创建一个原始 SQL 用户 新增,删除,编辑
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parameters"></param>
            /// <returns>返回受影响的行数</returns>
            int ExecuteSqlCommand(string sql, params SqlParameter[] parameters);
    
    
            /// <summary>
            /// 创建一个原始 SQL 用户 新增,删除,编辑 (用于MySql数据库,需要安装MySql.Data插件)
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parameters"></param>
            /// <returns>返回受影响的行数</returns>
            int ExecuteMySqlCommand(string sql, params MySqlParameter[] parameters);
    
    
            /// <summary>  
            /// 获取带 in 的sql参数列表  
            /// </summary>  
            /// <param name="sql">带in ( {0} )的sql</param>  
            /// <param name="ids">以逗号分隔的id字符串</param>  
            /// <returns>sql参数列表</returns>  
            SqlParameter[] GetWithInSqlParameters(ref string sql, string ids);
    
        }
    }
    



    DAL 类 BaseRepository.cs

    using MySql.Data.MySqlClient;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Reflection;
    using System.Text;
    using System.Transactions;
    using ZKHKCMS.Common;
    using ZKHKCMS.Entities;
    using ZKHKCMS.IRepository;
    using ZKHKCMS.Models;
    
    
    namespace ZKHKCMS.Repository
    {
        public class BaseRepository<T> : IBaseRepository<T> where T : BaseEntity, new()
        {
            DbContext db = DBContextFactory.GetCurrentObjectContext();
    
            /// <summary>  
            /// 新增实体,返回受影响的行数  
            /// </summary>  
            /// <param name="model"></param>  
            /// <returns>返回受影响的行数</returns>  
            public int Add(T model)
            {
                db.Set<T>().Add(model);
                return db.SaveChanges();
            }
    
    
            /// <summary>  
            ///新增实体,返回对应的实体对象  
            /// </summary>  
            /// <param name="model"></param>  
            /// <returns>新增的实体对象</returns>  
            public T AddReturnModel(T model)
            {
                db.Set<T>().Add(model);
                db.SaveChanges();
                return model;
            }
    
    
            /// <summary>  
            /// 批量新增实体
            /// </summary>  
            /// <typeparam name="T">泛型类型参数</typeparam>  
            /// <param name="entityList">待添加的实体集合</param>  
            /// <returns></returns>  
            public int AddRange(List<T> entityList)
            {
                db.Set<T>().AddRange(entityList);
                return db.SaveChanges();
            }
    
    
            /// <summary>
            /// 批量的新增实体(带事务)
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="Entity"></param>
            /// <returns></returns>
            public bool AddRangeTransaction(List<T> entityList)
            {
                using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required))
                {
                    db.Set<T>().AddRange(entityList);
                    int Count = db.SaveChanges();
                    Ts.Complete();
                    return Count > 0;
                }
            }
    
    
            /// <summary>
            /// 根据id删除数据
            /// </summary>
            /// <param name="id"></param>
            /// <returns>返回受影响的行数</returns>
            public int DelById(int id)
            {
                var model = GetById(id);
                if (model == null || model.id <= 0) return -1;
                db.Set<T>().Remove(model);
                return db.SaveChanges();
            }
    
    
            /// <summary>  
            /// 根据模型删除数据
            /// </summary>  
            /// <param name="model">该模型对象必须包含id值</param>  
            /// <returns>返回受影响的行数</returns>  
            public int Delete(T model)
            {
                db.Set<T>().Attach(model);
                db.Set<T>().Remove(model);
                return db.SaveChanges();
            }
    
    
            /// <summary>  
            /// 根据条件删除数据 (支持多条件查询)
            /// </summary>  
            /// <param name="delWhere"></param>  
            /// <returns>返回受影响的行数</returns>  
            public int Delete(Expression<Func<T, bool>> whereLambda)
            {
                //查询要删除的数据  
                List<T> listDeleting = db.Set<T>().Where(whereLambda).ToList();
                //将要删除的数据 用删除方法添加到 EF 容器中  
                listDeleting.ForEach(u =>
                {
                    db.Set<T>().Attach(u);  //先附加到EF 容器  
                    db.Set<T>().Remove(u); //标识为删除状态  
                });
                return db.SaveChanges();
            }
    
    
            /// <summary>  
            /// 修改实体  
            /// </summary>  
            /// <param name="model">该模型对象必须包含id值</param>  
            /// <returns>返回受影响的行数</returns>  
            public int Edit(T model)
            {
                DbEntityEntry entry = db.Entry<T>(model);
                entry.State = EntityState.Modified;
                return db.SaveChanges();
            }
    
    
            /// <summary>  
            /// 修改实体,可修改指定属性  
            /// </summary>  
            /// <param name="model">该模型对象必须包含id值</param>  
            /// <param name="propertyName">要修改的属性名称数组</param>  
            /// <returns></returns>  
            public int Edit(T model, params string[] propertyNames)
            {
                DbEntityEntry entry = db.Entry<T>(model); //将对象添加到EF中             
                entry.State = EntityState.Unchanged; //先设置对象的包装状态为 Unchanged            
                foreach (string propertyName in propertyNames)  //循环被修改的属性名数组 
                {
                    //将每个被修改的属性的状态设置为已修改状态;这样在后面生成的修改语句时,就只为标识为已修改的属性更新  
                    entry.Property(propertyName).IsModified = true;
                }
                return db.SaveChanges();
            }
    
    
            /// <summary>  
            /// 批量修改  (支持多条件查询)
            /// </summary>  
            /// <param name="model"></param>  
            /// <param name="whereLambda"></param>  
            /// <param name="modifiedPropertyNames"></param>  
            /// <returns></returns>  
            public int EditBatch(T model, Expression<Func<T, bool>> whereLambda, params string[] modifiedPropertyNames)
            {
                List<T> listModifing = db.Set<T>().Where(whereLambda).ToList(); //查询要修改的数据              
                Type t = typeof(T); //获取实体类类型对象             
                List<PropertyInfo> propertyInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();//获取实体类所有的公共属性 
                Dictionary<string, PropertyInfo> dicPropertys = new Dictionary<string, PropertyInfo>(); //创建实体属性字典集合  
                //将实体属性中要修改的属性名 添加到字典集合中  键:属性名  值:属性对象  
                propertyInfos.ForEach(p =>
                {
                    if (modifiedPropertyNames.Contains(p.Name))
                    {
                        dicPropertys.Add(p.Name, p);
                    }
                });
    
                foreach (string propertyName in modifiedPropertyNames)//循环要修改的属性名  
                {
                    if (dicPropertys.ContainsKey(propertyName)) //判断要修改的属性名是否在实体类的属性集合中存在  
                    {
                        PropertyInfo proInfo = dicPropertys[propertyName];//如果存在,则取出要修改的属性对象                      
                        object newValue = proInfo.GetValue(model, null);//取出要修改的值                    
                        foreach (T item in listModifing) //批量设置要修改对象的属性  
                        {
                            proInfo.SetValue(item, newValue, null);// 为要修改的对象的要修改的属性设置新的值
                        }
                    }
                }
                return db.SaveChanges();
            }
    
    
            /// <summary>
            /// 批量修改
            /// </summary>
            /// <param name="models"></param>
            /// <returns></returns>
            public int EditBatch(List<T> entityList)
            {
                foreach (var entity in entityList)
                {
                    DbEntityEntry entry = db.Entry(entity);
                    entry.State = EntityState.Modified;
                }
                return db.SaveChanges();
    
            }
    
    
            /// <summary>
            /// 批量的进行更新数据 (带事务)
            /// </summary>
            /// <param name="Entity"></param>
            /// <returns></returns>
            public bool EditBatchTransaction(List<T> EntityList)
            {
                int Count = 0;
                using (TransactionScope Ts = new TransactionScope(TransactionScopeOption.Required))
                {
                    if (EntityList != null)
                    {
                        foreach (var items in EntityList)
                        {
                            var EntityModel = db.Entry(EntityList);
                            db.Set<T>().Attach(items);
                            EntityModel.State = EntityState.Modified;
                        }
                    }
                    Count = db.SaveChanges();
                    Ts.Complete();
                }
                return Count > 0;
            }
    
    
            /// <summary>
            /// 根据Id查询单个Model
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            public T GetById(int id)
            {
                return db.Set<T>().FirstOrDefault(r => r.id == id);
            }
    
    
            /// <summary>  
            /// 根据条件查询单个model (支持多条件查询)
            /// </summary>  
            /// <param name="whereLambda"></param>  
            /// <returns></returns>  
            public T Get(Expression<Func<T, bool>> whereLambda)
            {
                return db.Set<T>().Where(whereLambda).FirstOrDefault();
            }
    
    
            /// <summary>  
            /// 根据条件查询单个model (支持多条件查询,支持多列排序)  
            /// </summary>  
            /// <typeparam name="TKey"></typeparam>  
            /// <param name="whereLambda">查询条件</param>  
            /// <param name="orderLambda">排序条件</param>  
            /// <param name="isAsc"></param>  
            /// <returns></returns>  
            public T Get(Expression<Func<T, bool>> whereLambda, Action<IOrderable<T>> orderBy = null)
            {
                IQueryable<T> QueryList = db.Set<T>();
                if (whereLambda != null)
                {
                    QueryList = QueryList.Where(whereLambda);
                }
                if (orderBy != null)
                {
                    var linq = new Orderable<T>(QueryList);
                    orderBy(linq);
                    return linq.Queryable.FirstOrDefault();
                }
                return QueryList.FirstOrDefault();
            }
    
    
            /// <summary>  
            /// 根据条件查询单个model (支持多条件查询,仅支持单列排序)  
            /// </summary>  
            /// <typeparam name="TKey"></typeparam>  
            /// <param name="whereLambda">查询条件</param>  
            /// <param name="orderLambda">排序条件</param>  
            /// <param name="isAsc">是否为升序排序,默认true</param>  
            /// <returns></returns>  
            public T Get<TKey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderByLambda, bool isAsc = true)
            {
                IQueryable<T> Query = isAsc == true ? db.Set<T>().OrderBy(orderByLambda) : db.Set<T>().OrderByDescending(orderByLambda);
                if (whereLambda != null)
                {
                    Query = Query.Where(whereLambda);
                }
                return Query.FirstOrDefault();
            }
    
    
            /// <summary>
            /// 查询所有数据
            /// </summary>
            /// <returns></returns>
            public IQueryable<T> GetAll()
            {
                return db.Set<T>();
            }
    
    
            /// <summary>
            /// 获取查询条件的数据总条数 (支持多条件查询)
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            public int GetCount(Expression<Func<T, bool>> whereLambda = null)
            {
                return whereLambda != null ? db.Set<T>().Where(whereLambda).Count() : db.Set<T>().Count();
            }
    
    
            /// <summary>
            /// 判断对象是否存在 (支持多条件查询)
            /// </summary>
            /// <param name="whereLambda">查询条件</param>
            /// <returns>对象存在则返回true,不存在则返回false</returns>
            public bool GetAny(Expression<Func<T, bool>> whereLambda = null)
            {
                return whereLambda != null ? db.Set<T>().Where(whereLambda).Any() : db.Set<T>().Any();
            }
    
    
            /// <summary>  
            /// 获取数据集合 (支持多条件查询) 
            /// </summary>  
            /// <param name="whereLambda"></param>  
            /// <returns></returns>  
            public IQueryable<T> GetList(Expression<Func<T, bool>> whereLambda)
            {
                return db.Set<T>().Where(whereLambda);
            }
    
    
            /// <summary>  
            ///  获取数据集合 (支持多条件查询,仅支持单条件排序)
            /// </summary>  
            /// <typeparam name="TKey"></typeparam>  
            /// <param name="whereLambda"></param>  
            /// <param name="orderLambda"></param>  
            /// <param name="isAsc">是否为升序排序,默认为true</param>  
            /// <returns></returns>  
            public IQueryable<T> GetList<TKey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderByLambda, bool isAsc = true)
            {
                IQueryable<T> QueryList = isAsc == true ? db.Set<T>().OrderBy(orderByLambda) : db.Set<T>().OrderByDescending(orderByLambda);
                if (whereLambda != null)
                {
                    QueryList = QueryList.Where(whereLambda);
                }
                return QueryList;
    
            }
    
    
            /// <summary>
            /// 获取数据集合 (支持多条件查询,支持多列排序)
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <param name="orderBy"></param>
            /// <returns></returns>
            public IQueryable<T> GetList(Expression<Func<T, bool>> whereLambda, Action<IOrderable<T>> orderBy)
            {
                IQueryable<T> QueryList = db.Set<T>();
                if (whereLambda != null)
                {
                    QueryList = QueryList.Where(whereLambda);
                }
                var linq = new Orderable<T>(QueryList);
                orderBy(linq);
                return linq.Queryable;
            }
    
    
            /// <summary>
            /// 获取数据集合 (支持多条件查询,支持多列排序)
            /// </summary>
            /// <typeparam name="Entity">目标类型:说明:将查询出来的数据转换成目标类型实体</typeparam>
            /// <param name="orderBy">排序(可选)</param>
            /// <returns></returns>
            public IQueryable<E> GetList<E>(string sql, Action<IOrderable<E>> orderBy = null)
            {
                IQueryable<E> QueryList = db.Database.SqlQuery<E>(sql).AsQueryable();
                if (orderBy != null)
                {
                    var linq = new Orderable<E>(QueryList);
                    orderBy(linq);
                    return linq.Queryable;
                }
                return QueryList;
            }
    
    
            /// <summary>
            /// 分页查询 (支持多条件查询,仅支持单列排序)
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="pageIndex">当前页</param>
            /// <param name="pageSize">每页显示数据的条数</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="orderByLambda">排序条件</param>
            /// <param name="isAsc">是否为升序排序,默认为true</param>
            /// <returns></returns>
            public IQueryable<T> GetPagedList<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderByLambda, bool isAsc = true)
            {
                //分页的时候一定要注意 Order一定在Skip 之前
                IQueryable<T> QueryList = isAsc == true ? db.Set<T>().OrderBy(orderByLambda) : db.Set<T>().OrderByDescending(orderByLambda);
    
                if (whereLambda != null)
                {
                    QueryList = QueryList.Where(whereLambda);
                }
                return QueryList.Skip(pageSize * (pageIndex - 1)).Take(pageSize);
            }
    
    
            /// <summary>
            /// 分页查询 ,带输出数据总条数 (支持多条件查询,仅支持单列排序)
            /// </summary>
            /// <typeparam name="TKey"></typeparam>
            /// <param name="pageIndex">当前页</param>
            /// <param name="pageSize">每页显示数据的条数</param>
            /// <param name="totalCount">数据总条数</param>
            /// <param name="orderByLambda">排序条件</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="isAsc">是否为升序排序,默认为true</param>
            /// <returns></returns>
            public IQueryable<T> GetPagedList<TKey>(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, TKey>> orderByLambda, Expression<Func<T, bool>> whereLambda = null, bool isAsc = true)
            {
                //分页的时候一定要注意 Order一定在Skip 之前
                IQueryable<T> QueryList = isAsc == true ? db.Set<T>().OrderBy(orderByLambda) : db.Set<T>().OrderByDescending(orderByLambda);
                if (whereLambda != null)
                {
                    QueryList = QueryList.Where(whereLambda);
                }
                totalCount = QueryList.Count();
                return QueryList.Skip(pageSize * (pageIndex - 1)).Take(pageSize);
            }
    
    
            /// <summary>
            /// 分页查询,带输出数据总条数  (支持多条件查询,支持多列排序)
            /// </summary>
            /// <param name="pageIndex">当前页</param>
            /// <param name="pageSize">每页显示数据的条数</param>
            /// <param name="totalCount">输出数据:数据总条数</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="orderBy">排序条件</param>
            /// <returns></returns>
            public IQueryable<T> GetPagedList(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, bool>> whereLambda = null, Action<IOrderable<T>> orderBy = null)
            {
                IQueryable<T> QueryList = db.Set<T>();
                if (whereLambda != null)
                {
                    QueryList = QueryList.Where(whereLambda);
                }
                totalCount = QueryList.Count();
                if (orderBy != null)
                {
                    var linq = new Orderable<T>(QueryList);
                    orderBy(linq);
                    return linq.Queryable.Skip(pageSize * (pageIndex - 1)).Take(pageSize);
                }
                return QueryList.Skip(pageSize * (pageIndex - 1)).Take(pageSize); ;
            }
    
    
            /// <summary>
            /// 执行存储过程的SQL 语句
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="Sql">执行的SQL语句</param>
            /// <param name="Parms">SQL 语句的参数</param>
            /// <param name="CmdType"> SQL命令(默认为Text)</param>
            /// <returns></returns>
            public IQueryable<T> ProQuery(string Sql, List<SqlParameter> Parms, CommandType CmdType = CommandType.Text)
            {
                //进行执行存储过程
                if (CmdType == CommandType.StoredProcedure)
                {
                    StringBuilder paraNames = new StringBuilder();
                    foreach (var item in Parms)
                    {
                        paraNames.Append($" @{item},");
                    }
                    Sql = paraNames.Length > 0 ? $"exec {Sql} {paraNames.ToString().Trim(',')}" : $"exec {Sql} ";
                }
                return db.Set<T>().SqlQuery(Sql, Parms.ToArray()).AsQueryable();
            }
    
    
            /// <summary>
            /// 创建一个原始 SQL 查询,该查询将返回给定泛型类型的元素
            /// </summary>
            /// <typeparam name="T">查询所返回对象的类型</typeparam>
            /// <param name="sql">SQL 查询字符串</param>
            /// <param name="parameters">要应用于 SQL 查询字符串的参数</param>
            /// <returns></returns>
            public IQueryable<T> SqlQuery(string sql, params SqlParameter[] parameters)
            {
                return db.Database.SqlQuery<T>(sql, parameters).AsQueryable();
            }
    
    
            /// <summary>
            /// 创建一个原始 SQL 查询,该查询将返回给定泛型类型的元素 (用于MySql数据库,需要安装MySql.Data插件)
            /// </summary>
            /// <typeparam name="T">查询所返回对象的类型</typeparam>
            /// <param name="sql">SQL 查询字符串</param>
            /// <param name="parameters">要应用于 SQL 查询字符串的参数</param>
            /// <returns></returns>
            public IQueryable<T> MySqlQuery(string sql, params MySqlParameter[] parameters)
            {
                return db.Database.SqlQuery<T>(sql, parameters).AsQueryable();
            }
    
    
            /// <summary>
            /// 创建一个原始 SQL 用户 新增,删除,编辑
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parameters"></param>
            /// <returns>返回受影响的行数</returns>
            public int ExecuteSqlCommand(string sql, params SqlParameter[] parameters)
            {
                return db.Database.ExecuteSqlCommand(sql, parameters);
            }
    
    
            /// <summary>
            /// 创建一个原始 SQL 用户 新增,删除,编辑 (用于MySql数据库,需要安装MySql.Data插件)
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parameters"></param>
            /// <returns>返回受影响的行数</returns>
            public int ExecuteMySqlCommand(string sql, params MySqlParameter[] parameters)
            {
                return db.Database.ExecuteSqlCommand(sql, parameters);
            }
    
    
            /// <summary>  
            /// 获取带 in 的sql参数列表  
            /// </summary>  
            /// <param name="sql">带in ( {0} )的sql</param>  
            /// <param name="ids">以逗号分隔的id字符串</param>  
            /// <returns>sql参数列表</returns>  
            public SqlParameter[] GetWithInSqlParameters(ref string sql, string ids)
            {
                if (string.IsNullOrEmpty(ids))
                {
                    return null;
                }
                string[] idArr = ids.Split(',');
                //组建sql在in中的字符串  
                StringBuilder sbCondition = new StringBuilder();
                List<SqlParameter> spList = new List<SqlParameter>();
                for (int i = 0; i < idArr.Length; i++)
                {
                    string id = idArr[i];
                    sbCondition.Append("@id" + i + ",");
                    spList.Add(new SqlParameter("@id" + i.ToString(), id));
                }
                //重新构建sql  
                sql = string.Format(sql, sbCondition.ToString().TrimEnd(','));
                return spList.ToArray();
            }
        }
    }
    




    控制器中调用示例--(多条件查询,多列排序示例)

    /// <summary>
    /// 菜单
    /// </summary>
    public class MenuController : BaseController
    {
        public IMenuRepository menu { get; set; } /IOC属性注入
    	
    	/// <summary>
    	/// 分页查询
    	/// </summary>
    	/// <param name="pageIndex">当前页</param>
    	/// <param name="menuName">菜单名称</param>
    	/// <param name="menuLevel">菜单级别</param>
    	/// <returns></returns>
    	[HttpPost]
    	public JsonResult List(int pageIndex, int pageSize, string menuName,int menuLevel)
    	{
    		Expression<Func<Menu, bool>> filter = r => true;
    				
    		if (!string.IsNullOrEmpty(menuName))//条件查询一
    		{
    			filter = filter.And(r => r.MenuName.Contains(menuName));
    		}
    		if (menuLevel > 0)//条件查询二
    		{
    			filter = filter.And(r => r.MenuLevel == menuLevel);
    		}
    	
    		//先对sort列进行升序排序,然后对menuParentId列进行降序排序 (定义排序条件)
    		Action<IOrderable<Menu>> orderBy = query => query.Asc(j => j.sort).ThenDesc(k => k.menuParentId);
    	
    		var totalCount=0;
    		var data = menu.GetPagedList(pageIndex, pageSize,ref totalCount, filter, orderBy); //调用分页方法(根据多条件查询,根据多列排序)
    	
    		var result = new { TotalCount = totalCount, Data = data.ToList() }; //返回数据
    		return Success(result);
    	}
    }



    附送获取EF上下文对象实例 DBContextFactory.cs类

    using System.Data.Entity;
    using System.Runtime.Remoting.Messaging;
    namespace ZKHKCMS.Entities
    {
        public class DBContextFactory
        {
            /// <summary>
            /// 获取EF上下文容器类对象实例
            /// </summary>
            /// <returns></returns>
            public static DbContext GetCurrentObjectContext()
            {
                //从CallContext数据槽中获取EF上下文  
                DbContext objectContext = CallContext.GetData(typeof(DBContextFactory).FullName) as DbContext;
                if (objectContext == null)
                {
                    //如果CallContext数据槽中没有EF上下文,则创建EF上下文,并保存到CallContext数据槽中  
                    objectContext = new HKEntitiesDbContext();//当数据库替换为MySql等,只要在此处EF更换上下文即可。这里的DBContent是model.context.cs中的局部类  
                    CallContext.SetData(typeof(DBContextFactory).FullName, objectContext);
                }
                return objectContext;
            }
    
            //public static DbContext GetCurrentContext() //写法二
            //{
            //    //CallContext:保证线程内创建的数据操作上下文是唯一的。
            //    DbContext DbContext = (DbContext)CallContext.GetData("context");
            //    if (DbContext == null)
            //    {
            //        DbContext = new HKEntitiesDbContext();
            //        CallContext.SetData("context", DbContext);
            //    }
            //    return DbContext;
            //}
        }
    }
    



    展开全文
  • 浅谈orm浅谈Entity FrameworkEntity Framework 通用数据层类扩展类,实现读写分离进一步改造,实现多从库读取参考文章源码分享 回到顶部 浅谈orm  记得四年前在学校第一次接触到 Ling to Sql,那时候瞬间发现...
    
    
    

    浅谈orm

      记得四年前在学校第一次接触到 Ling to Sql,那时候瞬间发现不用手写sql语句是多么的方便,后面慢慢的接触了许多orm框架,像 EF,Dapper,Hibernate,ServiceStack.OrmLite 等。当然每种orm都有各自的优势,也有不足的地方。园子里也有很多大神开源了他们写的orm,如SqlSugar,Chloe.ORM,CYQ.Data 等。先不说这些开源的orm使用度怎么样,我觉得起码从开源的精神上就很可嘉了,我也曾下载过这几位大神的源码进行学习。
      所有orm最终体现的一点就是方便,减少程序员重复性的工作,当然目前还有一些公司还是使用手写sql的方式来做,我觉得整个项目都使用手写sql来做真的是有点闲到蛋疼,并不是不推荐手写sql的方式,只是个人觉得最基本的增删改查这些都手写的话,那其实考验不是能力,而是耐力。有人说手写sql的方式可控性强,性能高,我想说的是orm也能做到,关键是你怎么去使用。
      orm的优点非常明显,开发便捷,但或许也是由于这个优点,让很多偷懒的程序员也会渐渐忘了sql语句的写法,我遇到过很多的程序员朋友用了EF后,手写sql,视图、存储过程这些都不想用了,我个人觉手写sql这种还是必要的。不然某一天你看到别人的程序里面写着 “exec xxxx”,你就会突然觉得“啊,好像在哪里见过.....”。所以我想说的是“该出手时还是得出手"。

    浅谈Entity Framework

      Entity Framework 是微软家的orm框架,随着 Entity Framework 不断的完善强化,目前相信使用的比例相对其他的orm来说还是较高的。像我目前使用的最多的就是EF和Dapper。确实,EF用起来开发过程中会方便很多,毕竟EF走过了这么年头,无论是成熟度,还是性能等都提高了很多,也有很多开发者为EF提供了扩展功能,如entity framework extended 等。而且作为.net开发者来说项目通用性也很强,资料也多,微软在这块的更新力度也很给力。不过之前刚出的EF Core也存在一些坑,毕竟还是初期阶段,相信现在后面会越来越好的。

      Entity Framework  提供了三种开发模式,code first,db first,model first。目前用的最多的就属code first了。至于这三种模式的简单使用和区别,大家可以参考下这篇文章

      我曾听一些朋友说过说EF使用起来性能很差,生成的sql语句很难看等。我觉得说这种话之前还是先检查下代码或者多看下一些EF文章吧,要先确保自己没给自己挖坑,然后才能指责别人的不好。如果真心觉得EF或者其他的orm用起来很不爽,那就自己写一个吧,我也曾经和同事用Dapper扩展一个通用的orm,当时是出于一种学习和使用方便的角度。

    Entity Framework 通用数据层类

      这里提供下 EF 通用数据层父类方法,其实网上也有很多人提供了自己项目中的 EF 通用数据层父类方法,所以这里提供的并不是最优和最好的选择,只能说是可以通用的类,方便大家学习和使用,具体代码如下:

    DbContextFactory DbContext工厂类

    复制代码
        public class DbContextFactory
        {
            public  DbContext GetDbContext()
            {
                string key = typeof(DBContext.DbContextFactory).Name + "XJHDbContext";
                DbContext dbContext = CallContext.GetData(key) as DbContext;
                if (dbContext == null)
                {
                    dbContext = new XJHDbContext();
                    CallContext.SetData(key, dbContext);
                }
                return dbContext;
            }
        }
    复制代码

    DbBase 数据层通用操作类

    复制代码
     public class DbBase
        {
            protected DbContext Db = new DbContextFactory().GetDbContext();
            
            #region 自定义其他方法
    
            /// <summary>
            /// 执行存储过程或自定义sql语句--返回集合(自定义返回类型)
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parms"></param>
            /// <param name="cmdType"></param>
            /// <returns></returns>
            public List<TModel> Query<TModel>(string sql, List<SqlParameter> 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<TModel>(sql, parms.ToArray());
                var enityList = list.ToList();
                return enityList;
            }
    
            /// <summary>
            /// 自定义语句和存储过程的增删改--返回影响的行数
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parms"></param>
            /// <param name="cmdType"></param>
            /// <returns></returns>
            public int Execute(string sql, List<SqlParameter> 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 自定义其他方法
        }
    
        /// <summary>
        /// mssql数据库 数据层 父类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public class DbBase<T> : DbBase where T : class, new()
        {
            #region INSERT
    
            /// <summary>
            /// 新增 实体
            /// </summary>
            /// <param name="model"></param>
            /// <returns></returns>
            public void Insert(T model)
            {
                Db.Set<T>().Add(model);
    
            }
    
            /// <summary>
            /// 普通批量插入
            /// </summary>
            /// <param name="datas"></param>
            public void InsertRange(List<T> datas)
            {
                Db.Set<T>().AddRange(datas);
            }
    
            #endregion INSERT
    
            #region DELETE
    
            /// <summary>
            /// 根据模型删除
            /// </summary>
            /// <param name="model">包含要删除id的对象</param>
            /// <returns></returns>
            public void Delete(T model)
            {
                Db.Set<T>().Attach(model);
                Db.Set<T>().Remove(model);
            }
    
            /// <summary>
            /// 删除
            /// </summary>
            /// <param name="whereLambda"></param>
            public void Delete(Expression<Func<T, bool>> whereLambda)
            {
                Db.Set<T>().Where(whereLambda).Delete();
            }
    
            #endregion DELETE
    
            #region UPDATE
    
            /// <summary>
            /// 单个对象指定列修改
            /// </summary>
            /// <param name="model">要修改的实体对象</param>
            /// <param name="proNames">要修改的 属性 名称</param>
            /// <param name="isProUpdate"></param>
            /// <returns></returns>
            public void Update(T model, List<string> proNames, bool isProUpdate = true)
            {
                //将 对象 添加到 EF中
                Db.Set<T>().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<PropertyInfo> 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);
                    }
                }
            }
    
            /// <summary>
            /// 单个对象修改
            /// </summary>
            /// <param name="model"></param>
            /// <returns></returns>
            public void Update(T model)
            {
                DbEntityEntry entry = Db.Entry<T>(model);
                Db.Set<T>().Attach(model);
                entry.State = EntityState.Modified;
    
            }
    
            /// <summary>
            /// 批量修改
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <param name="updateExpression"></param>
            public void Update(Expression<Func<T, bool>> whereLambda, Expression<Func<T, T>> updateExpression)
            {
                Db.Set<T>().Where(whereLambda).Update(updateExpression);
            }
    
            /// <summary>
            /// 批量修改
            /// </summary>
            /// <param name="models"></param>
            /// <returns></returns>
            public void UpdateAll(List<T> models)
            {
                foreach (var model in models)
                {
                    DbEntityEntry entry = Db.Entry(model);
                    entry.State = EntityState.Modified;
                }
    
    
            }
    
            /// <summary>
            /// 批量统一修改
            /// </summary>
            /// <param name="model">要修改的实体对象</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="modifiedProNames"></param>
            /// <returns></returns>
            public void Update(T model, Expression<Func<T, bool>> whereLambda, params string[] modifiedProNames)
            {
                //查询要修改的数据
                List<T> listModifing = Db.Set<T>().Where(whereLambda).ToList();
                Type t = typeof(T);
                List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
                Dictionary<string, PropertyInfo> dictPros = new Dictionary<string, PropertyInfo>();
                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
    
            /// <summary>
            /// 根据主键查询
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            public T FindById(dynamic id)
            {
                return Db.Set<T>().Find(id);
            }
    
            /// <summary>
            /// 获取默认一条数据,没有则为NULL
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            public T FirstOrDefault(Expression<Func<T, bool>> whereLambda = null)
            {
                if (whereLambda == null)
                {
                    return Db.Set<T>().FirstOrDefault();
                }
                return Db.Set<T>().FirstOrDefault(whereLambda);
            }
    
            /// <summary>
            /// 获取全部数据
            /// </summary>
            /// <returns></returns>
            public List<T> GetAll(string ordering = null)
            {
                return ordering == null
                    ? Db.Set<T>().ToList()
                    : Db.Set<T>().OrderBy(ordering).ToList();
            }
    
            /// <summary>
            /// 带条件查询获取数据
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <param name="ordering"></param>
            /// <returns></returns>
            public List<T> GetAll(Expression<Func<T, bool>> whereLambda, string ordering = null)
            {
                var iQueryable = Db.Set<T>().Where(whereLambda);
                return ordering == null
                    ? iQueryable.ToList()
                    : iQueryable.OrderBy(ordering).ToList();
            }
    
            /// <summary>
            /// 带条件查询获取数据
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            public IQueryable<T> GetAllIQueryable(Expression<Func<T, bool>> whereLambda = null)
            {
                return whereLambda == null ? Db.Set<T>() : Db.Set<T>().Where(whereLambda);
            }
    
            /// <summary>
            /// 获取数量
            /// </summary>
            /// <param name="whereLambd"></param>
            /// <returns></returns>
            public int GetCount(Expression<Func<T, bool>> whereLambd = null)
            {
                return whereLambd == null ? Db.Set<T>().Count() : Db.Set<T>().Where(whereLambd).Count();
            }
    
            /// <summary>
            /// 判断对象是否存在
            /// </summary>
            /// <param name="whereLambd"></param>
            /// <returns></returns>
            public bool Any(Expression<Func<T, bool>> whereLambd)
            {
                return Db.Set<T>().Where(whereLambd).Any();
            }
    
            /// <summary>
            /// 分页查询
            /// </summary>
            /// <param name="pageIndex">当前页码</param>
            /// <param name="pageSize">每页大小</param>
            /// <param name="rows">总条数</param>
            /// <param name="orderBy">排序条件(一定要有)</param>
            /// <param name="whereLambda">查询添加(可有,可无)</param>
            /// <param name="isOrder">是否是Order排序</param>
            /// <returns></returns>
            public List<T> Page<TKey>(int pageIndex, int pageSize, out int rows, Expression<Func<T, TKey>> orderBy, Expression<Func<T, bool>> whereLambda = null, bool isOrder = true)
            {
                IQueryable<T> data = isOrder ?
                    Db.Set<T>().OrderBy(orderBy) :
                    Db.Set<T>().OrderByDescending(orderBy);
    
                if (whereLambda != null)
                {
                    data = data.Where(whereLambda);
                }
                rows = data.Count();
                return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
            }
    
            /// <summary>
            /// 分页查询
            /// </summary>
            /// <param name="pageIndex">当前页码</param>
            /// <param name="pageSize">每页大小</param>
            /// <param name="rows">总条数</param>
            /// <param name="ordering">排序条件(一定要有)</param>
            /// <param name="whereLambda">查询添加(可有,可无)</param>
            /// <returns></returns>
            public List<T> Page(int pageIndex, int pageSize, out int rows, string ordering, Expression<Func<T, bool>> whereLambda = null)
            {
                // 分页 一定注意: Skip 之前一定要 OrderBy
                var data = Db.Set<T>().OrderBy(ordering);
                if (whereLambda != null)
                {
                    data = data.Where(whereLambda);
                }
                rows = data.Count();
                return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
            }
    
            /// <summary>
            /// 查询转换
            /// </summary>
            /// <typeparam name="TDto"></typeparam>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            public List<TDto> Select<TDto>(Expression<Func<T, bool>> whereLambda)
            {
                return Db.Set<T>().Where(whereLambda).Select<TDto>().ToList();
            }
    
            #endregion SELECT
    
            #region ORTHER
    
            /// <summary>
            /// 执行存储过程或自定义sql语句--返回集合
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parms"></param>
            /// <param name="cmdType"></param>
            /// <returns></returns>
            public List<T> Query(string sql, List<SqlParameter> parms, CommandType cmdType = CommandType.Text)
            {
                return Query<T>(sql, parms, cmdType);
            }
    
            /// <summary>
            /// 提交保存
            /// </summary>
            /// <returns></returns>
            public int SaveChanges()
            {
                return Db.SaveChanges();
            }
    
            /// <summary>
            /// 回滚
            /// </summary>
            public void RollBackChanges()
            {
                var items = Db.ChangeTracker.Entries().ToList();
                items.ForEach(o => o.State = EntityState.Unchanged);
            }
    
            #endregion ORTHER
    
        }
    复制代码

    扩展类,实现读写分离

      上面的通用类是比较基础简单通用的,适合于单库读写操作。对于EF实现读写分离,之前网上找过类似的参考文章,很多人文章都是使用 DbCommandInterceptor拦截器 来实现,具体的做法是通过拦截到sql语句,然后根据具体条件去判断是走主库还是从库。这种做法不是不行,只是个人感觉不是很好扩展,而且要在拦截器里面做限制判断。

      其实说白了EF本身就是一个读写分离的orm。用过EF的人知道,EF提供访问数据库的是 DbContext 这个对象,所以想实现读写分离的就很简单了,只要在程序中使用两个不同的DbContext对象,一个负责读,一个负责写就好了。

      所以在上面提供的通用封装类中稍微做下修改,修改如下DbContextFactory中获取DbContext的方法,实现一个读的DbContext和一个写的DbContext对象的获取。

      这里要注意下,对于读的DbContext来说,要做下设置
      1.使用 Database.SetInitializer(new NullDatabaseInitializer<ReadDbContext>()); 改变ef初始化策略,因为一般我们使用读写分离的话从库都是同步于主库的,所以不使用ef的自动创建数据库策略。
      2.重写 SaveChanges 方法,对应从库来说,只提供读取的功能,所以防止误操作,这里禁用掉SaveChanges方法,一般需要使用从读的保存方法,就对外抛出异常。

      代码如下:

    支持读写分离的 DbContextFactory 类

    复制代码
      public class DbContextFactory
        {
            public DbContext GetWriteDbContext()
            {
                string key = typeof(DbContextFactory).Name + "WriteDbContext";
                DbContext dbContext = CallContext.GetData(key) as DbContext;
                if (dbContext == null)
                {
                    dbContext = new WriteDbContext();
                    CallContext.SetData(key, dbContext);
                }
                return dbContext;
            }
    
            public DbContext GetReadDbContext()
            {
                string key = typeof(DbContextFactory).Name + "ReadDbContext";
                DbContext dbContext = CallContext.GetData(key) as DbContext;
                if (dbContext == null)
                {
                    dbContext = new ReadDbContext();
                    CallContext.SetData(key, dbContext);
                }
                return dbContext;
            }
        }
    复制代码

       对应的 DbBase 类也做下修改,主要将上面的Db对象改作 MasterDb  SlaveDb 对象,并且把上面的读写方法坐下调整,修改后如下:

    支持读写分离的 DbBase类

    复制代码
    public class DbBase
        {
            //是否读写分离(可以配置在配置文件中)
            private static readonly bool IsReadWriteSeparation = true;
    
            #region EF上下文对象(主库)
    
            protected DbContext MasterDb => _masterDb.Value;
            private readonly Lazy<DbContext> _masterDb = new Lazy<DbContext>(() => new DbContextFactory().GetWriteDbContext());
    
            #endregion EF上下文对象(主库)
    
            #region EF上下文对象(从库)
    
            protected DbContext SlaveDb => IsReadWriteSeparation ? _slaveDb.Value : _masterDb.Value;
            private readonly Lazy<DbContext> _slaveDb = new Lazy<DbContext>(() => new DbContextFactory().GetReadDbContext());
    
            #endregion EF上下文对象(从库)
    
            #region 自定义其他方法
    
            /// <summary>
            /// 执行存储过程或自定义sql语句--返回集合(自定义返回类型)
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parms"></param>
            /// <param name="cmdType"></param>
            /// <returns></returns>
            public List<TModel> Query<TModel>(string sql, List<SqlParameter> 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<TModel>(sql, parms.ToArray());
                var enityList = list.ToList();
                return enityList;
            }
    
            /// <summary>
            /// 自定义语句和存储过程的增删改--返回影响的行数
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parms"></param>
            /// <param name="cmdType"></param>
            /// <returns></returns>
            public int Execute(string sql, List<SqlParameter> 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 自定义其他方法
        }
    
        /// <summary>
        /// mssql数据库 数据层 父类
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public class DbBase<T> : DbBase where T : class, new()
        {
            #region INSERT
    
            /// <summary>
            /// 新增 实体
            /// </summary>
            /// <param name="model"></param>
            /// <returns></returns>
            public void Insert(T model)
            {
                MasterDb.Set<T>().Add(model);
            }
    
            /// <summary>
            /// 普通批量插入
            /// </summary>
            /// <param name="datas"></param>
            public void InsertRange(List<T> datas)
            {
                MasterDb.Set<T>().AddRange(datas);
            }
    
            #endregion INSERT
    
            #region DELETE
    
            /// <summary>
            /// 根据模型删除
            /// </summary>
            /// <param name="model">包含要删除id的对象</param>
            /// <returns></returns>
            public void Delete(T model)
            {
                MasterDb.Set<T>().Attach(model);
                MasterDb.Set<T>().Remove(model);
            }
    
            /// <summary>
            /// 删除
            /// </summary>
            /// <param name="whereLambda"></param>
            public void Delete(Expression<Func<T, bool>> whereLambda)
            {
                MasterDb.Set<T>().Where(whereLambda).Delete();
            }
    
            #endregion DELETE
    
            #region UPDATE
    
            /// <summary>
            /// 单个对象指定列修改
            /// </summary>
            /// <param name="model">要修改的实体对象</param>
            /// <param name="proNames">要修改的 属性 名称</param>
            /// <param name="isProUpdate"></param>
            /// <returns></returns>
            public void Update(T model, List<string> proNames, bool isProUpdate = true)
            {
                //将 对象 添加到 EF中
                MasterDb.Set<T>().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<PropertyInfo> 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);
                    }
                }
            }
    
            /// <summary>
            /// 单个对象修改
            /// </summary>
            /// <param name="model"></param>
            /// <returns></returns>
            public void Update(T model)
            {
                DbEntityEntry entry = MasterDb.Entry<T>(model);
                MasterDb.Set<T>().Attach(model);
                entry.State = EntityState.Modified;
            }
    
            /// <summary>
            /// 批量修改
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <param name="updateExpression"></param>
            public void Update(Expression<Func<T, bool>> whereLambda, Expression<Func<T, T>> updateExpression)
            {
                MasterDb.Set<T>().Where(whereLambda).Update(updateExpression);
            }
    
            /// <summary>
            /// 批量修改
            /// </summary>
            /// <param name="models"></param>
            /// <returns></returns>
            public void UpdateAll(List<T> models)
            {
                foreach (var model in models)
                {
                    DbEntityEntry entry = MasterDb.Entry(model);
                    entry.State = EntityState.Modified;
                }
            }
    
            /// <summary>
            /// 批量统一修改
            /// </summary>
            /// <param name="model">要修改的实体对象</param>
            /// <param name="whereLambda">查询条件</param>
            /// <param name="modifiedProNames"></param>
            /// <returns></returns>
            public void Update(T model, Expression<Func<T, bool>> whereLambda, params string[] modifiedProNames)
            {
                //查询要修改的数据
                List<T> listModifing = MasterDb.Set<T>().Where(whereLambda).ToList();
                Type t = typeof(T);
                List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
                Dictionary<string, PropertyInfo> dictPros = new Dictionary<string, PropertyInfo>();
                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
    
            /// <summary>
            /// 根据主键查询
            /// </summary>
            /// <param name="id"></param>
            /// <returns></returns>
            public T FindById(dynamic id)
            {
                return SlaveDb.Set<T>().Find(id);
            }
    
            /// <summary>
            /// 获取默认一条数据,没有则为NULL
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            public T FirstOrDefault(Expression<Func<T, bool>> whereLambda = null)
            {
                if (whereLambda == null)
                {
                    return SlaveDb.Set<T>().FirstOrDefault();
                }
                return SlaveDb.Set<T>().FirstOrDefault(whereLambda);
            }
    
            /// <summary>
            /// 获取全部数据
            /// </summary>
            /// <returns></returns>
            public List<T> GetAll(string ordering = null)
            {
                return ordering == null
                    ? SlaveDb.Set<T>().ToList()
                    : SlaveDb.Set<T>().OrderBy(ordering).ToList();
            }
    
            /// <summary>
            /// 带条件查询获取数据
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <param name="ordering"></param>
            /// <returns></returns>
            public List<T> GetAll(Expression<Func<T, bool>> whereLambda, string ordering = null)
            {
                var iQueryable = SlaveDb.Set<T>().Where(whereLambda);
                return ordering == null
                    ? iQueryable.ToList()
                    : iQueryable.OrderBy(ordering).ToList();
            }
    
            /// <summary>
            /// 带条件查询获取数据
            /// </summary>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            public IQueryable<T> GetAllIQueryable(Expression<Func<T, bool>> whereLambda = null)
            {
                return whereLambda == null ? SlaveDb.Set<T>() : SlaveDb.Set<T>().Where(whereLambda);
            }
    
            /// <summary>
            /// 获取数量
            /// </summary>
            /// <param name="whereLambd"></param>
            /// <returns></returns>
            public int GetCount(Expression<Func<T, bool>> whereLambd = null)
            {
                return whereLambd == null ? SlaveDb.Set<T>().Count() : SlaveDb.Set<T>().Where(whereLambd).Count();
            }
    
            /// <summary>
            /// 判断对象是否存在
            /// </summary>
            /// <param name="whereLambd"></param>
            /// <returns></returns>
            public bool Any(Expression<Func<T, bool>> whereLambd)
            {
                return SlaveDb.Set<T>().Where(whereLambd).Any();
            }
    
            /// <summary>
            /// 分页查询
            /// </summary>
            /// <param name="pageIndex">当前页码</param>
            /// <param name="pageSize">每页大小</param>
            /// <param name="rows">总条数</param>
            /// <param name="orderBy">排序条件(一定要有)</param>
            /// <param name="whereLambda">查询添加(可有,可无)</param>
            /// <param name="isOrder">是否是Order排序</param>
            /// <returns></returns>
            public List<T> Page<TKey>(int pageIndex, int pageSize, out int rows, Expression<Func<T, TKey>> orderBy, Expression<Func<T, bool>> whereLambda = null, bool isOrder = true)
            {
                IQueryable<T> data = isOrder ?
                    SlaveDb.Set<T>().OrderBy(orderBy) :
                    SlaveDb.Set<T>().OrderByDescending(orderBy);
    
                if (whereLambda != null)
                {
                    data = data.Where(whereLambda);
                }
                rows = data.Count();
                return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
            }
    
            /// <summary>
            /// 分页查询
            /// </summary>
            /// <param name="pageIndex">当前页码</param>
            /// <param name="pageSize">每页大小</param>
            /// <param name="rows">总条数</param>
            /// <param name="ordering">排序条件(一定要有)</param>
            /// <param name="whereLambda">查询添加(可有,可无)</param>
            /// <returns></returns>
            public List<T> Page(int pageIndex, int pageSize, out int rows, string ordering, Expression<Func<T, bool>> whereLambda = null)
            {
                // 分页 一定注意: Skip 之前一定要 OrderBy
                var data = SlaveDb.Set<T>().OrderBy(ordering);
                if (whereLambda != null)
                {
                    data = data.Where(whereLambda);
                }
                rows = data.Count();
                return data.PageBy((pageIndex - 1) * pageSize, pageSize).ToList();
            }
    
            /// <summary>
            /// 查询转换
            /// </summary>
            /// <typeparam name="TDto"></typeparam>
            /// <param name="whereLambda"></param>
            /// <returns></returns>
            public List<TDto> Select<TDto>(Expression<Func<T, bool>> whereLambda)
            {
                return SlaveDb.Set<T>().Where(whereLambda).Select<TDto>().ToList();
            }
    
            #endregion SELECT
    
            #region ORTHER
    
            /// <summary>
            /// 执行存储过程或自定义sql语句--返回集合
            /// </summary>
            /// <param name="sql"></param>
            /// <param name="parms"></param>
            /// <param name="cmdType"></param>
            /// <returns></returns>
            public List<T> Query(string sql, List<SqlParameter> parms, CommandType cmdType = CommandType.Text)
            {
                return Query<T>(sql, parms, cmdType);
            }
    
            /// <summary>
            /// 提交保存
            /// </summary>
            /// <returns></returns>
            public int SaveChanges()
            {
                return MasterDb.SaveChanges();
            }
    
            /// <summary>
            /// 回滚
            /// </summary>
            public void RollBackChanges()
            {
                var items = MasterDb.ChangeTracker.Entries().ToList();
                items.ForEach(o => o.State = EntityState.Unchanged);
            }
    
            #endregion ORTHER
        }
    复制代码

      这样简单的读写分离就实现了,实现逻辑也比较清晰,方便扩展。

    进一步改造,实现多从库读取

      一般做读写分离,都会做一主多从,特别对读取量比较大的项目,这样多库读取就能减轻读库的压力。所以对于上面的方法,做下改造。
      上面可以看到,主库和从库都是通过 DbContextFactory 这个类来获取的,在GetReadDbContext 方法中每次都是获取 ReadDbContext 这个对象。那么对于多个从库的情况下,每次读取到底要去哪个库读取数据呢?这里就是一个算法规则的问题了,或者说是策略吧,如果使用过nginx的朋友就知道,nginx本身内部在实现负载均衡的时候提供了多种策略,比如轮询,加权轮询,ip_hash等策略。其实上面获取同一个ReadDbContext 的方法也算一种策略,叫单一策略,每次都获取单一的对象。

      多从库的情况下,我们简单的来实现另一种获取策略,随机策略,每次都随机获取到一个从库的对象,这种是最简单的策略,当然,正式使用的话大家可以发挥自己的创造力,写出多了的算法策略。

    首先,定义一个策略接口,方便策略的扩展和切换,代码如下:

    IReadDbStrategy 接口

    复制代码
     /// <summary>
      /// 从数据库获取策略接口
      /// </summary>
      public interface IReadDbStrategy
      {
          /// <summary>
          /// 获取读库
          /// </summary>
          /// <returns></returns>
          DbContext GetDbContext();
      }
    复制代码

    单从库情况下,定义一个单一策略,代码如下:

    单一策略

    复制代码
       /// <summary>
       /// 单一策略
       /// </summary>
       public class SingleStrategy : IReadDbStrategy
       {
           public DbContext GetDbContext()
           {
               return new ReadDbContext();
           }
       }
    复制代码

    多从库情况下,定义一个随机策略,代码如下:

    随机策略

    复制代码
        /// <summary>
        /// 随机策略
        /// </summary>
        public class RandomStrategy : IReadDbStrategy
        {
            //所有读库类型
            public static List<Type> DbTypes;
     
            static RandomStrategy()
            {
                LoadDbs();
            }
     
            //加载所有的读库类型
            static void LoadDbs()
            {
                DbTypes = new List<Type>();
                var assembly = Assembly.GetExecutingAssembly();
                var types = assembly.GetTypes();
                foreach (var type in types)
                {
                    if (type.BaseType == typeof(BaseReadDbContext))
                    {
                        DbTypes.Add(type);
                    }
                }
            }
     
            public DbContext GetDbContext()
            {
                int randomIndex = new Random().Next(0, DbTypes.Count);
                var dbType = DbTypes[randomIndex];
                var dbContext = Activator.CreateInstance(dbType) as DbContext;
                return dbContext;
            }
        }
    复制代码

      这样,所有从库我们都基于策略去获取,扩展也比较方便。修改下 DbContextFactory 类的 GetReadDbContext 方法,通过策略接口来获取,代码如下:

    支持一主多从的 DbContextFactory 类

    复制代码
      public class DbContextFactory
        {
            //todo:这里可以自己通过注入的方式来实现,就会更加灵活
            private static readonly IReadDbStrategy ReadDbStrategy = new RandomStrategy();
            public DbContext GetWriteDbContext()
            {
                string key = typeof(DbContextFactory).Name + "WriteDbContext";
                DbContext dbContext = CallContext.GetData(key) as DbContext;
                if (dbContext == null)
                {
                    dbContext = new WriteDbContext();
                    CallContext.SetData(key, dbContext);
                }
                return dbContext;
            }
    
            public DbContext GetReadDbContext()
            {
                string key = typeof(DbContextFactory).Name + "ReadDbContext";
                DbContext dbContext = CallContext.GetData(key) as DbContext;
                if (dbContext == null)
                {
                    dbContext = ReadDbStrategy.GetDbContext();
                    CallContext.SetData(key, dbContext);
                }
                return dbContext;
            }
        }
    复制代码

      这样简单的一主多从也实现了。

    参考文章

      http://www.cnblogs.com/zhaopei/p/5721789.html
      http://www.cnblogs.com/GuZhenYin/p/5482288.html

    源码分享

      所有的代码提供给大家的更多的是一种思路和学习的参考,如果有什么不足的地方也欢迎大家批评指正,如果觉得对你有帮助,不要吝啬你的鼠标,帮忙点个星,点个赞吧。

      源码地址: https://github.com/qq1206676756/EF_DbHelper

    如果你觉得本文对你有帮助,可以在右边随意 打赏 博主 ~\(≧▽≦)/~

    ,,O(_)O~~~~

    作者:最爱晴天
    出处:http://www.cnblogs.com/qtqq/
    本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利。
    展开全文
  • 在项目过程中经常需要使用分页,EF分页基本实现思路是使用DB.OrderBy(c=&gt;c.id).Skip(10).Take(10) 如果你懂一下泛型和委托的用法的话可以自己封装一个分页函数,代码如下: using QYHT.WebUI.Models; ...

    在项目过程中经常需要使用分页,EF分页基本实现思路是使用DB.OrderBy(c=>c.id).Skip(10).Take(10)
    如果你懂一下泛型和委托的用法的话可以自己封装一个分页函数,代码如下:

    using QYHT.WebUI.Models;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Web;
    
    namespace QYHT.WebUI.Common
    {
        public class methods
        {
            /// <summary>  
            /// 分页查询 + 条件查询 + 排序  
            /// </summary>  
            /// <typeparam name="Tkey">泛型</typeparam>  
            /// <param name="pageSize">每页大小</param>  
            /// <param name="pageIndex">当前页码</param>  
            /// <param name="total">总数量</param>  
            /// <param name="whereLambda">查询条件</param>  
            /// <param name="orderbyLambda">排序条件</param>  
            /// <param name="isAsc">是否升序</param>  
            /// <returns>IQueryable 泛型集合</returns>  
            public static IQueryable<T> LoadPageItems<T, TKey>(int pageSize, int pageIndex, out int total, Expression<Func<T, bool>> whereLambda, Expression<Func<T, TKey>> orderbyLambda, bool isAsc) where T : class
            {
                //自己的EF数据上下文
                mk_kktjwEntities mkxx = new mk_kktjwEntities();
                total = mkxx.Set<T>().Where(whereLambda).Count();
                //倒序或升序
                if (isAsc)
                {
                    var temp = mkxx.Set<T>().Where(whereLambda)
                                 .OrderBy<T, TKey>(orderbyLambda)
                                 .Skip(pageSize * (pageIndex - 1))
                                 .Take(pageSize);
                    return temp.AsQueryable();
                }
                else
                {
                    var temp = mkxx.Set<T>().Where(whereLambda)
                               .OrderByDescending<T, TKey>(orderbyLambda)
                               .Skip(pageSize * (pageIndex - 1))
                               .Take(pageSize);
                    return temp.AsQueryable();
                }
            }
        }
    }

    上面代码要注意需要改写自己的EF数据上下文,LoadPageItems

     var orderlist = WeichatManageApiHelper.LoadPageItems<View_Mkxx_OrderStatistics, DateTime>(pageSize, pageIndex, out total, c => c.OrderDate <= end && c.OrderDate >= start, u => u.OrderDate, false).ToList();

    以上就是轻松实现EF中的分页功能

    展开全文
  • public class BaseDal<T> where T:class,new() { //此处生成自己的数据实体对象 Entities Db = new Entities(); //********** /// <summary> /// 添加 ///...
      public class BaseDal<T> where T:class,new()
        {
            //此处生成自己的数据实体对象   
            Entities Db = new Entities();
            //**********
    
            /// <summary>
            /// 添加
            /// </summary>
            /// <param name="entity"></param>
            /// <returns></returns>
            public T AddEntity(T entity)
            {
                Db.Set<T>().Add(entity);
                Db.SaveChanges();
                return entity;
            }
    
            /// <summary>
            /// 删除
            /// </summary>
            /// <param name="entity"></param>
            /// <returns></returns>
            public bool DeleteEntity(T entity)
            {
                Db.Entry<T>(entity).State = System.Data.Entity.EntityState.Deleted;
                return Db.SaveChanges() > 0;
                
            }
    
            /// <summary>
            /// 更新
            /// </summary>
            /// <param name="entity"></param>
            /// <returns></returns>
            public bool EditEntity(T entity)
            {
                Db.Entry<T>(entity).State = System.Data.Entity.EntityState.Modified;
                return Db.SaveChanges() > 0;
                
            }
    
            /// <summary>
            /// 查询
            /// </summary>
            /// <param name="whereLambda">System.Linq.Expressions.Expression<Func<UserInfo, bool>>为参数(lambda表达式树)类型</param>
            /// <returns></returns>
            public IQueryable<T> LoadEntities(Expression<Func<T, bool>> whereLambda)
            {
                return Db.Set<T>().Where<T>(whereLambda);
            }
    
            /// <summary>
            /// 分页
            /// </summary>
            /// <typeparam name="s">方法的泛型类型(由于可参照排序字段数据类型不一样)</typeparam>
            /// <param name="pageIndex">页码</param>
            /// <param name="pageSize">每页展示的数据条数</param>
            /// <param name="totalCount">数据总条数</param>
            /// <param name="whereLambda">用于数据过滤</param>
            /// <param name="orderbyLambda">用于数据排序</param>
            /// <param name="isAsc">判断升序或者降序true升序</param>
            /// <returns></returns>
            public IQueryable<T> LoadPageEntities<s>(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, bool>> whereLambda, Expression<Func<T, s>> orderbyLambda, bool isAsc)
            {
                var temp = Db.Set<T>().Where<T>(whereLambda);
                totalCount = temp.Count();
                if (isAsc)//true升序
                {
                    temp = temp.OrderBy<T, s>(orderbyLambda).Skip<T>((pageIndex - 1) * pageSize).Take<T>(pageSize);
                }
                else
                {
                    temp = temp.OrderBy<T, s>(orderbyLambda).Skip<T>((pageIndex - 1) * pageSize).Take<T>(pageSize);
                }
                return temp;
            }
        }

    以上代码在开发过程中可能会存在生成的实体对象即Entity不唯一的问题,即生成多个对象,会操作出错,因此要考虑实现线程内对象唯一的问题,实现方式在下一篇博客中。

    https://blog.csdn.net/qq_40564078/article/details/103112292

    展开全文
  • 1. Entity Framework是Microsoft的ORM框架,随着 Entity Framework 不断的完善强化已经到达了EF 6.0+ 还是非常的完善的,目前使用的比例相对于其他ORM 的框架还是比较多的。例如有我们目前使用较多的是EF和Drapper ...
  • using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Entity.Infrastructure; using System.Data.Entity; namespace DAL ... public class EF_Help {
  • EF事物处理封装公用

    2019-06-09 15:56:50
    /// <.../// EF事务封装 /// </summary> public class TransactionCommon { DbContextTransaction Transaction = null; /// <summary> /// 数据库连接 /// </summary> pri...
  • 利用EFC#泛型实现通用分页查询 Entity Framework是微软以 ADO.NET 为基础所发展出来的对象关系对应 (ORM) 解决方案,是微软的ORM框架。此框架将数据库中的表信息通过xml与实体类对象相关联,使得开发人员只...
  • 1.本实例是基于.Net Core 1.1,EF Core 1.1的代码实例,仅供参考 2.当前版本的EF Core好像还没有提供模型验证,异常跟踪等 二、实体,自动 生成 三 、接口 1.基础 接口  /// /// 基础访问接口,使用泛型 /...
  • Entity Framework的横空出世确立了其在.net领域官方ORM中的霸主地位,给我们开发者带来了福音,但是使用使用上还是有些不便捷的地方,尤其是在三架构的项目中,在业务不容许出现直接操作ObjectContext 的情况下...
  • 废话不多说 直接上代码 根据时间字段由高到低排序,按照起始索引取前20条数据 List<表名> list = 数据库名.表名.OrderByDescending(c=>c.时间字段).Skip(起始索引).Take(数量).ToList(); ...
  • using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data.Entity.Infrastructure; using System.Data.Entity; namespace DAL ... public cla...
  • 最近在研究DDD,同时也下载了一些基于DDD做的成熟案例用来学习,有一些吧,过于成熟,顺便就从里面取了取别的经,比如这个ByteartRetail项目,里面对数据的操作狠花了我一些时间 展开看看 其实有个问题很...
  • 通用DbContext封装

    2019-06-22 19:51:56
    参考资料:https://www.cnblogs.com/tuousi99/p/4455573.html using System;using System.Data; namespace Manjinba.Dynamics.Domain.Interfaces{ /// <summary> /// /// <... publi...
  • 完整EF实例demo

    2020-07-11 23:30:59
    完整EF实例demo,含增,删,查,改,可运用到各个企业级项目,解压即用。
  • 最近一边参与公司的项目开发,一边还...最近在使用存储过程的时候总觉得有点麻烦,尽管在前期对ORM和统一数据源接口封装已经下了很多功夫,对IDataParameter之类的接口已经进行了很好的封装,但是还是觉得麻烦。[王清
  • 分享一下自己在使用EF时,对增删改查基本操作的封装代码,一般在Biz使用一个基类(因公司风格叫Biz,其实就是DAL)。1.基类代码:使用虚方法子类可以重写。public class BizBase&lt;T&gt; where T : ...
  • 嗯嗯~我找到实习了。 ???你不是学Java的吗?...又来了一波屁话~抱歉~C#架构开发 5架构的构建体系 不学C#的也可以看看,其实我觉得微软在很多应用层面是后来居上的,以维护在开发圈的竞争地位。所以
  • 本项目结合EF 4.3及WCF实现了经典三架构,各层面向接口,WCF实现SOA,Repository封装调用,在此基础上实现了WCFContext,动态服务调用及一个分页的实例。 1. 项目架构图:     2. 项目解决方案...
1 2 3 4 5 ... 20
收藏数 1,018
精华内容 407