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 就不用说了，可以参看我前面这篇文章。

span,div { font-family:'Microsoft YaHei'; }