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~ 得到下面的异常：
<a target='_blank' href='http://www.daimami.com/search?q=System.ArgumentException'>System.ArgumentException</a> 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(<strong>EFTracingUtil.GetConnection</strong>(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 就不用说了，可以参看我前面这篇文章。