Entity Framework Tracing provider wrapper without config files

Question

I would like to use Entity Framework Code first approach with SQLCE4 database. Everything seems to be really nice but I have 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 app.config file. I am not big fan of this configuration. I want to use only C# code to set everything up and running. I think it should be fine 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 it doesn't work. It throws exception:

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

Is there any simple way how to correctly create wrapped connection only in code?

Answer 1

Solution for my problem is following 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 just context and connection is created automatically for wrapped or unwrapped SqlCe depending on TraceEnabled property.

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

Answer 2

I've done this by creating a wrapper class around the ObjectContext and using that wrapper instead of the original context. Here's an example 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 the tracing output method and has a static method to initialize tracing. 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 I'm using. You need to substitute your own interface/methods.

The InitSqlTracing method is invoked once when my program starts up, and then the LoggedContext class is used to log all the SQL generated by 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;
}

Answer 3

The genuine way to trace SQL queries is to call the ToString method like that :

var t = from c in _entities.CompanyDetail
        select c;

string test = t.ToString();

I don't know EFTracing, but you might want to try MVCMiniProfiler. Despite the name MVCMiniProfiler also provide SQL queries profiling and work without config file.