How to log SQL using entity framework 4.3 (code first) and SQL Azure database

Question

I realize this similar question has been asked a few times, and I have tried the recommendations in those questions without success.

I am using the entity framework (4.3) and running against SQL Azure (on a federated database). I'd like to be able to log the SQL that is being generated by the entity framework.

I've used the Entity Profiler Framework and while that is helpful during development, I am not sure it would be helpful during production.

I can't use SQL Profiler as this is a SQL Azure database.

I've tried using the EFTracingProvider and following the steps in here. Unfortunately, when I attempt to execute my first command (which is using the appropriate federation), I get an exception indicating that the "Specified method is not supported."

The code which generates the error is the following:

public MyContext(int tenantId) 
    : base(CreateTracingConnection("TheDb"), true)
{
    const string FederationCmdText =
        "USE FEDERATION TenantFederation(CustomerId = {0}) WITH RESET, FILTERING=ON";

    ((IObjectContextAdapter)this).ObjectContext.EnableTracing();

    ((IObjectContextAdapter)this).ObjectContext.Connection.Open();

    // This is the line which throws the exception
    this.Database.ExecuteSqlCommand(string.Format(FederationCmdText, tenantId));        
}

Here's the exception:

Specified method is not supported.
at EFProviderWrapperToolkit.DbConnectionWrapper.CreateDbCommand()
at System.Data.Common.DbConnection.CreateCommand()
...

So here are my questions:

Is EFTracingProvider the preferred approach for logging SQL queries (globally)?
If so, any ideas why I am getting the above exception?
If not, is there another mechanism that will allow me to log all of the SQL generated by the Entity Framework?

Thanks for your help, Eric

Eric · Accepted Answer · 2012-02-15 18:39:05Z

5

The issue that I was running into was because, I believe, the EFTracingProvider does not have support for executing SQL directly. In order to workaround that issue, one solution is the following (which I found in the Q&A from here)

Create the following class:

public class DbTracingConnection : EFTracingConnection
{
    protected override DbCommand CreateDbCommand()
    {
        return this.WrappedConnection.CreateCommand();
    }
}

When creating the connection, use the above class instead of the EFTracingConnection.

answered Feb 15, 2012 at 18:39

Eric

2,0873 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

slawek Over a year ago

This seems what authors recommend as well. Here in "Known Problems" they say to execute store commands by context.Connection.GetStoreConnection().CreateCommand(). So effectively doing what you did and use the wrapped connection :)

Collectives™ on Stack Overflow

How to log SQL using entity framework 4.3 (code first) and SQL Azure database

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related