第十九节：EFCore5.0基于【Pomelo.EntityFrameworkCore.MySql】和【MySql.EntityFrameworkCore】连接MySQL各场景测试

一. 说明

1. 环境准备

(1).测试载体：.Net 5.0  控制台

(2).数据库：MySQL 5.7 (事先改为大小写敏感 lower_case_table_names=0)

(3).EFCore相关程序集：（这里统一用5.0.6 版本）

　Microsoft.EntityFrameworkCore、Microsoft.EntityFrameworkCore.Design、Microsoft.EntityFrameworkCore.Tools

(4).MySQL相关程序集：

　A. MySQL官方出品：MySql.EntityFrameworkCore （5.0.3.1）                           

（旧版的MySql.Data.EntityFrameworkCore 8.0.22 官方不再维护）

　B. 社区出品：Pomelo.EntityFrameworkCore.MySql (版本：5.0.0)                     【github地址：https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql】

2. 配置日志

　为了便于调优，配置日志，把linq转换成SQL输出来.

　安装程序集：【Microsoft.Extensions.Logging.Console】

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
            optionsBuilder.UseLoggerFactory(LoggerFactory.Create(build =>
            {
                build.AddConsole();  // 用于控制台程序的输出
            }));
  }

3. DB准备

　这里为了配合框架,使用的CoreFrameDB数据库，详见数据库说明书，并且新增一张T_Test，用于演示MySQL各种数据类型的映射.

 

 

 

二. 映射剖析

1. 映射指令测试

　A.首次映射：详见指令文档

　B.修改后映射：-Force，会把上下午和全部实体的变化都修改一下

　C.局部映射： -tables，根据情况选择使用,他会把上下文中已经多余的关联实体关系删掉,仅保留 -tables后面表, 多余的实体并不修改, 谨慎使用。

　D. 不保留复数： -NoPluralize

指令分享：

(1). Pomelo.EntityFrameworkCore.MySql

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

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

-- 部分修改，没有上面bug
Scaffold-DbContext "Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456;" Pomelo.EntityFrameworkCore.MySql  -OutputDir Entity -Context CoreFrameDBContext -UseDatabaseNames -Tables T_Test -DataAnnotations  -Force -NoPluralize

(2). MySql.EntityFrameworkCore

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

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

-- 部分修改，没有上面bug
Scaffold-DbContext "Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456;" MySql.EntityFrameworkCore -OutputDir Entity -Context CoreFrameDBContext -UseDatabaseNames -Tables T_Test -DataAnnotations  -Force -NoPluralize

映射上下文和部分实体分享：

(1). Pomelo.EntityFrameworkCore.MySql

       protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(LoggerFactory.Create(build =>
            {
                build.AddConsole();  // 用于控制台程序的输出
            }));

            optionsBuilder.UseMySql("server=xxxxx;database=CoreFrameDB;user id=root;password=123456", Microsoft.EntityFrameworkCore.ServerVersion.Parse("5.7.28-mysql"));
        }

(2). MySql.EntityFrameworkCore

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(LoggerFactory.Create(build =>
            {
                build.AddConsole();  // 用于控制台程序的输出
            }));

            optionsBuilder.UseMySQL("Server=xxxx;Database=CoreFrameDB;User ID=root;Password=123456;");
        }

注：mysql中的 timestamp类型对应C#中的是DateTime类型，但是EFCore使用【MySql.EntityFrameworkCore】无法反向生成，使用【Pomelo.EntityFrameworkCore.MySql】可以反向生成。

　　针对无法映射生成的类型，会自动跳过，这个时候如果使用的是【MySql.EntityFrameworkCore】映射，需要自己根据类型手动加上即可。

2.数据类型对应测试

3.补充EFCore数据注解属性

　参考：https://docs.microsoft.com/zh-cn/ef/core/modeling/

　　　   https://www.cnblogs.com/yaopengfei/p/7813120.html

　部分特性如下： [NotMapped]、 [Required]、 [Column("blog_id")]、 [Key]、 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]、 [ConcurrencyCheck]、 [Timestamp]。

PS：关于数据注解后续再EFCore系列中单独写一个章节进行测试。

 

三. 实战测试1

前言：

　以下测试都基于【Pomelo.EntityFrameworkCore.MySql】来进行，连接字符串如下：

optionsBuilder.UseMySql("server=xxxx;database=CoreFrameDB;user id=root;password=xxxx", Microsoft.EntityFrameworkCore.ServerVersion.Parse("5.7.28-mysql"));

1. 基础CRUD(+封装)

 　经测试，均可正常使用。

封装方法：

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

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

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

        /****************************************下面进行方法的封装（同步）***********************************************/
        //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



        /****************************************下面进行方法的封装（异步）***********************************************/

        #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】大数据的处理 （同步）***********************************************/

        #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】大数据的处理 （异步）***********************************************/

        #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

    }
 /// <summary>
    /// 排序的扩展
    /// </summary>
    public static class SortExtension
    {

        #region 01-根据string名称排序扩展(单字段)
        /// <summary>
        /// 根据string名称排序扩展(单字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source">排序数据源</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">排序方式 asc或desc</param>
        /// <returns></returns>
        public static IQueryable<T> DataSorting<T>(this IQueryable<T> source, string sortName, string sortDirection)
        {
            string sortingDir = string.Empty;
            if (sortDirection.ToUpper().Trim() == "ASC")
            {
                sortingDir = "OrderBy";
            }
            else if (sortDirection.ToUpper().Trim() == "DESC")
            {
                sortingDir = "OrderByDescending";
            }
            ParameterExpression param = Expression.Parameter(typeof(T), sortName);
            PropertyInfo pi = typeof(T).GetProperty(sortName);
            Type[] types = new Type[2];
            types[0] = typeof(T);
            types[1] = pi.PropertyType;
            Expression expr = Expression.Call(typeof(Queryable), sortingDir, types, source.Expression, Expression.Lambda(Expression.Property(param, sortName), param));
            IQueryable<T> query = source.AsQueryable().Provider.CreateQuery<T>(expr);
            return query;
        }
        #endregion

        #region 02-根据多个string名称排序扩展(多字段)
        /// <summary>
        ///  根据多个string名称排序扩展(多字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data">数据源</param>
        /// <param name="orderParams">排序类</param>
        /// <returns></returns>
        public static IQueryable<T> DataManySorting<T>(this IQueryable<T> data, params FiledOrderParam[] orderParams) where T : class
        {
            var parameter = Expression.Parameter(typeof(T), "p");
            if (orderParams != null && orderParams.Length > 0)
            {
                for (int i = 0; i < orderParams.Length; i++)
                {
                    var property = typeof(T).GetProperty(orderParams[i].PropertyName);
                    if (property != null)
                    {
                        var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                        var orderByExpr = Expression.Lambda(propertyAccess, parameter);
                        string methodName = i > 0 ?
                            orderParams[i].IsDesc ? "ThenByDescending" : "ThenBy"
                            : orderParams[i].IsDesc ? "OrderByDescending" : "OrderBy";
                        var resultExp = Expression.Call(
                            typeof(Queryable), methodName,
                            new Type[] { typeof(T), property.PropertyType },
                            data.Expression, Expression.Quote(orderByExpr)
                            );
                        data = data.Provider.CreateQuery<T>(resultExp);
                    }
                }
            }
            return data;
        }

        #endregion
    }


    /// <summary>
    /// 排序类
    /// </summary>
    public class FiledOrderParam
    {
        //是否降序
        public bool IsDesc { get; set; }
        //排序名称
        public string PropertyName { get; set; }
    }

