两种查看EFCore生成Sql语句的方法
一.利用反射生成查询语句
该方法转载自: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输出