MysqlEFCore 查看生成SQL方法

如题项目都是MySql数据库，没有权限开SQL日志，也没有像SQLSERVER那样好用的工具sqlprofile，https://www.jb51.net/article/147117.htm?tdsourcetag=s_pctim_aiomsg
mysql中profile的使用方法教程，只能查看最近本地执行SQL，无法查看到ORM生成的SQL

终于找到了一个方法方便本地调试查看SQL语句（喜大普奔）
转自：https://blog.csdn.net/weixin_30800987/article/details/99083201
代码：

using Microsoft.EntityFrameworkCore.Query;
using Microsoft.EntityFrameworkCore.Query.Internal;
using Microsoft.EntityFrameworkCore.Storage;
using System.Linq;
using System.Reflection;


namespace Common.Extension
{
    public static class QueryableExtensions
    {
        private static readonly TypeInfo QueryCompilerTypeInfo = typeof(QueryCompiler).GetTypeInfo();
        private static readonly FieldInfo QueryCompilerField = typeof(EntityQueryProvider).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryCompiler");
        private static readonly FieldInfo QueryModelGeneratorField = typeof(QueryCompiler).GetTypeInfo().DeclaredFields.First(x => x.Name == "_queryModelGenerator");
        private static readonly FieldInfo DataBaseField = QueryCompilerTypeInfo.DeclaredFields.Single(x => x.Name == "_database");
        private static readonly PropertyInfo DatabaseDependenciesField = typeof(Database).GetTypeInfo().DeclaredProperties.Single(x => x.Name == "Dependencies");

        /// <summary>
        /// 获取本次查询SQL语句
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="query"></param>
        /// <returns></returns>
        public static string ToSql<TEntity>(this IQueryable<TEntity> query)
        {
            var queryCompiler = (QueryCompiler)QueryCompilerField.GetValue(query.Provider);
            var queryModelGenerator = (QueryModelGenerator)QueryModelGeneratorField.GetValue(queryCompiler);
            var queryModel = queryModelGenerator.ParseQuery(query.Expression);
            var database = DataBaseField.GetValue(queryCompiler);
            var databaseDependencies = (DatabaseDependencies)DatabaseDependenciesField.GetValue(database);
            var queryCompilationContext = databaseDependencies.QueryCompilationContextFactory.Create(false);
            var modelVisitor = (RelationalQueryModelVisitor)queryCompilationContext.CreateQueryModelVisitor();
            modelVisitor.CreateQueryExecutor<TEntity>(queryModel);
            var sql = modelVisitor.Queries.First().ToString();

            return sql;
        }
    }
}

测试：

 var monthlaborlist = dbContext.test.Where(c => c.orgid == orgid && c.accountsid == accountsid && c.isdel == 0);
                var test = QueryableExtensions.ToSql<test>(monthlaborlist);

结果：

SELECT `c`.`id`, `c`.`createdTime`, `c`.`customadd`, `c`.`customperson`, `c`.`customsub`, `c`.`customtaxadd`, `c`.`customtaxsub`, `c`.`dept`, `c`.`idnumber`, `c`.`isdel`, `c`.`jobnumber`, `c`.`mobile`, `c`.`month`, `c`.`name`, `c`.`noticeflag`, `c`.`orderid`, `c`.`orgid`, `c`.`period`, `c`.`quickcalculation`, `c`.`taxpay`, `c`.`taxrate`, `c`.`updatedTime`, `c`.`year`
FROM `test` AS `c`
WHERE ((`c`.`orgid` = '9001888') AND (`c`.`accountsid` = '888')) AND (`c`.`isdel` = 0)