测试代码： 

               #region 01-增加
                {
                    //T_Test tTest = new T_Test()
                    //{
                    //    age1 = 1,
                    //    age2 = 2,
                    //    age3 = 3,
                    //    age4 = 4,
                    //    age5 = 5,
                    //    money1 = (float)10.12,
                    //    money2 = 20.34,
                    //    money3 = (decimal)12.13,
                    //    addTime1 = 2020,
                    //    addTime2 = DateTime.Now,
                    //    addTime3 = TimeSpan.FromMinutes(10),
                    //    addTime4 = DateTime.Now,
                    //    addTime5 = DateTime.Now,
                    //    name1 = "ypf1",
                    //    name2 = "ypf2",
                    //    name3 = "ypf3",
                    //    name4 = "ypf4",
                    //    name5 = "ypf5",
                    //    name6 = "ypf6",
                    //    isSex1 = 1
                    //};
                    //int count1 = baseService.Add(tTest);

                    //T_SysErrorLog sErrorLog = new T_SysErrorLog();
                    //sErrorLog.id = Guid.NewGuid().ToString("N");
                    //sErrorLog.userId = "001";
                    //sErrorLog.userAccount = "12345";
                    //sErrorLog.logLevel = "Error";
                    //sErrorLog.logMessage = "出错了";
                    //sErrorLog.addTime = DateTime.Now;
                    //sErrorLog.delFlag = 0;
                    //int count2 = baseService.Add(sErrorLog);
                }
                #endregion

                #region 02-修改
                //{
                //    var data = baseService.Entities<T_SysErrorLog>().Where(u => u.id == "1").FirstOrDefault();
                //    data.userAccount = "123456";
                //    baseService.SaveChange();
                //    Console.WriteLine("修改成功");
                //}
                #endregion

                #region 03-删除
                //{
                //    baseService.DelBy<T_SysErrorLog>(u => u.id != "1");
                //    Console.WriteLine("删除成功");
                //}
                #endregion

                #region 04-根据条件查询和排序
                //{
                //    var list = baseService.GetListBy<T_SysErrorLog, DateTime?>(u => u.id != "xxx", p => p.addTime, false);
                //    foreach (var item in list)
                //    {
                //        Console.WriteLine($"id={item.id},userId={item.userId},userAccount={item.userAccount},addTime={item.addTime}");
                //    }

                //} 
                #endregion

                #region 05-根据字段名称升/降序分页查询
                //{
                //    int pageIndex = 1;
                //    int pageSize = 2;
                //    //1.分开写法
                //    var list1 = baseService.Entities<T_SysErrorLog>().Where(u => u.id != "fk").DataSorting("addTime", "desc").Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                //    //2. 封装调用
                //    int count = 0;
                //    var list2 = baseService.GetPageListByName<T_SysErrorLog>(pageIndex, pageSize, out count, u => u.id != "fk", "addTime", "desc");

                //    //3.多字段排序
                //    FiledOrderParam[] param = {
                //        new FiledOrderParam(){IsDesc=false,PropertyName="addTime"},
                //        new FiledOrderParam(){IsDesc=true,PropertyName="id"}
                //    };
                //    var list3 = baseService.Entities<T_SysErrorLog>().Where(u => u.id != "fk").DataManySorting(param).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                //}
                #endregion

2. 各种Linq测试

　详细测试了各种linq对应MySQL的翻译，代码如下：

                #region 01-First/Last/Single/ElementAt
                //{
                //    //说明：First/FirstOrDefault 翻译limit 1, Last/LastOrDefault不能翻译  Single/SingleOrDefault翻译成limit 2, ElementAt/ElementAtOrDefault不能翻译

                //    db.T_SysErrorLog.First();
                //    db.T_SysErrorLog.FirstOrDefault();

                //    db.T_SysErrorLog.Last();
                //    db.T_SysErrorLog.LastOrDefault();

                //    var d1 = db.T_SysErrorLog.Single();
                //    var d2 = db.T_SysErrorLog.SingleOrDefault();

                //    var d3 = db.T_SysErrorLog.ElementAt(1);
                //    var d4 = db.T_SysErrorLog.ElementAtOrDefault(1);

                //}
                #endregion

                #region 02-Select相关
                //{
                //    //正常翻译select
                //    var d1 = (from a in db.T_SysErrorLog
                //              select a.logLevel).ToList();
                //    //翻译出来别名和计算
                //    var d2 = (from a in db.T_SysErrorLog
                //              select new
                //              {
                //                  a.logLevel,
                //                  a.addTime,
                //                  myMsg = a.logMessage,
                //                  myflag = a.delFlag / 2
                //              }).ToList();

                //    //这种嵌套无法翻译，报错
                //    var d3 = (from a in db.T_SysErrorLog
                //              select new
                //              {
                //                  a.logLevel,
                //                  a.addTime,
                //                  myMsg = from b in db.T_SysUser
                //                          select b.userAccount,
                //                  myflag = a.delFlag / 2
                //              }).ToList();

                //}
                #endregion

                #region 03-基本函数
                {
                    //1.以下基础函数都可以翻译成对应的mysql中的函数
                    //db.T_SysErrorLog.Count();
                    //db.T_SysErrorLog.Select(o => o.delFlag).Sum();
                    //db.T_SysErrorLog.Sum(o => o.delFlag);
                    //db.T_SysErrorLog.Select(o => o.delFlag).Max();
                    //db.T_SysErrorLog.Select(o => o.delFlag).Min();
                    //db.T_SysErrorLog.Select(o => o.delFlag).Average();



                }
                #endregion

                #region 04-关联查询
                //{

                //    //隐式内连接 翻译成 cross join （在mysql中,join、cross join、inner join含义相同）
                //    var data1 = (from a in db.T_SysUser
                //                 from b in db.T_SysLoginLog
                //                 where a.id == b.userId
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     b.loginCity,
                //                     b.loginIp,
                //                     b.loginTime
                //                 }).ToList();

                //    //显式内连接 翻译成inner join 
                //    var data2 = (from a in db.T_SysUser
                //                 join b in db.T_SysLoginLog
                //                 on a.id equals b.userId
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     b.loginCity,
                //                     b.loginIp,
                //                     b.loginTime
                //                 }).ToList();

                //    //外链接翻译成 left join （linq中通过颠倒数据位置实现left 或 right join）
                //    var data3 = (from a in db.T_SysUser
                //                 join b in db.T_SysLoginLog
                //                 on a.id equals b.userId into fk
                //                 from c in fk.DefaultIfEmpty()
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     c.loginCity,
                //                     c.loginIp,
                //                     c.loginTime
                //                 }).ToList();

                //    //统计右表的数量，报错，翻译不出来！！
                //    var data4 = (from a in db.T_SysUser
                //                 join b in db.T_SysLoginLog
                //                 on a.id equals b.userId into fk
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     myCount = fk.Count()
                //                 }).ToList();


                //}
                #endregion

                #region 05-排序
                //{
                //    //升序和降序 正常翻译 mysql的排序
                //    var d1 = db.T_SysErrorLog.OrderBy(u => u.addTime).ThenByDescending(u => u.delFlag).ToList();
                //    var d2 = (from a in db.T_SysErrorLog
                //              orderby a.addTime, a.delFlag descending
                //              select a).ToList();
                //}
                #endregion

                #region 06-分组
                //{

                //    //前提：必须加.AsEnumerable(),否则报错
                //    //以下经测试均可以使用，但是翻译的sql语句不显示group
                //    var d1 = (from a in db.T_SysErrorLog.AsEnumerable()
                //              group a by a.delFlag into g
                //              select g).ToList();

                //    var d2 = (from a in db.T_SysErrorLog.AsEnumerable()
                //              group a by a.delFlag into g
                //              select new
                //              {
                //                  myKey = g.Key,
                //                  g
                //              }).ToList();

                //    var d3 = (from a in db.T_SysErrorLog.AsEnumerable()
                //              group a by new { myFlag = a.delFlag != 2 } into g
                //              select new
                //              {
                //                  myKey = g.Key,
                //                  g
                //              }).ToList();
                //}
                #endregion

                #region 07-分页
                //{
                //    //翻译成mysql的 limit+offset用法，注意不是单独的limit用法
                //    var d1 = db.T_SysErrorLog.Skip(2).Take(10).ToList();

                //    //下面两句都报错，无法翻译
                //    var d2 = db.T_SysErrorLog.SkipWhile(u => u.delFlag ==0).ToList();
                //    var d3 = db.T_SysErrorLog.TakeWhile(u => u.delFlag == 0).ToList();
                //}
                #endregion

                #region 08-Contains/EF.Functions.Like/Concat/Union/Intersect/Except
                //{
                //    //1. 这里成Contains翻译到mysql中的in
                //    string[] myList = { "222", "333", "444" };
                //    string mystr = "222,333,444";
                //    var d1 = db.T_SysErrorLog.Where(u => myList.Contains(u.logLevel)).ToList();
                //    // 这里的contians翻译成 LIKE '%222%'     （sqlserver翻译成charindex）
                //    var d2 = db.T_SysErrorLog.Where(u => u.logLevel.Contains("222")).ToList();
                //    //这里的contians翻译成 LOCATE
                //    //补充：locate(subStr,string) ：函数返回subStr在string中出现的位置
                //    var d3 = db.T_SysErrorLog.Where(u => mystr.Contains(u.logLevel)).ToList();

                //    //2. 翻译成 LIKE '%222%
                //    var d4 = db.T_SysErrorLog.Where(u => EF.Functions.Like(u.logLevel, "%222%")).ToList();

                //    //3. 翻译成Union All 不去重
                //    var d5 = ((from a in db.T_SysUser select a.id)
                //           .Concat
                //           (from a in db.T_SysRole select a.id)).ToList();
                //    //翻译成Union 去重
                //    var d6 = ((from a in db.T_SysUser select a.id)
                //        .Union
                //        (from a in db.T_SysRole select a.id)).ToList();
                //    //无法翻译报错
                //    var d7 = ((from a in db.T_SysUser select a.id)
                //        .Intersect
                //        (from a in db.T_SysRole select a.id)).ToList();

                //    //无法翻译报错
                //    var d8= ((from a in db.T_SysUser select a.id)
                //        .Except
                //        (from a in db.T_SysRole select a.id)).ToList();


                //}
                #endregion

                #region 09-DateTime/String部分方法
                //{
                //    //翻译成 EXTRACT方法
                //    var d1 = (from a in db.T_SysErrorLog
                //             where a.addTime.Value.Year == 2019
                //             select a).ToList();
                //    //翻译成 Like
                //    var d2 = (from a in db.T_SysErrorLog
                //              where a.logLevel.StartsWith("333")
                //              select a).ToList();

                //    //翻译成 SUBSTRING
                //    var d3 = (from a in db.T_SysErrorLog
                //             select a.logLevel.Substring(0,5)).ToList();
                //}
                #endregion

