ADO.NET Entity Framework CodeFirst 如何输出日志(EF4.3)

[示例代码下载]

之前写过一篇如何利用 EFProviderWrappers 在EF中增加日志的 blog，那篇文章是基于 ModelFirst 来写的，这里在 EF 4.3 CodeFirst 上再次实现。

1. 事前准备
下载 EFProviderWrappers 程序集（ 点击此处下载），添加：EFProviderWrapperToolkit.dll，EFTracingProvider.dll 引用。
并通过 NuGet 添加 EntityFramework 4.3， Log4Net。

App.config中添加连接字符串，注册 EFProviderWrappers
直接添加了一个空mdf文件(TestDB.mdf)作为Database，注意 CodeFirst 中连接字符串为普通的ADO.NET数据库连接字符串。ModelFirst中则是EntityConnectionString，而这一点也成为后面的一个小障碍。

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=4.3.1.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </configSections>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="Data Source=.\SQLEXPRESS; Integrated Security=True; MultipleActiveResultSets=True" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>

  <!-- 连接字符串 -->
  <connectionStrings>
    <add name="testDb" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;database=TestDB;AttachDBFilename=E:\Programming\VSProject2010\Linq\EFCodeFirstLogTractingTest\EFCodeFirstLogTractingTest\TestDB.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  <system.data>
    
  <!-- 注册 EF Provider Wrapper -->
   <DbProviderFactories>
      <add name="EF Tracing Data Provider" invariant="EFTracingProvider" description="Tracing Provider Wrapper" type="EFTracingProvider.EFTracingProviderFactory, EFTracingProvider, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
      <add name="EF Generic Provider Wrapper" invariant="EFProviderWrapper" description="Generic Provider Wrapper" type="EFProviderWrapperToolkit.EFProviderWrapperFactory, EFProviderWrapperToolkit, Version=1.0.0.0, Culture=neutral, PublicKeyToken=def642f226e0e59b" />
    </DbProviderFactories>
  </system.data>

</configuration>

2. 添加 Model, Entities, EntitiesInitializer
实体：Memo.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel.DataAnnotations;

namespace EFCodeFirstLogTractingTest.Models
{
    [Table("Memo")]
    public class Memo
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        public string Title { get; set; }
    }
}

TestDbEntities.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;

namespace EFCodeFirstLogTractingTest.Models
{
    public class TestDbEntities : DbContext
    {
        public TestDbEntities()
            : base("testDb")
        { }

        public DbSet<Memo> Memos { get; set; }
    }
}

TestDbEntitiesInitializer.cs
它负责初始化数据库，添加3条数据。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;

namespace EFCodeFirstLogTractingTest.Models
{
    public class TestDbEntitiesInitializer : DropCreateDatabaseAlways<TestDbEntities>
    {
        protected override void Seed(TestDbEntities context)
        {
            base.Seed(context);

            new List<Memo>
            {
                new Memo { Title="memo1" },
                new Memo { Title="memo2" },
                new Memo { Title="memo3" },
            }.ForEach(m => context.Memos.Add(m));
        }
    }
}

好了，CodeFirst已经可以工作了。

3. 添加 EFProviderWrappers
前面的经验告诉我，EFTracingProvider 通过扩展方法为 ObjectContext 添加了 GetTractingConnection()，TracingConnection里的 CommandExecuting 事件正是我们需要监听 Linq 转化成真正 SQL 的时机。
于是，我将上面的 TestDbEntities.cs 修改为如下，这样执行 Linq2EF 语句时，就能在控制台输出实际的SQL了。
（注意：此时 nameOrConnectionString 传入的是 name=testDb 而不是 testDb）

 public TestDbEntities()
     : this("name=testDb")
 { }

 public TestDbEntities(string nameOrConnectionString)
     : base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(nameOrConnectionString), true)
 {
     var ctx = ((IObjectContextAdapter)this).ObjectContext;
     ctx.GetTracingConnection().CommandExecuting += (s, e) =>
     {
         Console.WriteLine(e.ToTraceString());
     };
 }

看看好使不？Oh NO~ 得到下面的异常：

System.ArgumentException was unhandled
  HResult=-2147024809
  Message=The 'data source' keyword is not supported.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.EntityClient.EntityConnectionStringBuilder.set_Item(String keyword, Object value)
       at System.Data.Common.DbConnectionStringBuilder.set_ConnectionString(String value)
       at EFProviderWrapperToolkit.EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(String entityConnectionString, String[] wrapperProviders)
       ...

看了下 EFProviderWrappers 源码，才发现 EFProviderWrappers 只接受一个 EntityConnectionString( MSDN)。搜了下，按照下面的解决方法：
添加一个 EFTracingUtil 类：（别忘记添加 System.Configuration.dll）

