第四节:DB支持SQLServer和MySQL及BaseService大数据处理封装、常规封装

一. DB支持MySQL

 参考文章:

  映射指令:https://www.cnblogs.com/yaopengfei/p/11633385.html

  EFCore链接MySQL:https://www.cnblogs.com/yaopengfei/p/11507557.html

  EFCore链接MySQL各种测试:https://www.cnblogs.com/yaopengfei/p/14004719.html

1. 说明

 EFCore链接MySQL数据库主要有两个程序集:【MySql.Data.EntityFrameworkCore 8.0.22】和【Pomelo.EntityFrameworkCore.MySql 3.2.7】

 注:目前框架的配置所有的测试都是基于 Pomelo.EntityFrameworkCore.MySql 3.2.7进行测试的。

2. 步骤

(1). 映射指令

--全局新增

 Scaffold-DbContext "Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456;" Pomelo.EntityFrameworkCore.MySql -OutputDir Entity -Context CoreFrameDBContext -UseDatabaseNames -DataAnnotations

--全局修改

 Scaffold-DbContext "Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456;" Pomelo.EntityFrameworkCore.MySql -OutputDir Entity -Context CoreFrameDBContext -UseDatabaseNames -DataAnnotations  -Force

-- 部分修改

 Scaffold-DbContext "Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456;" Pomelo.EntityFrameworkCore.MySql  -OutputDir Entity -Context CoreFrameDBContext -UseDatabaseNames -Tables T_Test -DataAnnotations  -Force

(2). 其他说明

 (3). SQLServer和MySQL切换

 PS:这里通过简单的策略进行判断注册,后续将Dapper和ADO.Net都集成进来再统一改造。

策略代码:

    /// <summary>
    /// 数据库策略
    /// 不能写在YpfCore.Utils层中,会出现循环引用报错问题
    /// </summary>
    public static class DbStrategyExtensions
    {
        public static IServiceCollection AddDbContextStrategy(this IServiceCollection services, string DBType)
        {
            if (DBType == "MySQL")
            {
                services.AddDbContext<CoreFrameDBContext>(option => option.UseMySql(ConfigHelp.GetString("ConnectionStrings:MySQLStr")), ServiceLifetime.Scoped);
            }
            else
            {
                //默认是SQLServer
                services.AddDbContext<CoreFrameDBContext>(option => option.UseSqlServer(ConfigHelp.GetString("ConnectionStrings:SQLServerStr")), ServiceLifetime.Scoped);
            }
            return services;
        }

    }

 注册代码:

//添加数据库策略(MySQL or SQLServer)
services.AddDbContextStrategy(_Configuration["DBType"]);

配置文件:

  //数据库类型,可选值为:SQLServer 、MySQL
  "DBType": "MySQL",

 

二. 大数据处理封装、常规封装

参考文章:

 性能优化:https://www.cnblogs.com/yaopengfei/p/12194230.html

 性能测试和大数据处理:https://www.cnblogs.com/yaopengfei/p/12194230.html

 MySQL特有处理:https://www.cnblogs.com/yaopengfei/p/14004719.html

1. 大数据处理 

 (1). 基于【EFCore.BulkExtensions】进行处理:通过Nuget给YpfCore.Service、YpfCore.AdminWeb层添加对该程序集的引用,仅支持SQLServer 和 SQLite

 (2). 基于【Z.EntityFramework.Plus.EFCore 3.1.2】进行处理: 通过Nuget给YpfCore.Service、YpfCore.AdminWeb层添加对该程序集的引用,免费开源,支持MySQL、SQLServer,且目前已经支持EFCore5.x版本了, 但功能有限, 仅支持:Batch Delete、Batch Update. (删除和修改)在BaseSevice层中封装删除和修改的方法(同步和异步)。

代码分享:详见下面常规封装。

2. 常规封装

  基于EFCore的常规封装和上述大数据封装代码如下