3. 调用SQL语句

 　经测试，可以正常使用。需要注意的是参数化查询要用 MySqlParameter。

代码分享：

 #region 01-查询类（很鸡肋，只能单表全部查询，不能指定字段）
                //{
                //    //1.基本的原生SQL查询
                //    var userList1 = db.Set<T_SysErrorLog>().FromSqlRaw("select * from T_SysErrorLog where id!='123'").ToList();

                //    //2.利用$内插语法进行传递
                //    var myId = "1";
                //    var userList2 = db.Set<T_SysErrorLog>().FromSqlRaw($"select * from T_SysErrorLog where id= {myId}").ToList();

                //    //3.原生SQL与linq语法相结合
                //    var userList3 = db.Set<T_SysErrorLog>().FromSqlRaw($"select * from T_SysErrorLog")
                //        .Where(u => u.id == "2")
                //        .ToList();
                //    var userList4 = db.Set<T_SysErrorLog>().FromSqlRaw($"select * from T_SysErrorLog")
                //        .Where(u => u.id != "1111")
                //        .OrderBy(u => u.addTime)
                //        .ToList();

                //    //4.利用SqlParameter进行参数化查询 MySql.Data.MySqlClient.MySqlParameter
                //    MySqlParameter[] paras ={
                //                             new MySqlParameter("@id","2fc343069e0a4a559b62b08d5999dbcd"),
                //                             new MySqlParameter("@userAccount","ypf"),
                //                        };
                //    var userList5 = db.Set<T_SysErrorLog>().FromSqlRaw("select * from T_SysErrorLog where id=@id and userAccount=@userAccount", paras).ToList();

                //} 
                #endregion

                #region 02-执行类(增删改)
                //{
                //    //1.增加
                //    int result0 = db.Database.ExecuteSqlRaw("insert into T_SysErrorLog(id,userId,userAccount) values('44','11111','test1')");
                //    int result1 = db.Database.ExecuteSqlRaw("insert into T_SysErrorLog(id,userId,userAccount) values('55','11111','test1')");

                //    //2. 修改
                //    MySqlParameter[] paras ={
                //                                     new MySqlParameter("@id","1"),
                //                                     new MySqlParameter("@userAccount","未知"),
                //                                };
                //    int result2 = db.Database.ExecuteSqlRaw("update T_SysErrorLog set userAccount=@userAccount where id=@id", paras);

                //    //3. 删除
                //    var myId = "44";
                //    int result3 = db.Database.ExecuteSqlRaw($"delete from T_SysErrorLog where id={myId}");

                //    //4. 其它指令
                //    int result4 = db.Database.ExecuteSqlRaw("truncate table T_SysLoginLog");
                //}
                #endregion

4. 调用存储过程

 　待补充，后续结合存储过程章节一起补充

5. 事务

(1).SaveChanges：经测试SaveChanges事务一体是好用的，但是下面关闭默认事务无效!!.

　　db.Database.AutoTransactionsEnabled = false;

(2). DbContextTransaction：适用场景多次savechanges+SQL语句调用、多种数据库链接技术(EFCore和ADO.Net)

　A.场景多次savechanges+SQL语句调用：经测试，可以正常使用。

　B. 场景多种数据库链接技术(EFCore和ADO.Net)：存在一个事务类型转换bug，暂时未没有解决.

(3). 环境事务(TransactionScope)

　A.多个SaveChange+SQL场景：经测试，没问题。

　B.多种数据库链接技术(EFCore和ADO.Net)场景：存在一个事务类型转换bug，暂时未没有解决。

　C.多个EF上下链接同一个数据库：经测试，没问题。

代码分享： 

              #region 01-SaveChange事务
                //{
                //    try
                //    {

                //        for (int i = 0; i < 5; i++)
                //        {
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //        }
                //        //模拟失败
                //        baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N")+"1", addTime = DateTime.Now });  //模拟失败
                //        int count = baseService.SaveChange();
                //    }
                //    catch (Exception ex)
                //    {
                //        Console.WriteLine(ex.Message);
                //    }

                //}
                #endregion

                #region 02-DbContextTransaction(多个SaveChange)
                //{
                //    using (var transaction = db.Database.BeginTransaction())
                //    {
                //        //using包裹,catch中可以不用写rollback，自动回滚
                //        try
                //        {
                //            //1. 业务1
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //            baseService.SaveChange();

                //            //2. 业务2
                //            db.Database.ExecuteSqlRaw($"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111','test1')");

                //            //3.模拟失败
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //            baseService.SaveChange();

                //            //统一提交
                //            transaction.Commit();
                //        }
                //        catch (Exception ex)
                //        {
                //            Console.WriteLine(ex.Message);                     
                //        }
                //    }

                //}
                #endregion

                #region 02-DbContextTransaction(多种数据库技术)--有bug
                //{
                //    var conStr = @"Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456";
                //    using (var connection = new MySql.Data.MySqlClient.MySqlConnection(conStr))
                //    {
                //        connection.Open();
                //        using (var transaction = db.Database.BeginTransaction())
                //        {
                //            try
                //            {
                //                //1. ADO.Net
                //                var command = connection.CreateCommand();
                //                command.Transaction = (MySqlTransaction)transaction;
                //                command.CommandText = $"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111111111','test1')";
                //                command.ExecuteNonQuery();

                //                //2. EF Core
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = "000000000000", addTime = DateTime.Now });
                //                baseService.SaveChange();


                //                //3.模拟失败
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //                baseService.SaveChange();


                //                //综合提交
                //                transaction.Commit();

                //            }
                //            catch (Exception ex)
                //            {
                //                Console.WriteLine(ex.Message);
                //            }
                //        }

                //    }

                //}
                #endregion

                #region 03-TransactionScope(多个SaveChange+SQL)
                //{
                //    using (var transaction = new TransactionScope())
                //    {
                //        //using包裹,catch中可以不用写rollback，自动回滚
                //        try
                //        {
                //            //1. 业务1
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //            baseService.SaveChange();

                //            //2. 业务2
                //            db.Database.ExecuteSqlRaw($"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111','test1')");

                //            //3.模拟失败
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //            baseService.SaveChange();

                //            //统一提交
                //            transaction.Complete();
                //        }
                //        catch (Exception ex)
                //        {
                //            Console.WriteLine(ex.Message);
                //        }
                //    }

                //}
                #endregion

                #region 03-TransactionScope(多种数据库技术)--有bug
                //{
                //    var conStr = @"Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456";
                //    using (var connection = new MySql.Data.MySqlClient.MySqlConnection(conStr))
                //    {
                //        connection.Open();
                //        using (var transaction = new TransactionScope())
                //        {
                //            try
                //            {
                //                //1. ADO.Net
                //                var command = connection.CreateCommand();
                //                command.Transaction = (MySqlTransaction)transaction;
                //                command.CommandText = $"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111111111','test1')";
                //                command.ExecuteNonQuery();

                //                //2. EF Core
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = "000000000000", addTime = DateTime.Now });
                //                baseService.SaveChange();


                //                //3.模拟失败
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //                baseService.SaveChange();


                //                //综合提交
                //                transaction.Complete();

                //            }
                //            catch (Exception ex)
                //            {
                //                Console.WriteLine(ex.Message);
                //            }
                //        }

                //    }

                //}
                #endregion

                #region 03-(同一个数据库不同上下)
                //{
                //    using (var scope = new TransactionScope())
                //    {
                //        try
                //        {
                //            //1.业务1
                //            using (var context = new CoreFrameDBContext())
                //            {
                //                context.Add(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //                context.SaveChanges();
                //            }
                //            //2.业务2
                //            using (var context = new CoreFrameDBContext2())
                //            {
                //                context.Add(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //                context.SaveChanges();
                //            }
                //            //3.模拟失败
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //            baseService.SaveChange();

                //            //综合提交
                //            scope.Complete();

                //            Console.WriteLine("成功了");
                //        }
                //        catch (Exception ex)
                //        {
                //            Console.WriteLine(ex.Message);
                //        }
                //    }

                //}
                #endregion

