Entity Framework trace provider wrapper without configuration files

Question

I would like to use the first Entity Framework Code approach with the SQLCE4 database. Everything seems really nice, but I have a problem with debugging sql queries. I found that EFTracing from http://efwrappers.codeplex.com/ should be exactly what I need, but I don’t know how to use it without the app.config file. I am not a big fan of this configuration. I want to use only C # code to configure and run. I think it should be good to use code like this:

using (System.Data.Common.DbConnection c = new EFTracingProvider.EFTracingConnection( new System.Data.SqlServerCe.SqlCeConnection(conn))) { using (var context = new MyContext(c)) { var a = from data in context.Projects select data; } } 

But that will not work. This throws an exception:

Unable to determine the provider name for the connection type EFTracingProvider.EFTracingConnection ".

Is there an easy way to correctly create a wrapped connection only in code?

Answer 1

The solution to my problem is with the DbContext object.

 public class MyContext : DbContext { public MyContext() : base(CreateConnection("Data Source=file.sdf", "System.Data.SqlServerCe.4.0"), true) { } public DbSet<Project> Projects { get; set; } public static bool TraceEnabled = true; private static DbConnection CreateConnection(string connectionString, string providerInvariantName) { DbConnection connection = null; if (TraceEnabled) { EFTracingProviderConfiguration.RegisterProvider(); EFTracingProviderConfiguration.LogToConsole = true; string wrapperConnectionString = String.Format(@"wrappedProvider={0};{1}", providerInvariantName, connectionString); connection = new EFTracingConnection() { ConnectionString = wrapperConnectionString }; } else { DbProviderFactory factory = DbProviderFactories.GetFactory(providerInvariantName); connection = factory.CreateConnection(); connection.ConnectionString = connectionString; } return connection; } } 

So, now I can use only the context, and the connection is created automatically for the wrapped or expanded SqlCe depending on the TraceEnabled property.

 using (var context = new MyContext()) { var a = context.Projects.FirstOrDefault(); } 

Answer 2

The true way to track SQL queries is to call the ToString method as follows:

 var t = from c in _entities.CompanyDetail select c; string test = t.ToString(); 

I do not know EFTracing, but you can try MVCMiniProfiler . Despite the fact that the name MVCMiniProfiler also provides profiling of SQL queries and work without a configuration file.

Answer 3

I did this by creating a wrapper class around an ObjectContext and using that wrapper instead of the original context. Here is an example of a context wrapper:

 public partial class LoggedContext : MyContext { public LoggedContext() : this("name=MyEntities") // Adjust this to match your entities { } public LoggedContext(string connectionString) : base(EntityConnectionWrapperUtils.CreateEntityConnectionWithWrappers(connectionString) { } private EFTracingConnection TracingConnection { get { return this.UnwrapConnection<EFTracingConnection>(); } } public event EventHandler<CommandExecutionEventArgs> CommandExecuting { add { this.TracingConnection.CommandExecuting += value; } remove { this.TracingConnection.CommandExecuting -= value; } } public event EventHandler<CommandExecutionEventArgs> CommandFinished { add { this.TracingConnection.CommandFinished += value; } remove { this.TracingConnection.CommandFinished -= value; } } public event EventHandler<CommandExecutionEventArgs> CommandFailed { add { this.TracingConnection.CommandFailed += value; } remove { this.TracingConnection.CommandFailed -= value; } } } 

I also have a static class that defines a trace output method and has a static method to initialize the trace. Here:

 public static class EFTracingExtensions { private static ILogger _logger; public static void InitSqlTracing(ILogger logger) { _logger = logger; EFTracingProviderConfiguration.RegisterProvider(); if (logger.IsLoggingEnabled()) // Don't add logging hooks if logging isn't enabled { EFTracingProviderConfiguration.LogAction = new Action<CommandExecutionEventArgs>(AppendSqlLog); } } private static void AppendSqlLog(CommandExecutionEventArgs e) { if (e.Status != CommandExecutionStatus.Executing) // we only care about Finished and Failed { StringBuilder msg = new StringBuilder(e.ToTraceString().TrimEnd()); msg.Append(Environment.NewLine); if (e.Result is SqlDataReader) { int rows = ((SqlDataReader)e.Result).HasRows ? ((SqlDataReader)e.Result).RecordsAffected : 0; msg.AppendFormat("*** {0} rows affected", rows); } else if (e.Result is int) { msg.AppendFormat("*** result: {0}", e.Result); } else { msg.AppendFormat("*** finished, result: {0}", e.Result); } msg.Append(Environment.NewLine); msg.AppendFormat(" [{0}] [{1}] in {2} seconds", e.Method, e.Status, e.Duration); _logger.Log(msg.ToString(), LoggerCategories.SQL); } } } 

ILogger is the logging interface that I use. You need to substitute your own interface / methods.

The InitSqlTracing method runs once when my program starts, and then the LoggedContext class is used to register all the SQL generated by the Entity Framework.

Putting it all together with your sample code:

 EFTracingExtensions.InitSqlTracing(logger); // only call this once using (var context = new LoggedContext()) { var a = from data in context.Projects select data; }