Sql – Profile EntityFramework

entity-frameworksql

I have the following sample code:

  Context context = new Context();
  Repository repository = new Repository(context);

  Post post = repository.First<Post>(x => x.Id == 1);

  Model model = new Model {
    Created = cube.Created,
    Id = cube.Id,
    Name = cube.Name,
    Pack = cube.Pack.Id,
    Mimes = context.Files.Where(x => x.Id == 1).Select(x => x.Mime).ToList()
  };

I need to find which SQL queries are being sent to the database.

How can I profile the EF queries using SQL Express and VS 2012?

Is there any tool for this?

Best Answer

The EF Profiler specified in the other answer is a beast - wildly powerful, but that doesn't come for free ($$, disk space and time). It's also made by Oren Eini of NHibernate and RavenDB fame.

The tools I like to use in a pinch are way, way more light-weight, and of course can't begin to compare to EF Prof in terms of features - but the cost ($$, time and disk) is, or approaches, zero.

I tested these lightweight profilers with EF on LocalDb, but their main use cases are the more traditional Sql Server flavors (including Express).

ExpressProfiler

By far the easiest to use one (and also the most bare-bones-show-me-the-SQL-statements-kthxbye) is ExpressProfiler on CodePlex.

enter image description here

And the whole program is a single 126 KB EXE ! Now that's lightweight!

AnjLab Sql Profiler

The other one is the one from DataWizard, which used to be free (apparently prices now start at $5).

Somebody managed to save a snapshot on GitHub (including xcopy-installable binaries) when it was open-source.

The executable presents itself as "AnjLab Sql Profiler" and allows some filtering of the displayed events - but this strength is also its weakness, as there are sooo many event types that can be enabled/disabled. If everything is selected, the output is overwhelmingly verbose.

A saner output can be obtain by only selecting specific events.

enter image description here

Here are the events I have selected in order to get the above output:

enter image description here

Related Solutions

Entity-framework – SQL Server Express connection string for Entity Framework Code First

The problem with your connection string here is:

<add name="TrempimModel"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;
                       AttachDBFilename=|DataDirectory|aspnetdb.sdf;
                       User Instance=true"
     providerName="System.Data.SqlClient" />

You're basically defining what "server" you're connecting to - but you're not saying what database inside the file to connect to. ~~Also - the file extension for SQL Server Express database files is .mdf (not .sdf - that's SQL Server Compact Edition) - you need to take that into account, too!~~ (was a typo, according to comment by OP).

You need to define an extra database=.... (or Initial Catalog=.....) in your connection string:

<add name="TrempimModel"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;
                       database=YourDatabaseName;
                       AttachDBFilename=|DataDirectory|aspnetdb.mdf;
                       User Instance=true"
     providerName="System.Data.SqlClient" />

Then it should work just fine.

For more background and tons of samples, check out the ConnectionStrings web site.

Entity-framework – EntityFramework code-first custom connection string and migrations

If your migration does not work correctly try to set Database.Initialize(true) in DbContext ctor.

public CustomContext(DbConnection connection)
: base(connection, true)    
{    
        Database.Initialize(true);    
}

I have similar problem with migrations. And in my solution I have to always set database initializer in ctor, like below

public CustomContext(DbConnection connection)
: base(connection, true)    
{    
        Database.SetInitializer(new CustomInitializer());
        Database.Initialize(true);    
}

In custom initializer you have to implement InitalizeDatabase(CustomContex context) method, eg.

class CustomInitializer : IDatabaseInitializer<CustomContext>
{
    public void InitializeDatabase(CustomContext context)
    {
        if (!context.Database.Exists || !context.Database.CompatibleWithModel(false))
        {
            var configuration = new Configuration();
            var migrator = new DbMigrator(configuration);
            migrator.Configuration.TargetDatabase = new DbConnectionInfo(context.Database.Connection.ConnectionString, "System.Data.SqlClient");
            var migrations = migrator.GetPendingMigrations();
            if (migrations.Any())
            {
                var scriptor = new MigratorScriptingDecorator(migrator);
                string script = scriptor.ScriptUpdate(null, migrations.Last());
                if (!String.IsNullOrEmpty(script))
                {
                    context.Database.ExecuteSqlCommand(script);
                }
            }
        }
    }
}

UPDATED

Best Answer

ExpressProfiler

AnjLab Sql Profiler

Related Solutions

Entity-framework – SQL Server Express connection string for Entity Framework Code First

Entity-framework – EntityFramework code-first custom connection string and migrations

Related Topic