6. 性能测试 

 结论：以上测试得出来一个结论，EFCore处理增删改在10000条数据以内速度还是可以接受的，并且EFCore调用SQL语句组装并不能提升性能，反而下降明显！！

 代码分享：

             #region 01-增加(EFCore)
                //{
                //    for (int i = 0; i < 10; i++)
                //    {
                //        T_SysErrorLog sErrorLog = new T_SysErrorLog();
                //        sErrorLog.id = Guid.NewGuid().ToString("N");
                //        sErrorLog.userId = "001";
                //        sErrorLog.userAccount = "12345";
                //        sErrorLog.logLevel = "Error";
                //        sErrorLog.logMessage = "出错了";
                //        sErrorLog.addTime = DateTime.Now;
                //        sErrorLog.delFlag = 0;
                //        baseService.AddNo(sErrorLog);
                //    }
                //    int count = baseService.SaveChange();
                //    Console.WriteLine("执行成功");
                //}
                #endregion

                #region 01-增加(EFCore调用SQL)
                //{
                //    string sqlStr = "";
                //    for (int i = 0; i < 1000; i++)
                //    {
                //        sqlStr = sqlStr + $"insert into T_SysErrorLog values('{Guid.NewGuid().ToString("N")}', '001', '12345','Error','出错了','{DateTime.Now}',0);";
                //    }
                //    int count = db.Database.ExecuteSqlRaw(sqlStr);
                //    Console.WriteLine("执行成功");
                //}
                #endregion

                #region 02-修改(EFCore)
                //{
                //    //先用上面的增加语句添加指定条目的数据

                //    var list = baseService.GetListBy<T_SysErrorLog>(u => u.id != "000");
                //    foreach (var item in list)
                //    {
                //        item.logLevel = "ERROR1110";
                //        item.logMessage = "出错了2220";
                //        item.addTime = DateTime.Now;
                //    }
                //    int count = baseService.SaveChange();

                //}
                #endregion

                #region 02-修改(EFCore调用SQL)
                //{
                //    //先用上面的增加语句添加指定条目的数据
                //    var list = baseService.GetListBy<T_SysErrorLog>(u => u.id != "000");
                //    string sqlStr = "";
                //    foreach (var item in list)
                //    {
                //        sqlStr = sqlStr + $"update T_SysErrorLog set logLevel='ERROR110',logMessage='出错了220',addTime='{DateTime.Now}' where id='{item.id}';";
                //    }
                //    int count = db.Database.ExecuteSqlRaw(sqlStr);
                //    Console.WriteLine("执行成功");
                //}
                #endregion

                #region 03-删除(EFCore)
                //{
                //    //先用上面的增加语句添加指定条目的数据
                //    int count = baseService.DelBy<T_SysErrorLog>(u => u.id != "fk");

                //}
                #endregion

                #region 03-删除(EFCore调用SQL)
                //{
                //    //先用上面的增加语句添加指定条目的数据
                //    var list = baseService.Entities<T_SysErrorLog>().Where(u => u.id != "000").Select(u => u.id).ToList();
                //    string sqlStr = "";
                //    foreach (var item in list)
                //    {
                //        sqlStr = sqlStr + $"delete from T_SysErrorLog where id='{item}';";
                //    }
                //    int count = db.Database.ExecuteSqlRaw(sqlStr);
                //    Console.WriteLine("执行成功");
                //}
                #endregion

7. 性能优化

(1).SqlBulkCopy：基于 System.Data.SqlClient ，仅支持SQLServer, Pass掉。

(2).EFCore.BulkExtensions：仅支持SQLServer 和 SQLite，Pass掉  【该组件已收费，不再使用】

(3).Z.EntityFramework.Plus.EFCore：    【收费的组价叫：Z.EntityFramework.Extensions.EFCore】

A.说明：免费, 支持MySQL,，且目前已经支持EFCore5.x版本了, 但功能有限, 仅支持：Batch Delete、Batch Update. （删除和修改）

　GitHub地址：https://github.com/zzzprojects/EntityFramework-Plus

　官网文档地址：http://entityframework-plus.net/batch-delete 

 

注意：更强大的BulkSaveChanges、 BulkInsert、 BulkUpdate、BulkDelete、BulkMerge 对应收费的程序集 Z.EntityFramework.Extensions （收费！！！）

B.性能测试：

C. 测试是否支持事务：

　经测试,支持Transaction事务的统一提交和回滚。

最后总结：目前只找到大数据删除和修改的组件，增加的组件目前没有找到！！！

