DbContextのSQLログ出力

こんにちは。beaglesoftの真鍋です。

ASP.NETとEntity Frameworkを利用して開発をしています。今回はEFが実行したSQLのログを出力する設定についてです。 なお、Unity.MVCを利用してDbContextを設定しています。

public class UnityConfig
    {
        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();

        #region Unity Container
        private static Lazy<IUnityContainer> container = new Lazy<IUnityContainer>(() =>
        {
            var container = new UnityContainer();
            RegisterTypes(container);
            return container;
        });

        /// <summary>
        /// Gets the configured Unity container.
        /// </summary>
        public static IUnityContainer GetConfiguredContainer()
        {
            return container.Value;
        }
        #endregion

        /// <summary>Registers the type mappings with the Unity container.</summary>
        /// <param name="container">The unity container to configure.</param>
        /// <remarks>There is no need to register concrete types such as controllers or API controllers (unless you want to 
        /// change the defaults), as Unity allows resolving a concrete type even if it was not previously registered.</remarks>
        public static void RegisterTypes(IUnityContainer container)
        {
            // NOTE: To load from web.config uncomment the line below. Make sure to add a Microsoft.Practices.Unity.Configuration to the using statements.
            // container.LoadConfiguration();
            ...
            var basicContext = new BasicContext();
            basicContext.Database.Log = s =>
            {
                if (Logger.IsDebugEnabled)
                {
                    Logger.Debug("SQL:{0}", s);
                }
            };
            container.RegisterType<BasicContext>(new PerRequestLifetimeManager(),new InjectionFactory(_ => basicContext));
        }
    }

このようにUnityConfig.csのRegisyterTypesメソッドで設定するDbContextであるBasicContextを作成してNLogでSQLを出力しています。

これでSQLの実行内容がログファイルやデバッグウィンドウに出力できます。

2016-01-01 11:02:42.7673 DEBUG SQL:2016/01/01 11:02:42 +09:00
 で接続を開きました
2016-01-01 11:02:43.1325 DEBUG SQL:SELECT 
    [Limit1].[AccessFailedCount] AS [AccessFailedCount], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[EmailAddress] AS [EmailAddress], 
...
    FROM ( SELECT TOP (2) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[EmailAddress] AS [EmailAddress], 
...
        WHERE [Extent1].[Id] = @p__linq__0
    )  AS [Limit1]
2016-01-01 11:02:43.1325 DEBUG SQL:

2016-01-01 11:02:43.1325 DEBUG SQL:-- p__linq__0: 'de2f4ea5-6966-4efd-b0b9-ae8a4130dbae' (Type = String, Size = 4000)

2016-01-01 11:02:43.1425 DEBUG SQL:-- 2016/01/01 11:02:43 +09:00
 で実行しています
2016-01-01 11:02:43.1425 DEBUG SQL:-- 3 ミリ秒で完了しました。結果: SqlDataReader

このUnityやNLogはとても便利なもので、一昔前の面倒な設定を考えると素晴らしいですね。