IBaseService:

  public interface IBaseService
    {
        /****************************************下面进行方法的封装(同步)***********************************************/
        //1. 直接提交数据库

        #region 01-数据源
        IQueryable<T> Entities<T>() where T : class;

        IQueryable<T> EntitiesNoTrack<T>() where T : class;

        #endregion

        #region 02-新增
        int Add<T>(T model) where T : class;

        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model">需要删除的实体</param>
        /// <returns></returns>
        int Del<T>(T model) where T : class;

        #endregion

        #region 04-根据条件删除(支持批量删除)
        /// <summary>
        /// 根据条件删除(支持批量删除)
        /// </summary>
        /// <param name="delWhere">传入Lambda表达式(生成表达式目录树)</param>
        /// <returns></returns>
        int DelBy<T>(Expression<Func<T, bool>> delWhere) where T : class;

        #endregion

        #region 05-单实体修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">修改后的实体</param>
        /// <returns></returns>
        int Modify<T>(T model) where T : class;

        #endregion

        #region 06-批量修改(非lambda)
        /// <summary>
        /// 批量修改(非lambda)
        /// </summary>
        /// <param name="model">要修改实体中 修改后的属性 </param>
        /// <param name="whereLambda">查询实体的条件</param>
        /// <param name="proNames">lambda的形式表示要修改的实体属性名</param>
        /// <returns></returns>
        int ModifyBy<T>(T model, Expression<Func<T, bool>> whereLambda, params string[] proNames) where T : class;

        #endregion

        #region 07-根据条件查询
        /// <summary>
        /// 根据条件查询
        /// </summary>
        /// <param name="whereLambda">查询条件(lambda表达式的形式生成表达式目录树)</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        List<T> GetListBy<T>(Expression<Func<T, bool>> whereLambda, bool isTrack = true) where T : class;

        #endregion

        #region 08-根据条件排序和查询
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        List<T> GetListBy<T, Tkey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class;

        #endregion

        #region 09-分页查询(根据Lambda排序)
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        List<T> GetPageList<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class;

        #endregion

        #region 10-分页查询(根据名称排序)
        /// <summary>
        /// 分页查询输出总行数(根据名称排序)
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="rowCount">输出的总数量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">asc 或 desc</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        List<T> GetPageListByName<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, string sortName, string sortDirection, bool isTrack = true) where T : class;

        #endregion

        #region 11-分页查询输出总行数(根据Lambda排序)
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        List<T> GetPageList<T, Tkey>(int pageIndex, int pageSize, out int rowCount, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class;

        #endregion

        #region 12-分页查询输出总行数(根据名称排序)
        /// <summary>
        /// 分页查询输出总行数(根据名称排序)
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="rowCount">输出的总数量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">asc 或 desc</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        List<T> GetPageListByName<T>(int pageIndex, int pageSize, out int rowCount, Expression<Func<T, bool>> whereLambda, string sortName, string sortDirection, bool isTrack = true) where T : class;

        #endregion





        //2. SaveChange剥离出来,处理事务

        #region 01-批量处理SaveChange()
        /// <summary>
        /// 事务批量处理
        /// </summary>
        /// <returns></returns>
        int SaveChange();

        #endregion

        #region 02-新增
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model">需要新增的实体</param>
        void AddNo<T>(T model) where T : class;

        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model">需要删除的实体</param>
        void DelNo<T>(T model) where T : class;

        #endregion

        #region 04-根据条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        void DelByNo<T>(Expression<Func<T, bool>> delWhere) where T : class;

        #endregion

        #region 05-修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">修改后的实体</param>
        void ModifyNo<T>(T model) where T : class;

        #endregion


        //3. EF调用sql语句

        #region 01-执行增加,删除,修改操作(或调用存储过程)
        /// <summary>
        /// 执行增加,删除,修改操作(或调用存储过程)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        int ExecuteSql(string sql, params SqlParameter[] pars);


        #endregion

        #region 02-执行查询操作
        /// <summary>
        /// 执行查询操作
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        List<T> ExecuteQuery<T>(string sql, bool isTrack = true, params SqlParameter[] pars) where T : class;

        #endregion

        #region 03-执行查询操作(与Linq相结合)
        /// <summary>
        /// 执行查询操作
        /// 注:查询必须返回实体的所有属性字段;结果集中列名必须与属性映射的项目匹配;查询中不能包含关联数据
        /// 除Select以外其他的SQL语句无法执行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        ///  <param name="whereLambda">查询条件</param>
        /// <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <param name="pars"></param>
        /// <returns></returns>
        List<T> ExecuteQueryWhere<T>(string sql, Expression<Func<T, bool>> whereLambda, bool isTrack = true, params SqlParameter[] pars) where T : class;

        #endregion



        /****************************************下面进行方法的封装(异步)***********************************************/
        //1. 直接提交数据库

        #region 01-新增
        Task<int> AddAsync<T>(T model) where T : class;

        #endregion

        #region 02-删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model">需要删除的实体</param>
        /// <returns></returns>
        Task<int> DelAsync<T>(T model) where T : class;

        #endregion

        #region 03-根据条件删除(支持批量删除)
        /// <summary>
        /// 根据条件删除(支持批量删除)
        /// </summary>
        /// <param name="delWhere">传入Lambda表达式(生成表达式目录树)</param>
        /// <returns></returns>
        Task<int> DelByAsync<T>(Expression<Func<T, bool>> delWhere) where T : class;

        #endregion

        #region 04-单实体修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">修改后的实体</param>
        /// <returns></returns>
        Task<int> ModifyAsync<T>(T model) where T : class;

        #endregion

        #region 05-批量修改(非lambda)
        /// <summary>
        /// 批量修改(非lambda)
        /// </summary>
        /// <param name="model">要修改实体中 修改后的属性 </param>
        /// <param name="whereLambda">查询实体的条件</param>
        /// <param name="proNames">lambda的形式表示要修改的实体属性名</param>
        /// <returns></returns>
        Task<int> ModifyByAsync<T>(T model, Expression<Func<T, bool>> whereLambda, params string[] proNames) where T : class;

        #endregion

        #region 06-根据条件查询
        /// <summary>
        /// 根据条件查询
        /// </summary>
        /// <param name="whereLambda">查询条件(lambda表达式的形式生成表达式目录树)</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        Task<List<T>> GetListByAsync<T>(Expression<Func<T, bool>> whereLambda, bool isTrack = true) where T : class;

        #endregion

        #region 07-根据条件排序和查询
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        Task<List<T>> GetListByAsync<T, Tkey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class;

        #endregion

        #region 08-分页查询(根据Lambda排序)
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        Task<List<T>> GetPageListAsync<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class;

        #endregion

        #region 09-分页查询(根据名称排序)
        /// <summary>
        /// 分页查询输出总行数(根据名称排序)
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="rowCount">输出的总数量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">asc 或 desc</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        Task<List<T>> GetPageListByNameAsync<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, string sortName, string sortDirection, bool isTrack = true) where T : class;

        #endregion




        //2. SaveChange剥离出来,处理事务

        #region 01-批量处理SaveChange()
        /// <summary>
        /// 事务批量处理
        /// </summary>
        /// <returns></returns>
        Task<int> SaveChangeAsync();

        #endregion

        #region 02-新增
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model">需要新增的实体</param>
        Task<EntityEntry<T>> AddNoAsync<T>(T model) where T : class;

        #endregion

        #region 03-根据条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        Task DelByNoAsync<T>(Expression<Func<T, bool>> delWhere) where T : class;

        #endregion


        //3. EF调用sql语句

        #region 01-执行增加,删除,修改操作(或调用存储过程)
        /// <summary>
        /// 执行增加,删除,修改操作(或调用存储过程)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        Task<int> ExecuteSqlAsync(string sql, params SqlParameter[] pars);


        #endregion



        /****************************************下面是基于【EFCore.BulkExtensions】大数据的处理 (同步)***********************************************/

        #region 01-增加
        /// <summary>
        /// 增加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        void BulkInsert<T>(List<T> list) where T : class;

        #endregion

        #region 02-修改
        /// <summary>
        /// 修改
        /// PS:传入的实体如果不赋值,则更新为null,即传入的实体每个字段都要有值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        void BulkUpdate<T>(List<T> list) where T : class;

        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// PS:传入的list中的实体仅需要主键有值,它是根据主键删除的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        void BulkDelete<T>(List<T> list) where T : class;

        #endregion

        #region 04-条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        int BatchDelete<T>(Expression<Func<T, bool>> delWhere) where T : class;

        #endregion

        #region 05-条件更新1
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        int BatchUpdate<T>(Expression<Func<T, bool>> delWhere, T model) where T : class, new();

        #endregion

        #region 06-条件更新2
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        int BatchUpdate2<T>(Expression<Func<T, bool>> delWhere, Expression<Func<T, T>> modelWhere) where T : class, new();

        #endregion


        /****************************************下面是基于【EFCore.BulkExtensions】大数据的处理 (异步)***********************************************/
        #region 01-增加
        /// <summary>
        /// 增加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        void BulkInsertAsync<T>(List<T> list) where T : class;

        #endregion

        #region 02-修改
        /// <summary>
        /// 修改
        /// PS:传入的实体如果不赋值,则更新为null,即传入的实体每个字段都要有值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        void BulkUpdateAsync<T>(List<T> list) where T : class;

        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// PS:传入的list中的实体仅需要主键有值,它是根据主键删除的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        void BulkDeleteAsync<T>(List<T> list) where T : class;

        #endregion

        #region 04-条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        Task<int> BatchDeleteAsync<T>(Expression<Func<T, bool>> delWhere) where T : class;

        #endregion

        #region 05-条件更新1
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        Task<int> BatchUpdateAsync<T>(Expression<Func<T, bool>> delWhere, T model) where T : class, new();

        #endregion

        #region 06-条件更新2
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        Task<int> BatchUpdate2Async<T>(Expression<Func<T, bool>> delWhere, Expression<Func<T, T>> modelWhere) where T : class, new();

        #endregion


        /****************************************下面是基于【Z.EntityFramework.Plus.EFCore】大数据的处理,仅支持SQLServer和MySQL(同步)***************************************/

        #region 01-批量删除
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="delWhere">删除条件</param>
        /// <param name="batchSize">内部块级处理的大小,不传的话走的是内部默认大小</param>
        int BulkDeleteBy<T>(Expression<Func<T, bool>> delWhere, int batchSize = 0) where T : class;

        #endregion

        #region 02-批量修改
        /// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateWhere">修改条件</param>
        /// <param name="updateData">需要修改成的数据实体,实体中未传递的属性保持不变</param>
        int BulkUpdateBy<T>(Expression<Func<T, bool>> updateWhere, Expression<Func<T, T>> updateData) where T : class;

        #endregion



        /****************************************下面是基于【Z.EntityFramework.Plus.EFCore】大数据的处理,仅支持SQLServer和MySQL(异步)***************************************/

        #region 01-批量删除
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="delWhere">删除条件</param>
        /// <param name="batchSize">内部块级处理的大小,不传的话走的是内部默认大小</param>
        Task<int> BulkDeleteByAsync<T>(Expression<Func<T, bool>> delWhere, int batchSize = 0) where T : class;

        #endregion

        #region 02-批量修改
        /// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateWhere">修改条件</param>
        /// <param name="updateData">需要修改成的数据实体,实体中未传递的属性保持不变</param>
        Task<int> BulkUpdateByAsync<T>(Expression<Func<T, bool>> updateWhere, Expression<Func<T, T>> updateData) where T : class;

        #endregion


    }
View Code

BaseService:

 /// <summary>
    /// 泛型方法,直接注入EF上下文
    /// </summary>
    public class BaseService : IBaseService, ISupport
    {
        public DbContext db;

        /// <summary>
        /// 在使用的时候,自动注入db上下文
        /// </summary>
        /// <param name="db"></param>
        public BaseService(CoreFrameDBContext db)
        {
            this.db = db;

            //关闭全局追踪的代码
            //db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
        }

        /****************************************下面EFCore基础方法的封装(同步)***********************************************/
        //1. 直接提交数据库

        #region 01-数据源
        public IQueryable<T> Entities<T>() where T : class
        {
            return db.Set<T>();
        }

        public IQueryable<T> EntitiesNoTrack<T>() where T : class
        {
            return db.Set<T>().AsNoTracking();
        }

        #endregion

        #region 02-新增
        public int Add<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Added;
            return db.SaveChanges();

        }
        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model">需要删除的实体</param>
        /// <returns></returns>
        public int Del<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Deleted;
            return db.SaveChanges();
        }
        #endregion

        #region 04-根据条件删除(支持批量删除)
        /// <summary>
        /// 根据条件删除(支持批量删除)
        /// </summary>
        /// <param name="delWhere">传入Lambda表达式(生成表达式目录树)</param>
        /// <returns></returns>
        public int DelBy<T>(Expression<Func<T, bool>> delWhere) where T : class
        {
            List<T> listDels = db.Set<T>().Where(delWhere).ToList();
            listDels.ForEach(model =>
            {
                db.Entry(model).State = EntityState.Deleted;
            });
            return db.SaveChanges();
        }
        #endregion

        #region 05-单实体修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">修改后的实体</param>
        /// <returns></returns>
        public int Modify<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Modified;
            return db.SaveChanges();
        }
        #endregion

        #region 06-批量修改(非lambda)
        /// <summary>
        /// 批量修改(非lambda)
        /// </summary>
        /// <param name="model">要修改实体中 修改后的属性 </param>
        /// <param name="whereLambda">查询实体的条件</param>
        /// <param name="proNames">lambda的形式表示要修改的实体属性名</param>
        /// <returns></returns>
        public int ModifyBy<T>(T model, Expression<Func<T, bool>> whereLambda, params string[] proNames) where T : class
        {
            List<T> listModifes = db.Set<T>().Where(whereLambda).ToList();
            Type t = typeof(T);
            List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            Dictionary<string, PropertyInfo> dicPros = new Dictionary<string, PropertyInfo>();
            proInfos.ForEach(p =>
            {
                if (proNames.Contains(p.Name))
                {
                    dicPros.Add(p.Name, p);
                }
            });
            foreach (string proName in proNames)
            {
                if (dicPros.ContainsKey(proName))
                {
                    PropertyInfo proInfo = dicPros[proName];
                    object newValue = proInfo.GetValue(model, null);
                    foreach (T m in listModifes)
                    {
                        proInfo.SetValue(m, newValue, null);
                    }
                }
            }
            return db.SaveChanges();
        }
        #endregion

        #region 07-根据条件查询
        /// <summary>
        /// 根据条件查询
        /// </summary>
        /// <param name="whereLambda">查询条件(lambda表达式的形式生成表达式目录树)</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public List<T> GetListBy<T>(Expression<Func<T, bool>> whereLambda, bool isTrack = true) where T : class
        {
            if (isTrack)
            {
                return db.Set<T>().Where(whereLambda).ToList();
            }
            else
            {
                return db.Set<T>().Where(whereLambda).AsNoTracking().ToList();
            }

        }
        #endregion

        #region 08-根据条件排序和查询
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public List<T> GetListBy<T, Tkey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class
        {
            IQueryable<T> data = null;
            if (isTrack)
            {
                data = db.Set<T>().Where(whereLambda);
            }
            else
            {
                data = db.Set<T>().Where(whereLambda).AsNoTracking();
            }
            if (isAsc)
            {
                data = data.OrderBy(orderLambda);
            }
            else
            {
                data = data.OrderByDescending(orderLambda);
            }
            return data.ToList();
        }
        #endregion

        #region 09-分页查询(根据Lambda排序)
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public List<T> GetPageList<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class
        {

            IQueryable<T> data = null;
            if (isTrack)
            {
                data = db.Set<T>().Where(whereLambda);
            }
            else
            {
                data = db.Set<T>().Where(whereLambda).AsNoTracking();
            }
            if (isAsc)
            {
                data = data.OrderBy(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize);
            }
            else
            {
                data = data.OrderByDescending(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize);
            }
            return data.ToList();
        }
        #endregion

        #region 10-分页查询(根据名称排序)
        /// <summary>
        /// 分页查询输出总行数(根据名称排序)
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="rowCount">输出的总数量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">asc 或 desc</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public List<T> GetPageListByName<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, string sortName, string sortDirection, bool isTrack = true) where T : class
        {
            List<T> list = null;
            if (isTrack)
            {
                list = db.Set<T>().Where(whereLambda).DataSorting(sortName, sortDirection)
                 .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            else
            {
                list = db.Set<T>().Where(whereLambda).AsNoTracking().DataSorting(sortName, sortDirection)
                 .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            return list;
        }
        #endregion

        #region 11-分页查询输出总行数(根据Lambda排序)
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public List<T> GetPageList<T, Tkey>(int pageIndex, int pageSize, out int rowCount, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class
        {
            int count = db.Set<T>().Where(whereLambda).Count();
            IQueryable<T> data = null;
            if (isTrack)
            {
                data = db.Set<T>().Where(whereLambda);
            }
            else
            {
                data = db.Set<T>().Where(whereLambda).AsNoTracking();
            }
            if (isAsc)
            {
                data = data.OrderBy(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize);
            }
            else
            {
                data = data.OrderByDescending(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize);
            }
            rowCount = count;
            return data.ToList();
        }
        #endregion

        #region 12-分页查询输出总行数(根据名称排序)
        /// <summary>
        /// 分页查询输出总行数(根据名称排序)
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="rowCount">输出的总数量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">asc 或 desc</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public List<T> GetPageListByName<T>(int pageIndex, int pageSize, out int rowCount, Expression<Func<T, bool>> whereLambda, string sortName, string sortDirection, bool isTrack = true) where T : class
        {
            int count = 0;
            count = db.Set<T>().Where(whereLambda).Count();
            List<T> list = null;
            if (isTrack)
            {
                list = db.Set<T>().Where(whereLambda).DataSorting(sortName, sortDirection)
                 .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
            }
            else
            {
                list = db.Set<T>().Where(whereLambda).AsNoTracking().DataSorting(sortName, sortDirection)
                   .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();

            }
            rowCount = count;
            return list;
        }
        #endregion


        //2. SaveChange剥离出来,处理事务

        #region 01-批量处理SaveChange()
        /// <summary>
        /// 事务批量处理
        /// </summary>
        /// <returns></returns>
        public int SaveChange()
        {
            return db.SaveChanges();
        }
        #endregion

        #region 02-新增
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model">需要新增的实体</param>
        public void AddNo<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Added;
        }
        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model">需要删除的实体</param>
        public void DelNo<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Deleted;
        }
        #endregion

        #region 04-根据条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        public void DelByNo<T>(Expression<Func<T, bool>> delWhere) where T : class
        {
            List<T> listDels = db.Set<T>().Where(delWhere).ToList();
            listDels.ForEach(model =>
            {
                db.Entry(model).State = EntityState.Deleted;
            });
        }
        #endregion

        #region 05-修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">修改后的实体</param>
        public void ModifyNo<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Modified;
        }
        #endregion


        //3. EF调用sql语句

        #region 01-执行增加,删除,修改操作(或调用相关存储过程)
        /// <summary>
        /// 执行增加,删除,修改操作(或调用存储过程)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public int ExecuteSql(string sql, params SqlParameter[] pars)
        {
            return db.Database.ExecuteSqlRaw(sql, pars);
        }

        #endregion

        #region 02-执行查询操作(调用查询类的存储过程)
        /// <summary>
        /// 执行查询操作
        /// 注:查询必须返回实体的所有属性字段;结果集中列名必须与属性映射的项目匹配;查询中不能包含关联数据
        /// 除Select以外其他的SQL语句无法执行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public List<T> ExecuteQuery<T>(string sql, bool isTrack = true, params SqlParameter[] pars) where T : class
        {
            if (isTrack)
            {
                //表示跟踪状态(默认是跟踪的)
                return db.Set<T>().FromSqlRaw(sql, pars).ToList();
            }
            else
            {
                //表示不跟踪状态
                return db.Set<T>().FromSqlRaw(sql, pars).AsNoTracking().ToList();
            }
        }
        #endregion

        #region 03-执行查询操作(与Linq相结合)
        /// <summary>
        /// 执行查询操作
        /// 注:查询必须返回实体的所有属性字段;结果集中列名必须与属性映射的项目匹配;查询中不能包含关联数据
        /// 除Select以外其他的SQL语句无法执行
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        ///  <param name="whereLambda">查询条件</param>
        /// <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public List<T> ExecuteQueryWhere<T>(string sql, Expression<Func<T, bool>> whereLambda, bool isTrack = true, params SqlParameter[] pars) where T : class
        {
            if (isTrack)
            {
                //表示跟踪状态(默认是跟踪的)
                return db.Set<T>().FromSqlRaw(sql, pars).Where(whereLambda).ToList();
            }
            else
            {
                //表示不跟踪状态
                return db.Set<T>().FromSqlRaw(sql, pars).Where(whereLambda).AsNoTracking().ToList();
            }
        }
        #endregion



        /****************************************下面EFCore基础方法的封装(异步)***********************************************/

        #region 01-新增
        public async Task<int> AddAsync<T>(T model) where T : class
        {
            await db.AddAsync(model);
            return await db.SaveChangesAsync();
        }
        #endregion

        #region 02-删除
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model">需要删除的实体</param>
        /// <returns></returns>
        public async Task<int> DelAsync<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Deleted;
            return await db.SaveChangesAsync();
        }
        #endregion

        #region 03-根据条件删除(支持批量删除)
        /// <summary>
        /// 根据条件删除(支持批量删除)
        /// </summary>
        /// <param name="delWhere">传入Lambda表达式(生成表达式目录树)</param>
        /// <returns></returns>
        public async Task<int> DelByAsync<T>(Expression<Func<T, bool>> delWhere) where T : class
        {
            List<T> listDels = await db.Set<T>().Where(delWhere).ToListAsync();
            listDels.ForEach(model =>
            {
                db.Entry(model).State = EntityState.Deleted;
            });
            return await db.SaveChangesAsync();
        }
        #endregion

        #region 04-单实体修改
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model">修改后的实体</param>
        /// <returns></returns>
        public async Task<int> ModifyAsync<T>(T model) where T : class
        {
            db.Entry(model).State = EntityState.Modified;
            return await db.SaveChangesAsync();
        }
        #endregion

        #region 05-批量修改(非lambda)
        /// <summary>
        /// 批量修改(非lambda)
        /// </summary>
        /// <param name="model">要修改实体中 修改后的属性 </param>
        /// <param name="whereLambda">查询实体的条件</param>
        /// <param name="proNames">lambda的形式表示要修改的实体属性名</param>
        /// <returns></returns>
        public async Task<int> ModifyByAsync<T>(T model, Expression<Func<T, bool>> whereLambda, params string[] proNames) where T : class
        {
            List<T> listModifes = await db.Set<T>().Where(whereLambda).ToListAsync();
            Type t = typeof(T);
            List<PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
            Dictionary<string, PropertyInfo> dicPros = new Dictionary<string, PropertyInfo>();
            proInfos.ForEach(p =>
            {
                if (proNames.Contains(p.Name))
                {
                    dicPros.Add(p.Name, p);
                }
            });
            foreach (string proName in proNames)
            {
                if (dicPros.ContainsKey(proName))
                {
                    PropertyInfo proInfo = dicPros[proName];
                    object newValue = proInfo.GetValue(model, null);
                    foreach (T m in listModifes)
                    {
                        proInfo.SetValue(m, newValue, null);
                    }
                }
            }
            return await db.SaveChangesAsync();
        }
        #endregion

        #region 06-根据条件查询
        /// <summary>
        /// 根据条件查询
        /// </summary>
        /// <param name="whereLambda">查询条件(lambda表达式的形式生成表达式目录树)</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public async Task<List<T>> GetListByAsync<T>(Expression<Func<T, bool>> whereLambda, bool isTrack = true) where T : class
        {
            if (isTrack)
            {
                return await db.Set<T>().Where(whereLambda).ToListAsync();
            }
            else
            {
                return await db.Set<T>().Where(whereLambda).AsNoTracking().ToListAsync();
            }

        }
        #endregion

        #region 07-根据条件排序和查询
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public async Task<List<T>> GetListByAsync<T, Tkey>(Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class
        {
            IQueryable<T> data = null;
            if (isTrack)
            {
                data = db.Set<T>().Where(whereLambda);
            }
            else
            {
                data = db.Set<T>().Where(whereLambda).AsNoTracking();
            }
            if (isAsc)
            {
                data = data.OrderBy(orderLambda);
            }
            else
            {
                data = data.OrderByDescending(orderLambda);
            }
            return await data.ToListAsync();
        }
        #endregion

        #region 08-分页查询(根据Lambda排序)
        /// <summary>
        /// 根据条件排序和查询
        /// </summary>
        /// <typeparam name="Tkey">排序字段类型</typeparam>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageSize">页容量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="orderLambda">排序条件</param>
        /// <param name="isAsc">升序or降序</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public async Task<List<T>> GetPageListAsync<T, Tkey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, Expression<Func<T, Tkey>> orderLambda, bool isAsc = true, bool isTrack = true) where T : class
        {

            IQueryable<T> data = null;
            if (isTrack)
            {
                data = db.Set<T>().Where(whereLambda);
            }
            else
            {
                data = db.Set<T>().Where(whereLambda).AsNoTracking();
            }
            if (isAsc)
            {
                data = data.OrderBy(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize);
            }
            else
            {
                data = data.OrderByDescending(orderLambda).Skip((pageIndex - 1) * pageSize).Take(pageSize);
            }
            return await data.ToListAsync();
        }
        #endregion

        #region 09-分页查询(根据名称排序)
        /// <summary>
        /// 分页查询输出总行数(根据名称排序)
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="rowCount">输出的总数量</param>
        /// <param name="whereLambda">查询条件</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">asc 或 desc</param>
        ///  <param name="isTrack">是否跟踪状态,默认是跟踪的</param>
        /// <returns></returns>
        public async Task<List<T>> GetPageListByNameAsync<T>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, string sortName, string sortDirection, bool isTrack = true) where T : class
        {
            List<T> list = null;
            if (isTrack)
            {
                list = await db.Set<T>().Where(whereLambda).DataSorting(sortName, sortDirection)
                 .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
            }
            else
            {
                list = await db.Set<T>().Where(whereLambda).AsNoTracking().DataSorting(sortName, sortDirection)
                 .Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
            }
            return list;
        }
        #endregion



        //2. SaveChange剥离出来,处理事务

        #region 01-批量处理SaveChange()
        /// <summary>
        /// 事务批量处理
        /// </summary>
        /// <returns></returns>
        public async Task<int> SaveChangeAsync()
        {
            return await db.SaveChangesAsync();
        }
        #endregion

        #region 02-新增
        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model">需要新增的实体</param>
        public async Task<EntityEntry<T>> AddNoAsync<T>(T model) where T : class
        {
            return await db.AddAsync(model);
        }
        #endregion

        #region 03-根据条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        public async Task DelByNoAsync<T>(Expression<Func<T, bool>> delWhere) where T : class
        {
            List<T> listDels = await db.Set<T>().Where(delWhere).ToListAsync();
            listDels.ForEach(model =>
            {
                db.Entry(model).State = EntityState.Deleted;
            });
        }
        #endregion


        //3. EF调用sql语句

        #region 01-执行增加,删除,修改操作(或调用存储过程)
        /// <summary>
        /// 执行增加,删除,修改操作(或调用存储过程)
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pars"></param>
        /// <returns></returns>
        public async Task<int> ExecuteSqlAsync(string sql, params SqlParameter[] pars)
        {
            return await db.Database.ExecuteSqlRawAsync(sql, pars);
        }
        #endregion


        /****************************************下面是基于【EFCore.BulkExtensions】大数据的处理,仅支持SQLServer和SQLite (同步)***********************************************/

        #region 01-增加
        /// <summary>
        /// 增加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public void BulkInsert<T>(List<T> list) where T : class
        {
            db.BulkInsert<T>(list);
        }
        #endregion

        #region 02-修改
        /// <summary>
        /// 修改
        /// PS:传入的实体如果不赋值,则更新为null,即传入的实体每个字段都要有值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public void BulkUpdate<T>(List<T> list) where T : class
        {
            db.BulkUpdate<T>(list);
        }
        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// PS:传入的list中的实体仅需要主键有值,它是根据主键删除的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public void BulkDelete<T>(List<T> list) where T : class
        {
            db.BulkDelete<T>(list);
        }
        #endregion

        #region 04-条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        public int BatchDelete<T>(Expression<Func<T, bool>> delWhere) where T : class
        {
            return db.Set<T>().Where(delWhere).BatchDelete();
        }
        #endregion

        #region 05-条件更新1
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        public int BatchUpdate<T>(Expression<Func<T, bool>> delWhere, T model) where T : class, new()
        {
            return db.Set<T>().Where(delWhere).BatchUpdate(model);
        }
        #endregion

        #region 06-条件更新2
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        public int BatchUpdate2<T>(Expression<Func<T, bool>> delWhere, Expression<Func<T, T>> modelWhere) where T : class, new()
        {
            return db.Set<T>().Where(delWhere).BatchUpdate(modelWhere);
        }
        #endregion


        /****************************************下面是基于【EFCore.BulkExtensions】大数据的处理 仅支持SQLServer和SQLite(异步)***********************************************/

        #region 01-增加
        /// <summary>
        /// 增加
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public async void BulkInsertAsync<T>(List<T> list) where T : class
        {
            await db.BulkInsertAsync<T>(list);
        }
        #endregion

        #region 02-修改
        /// <summary>
        /// 修改
        /// PS:传入的实体如果不赋值,则更新为null,即传入的实体每个字段都要有值
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public async void BulkUpdateAsync<T>(List<T> list) where T : class
        {
            await db.BulkUpdateAsync<T>(list);
        }
        #endregion

        #region 03-删除
        /// <summary>
        /// 删除
        /// PS:传入的list中的实体仅需要主键有值,它是根据主键删除的
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        public async void BulkDeleteAsync<T>(List<T> list) where T : class
        {
            await db.BulkDeleteAsync<T>(list);
        }
        #endregion

        #region 04-条件删除
        /// <summary>
        /// 条件删除
        /// </summary>
        /// <param name="delWhere">需要删除的条件</param>
        public async Task<int> BatchDeleteAsync<T>(Expression<Func<T, bool>> delWhere) where T : class
        {
            return await db.Set<T>().Where(delWhere).BatchDeleteAsync();
        }
        #endregion

        #region 05-条件更新1
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        public async Task<int> BatchUpdateAsync<T>(Expression<Func<T, bool>> delWhere, T model) where T : class, new()
        {
            return await db.Set<T>().Where(delWhere).BatchUpdateAsync(model);
        }
        #endregion

        #region 06-条件更新2
        /// <summary>
        /// 条件更新
        /// PS:要更新哪几个字段,就给传入的实体中的哪几个字段赋值
        /// </summary>
        /// <param name="delWhere">需要更新的条件</param>
        /// <param name="model">更新为的实体</param>
        public async Task<int> BatchUpdate2Async<T>(Expression<Func<T, bool>> delWhere, Expression<Func<T, T>> modelWhere) where T : class, new()
        {
            return await db.Set<T>().Where(delWhere).BatchUpdateAsync(modelWhere);
        }
        #endregion


        /****************************************下面是基于【Z.EntityFramework.Plus.EFCore】大数据的处理,仅支持SQLServer和MySQL(同步)***************************************/

        #region 01-批量删除
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="delWhere">删除条件</param>
        /// <param name="batchSize">内部块级处理的大小,不传的话走的是内部默认大小</param>
        public int BulkDeleteBy<T>(Expression<Func<T, bool>> delWhere, int batchSize = 0) where T : class
        {
            if (batchSize == 0)
            {
                //表示默认值
                return db.Set<T>().Where(delWhere).Delete();
            }
            else
            {
                //表示指定大小
                return db.Set<T>().Where(delWhere).Delete(x => x.BatchSize = batchSize);
            }
        }
        #endregion

        #region 02-批量修改
        /// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateWhere">修改条件</param>
        /// <param name="updateData">需要修改成的数据实体,实体中未传递的属性保持不变</param>
        public int BulkUpdateBy<T>(Expression<Func<T, bool>> updateWhere, Expression<Func<T, T>> updateData) where T : class
        {
            return db.Set<T>().Where(updateWhere).Update(updateData);

        }
        #endregion



        /****************************************下面是基于【Z.EntityFramework.Plus.EFCore】大数据的处理,仅支持SQLServer和MySQL(异步)***************************************/

        #region 01-批量删除
        /// <summary>
        /// 批量删除
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="delWhere">删除条件</param>
        /// <param name="batchSize">内部块级处理的大小,不传的话走的是内部默认大小</param>
        public async Task<int> BulkDeleteByAsync<T>(Expression<Func<T, bool>> delWhere, int batchSize = 0) where T : class
        {
            if (batchSize == 0)
            {
                //表示默认值
                return await db.Set<T>().Where(delWhere).DeleteAsync();
            }
            else
            {
                //表示指定大小
                return await db.Set<T>().Where(delWhere).DeleteAsync(x => x.BatchSize = batchSize);
            }
        }
        #endregion

        #region 02-批量修改
        /// <summary>
        /// 批量修改
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="updateWhere">修改条件</param>
        /// <param name="updateData">需要修改成的数据实体,实体中未传递的属性保持不变</param>
        public async Task<int> BulkUpdateByAsync<T>(Expression<Func<T, bool>> updateWhere, Expression<Func<T, T>> updateData) where T : class
        {
            return await db.Set<T>().Where(updateWhere).UpdateAsync(updateData);

        }
        #endregion



    }
