记录EFCore产生的SQL语句到日志

记录EFCore产生的SQL语句到日志

以下以ABP项目为例子

1, 先创建LoggerProvider

using Microsoft.Extensions.Logging;
using System;

namespace demo
{
    public class EfLoggerProvider : ILoggerProvider
    {
        public Castle.Core.Logging.ILogger Logger;

        public EfLoggerProvider(Castle.Core.Logging.ILogger logger)
        {
            Logger = logger;
        }

        public ILogger CreateLogger(string categoryName)
        {
            return new EfLogger(categoryName, Logger);
        }

        public void Dispose()
        {
        }
    }

    public class EfLogger : ILogger
    {
        public Castle.Core.Logging.ILogger Logger { get; set; }

        private readonly string _categoryName;

        public EfLogger(string categoryName, Castle.Core.Logging.ILogger logger)
        {
            this._categoryName = categoryName;
            this.Logger = logger;
        }

        public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
        {
            //ef core执行数据库查询时的categoryName为Microsoft.EntityFrameworkCore.Database.Command,日志级别为Information
            //过滤掉了定时任务的日志,读取多语言,记录接口日志等SQL
            var logContent = formatter(state, exception);

            if (logLevel == LogLevel.Error)
            {
                Logger.Fatal(logContent); //错误日志
                return;
            }

            //过滤掉定时任务等脚本的日志
            if (logContent.Contains("AbpBackgroundJob") || logContent.Contains("AbpLanguage") || logContent.Contains("AbpAuditLog") || logContent.Contains("AbpSetting"))
            {
                return;
            }

            if (_categoryName == "Microsoft.EntityFrameworkCore.Database.Command" && logLevel == LogLevel.Debug && logContent.Contains("Executing DbCommand"))
            {
                Logger.Fatal(logContent); //创建的sql
                return;
            }
        }

        IDisposable ILogger.BeginScope<TState>(TState state)
        {
            return null;
        }

        public bool IsEnabled(LogLevel logLevel) => true;
    }
}

2, 创建LoggerFactory,并给DbContextOptionsBuilder使用

public class bsmEntityFrameworkModule : AbpModule
{
	/* Used it tests to skip dbcontext registration, in order to use in-memory database of EF Core */

	public override void PreInitialize()
	{
		if (!SkipDbContextRegistration)
		{
			Configuration.Modules.AbpEfCore().AddDbContext<bsmDbContext>(options =>
			{
				if (options.ExistingConnection != null)
				{
					bsmDbContextConfigurer.Configure(options.DbContextOptions, options.ExistingConnection);
				}
				else
				{
					//2,在这里使用
					bsmDbContextConfigurer.Configure(options.DbContextOptions, options.ConnectionString, DbLoggerFactory);
				}
			});
		}
	}

 ......

	//1,构建单例对象,记录数据库日志
	private static LoggerFactory _loggerFactory { get; set; }

	private Microsoft.Extensions.Logging.LoggerFactory DbLoggerFactory
	{
		get
		{
			if (null == _loggerFactory)
			{
				_loggerFactory = new Microsoft.Extensions.Logging.LoggerFactory();
				_loggerFactory.AddProvider(new EfLoggerProvider(Logger));
			}

			return _loggerFactory;
		}
	}
}

配合修改DbContextConfigurer

using System.Data.Common;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace Best.EntityFrameworkCore
{
    public static class BestDbContextConfigurer
    {
        public static void Configure(DbContextOptionsBuilder<BestDbContext> builder, string connectionString, ILoggerFactory _loggerFactory)
        {
            //使用传进来的_loggerFactory
            builder.UseLoggerFactory(_loggerFactory).UseSqlServer(connectionString);
            builder.EnableSensitiveDataLogging(); //日志显示脚本参数
        }

        public static void Configure(DbContextOptionsBuilder<BestDbContext> builder, DbConnection connection)
        {
            builder.UseSqlServer(connection);
        }
    }
}

log4net.config推荐配置

<?xml version="1.0" encoding="utf-8"?>