代码分享：

   {
                Stopwatch watch = new Stopwatch();
                watch.Start();
                Console.WriteLine("开始执行。。。。。。");

                #region 01-删除
                //{
                //    int count1 = db.T_SysErrorLog.Where(u => u.id != "1").Delete();
                //    //db.T_SysErrorLog.Where(u => u.id != "1").Delete(x => x.BatchSize = 1000);
                //} 
                #endregion

                #region 02-修改
                //{
                //    int count1 = db.T_SysErrorLog.Where(u => u.id != "1").Update(x=>new T_SysErrorLog() { logLevel="Error33324",logMessage="出3错4342了",addTime=DateTime.Now});

                //} 
                #endregion

                #region 03-测试事务
                {
                    using (var transaction = db.Database.BeginTransaction())
                    {
                        BaseService baseService1 = new BaseService(db);
                        //using包裹，不需要手动写rollback
                        try
                        {
                            //1.普通增加
                            for (int i = 0; i < 5; i++)
                            {
                                baseService.AddNo(new T_SysErrorLog() { id = i.ToString(), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                            }
                            baseService.SaveChange();

                            //2. 组件的删除
                            db.T_SysErrorLog.Where(u => u.id == "1").Delete();

                            //3. 组件的更新
                            db.T_SysErrorLog.Where(u => u.id != "0001").Update(x => new T_SysErrorLog() { logLevel = "Error33324", logMessage = "出3错4342了", addTime = DateTime.Now });

                            //4. 模拟失败
                            baseService1.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                            baseService1.SaveChange();

                            //5.最后提交
                            transaction.Commit();

                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);

                        }
                    }

                }
                #endregion



                Console.WriteLine("执行完成");
                watch.Stop();
                Console.WriteLine($"时间为:{watch.ElapsedMilliseconds}ms");
            }

PS：上述代码事务的时候必须是同一个db

8. 并发测试

(1). 监测单个字段： [ConcurrencyCheck] 或 entity.Property(p => p.age).IsConcurrencyToken();

　配置T_Test表中的age4字段,进行测试, 有效可以使用。

(2). 监测整条数据：[Timestamp]或entity.Property(e => e.rowVersion).IsRowVersion();

　配置T_Test表新增timestamp类型的rowVersion字段，实体中进行上述配置，进行测试, 发现无效不能使用！！！

代码分享：

   #region 01-单字段监控
                //{
                //    CoreFrameDBContext db1 = new CoreFrameDBContext();
                //    CoreFrameDBContext db2 = new CoreFrameDBContext();
                //    try
                //    {
                //        var data1 = db1.T_Test.Where(u => u.id == 1).FirstOrDefault();
                //        var data2 = db2.T_Test.Where(u => u.id ==1).FirstOrDefault();

                //        data1.age4 = data1.age4 - 2;
                //        int result1 = db1.SaveChanges();

                //        data2.age4 = data2.age4 - 4;
                //        int result2 = db2.SaveChanges();  //发现age的值和原先查出来的不一致，会抛异常进入cache
                //    }
                //    catch (DbUpdateConcurrencyException ex)
                //    {

                //        var entityEntry = ex.Entries.Single();
                //        var original = entityEntry.OriginalValues.ToObject() as T_Test; //数据库原始值  10
                //        var database = entityEntry.GetDatabaseValues().ToObject() as T_Test; //数据库现在值 8
                //        var current = entityEntry.CurrentValues.ToObject() as T_Test; //当前内存值 6
                //        entityEntry.Reload(); //放弃当前内存中的实体，重新到数据库中加载当前实体

                //        current.age4 = database.age4 - 4;  //应该拿着当前数据库实际的值去处理，即8-4=4
                //        entityEntry.CurrentValues.SetValues(current);
                //        int result3 = db2.SaveChanges();
                //    }
                //}
                #endregion

                #region 02-全字段监控
                //{
                //    CoreFrameDBContext db1 = new CoreFrameDBContext();
                //    CoreFrameDBContext db2 = new CoreFrameDBContext();
                //    try
                //    {
                //        var data1 = db1.T_Test.Where(u => u.id == 1).FirstOrDefault();
                //        var data2 = db2.T_Test.Where(u => u.id == 1).FirstOrDefault();

                //        data1.age4 = data1.age4 - 2;
                //        int result1 = db1.SaveChanges();

                //        data2.age4 = data2.age4 - 4;
                //        int result2 = db2.SaveChanges();  //发现age的值和原先查出来的不一致，会抛异常进入cache
                //    }
                //    catch (DbUpdateConcurrencyException ex)
                //    {

                //        var entityEntry = ex.Entries.Single();
                //        var original = entityEntry.OriginalValues.ToObject() as T_Test; //数据库原始值  10
                //        var database = entityEntry.GetDatabaseValues().ToObject() as T_Test; //数据库现在值 8
                //        var current = entityEntry.CurrentValues.ToObject() as T_Test; //当前内存值 6
                //        entityEntry.Reload(); //放弃当前内存中的实体，重新到数据库中加载当前实体

                //        current.age4 = database.age4 - 4;  //应该拿着当前数据库实际的值去处理，即8-4=4
                //        entityEntry.CurrentValues.SetValues(current);
                //        int result3 = db2.SaveChanges();
                //    }
                //}
                #endregion

9. 索引映射

　给T_Test表中的name1添加索引,age1和age2添加联合索引,通过指令映射，发现索引映射成功。

    [Index(nameof(age1), nameof(age2), Name = "ids_age")]
    [Index(nameof(name1), Name = "ids_name1")]
    public partial class T_Test{}

 

四. 实战测试2 

前言：

　以下测试都基于【Microsoft.EntityFrameworkCore】来进行，连接字符串如下：

 optionsBuilder.UseMySQL("Server=xxxx;Database=CoreFrameDB;User ID=root;Password=xxxx;");

1. 基础CRUD(+封装)

 　经测试，均可正常使用。

封装方法：

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

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

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

        /****************************************下面进行方法的封装（同步）***********************************************/
        //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



        /****************************************下面进行方法的封装（异步）***********************************************/

        #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】大数据的处理 （同步）***********************************************/

        #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】大数据的处理 （异步）***********************************************/

        #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

    }
 /// <summary>
    /// 排序的扩展
    /// </summary>
    public static class SortExtension
    {

        #region 01-根据string名称排序扩展(单字段)
        /// <summary>
        /// 根据string名称排序扩展(单字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="source">排序数据源</param>
        /// <param name="sortName">排序名称</param>
        /// <param name="sortDirection">排序方式 asc或desc</param>
        /// <returns></returns>
        public static IQueryable<T> DataSorting<T>(this IQueryable<T> source, string sortName, string sortDirection)
        {
            string sortingDir = string.Empty;
            if (sortDirection.ToUpper().Trim() == "ASC")
            {
                sortingDir = "OrderBy";
            }
            else if (sortDirection.ToUpper().Trim() == "DESC")
            {
                sortingDir = "OrderByDescending";
            }
            ParameterExpression param = Expression.Parameter(typeof(T), sortName);
            PropertyInfo pi = typeof(T).GetProperty(sortName);
            Type[] types = new Type[2];
            types[0] = typeof(T);
            types[1] = pi.PropertyType;
            Expression expr = Expression.Call(typeof(Queryable), sortingDir, types, source.Expression, Expression.Lambda(Expression.Property(param, sortName), param));
            IQueryable<T> query = source.AsQueryable().Provider.CreateQuery<T>(expr);
            return query;
        }
        #endregion

        #region 02-根据多个string名称排序扩展(多字段)
        /// <summary>
        ///  根据多个string名称排序扩展(多字段)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data">数据源</param>
        /// <param name="orderParams">排序类</param>
        /// <returns></returns>
        public static IQueryable<T> DataManySorting<T>(this IQueryable<T> data, params FiledOrderParam[] orderParams) where T : class
        {
            var parameter = Expression.Parameter(typeof(T), "p");
            if (orderParams != null && orderParams.Length > 0)
            {
                for (int i = 0; i < orderParams.Length; i++)
                {
                    var property = typeof(T).GetProperty(orderParams[i].PropertyName);
                    if (property != null)
                    {
                        var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                        var orderByExpr = Expression.Lambda(propertyAccess, parameter);
                        string methodName = i > 0 ?
                            orderParams[i].IsDesc ? "ThenByDescending" : "ThenBy"
                            : orderParams[i].IsDesc ? "OrderByDescending" : "OrderBy";
                        var resultExp = Expression.Call(
                            typeof(Queryable), methodName,
                            new Type[] { typeof(T), property.PropertyType },
                            data.Expression, Expression.Quote(orderByExpr)
                            );
                        data = data.Provider.CreateQuery<T>(resultExp);
                    }
                }
            }
            return data;
        }

        #endregion
    }


    /// <summary>
    /// 排序类
    /// </summary>
    public class FiledOrderParam
    {
        //是否降序
        public bool IsDesc { get; set; }
        //排序名称
        public string PropertyName { get; set; }
    }

测试代码： 

               #region 01-增加
                {
                    //T_Test tTest = new T_Test()
                    //{
                    //    age1 = 1,
                    //    age2 = 2,
                    //    age3 = 3,
                    //    age4 = 4,
                    //    age5 = 5,
                    //    money1 = (float)10.12,
                    //    money2 = 20.34,
                    //    money3 = (decimal)12.13,
                    //    addTime1 = 2020,
                    //    addTime2 = DateTime.Now,
                    //    addTime3 = TimeSpan.FromMinutes(10),
                    //    addTime4 = DateTime.Now,
                    //    addTime5 = DateTime.Now,
                    //    name1 = "ypf1",
                    //    name2 = "ypf2",
                    //    name3 = "ypf3",
                    //    name4 = "ypf4",
                    //    name5 = "ypf5",
                    //    name6 = "ypf6",
                    //    isSex1 = 1
                    //};
                    //int count1 = baseService.Add(tTest);

                    //T_SysErrorLog sErrorLog = new T_SysErrorLog();
                    //sErrorLog.id = Guid.NewGuid().ToString("N");
                    //sErrorLog.userId = "001";
                    //sErrorLog.userAccount = "12345";
                    //sErrorLog.logLevel = "Error";
                    //sErrorLog.logMessage = "出错了";
                    //sErrorLog.addTime = DateTime.Now;
                    //sErrorLog.delFlag = 0;
                    //int count2 = baseService.Add(sErrorLog);
                }
                #endregion

                #region 02-修改
                //{
                //    var data = baseService.Entities<T_SysErrorLog>().Where(u => u.id == "1").FirstOrDefault();
                //    data.userAccount = "123456";
                //    baseService.SaveChange();
                //    Console.WriteLine("修改成功");
                //}
                #endregion

                #region 03-删除
                //{
                //    baseService.DelBy<T_SysErrorLog>(u => u.id != "1");
                //    Console.WriteLine("删除成功");
                //}
                #endregion

                #region 04-根据条件查询和排序
                //{
                //    var list = baseService.GetListBy<T_SysErrorLog, DateTime?>(u => u.id != "xxx", p => p.addTime, false);
                //    foreach (var item in list)
                //    {
                //        Console.WriteLine($"id={item.id},userId={item.userId},userAccount={item.userAccount},addTime={item.addTime}");
                //    }

                //} 
                #endregion

                #region 05-根据字段名称升/降序分页查询
                //{
                //    int pageIndex = 1;
                //    int pageSize = 2;
                //    //1.分开写法
                //    var list1 = baseService.Entities<T_SysErrorLog>().Where(u => u.id != "fk").DataSorting("addTime", "desc").Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                //    //2. 封装调用
                //    int count = 0;
                //    var list2 = baseService.GetPageListByName<T_SysErrorLog>(pageIndex, pageSize, out count, u => u.id != "fk", "addTime", "desc");

                //    //3.多字段排序
                //    FiledOrderParam[] param = {
                //        new FiledOrderParam(){IsDesc=false,PropertyName="addTime"},
                //        new FiledOrderParam(){IsDesc=true,PropertyName="id"}
                //    };
                //    var list3 = baseService.Entities<T_SysErrorLog>().Where(u => u.id != "fk").DataManySorting(param).Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                //}
                #endregion

2. 各种Linq测试

