How to allow MiniProfiler to see EF DbContext.Database.SqlQuery queries

Question

I'm tuning some Entity Framework queries that are taking too long, and in some cases am resorting to executing raw SQL via dbContext.Database.SqlQuery<Entity>(sql, [parameters]).

However, these queries are no longer shown in MiniProfiler next to the profiler.Step text. What do I need to do to get them to show up again?

I have done some searching and am finding little. Since I'm doing the query through EF I thought it would work. I'm pretty sure I've seen Dapper queries appear in MiniProfiler, so obviously random queries are possible to hand over to MiniProfiler, I just don't know what I'm doing wrong.

See the red oval in the below image: it should be showing a time there that I can click on to view the SQL. In addition, the SQL time just below that now omits the queries I've converted.

The only kind of change I made was to convert a query like this:

return db.Blornk.Where(b => b.HasPlutonium = @flag);

to one like this:

return db.Database.SqlQuery<Blornk>(@"
   SELECT *
   FROM Blornk
   WHERE HasPlutonium = @flag",
   new SqlParameter("@flag", flag)
);

Of course, EF was being retarded which is why I made the change in the first place, but it doesn't even matter if it's the same query, I just need to know how to get MiniProfiler happy again.

Answer 1

According to the MiniProfiler authors, it can't be done due to the way MiniProfiler hooks into EF6. A workaround is shown but it requires manually sending our query through a profiled connection.

There is a workaround. The code below uses Dapper and the datacontext connection to create a ProfiledDbConnection, which accepts the same sql, returns the same result and does record the sql.

using (MiniProfiler.Current.Step("Get Count using ProfiledConnection - sql recorded"))
{
    using (var conn = new ProfiledDbConnection(context.Database.Connection, MiniProfiler.Current))
    {
        conn.Open();
        newCount = conn.Query<int>(sql).Single();
        conn.Close();
    }
}