public class EFTracingUtil
    {
        public static DbConnection GetConnection(string nameOrConnectionString)
        {
            try
            {
                // this only supports entity connection strings http://msdn.microsoft.com/en-us/library/cc716756.aspx
                return EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(nameOrConnectionString);
            }
            catch (ArgumentException)
            {
                if (nameOrConnectionString.Contains('='))
                {
                    nameOrConnectionString = nameOrConnectionString.Substring(nameOrConnectionString.IndexOf('=') + 1);
                }
                // an invalid entity connection string is assumed to be a normal connection string name or connection string (Code First)
                ConnectionStringSettings connectionStringSetting =
                    ConfigurationManager.ConnectionStrings[nameOrConnectionString];
                string connectionString;
                string providerName;

                if (connectionStringSetting != null)
                {
                    connectionString = connectionStringSetting.ConnectionString;
                    providerName = connectionStringSetting.ProviderName;
                }
                else
                {
                    providerName = "System.Data.SqlClient";
                    connectionString = nameOrConnectionString;
                }

                return CreateTracingConnection(connectionString, providerName);
            }
        }

        private static EFTracingConnection CreateTracingConnection(string connectionString, string providerInvariantName)
        {
            string wrapperConnectionString =
                String.Format(@"wrappedProvider={0};{1}", providerInvariantName, connectionString);

            EFTracingConnection connection =
                new EFTracingConnection
                {
                    ConnectionString = wrapperConnectionString
                };

            return connection;
        }
    }

把 TestDbEntities 的构造函数修改为如下：

public TestDbEntities(string nameOrConnectionString)
    : base(EFTracingUtil.GetConnection(nameOrConnectionString), true)
{
    var ctx = ((IObjectContextAdapter)this).ObjectContext;
    ctx.GetTracingConnection().CommandExecuting += (s, e) =>
    {
        Console.WriteLine(e.ToTraceString());
    };
}

再来试试，Oh Shit 还是有异常！

System.ArgumentException was unhandled
  HResult=-2147024809
  Message=The provider manifest given is not of type 'System.Data.SqlClient.SqlProviderManifest'.
  Source=System.Data.Entity
  StackTrace:
       at System.Data.SqlClient.SqlProviderServices.GetSqlVersion(StoreItemCollection storeItemCollection)
       at System.Data.SqlClient.SqlProviderServices.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
       at System.Data.Common.DbProviderServices.CreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
       at EFProviderWrapperToolkit.DbProviderServicesBase.DbCreateDatabase(DbConnection connection, Nullable`1 commandTimeout, StoreItemCollection storeItemCollection)
       at System.Data.Objects.ObjectContext.CreateDatabase()
......

错误来源很可能是 SqlClient 内部的，它预期得到 SqlProviderManifest 但得到的是 DbProviderManifestWrapper。只好绕道了，通过 Database.SetInitializer 不行... 还好EF 4.3的新特性：数据库迁移。在 Package Manager Console 里：Enable-Migrations
运行命令还是会得到下面的错误：

System.Collections.Generic.KeyNotFoundException: The given key was not 
present in the dictionary.

其实这个原因是因为我的 TestDbEntities() 无参数的默认构造方法：

public TestDbEntities()
    : this("name=testDb")
{ }

刚才改为 "name=testDb"， 而 Migrations 只认 "testDb" (因为它直接到 config 文件中读取 ConnectionString)
解决方法有两个：
(1) 改回去，将默认的构造方法修改为：

public TestDbEntities()
    : base("testDb")
{ }

注意：外部使用时如果想要 Tracing 就不能用默认构造方法了，因为它没用注册 CommandExecuting 事件处理。
(2) 修改 Migrations.Config 里的 TargetDatabase，这里是允许开发者直接修改它来实现各种复杂的数据迁移的。

public Configuration()
{
    var connectionString = ConfigurationManager.ConnectionStrings["TestDb"].ConnectionString;
    TargetDatabase = new DbConnectionInfo(connectionString, "System.Data.SqlClient");
    AutomaticMigrationsEnabled = true;
}

这里还是用了方法(1)，那么最后的使用如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using EFCodeFirstLogTractingTest.Models;
using System.Data.Entity.Migrations;

namespace EFCodeFirstLogTractingTest
{
    class Program
    {
        static void Main(string[] args)
        {
            //Database.SetInitializer<TestDbEntities>(new TestDbEntitiesInitializer());
            Migrations.Configuration config = new Migrations.Configuration();
            var migrator = new DbMigrator(config);
            migrator.Update();

            using (var db = new TestDbEntities("name=testDb"))
            {
                var result = db.Memos.OrderBy(m => m.Id).Skip(2).First();
                Console.WriteLine(result.Title);
            }

            Console.Read();
        }
    }
}

利用 Log4Net 就不用说了，可以参看我前面这篇文章。