<log4net>
	<!-- 全部的日志 OFF > FATAL > ERROR > WARN > INFO > DEBUG  > ALL  -->
	<!-- 正在执行的sql -->
	<appender name="SQLExecuting" type="log4net.Appender.RollingFileAppender" >
		<file value="App_Data/Logs/SQLExecuting" />
		<appendToFile value="true" />
		<rollingStyle value="Date" />
		<datePattern value="yyyyMM\\'SQL'yyyyMMddHH'.txt'"/>
		<maxSizeRollBackups value="100" />
		<maximumFileSize value="10MB" />
		<staticLogFileName value="false" />
		<layout type="log4net.Layout.PatternLayout">
			<conversionPattern value="%newline%date [%thread] [%-5level] %logger %newline%message%newline"/>
		</layout>
		<filter type="log4net.Filter.LevelRangeFilter">
			<levelMin value="Fatal" />
			<acceptOnMatch value="false" />
		</filter>
		<filter type="log4net.Filter.StringMatchFilter">
			<stringToMatch value="Executed" />
			<!-- 不显示包含executed的日志 -->
			<acceptOnMatch value="false" />
		</filter>
	</appender>

	<appender name="Error" type="log4net.Appender.RollingFileAppender" >
		<file value="App_Data/Logs/Error" />
		<appendToFile value="true" />
		<rollingStyle value="Date" />
		<datePattern value="yyyyMM/'Error'yyyyMMddHH'.txt'"/>
		<maxSizeRollBackups value="100" />
		<maximumFileSize value="10MB" />
		<staticLogFileName value="false" />
		<layout type="log4net.Layout.PatternLayout">
			<conversionPattern value="%newline%date [%thread] [%-5level] %logger property: [%property{NDC}] %newline%message%newline"/>
		</layout>
		<filter type="log4net.Filter.LevelRangeFilter">
			<levelMin value="Error" />
		</filter>
	</appender>

	<appender name="Info" type="log4net.Appender.RollingFileAppender" >
		<file value="App_Data/Logs/Info" />
		<appendToFile value="true" />
		<rollingStyle value="Composite" />
		<datePattern value="yyyyMM\\'Info'yyyyMMddHH'.txt'"/>
		<maxSizeRollBackups value="100" />
		<maximumFileSize value="10MB" />
		<staticLogFileName value="false" />
		<layout type="log4net.Layout.PatternLayout">
			<conversionPattern value="%newline%date [%thread] [%-5level] %logger %newline%message%newline"/>
		</layout>
		<filter type="log4net.Filter.LevelRangeFilter">
			<levelMin value="Info" />
			<levelMax value="Info" />
		</filter>
	</appender>

	<appender name="Warn" type="log4net.Appender.RollingFileAppender" >
		<file value="App_Data/Logs/Warn" />
		<appendToFile value="true" />
		<rollingStyle value="Composite" />
		<datePattern value="yyyyMM\\'Warn'yyyyMMddHH'.txt'"/>
		<maxSizeRollBackups value="100" />
		<maximumFileSize value="10MB" />
		<staticLogFileName value="false" />
		<layout type="log4net.Layout.PatternLayout">
			<conversionPattern value="%newline%date [%thread] [%-5level] %logger %newline%message%newline"/>
		</layout>
		<filter type="log4net.Filter.LevelRangeFilter">
			<levelMin value="Warn" />
			<levelMax value="Warn" />
		</filter>
	</appender>

	<appender name="Debug" type="log4net.Appender.RollingFileAppender" >
		<file value="App_Data/Logs/Debug" />
		<appendToFile value="true" />
		<rollingStyle value="Composite" />
		<datePattern value="yyyyMM\\'Debug'yyyyMMddHH'.txt'"/>
		<maxSizeRollBackups value="100" />
		<maximumFileSize value="10MB" />
		<staticLogFileName value="false" />
		<layout type="log4net.Layout.PatternLayout">
			<conversionPattern value="%newline%date [%thread] [%-5level] %logger %newline%message%newline"/>
		</layout>
		<filter type="log4net.Filter.LevelRangeFilter">
			<levelMin value="Debug" />
			<levelMax value="Debug" />
		</filter>
	</appender>

	<root>
		<appender-ref ref="Info" />
		<appender-ref ref="Error" />
		<appender-ref ref="Debug" />
		<appender-ref ref="Warn" />
		<appender-ref ref="SQLExecuting" />
		<appender-ref ref="SQLExecuted" />
		<level value="DEBUG" />
		<!-- Debug以上才记录 -->
	</root>

</log4net>