　详细测试了各种linq对应MySQL的翻译，代码如下：

                #region 01-First/Last/Single/ElementAt
                //{
                //    //说明：First/FirstOrDefault 翻译limit 1, Last/LastOrDefault不能翻译  Single/SingleOrDefault翻译成limit 2, ElementAt/ElementAtOrDefault不能翻译

                //    db.T_SysErrorLog.First();
                //    db.T_SysErrorLog.FirstOrDefault();

                //    db.T_SysErrorLog.Last();
                //    db.T_SysErrorLog.LastOrDefault();

                //    var d1 = db.T_SysErrorLog.Single();
                //    var d2 = db.T_SysErrorLog.SingleOrDefault();

                //    var d3 = db.T_SysErrorLog.ElementAt(1);
                //    var d4 = db.T_SysErrorLog.ElementAtOrDefault(1);

                //}
                #endregion

                #region 02-Select相关
                //{
                //    //正常翻译select
                //    var d1 = (from a in db.T_SysErrorLog
                //              select a.logLevel).ToList();
                //    //翻译出来别名和计算
                //    var d2 = (from a in db.T_SysErrorLog
                //              select new
                //              {
                //                  a.logLevel,
                //                  a.addTime,
                //                  myMsg = a.logMessage,
                //                  myflag = a.delFlag / 2
                //              }).ToList();

                //    //这种嵌套无法翻译，报错
                //    var d3 = (from a in db.T_SysErrorLog
                //              select new
                //              {
                //                  a.logLevel,
                //                  a.addTime,
                //                  myMsg = from b in db.T_SysUser
                //                          select b.userAccount,
                //                  myflag = a.delFlag / 2
                //              }).ToList();

                //}
                #endregion

                #region 03-基本函数
                {
                    //1.以下基础函数都可以翻译成对应的mysql中的函数
                    //db.T_SysErrorLog.Count();
                    //db.T_SysErrorLog.Select(o => o.delFlag).Sum();
                    //db.T_SysErrorLog.Sum(o => o.delFlag);
                    //db.T_SysErrorLog.Select(o => o.delFlag).Max();
                    //db.T_SysErrorLog.Select(o => o.delFlag).Min();
                    //db.T_SysErrorLog.Select(o => o.delFlag).Average();



                }
                #endregion

                #region 04-关联查询
                //{

                //    //隐式内连接 翻译成 cross join （在mysql中,join、cross join、inner join含义相同）
                //    var data1 = (from a in db.T_SysUser
                //                 from b in db.T_SysLoginLog
                //                 where a.id == b.userId
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     b.loginCity,
                //                     b.loginIp,
                //                     b.loginTime
                //                 }).ToList();

                //    //显式内连接 翻译成inner join 
                //    var data2 = (from a in db.T_SysUser
                //                 join b in db.T_SysLoginLog
                //                 on a.id equals b.userId
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     b.loginCity,
                //                     b.loginIp,
                //                     b.loginTime
                //                 }).ToList();

                //    //外链接翻译成 left join （linq中通过颠倒数据位置实现left 或 right join）
                //    var data3 = (from a in db.T_SysUser
                //                 join b in db.T_SysLoginLog
                //                 on a.id equals b.userId into fk
                //                 from c in fk.DefaultIfEmpty()
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     c.loginCity,
                //                     c.loginIp,
                //                     c.loginTime
                //                 }).ToList();

                //    //统计右表的数量，报错，翻译不出来！！
                //    var data4 = (from a in db.T_SysUser
                //                 join b in db.T_SysLoginLog
                //                 on a.id equals b.userId into fk
                //                 select new
                //                 {
                //                     a.userAccount,
                //                     a.userPhone,
                //                     myCount = fk.Count()
                //                 }).ToList();


                //}
                #endregion

                #region 05-排序
                //{
                //    //升序和降序 正常翻译 mysql的排序
                //    var d1 = db.T_SysErrorLog.OrderBy(u => u.addTime).ThenByDescending(u => u.delFlag).ToList();
                //    var d2 = (from a in db.T_SysErrorLog
                //              orderby a.addTime, a.delFlag descending
                //              select a).ToList();
                //}
                #endregion

                #region 06-分组
                //{

                //    //前提：必须加.AsEnumerable(),否则报错
                //    //以下经测试均可以使用，但是翻译的sql语句不显示group
                //    var d1 = (from a in db.T_SysErrorLog.AsEnumerable()
                //              group a by a.delFlag into g
                //              select g).ToList();

                //    var d2 = (from a in db.T_SysErrorLog.AsEnumerable()
                //              group a by a.delFlag into g
                //              select new
                //              {
                //                  myKey = g.Key,
                //                  g
                //              }).ToList();

                //    var d3 = (from a in db.T_SysErrorLog.AsEnumerable()
                //              group a by new { myFlag = a.delFlag != 2 } into g
                //              select new
                //              {
                //                  myKey = g.Key,
                //                  g
                //              }).ToList();
                //}
                #endregion

                #region 07-分页
                //{
                //    //翻译成mysql的 limit+offset用法，注意不是单独的limit用法
                //    var d1 = db.T_SysErrorLog.Skip(2).Take(10).ToList();

                //    //下面两句都报错，无法翻译
                //    var d2 = db.T_SysErrorLog.SkipWhile(u => u.delFlag ==0).ToList();
                //    var d3 = db.T_SysErrorLog.TakeWhile(u => u.delFlag == 0).ToList();
                //}
                #endregion

                #region 08-Contains/EF.Functions.Like/Concat/Union/Intersect/Except
                //{
                //    //1. 这里成Contains翻译到mysql中的in
                //    string[] myList = { "222", "333", "444" };
                //    string mystr = "222,333,444";
                //    var d1 = db.T_SysErrorLog.Where(u => myList.Contains(u.logLevel)).ToList();
                //    // 这里的contians翻译成 LIKE '%222%'     （sqlserver翻译成charindex）
                //    var d2 = db.T_SysErrorLog.Where(u => u.logLevel.Contains("222")).ToList();
                //    //这里的contians翻译成 LOCATE
                //    //补充：locate(subStr,string) ：函数返回subStr在string中出现的位置
                //    var d3 = db.T_SysErrorLog.Where(u => mystr.Contains(u.logLevel)).ToList();

                //    //2. 翻译成 LIKE '%222%
                //    var d4 = db.T_SysErrorLog.Where(u => EF.Functions.Like(u.logLevel, "%222%")).ToList();

                //    //3. 翻译成Union All 不去重
                //    var d5 = ((from a in db.T_SysUser select a.id)
                //           .Concat
                //           (from a in db.T_SysRole select a.id)).ToList();
                //    //翻译成Union 去重
                //    var d6 = ((from a in db.T_SysUser select a.id)
                //        .Union
                //        (from a in db.T_SysRole select a.id)).ToList();
                //    //无法翻译报错
                //    var d7 = ((from a in db.T_SysUser select a.id)
                //        .Intersect
                //        (from a in db.T_SysRole select a.id)).ToList();

                //    //无法翻译报错
                //    var d8= ((from a in db.T_SysUser select a.id)
                //        .Except
                //        (from a in db.T_SysRole select a.id)).ToList();


                //}
                #endregion

                #region 09-DateTime/String部分方法
                //{
                //    //翻译成 EXTRACT方法
                //    var d1 = (from a in db.T_SysErrorLog
                //             where a.addTime.Value.Year == 2019
                //             select a).ToList();
                //    //翻译成 Like
                //    var d2 = (from a in db.T_SysErrorLog
                //              where a.logLevel.StartsWith("333")
                //              select a).ToList();

                //    //翻译成 SUBSTRING
                //    var d3 = (from a in db.T_SysErrorLog
                //             select a.logLevel.Substring(0,5)).ToList();
                //}
                #endregion

3. 调用SQL语句

 　经测试，可以正常使用。需要注意的是参数化查询要用 MySqlParameter。

代码分享：

 #region 01-查询类（很鸡肋，只能单表全部查询，不能指定字段）
                //{
                //    //1.基本的原生SQL查询
                //    var userList1 = db.Set<T_SysErrorLog>().FromSqlRaw("select * from T_SysErrorLog where id!='123'").ToList();

                //    //2.利用$内插语法进行传递
                //    var myId = "1";
                //    var userList2 = db.Set<T_SysErrorLog>().FromSqlRaw($"select * from T_SysErrorLog where id= {myId}").ToList();

                //    //3.原生SQL与linq语法相结合
                //    var userList3 = db.Set<T_SysErrorLog>().FromSqlRaw($"select * from T_SysErrorLog")
                //        .Where(u => u.id == "2")
                //        .ToList();
                //    var userList4 = db.Set<T_SysErrorLog>().FromSqlRaw($"select * from T_SysErrorLog")
                //        .Where(u => u.id != "1111")
                //        .OrderBy(u => u.addTime)
                //        .ToList();

                //    //4.利用SqlParameter进行参数化查询 MySql.Data.MySqlClient.MySqlParameter
                //    MySqlParameter[] paras ={
                //                             new MySqlParameter("@id","2fc343069e0a4a559b62b08d5999dbcd"),
                //                             new MySqlParameter("@userAccount","ypf"),
                //                        };
                //    var userList5 = db.Set<T_SysErrorLog>().FromSqlRaw("select * from T_SysErrorLog where id=@id and userAccount=@userAccount", paras).ToList();

                //} 
                #endregion

                #region 02-执行类(增删改)
                //{
                //    //1.增加
                //    int result0 = db.Database.ExecuteSqlRaw("insert into T_SysErrorLog(id,userId,userAccount) values('44','11111','test1')");
                //    int result1 = db.Database.ExecuteSqlRaw("insert into T_SysErrorLog(id,userId,userAccount) values('55','11111','test1')");

                //    //2. 修改
                //    MySqlParameter[] paras ={
                //                                     new MySqlParameter("@id","1"),
                //                                     new MySqlParameter("@userAccount","未知"),
                //                                };
                //    int result2 = db.Database.ExecuteSqlRaw("update T_SysErrorLog set userAccount=@userAccount where id=@id", paras);

                //    //3. 删除
                //    var myId = "44";
                //    int result3 = db.Database.ExecuteSqlRaw($"delete from T_SysErrorLog where id={myId}");

                //    //4. 其它指令
                //    int result4 = db.Database.ExecuteSqlRaw("truncate table T_SysLoginLog");
                //}
                #endregion

