之前写过一篇如何利用 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,而这一点也成为后面的一个小障碍。
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; }
- }
- }
它负责初始化数据库,添加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));
- }
- }
- }
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());
- };
- }
- 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)
- ...
添加一个 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;
- }
- }
- 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());
- };
- }
- 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()
- ......
运行命令还是会得到下面的错误:
- System.Collections.Generic.KeyNotFoundException: The given key was not
- present in the dictionary.
- public TestDbEntities()
- : this("name=testDb")
- { }
解决方法有两个:
(1) 改回去,将默认的构造方法修改为:
- public TestDbEntities()
- : base("testDb")
- { }
(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 就不用说了,可以参看我前面这篇文章。