两种查看EFCore生成Sql语句的方法

作者: 别吵吵吻我
来源: 51数据库
2021-11-10

一.利用反射生成查询语句

该方法转载自：https://jhrs.com/2019/28488.html

using microsoft.entityframeworkcore.query;
using microsoft.entityframeworkcore.query.internal;
using microsoft.entityframeworkcore.storage;
using system.linq;
using system.reflection;

namespace common.standard.efcore
{
    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 ss = _context.usertable.where(m => m.username == "admin");
string sql = ss.tosql();

效果

备注：该扩展貌似只能应用于ef查询方法，我尝试过各种重写方法，仍然不能完美的生成真实执行的sql语句，如果哪位高人有办法做到请在评论区指导一下。

二、microsoft.extensions.logging.debug在控制台输出完整调试日志

这种方法是比较推荐的，可以完整的输出ef生成的sql语句

1.用nuget管理器添加microsoft.extensions.logging.debug包

2.修改dbcontext类

public partial class mydbncontext : dbcontext
{
        [obsolete]
        public static readonly loggerfactory loggerfactory = new loggerfactory(new[] { new debugloggerprovider((_, __) => true) });
      
... ...


        protected override void onconfiguring(dbcontextoptionsbuilder optionsbuilder)
        {
            base.onconfiguring(optionsbuilder);
            optionsbuilder.useloggerfactory(loggerfactory);
        }

... ...
}

3.在想要查看sql的db.savechanges()方法上加断点，然后调试程序。

4.点击visualstudio工具条：调试->窗口->输出，打开输出日志，并搜索entityframeworkcore,结果如下

红框内圈出的就是完整的sql输出

参考项目：