Create a class EntityFrameworkSqlLogger.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Microsoft.Extensions.Logging;
namespace MyApp.Logging
{
/// <summary>
/// ILogger implementation
/// </summary>
public class EntityFrameworkSqlLogger : ILogger
{
#region Fields
Action<EntityFrameworkSqlLogMessage> _logMessage;
#endregion
#region Constructor
public EntityFrameworkSqlLogger(Action<EntityFrameworkSqlLogMessage> logMessage)
{
_logMessage = logMessage;
}
#endregion
#region Implementation
public IDisposable BeginScope<TState>(TState state)
{
return default;
}
public bool IsEnabled(LogLevel logLevel)
{
return true;
}
public void Log<TState>(LogLevel logLevel, EventId eventId, TState state, Exception exception, Func<TState, Exception, string> formatter)
{
if (eventId.Id != 20101)
{
//Filter messages that aren't relevant.
//There may be other types of messages that are relevant for other database platforms...
return;
}
if (state is IReadOnlyList<KeyValuePair<string, object>> keyValuePairList)
{
var entityFrameworkSqlLogMessage = new EntityFrameworkSqlLogMessage
(
eventId,
(string)keyValuePairList.FirstOrDefault(k => k.Key == "commandText").Value,
(string)keyValuePairList.FirstOrDefault(k => k.Key == "parameters").Value,
(CommandType)keyValuePairList.FirstOrDefault(k => k.Key == "commandType").Value,
(int)keyValuePairList.FirstOrDefault(k => k.Key == "commandTimeout").Value,
(string)keyValuePairList.FirstOrDefault(k => k.Key == "elapsed").Value
);
_logMessage(entityFrameworkSqlLogMessage);
}
}
#endregion
}
/// <summary>
/// Data model
/// </summary>
public class EntityFrameworkSqlLogMessage
{
public EntityFrameworkSqlLogMessage(
EventId eventId,
string commandText,
string parameters,
CommandType commandType,
int commandTimeout,
string elapsed
)
{
EventId = eventId;
CommandText = commandText;
Parameters = parameters;
CommandType = commandType;
Elapsed = elapsed;
CommandTimeout = commandTimeout;
}
public string Elapsed { get; }
public int CommandTimeout { get; }
public EventId EventId { get; }
public string CommandText { get; }
public string Parameters { get; }
public CommandType CommandType { get; }
}
/// <summary>
/// ILogger provider
/// </summary>
public class SingletonLoggerProvider : ILoggerProvider
{
#region Fields
ILogger _logger;
#endregion
#region Constructor
public SingletonLoggerProvider(ILogger logger)
{
_logger = logger;
}
#endregion
#region Implementation
public ILogger CreateLogger(string categoryName)
{
return _logger;
}
public void Dispose()
{
}
#endregion
}
}Add Logger to DbContext OnConfiguring Met
public class CartServiceDbContext : DbContext
{
...
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
var entityFrameworkSqlLogger = new EntityFrameworkSqlLogger((m) =>
{
System.Console.WriteLine($"SQL Query:\r\n{m.CommandText}\r\nElapsed:{m.Elapsed} millisecods\r\n\r\n");
});
var myLoggerFactory = LoggerFactory.Create(builder =>
{
builder
.AddFilter((category, level) =>
category == DbLoggerCategory.Database.Command.Name
&& level == LogLevel.Information);
});
myLoggerFactory.AddProvider(new SingletonLoggerProvider(entityFrameworkSqlLogger));
optionsBuilder.UseLoggerFactory(myLoggerFactory);
DbConnectorUtils.ConfigureOptionsBuilder(optionsBuilder);
}
...
}References