4. 调用存储过程

 　待补充，后续结合存储过程章节一起补充

5. 事务

(1).SaveChanges：经测试SaveChanges事务一体是好用的，但是下面关闭默认事务无效!!.

　　db.Database.AutoTransactionsEnabled = false;

(2). DbContextTransaction：适用场景多次savechanges+SQL语句调用、多种数据库链接技术(EFCore和ADO.Net)

　A.场景多次savechanges+SQL语句调用：经测试，可以正常使用。

　B. 场景多种数据库链接技术(EFCore和ADO.Net)：存在一个事务类型转换bug，暂时未没有解决.

(3). 环境事务(TransactionScope)

　A.多个SaveChange+SQL场景：经测试，没问题。

　B.多种数据库链接技术(EFCore和ADO.Net)场景：存在一个事务类型转换bug，暂时未没有解决。

　C.多个EF上下链接同一个数据库：经测试，没问题。

代码分享： 

              #region 01-SaveChange事务
                //{
                //    try
                //    {

                //        for (int i = 0; i < 5; i++)
                //        {
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //        }
                //        //模拟失败
                //        baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N")+"1", addTime = DateTime.Now });  //模拟失败
                //        int count = baseService.SaveChange();
                //    }
                //    catch (Exception ex)
                //    {
                //        Console.WriteLine(ex.Message);
                //    }

                //}
                #endregion

                #region 02-DbContextTransaction(多个SaveChange)
                //{
                //    using (var transaction = db.Database.BeginTransaction())
                //    {
                //        //using包裹,catch中可以不用写rollback，自动回滚
                //        try
                //        {
                //            //1. 业务1
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //            baseService.SaveChange();

                //            //2. 业务2
                //            db.Database.ExecuteSqlRaw($"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111','test1')");

                //            //3.模拟失败
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //            baseService.SaveChange();

                //            //统一提交
                //            transaction.Commit();
                //        }
                //        catch (Exception ex)
                //        {
                //            Console.WriteLine(ex.Message);                     
                //        }
                //    }

                //}
                #endregion

                #region 02-DbContextTransaction(多种数据库技术)--有bug
                //{
                //    var conStr = @"Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456";
                //    using (var connection = new MySql.Data.MySqlClient.MySqlConnection(conStr))
                //    {
                //        connection.Open();
                //        using (var transaction = db.Database.BeginTransaction())
                //        {
                //            try
                //            {
                //                //1. ADO.Net
                //                var command = connection.CreateCommand();
                //                command.Transaction = (MySqlTransaction)transaction;
                //                command.CommandText = $"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111111111','test1')";
                //                command.ExecuteNonQuery();

                //                //2. EF Core
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = "000000000000", addTime = DateTime.Now });
                //                baseService.SaveChange();


                //                //3.模拟失败
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //                baseService.SaveChange();


                //                //综合提交
                //                transaction.Commit();

                //            }
                //            catch (Exception ex)
                //            {
                //                Console.WriteLine(ex.Message);
                //            }
                //        }

                //    }

                //}
                #endregion

                #region 03-TransactionScope(多个SaveChange+SQL)
                //{
                //    using (var transaction = new TransactionScope())
                //    {
                //        //using包裹,catch中可以不用写rollback，自动回滚
                //        try
                //        {
                //            //1. 业务1
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //            baseService.SaveChange();

                //            //2. 业务2
                //            db.Database.ExecuteSqlRaw($"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111','test1')");

                //            //3.模拟失败
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //            baseService.SaveChange();

                //            //统一提交
                //            transaction.Complete();
                //        }
                //        catch (Exception ex)
                //        {
                //            Console.WriteLine(ex.Message);
                //        }
                //    }

                //}
                #endregion

                #region 03-TransactionScope(多种数据库技术)--有bug
                //{
                //    var conStr = @"Server=localhost;Database=CoreFrameDB;User ID=root;Password=123456";
                //    using (var connection = new MySql.Data.MySqlClient.MySqlConnection(conStr))
                //    {
                //        connection.Open();
                //        using (var transaction = new TransactionScope())
                //        {
                //            try
                //            {
                //                //1. ADO.Net
                //                var command = connection.CreateCommand();
                //                command.Transaction = (MySqlTransaction)transaction;
                //                command.CommandText = $"insert into T_SysErrorLog(id,userId,userAccount) values('{Guid.NewGuid().ToString("N")}','11111111111','test1')";
                //                command.ExecuteNonQuery();

                //                //2. EF Core
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = "000000000000", addTime = DateTime.Now });
                //                baseService.SaveChange();


                //                //3.模拟失败
                //                baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //                baseService.SaveChange();


                //                //综合提交
                //                transaction.Complete();

                //            }
                //            catch (Exception ex)
                //            {
                //                Console.WriteLine(ex.Message);
                //            }
                //        }

                //    }

                //}
                #endregion

                #region 03-(同一个数据库不同上下)
                //{
                //    using (var scope = new TransactionScope())
                //    {
                //        try
                //        {
                //            //1.业务1
                //            using (var context = new CoreFrameDBContext())
                //            {
                //                context.Add(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //                context.SaveChanges();
                //            }
                //            //2.业务2
                //            using (var context = new CoreFrameDBContext2())
                //            {
                //                context.Add(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                //                context.SaveChanges();
                //            }
                //            //3.模拟失败
                //            baseService.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                //            baseService.SaveChange();

                //            //综合提交
                //            scope.Complete();

                //            Console.WriteLine("成功了");
                //        }
                //        catch (Exception ex)
                //        {
                //            Console.WriteLine(ex.Message);
                //        }
                //    }

                //}
                #endregion

6. 性能测试 

 结论：以上测试得出来一个结论，EFCore处理增删改在10000条数据以内速度还是可以接受的，并且EFCore调用SQL语句组装并不能提升性能，反而下降明显！！

 代码分享：

             #region 01-增加(EFCore)
                //{
                //    for (int i = 0; i < 10; i++)
                //    {
                //        T_SysErrorLog sErrorLog = new T_SysErrorLog();
                //        sErrorLog.id = Guid.NewGuid().ToString("N");
                //        sErrorLog.userId = "001";
                //        sErrorLog.userAccount = "12345";
                //        sErrorLog.logLevel = "Error";
                //        sErrorLog.logMessage = "出错了";
                //        sErrorLog.addTime = DateTime.Now;
                //        sErrorLog.delFlag = 0;
                //        baseService.AddNo(sErrorLog);
                //    }
                //    int count = baseService.SaveChange();
                //    Console.WriteLine("执行成功");
                //}
                #endregion

                #region 01-增加(EFCore调用SQL)
                //{
                //    string sqlStr = "";
                //    for (int i = 0; i < 1000; i++)
                //    {
                //        sqlStr = sqlStr + $"insert into T_SysErrorLog values('{Guid.NewGuid().ToString("N")}', '001', '12345','Error','出错了','{DateTime.Now}',0);";
                //    }
                //    int count = db.Database.ExecuteSqlRaw(sqlStr);
                //    Console.WriteLine("执行成功");
                //}
                #endregion

                #region 02-修改(EFCore)
                //{
                //    //先用上面的增加语句添加指定条目的数据

                //    var list = baseService.GetListBy<T_SysErrorLog>(u => u.id != "000");
                //    foreach (var item in list)
                //    {
                //        item.logLevel = "ERROR1110";
                //        item.logMessage = "出错了2220";
                //        item.addTime = DateTime.Now;
                //    }
                //    int count = baseService.SaveChange();

                //}
                #endregion

                #region 02-修改(EFCore调用SQL)
                //{
                //    //先用上面的增加语句添加指定条目的数据
                //    var list = baseService.GetListBy<T_SysErrorLog>(u => u.id != "000");
                //    string sqlStr = "";
                //    foreach (var item in list)
                //    {
                //        sqlStr = sqlStr + $"update T_SysErrorLog set logLevel='ERROR110',logMessage='出错了220',addTime='{DateTime.Now}' where id='{item.id}';";
                //    }
                //    int count = db.Database.ExecuteSqlRaw(sqlStr);
                //    Console.WriteLine("执行成功");
                //}
                #endregion

                #region 03-删除(EFCore)
                //{
                //    //先用上面的增加语句添加指定条目的数据
                //    int count = baseService.DelBy<T_SysErrorLog>(u => u.id != "fk");

                //}
                #endregion

                #region 03-删除(EFCore调用SQL)
                //{
                //    //先用上面的增加语句添加指定条目的数据
                //    var list = baseService.Entities<T_SysErrorLog>().Where(u => u.id != "000").Select(u => u.id).ToList();
                //    string sqlStr = "";
                //    foreach (var item in list)
                //    {
                //        sqlStr = sqlStr + $"delete from T_SysErrorLog where id='{item}';";
                //    }
                //    int count = db.Database.ExecuteSqlRaw(sqlStr);
                //    Console.WriteLine("执行成功");
                //}
                #endregion