View Code

3. 测试 

代码如下:(详细的测试见开篇的参考链接)

{
                //1. 增加
                //List<T_SysErrorLog> list1 = new List<T_SysErrorLog>();
                //for (int i = 0; i < 10000; i++)
                //{
                //    T_SysErrorLog model = new T_SysErrorLog()
                //    {
                //        id = i.ToString(),
                //        userId = i.ToString(),
                //        addTime = DateTime.Now
                //    };
                //    list1.Add(model);
                //}
                //_baseService.BulkInsert(list1);

                //2. 修改 (传入那个字段,更新哪个,不传则会把原先有值的字段改为null)
                //List<T_SysErrorLog> list2 = new List<T_SysErrorLog>();
                //for (int i = 0; i < 10000; i++)
                //{
                //    T_SysErrorLog model = new T_SysErrorLog()
                //    {
                //        id = i.ToString(),
                //        userId = i.ToString(),
                //        //addTime = DateTime.Now,
                //        logLevel="1",
                //        logMessage ="12345",
                //        delFlag = 1
                //    };
                //    list2.Add(model);
                //}
                //_baseService.BulkUpdate(list2);

                //3.删除
                //List<T_SysErrorLog> list3 = new List<T_SysErrorLog>();
                //for (int i = 0; i < 1000; i++)
                //{
                //    T_SysErrorLog model = new T_SysErrorLog()
                //    {
                //        id = i.ToString()
                //    };
                //    list3.Add(model);
                //}
                //_baseService.BulkDelete(list3);

                //4. 条件更新
                //4.1 改为新值
                //int count1 = _baseService.BatchUpdate<T_SysErrorLog>(u => u.id.Contains("2"), new T_SysErrorLog() { userAccount = "001" });
                //4.2 在原值的基础上改
                //int count2 = _baseService.BatchUpdate2<T_SysErrorLog>(u => u.id.Contains("3"), a => new T_SysErrorLog() { userId = a.userId + "fk" });

                //5. 条件删除
                //int count3 = _baseService.BatchDelete<T_SysErrorLog>(u => u.userId.StartsWith("4"));

                //6. 测试事务
                //_test1Service.TestTrans();


            }
View Code

 

 

 

!

  • 作       者 : Yaopengfei(姚鹏飞)
  • 博客地址 : http://www.cnblogs.com/yaopengfei/
  • 声     明1 : 如有错误,欢迎讨论,请勿谩骂^_^。
  • 声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址,否则保留追究法律责任的权利。
 
posted @ 2021-01-13 20:55  Yaopengfei  阅读(109)  评论(1编辑  收藏