How to monitor database roundtrips

I have a web application that is using Entity Framework to query a SQL Server database. I would like 开发者_运维技巧to be able to know programmatically how many queries (round-trips) were executed on the database. The idea is to log that information to easily detect errors where a relationship was not included and caused lots of round-trips.

Is there a way to achieve this? I don't mind if the solution is specific to SQL Server.

Note: I want to monitor the database programmatically, therefore tools such as SQL Server Profiler are no use to me. I want to be able to know, at the end of a request and in the code that handles the request, how many queries were executed by that request.

This is my final solution, based on Craig Stuntz suggestion.

using(var database = new MyEntities())
{
    SqlConnection sqlConnection = null;

    var entityConnection = _database.Connection as EntityConnection;
    if (entityConnection != null)
    {
        sqlConnection = entityConnection.StoreConnection as SqlConnection;

        // Enable statistics
        sqlConnection.StatisticsEnabled = true;
    }

    // Access the database

    if (sqlConnection != null)
    {
        var statistics = sqlConnection.RetrieveStatistics();
        var selectCount = (long)statistics["SelectCount"];

        // Do something with the statistics
    }
}

You can use the provider statistics feature of the SQL connection.

Maybe use EFProf?

A couple of thoughts:

• If you use T4 templates to produce your entity contexts, you can alter the generated code to provide logging information or whatever you want.
• If you connect to your dlls using LinqPad, you can run any methods you want and LinqPad will capture the queries in the SQL tab.

I did this the hard way using SQL Server 2008 Profiler, if you set the right filters in profiler you will see only the calls made by EF.