7. 性能优化

(1).SqlBulkCopy：基于 System.Data.SqlClient ，仅支持SQLServer, Pass掉。

(2).EFCore.BulkExtensions：仅支持SQLServer 和 SQLite，Pass掉  【该组件已收费，不再使用】

(3).Z.EntityFramework.Plus.EFCore：

A.说明：免费, 支持MySQL,，且目前已经支持EFCore5.x版本了, 但功能有限, 仅支持：Batch Delete、Batch Update. （删除和修改）

　GitHub地址：https://github.com/zzzprojects/EntityFramework-Plus

　官网文档地址：http://entityframework-plus.net/batch-delete 

 

注意：更强大的BulkSaveChanges、 BulkInsert、 BulkUpdate、BulkDelete、BulkMerge 对应收费的程序集 Z.EntityFramework.Extensions （收费！！！）

B.性能测试：

C. 测试是否支持事务：

　经测试,支持Transaction事务的统一提交和回滚。

最后总结：目前只找到大数据删除和修改的组件，增加的组件目前没有找到！！！

代码分享：

   {
                Stopwatch watch = new Stopwatch();
                watch.Start();
                Console.WriteLine("开始执行。。。。。。");

                #region 01-删除
                //{
                //    int count1 = db.T_SysErrorLog.Where(u => u.id != "1").Delete();
                //    //db.T_SysErrorLog.Where(u => u.id != "1").Delete(x => x.BatchSize = 1000);
                //} 
                #endregion

                #region 02-修改
                //{
                //    int count1 = db.T_SysErrorLog.Where(u => u.id != "1").Update(x=>new T_SysErrorLog() { logLevel="Error33324",logMessage="出3错4342了",addTime=DateTime.Now});

                //} 
                #endregion

                #region 03-测试事务
                {
                    using (var transaction = db.Database.BeginTransaction())
                    {
                        BaseService baseService1 = new BaseService(db);
                        //using包裹，不需要手动写rollback
                        try
                        {
                            //1.普通增加
                            for (int i = 0; i < 5; i++)
                            {
                                baseService.AddNo(new T_SysErrorLog() { id = i.ToString(), userId = Guid.NewGuid().ToString("N"), addTime = DateTime.Now });
                            }
                            baseService.SaveChange();

                            //2. 组件的删除
                            db.T_SysErrorLog.Where(u => u.id == "1").Delete();

                            //3. 组件的更新
                            db.T_SysErrorLog.Where(u => u.id != "0001").Update(x => new T_SysErrorLog() { logLevel = "Error33324", logMessage = "出3错4342了", addTime = DateTime.Now });

                            //4. 模拟失败
                            baseService1.AddNo(new T_SysErrorLog() { id = Guid.NewGuid().ToString("N"), userId = Guid.NewGuid().ToString("N") + "1", addTime = DateTime.Now });  //模拟失败
                            baseService1.SaveChange();

                            //5.最后提交
                            transaction.Commit();

                        }
                        catch (Exception ex)
                        {
                            Console.WriteLine(ex.Message);

                        }
                    }

                }
                #endregion



                Console.WriteLine("执行完成");
                watch.Stop();
                Console.WriteLine($"时间为:{watch.ElapsedMilliseconds}ms");
            }

PS：上述代码事务的时候必须是同一个db

8. 并发测试

(1). 监测单个字段： [ConcurrencyCheck] 或 entity.Property(p => p.age).IsConcurrencyToken();

　配置T_Test表中的age4字段,进行测试, 有效可以使用。

(2). 监测整条数据：[Timestamp]或entity.Property(e => e.rowVersion).IsRowVersion();

　配置T_Test表新增timestamp类型的rowVersion字段，实体中进行上述配置，进行测试, 发现无效不能使用！！！

代码分享：

   #region 01-单字段监控
                //{
                //    CoreFrameDBContext db1 = new CoreFrameDBContext();
                //    CoreFrameDBContext db2 = new CoreFrameDBContext();
                //    try
                //    {
                //        var data1 = db1.T_Test.Where(u => u.id == 1).FirstOrDefault();
                //        var data2 = db2.T_Test.Where(u => u.id ==1).FirstOrDefault();

                //        data1.age4 = data1.age4 - 2;
                //        int result1 = db1.SaveChanges();

                //        data2.age4 = data2.age4 - 4;
                //        int result2 = db2.SaveChanges();  //发现age的值和原先查出来的不一致，会抛异常进入cache
                //    }
                //    catch (DbUpdateConcurrencyException ex)
                //    {

                //        var entityEntry = ex.Entries.Single();
                //        var original = entityEntry.OriginalValues.ToObject() as T_Test; //数据库原始值  10
                //        var database = entityEntry.GetDatabaseValues().ToObject() as T_Test; //数据库现在值 8
                //        var current = entityEntry.CurrentValues.ToObject() as T_Test; //当前内存值 6
                //        entityEntry.Reload(); //放弃当前内存中的实体，重新到数据库中加载当前实体

                //        current.age4 = database.age4 - 4;  //应该拿着当前数据库实际的值去处理，即8-4=4
                //        entityEntry.CurrentValues.SetValues(current);
                //        int result3 = db2.SaveChanges();
                //    }
                //}
                #endregion

                #region 02-全字段监控
                //{
                //    CoreFrameDBContext db1 = new CoreFrameDBContext();
                //    CoreFrameDBContext db2 = new CoreFrameDBContext();
                //    try
                //    {
                //        var data1 = db1.T_Test.Where(u => u.id == 1).FirstOrDefault();
                //        var data2 = db2.T_Test.Where(u => u.id == 1).FirstOrDefault();

                //        data1.age4 = data1.age4 - 2;
                //        int result1 = db1.SaveChanges();

                //        data2.age4 = data2.age4 - 4;
                //        int result2 = db2.SaveChanges();  //发现age的值和原先查出来的不一致，会抛异常进入cache
                //    }
                //    catch (DbUpdateConcurrencyException ex)
                //    {

                //        var entityEntry = ex.Entries.Single();
                //        var original = entityEntry.OriginalValues.ToObject() as T_Test; //数据库原始值  10
                //        var database = entityEntry.GetDatabaseValues().ToObject() as T_Test; //数据库现在值 8
                //        var current = entityEntry.CurrentValues.ToObject() as T_Test; //当前内存值 6
                //        entityEntry.Reload(); //放弃当前内存中的实体，重新到数据库中加载当前实体

                //        current.age4 = database.age4 - 4;  //应该拿着当前数据库实际的值去处理，即8-4=4
                //        entityEntry.CurrentValues.SetValues(current);
                //        int result3 = db2.SaveChanges();
                //    }
                //}
                #endregion

9. 索引映射

　给T_Test表中的name1添加索引,age1和age2添加联合索引,通过指令映射，发现索引映射成功。

    [Index(nameof(age1), nameof(age2), Name = "ids_age")]
    [Index(nameof(name1), Name = "ids_name1")]
    public partial class T_Test{}

 

 

 

 

 

 

 

 

!

• 作       者 : Yaopengfei(姚鹏飞)
• 博客地址 : http://www.cnblogs.com/yaopengfei/
• 声     明1 : 如有错误，欢迎讨论，请勿谩骂^_^。
• 声     明2 : 原创博客请在转载时保留原文链接或在文章开头加上本人博客地址，否则保留追究法律责任的权利。