Working with Entity Framework Profiler

I am very new to Entity Framework and only just getting my head around using it in my own projects.  But first I had an application with I knew was built on EF but it was exhibiting some concerning behaviour.  It was using a large number of queries to the database as determined from the SQL Profiler that comes with Sql Server 2005.  I felt this was a little excessive and suspected a SELECT N+1 Anti-Pattern was involved.

But how do I prove it?

I knew of a great sounded tool called EFProfiler and it sounded like a great way to get concrete evidence that the application was not playing nice with the database.

So I downloaded the trial version of EFProfiler to test my hypothesis.

But I run into a problem.

The documentation says I have to add a reference to a dll and in Application_Start in the Global.asax file, I have to add a single line of code.  This is a problem as I do not have access to the code and so I am very limited as to what I can change.

I consider what my options are and I recall that another great tool I make use of called Elmah has a means of easily slipping itself into a web application without needing to add code to reference it.  How it does this is by being an HttpModule.

HttpModules can be added to an application through the web.config which references the dll the module is conpiled and becomes part of the HttpApplication pipeline.  But my first read of the documentation, I can hook into events like BeginRefresh, but there is no indication that I can hook into Application_Start.

This is until I found this question on StackOverflow which lead me to an article on How to correctly use IHttpModule to handle Application_OnStart event.

So I quickly throw together a little project to create my own HttpModule.

Imports System.Web
Imports HibernatingRhinos.Profiler.Appender

Public Class EFProfilerModule
  Implements IHttpModule

  Private Shared ApplicationStarted As Boolean = False
  Private Shared ApplicationStartLock As New Object()

  Public Sub Dispose() Implements System.Web.IHttpModule.Dispose

  End Sub

  Public Sub Init(application As System.Web.HttpApplication) Implements System.Web.IHttpModule.Init

    If Not ApplicationStarted Then
      SyncLock ApplicationStartLock
        If Not ApplicationStarted Then
          Me.OnStart(application)
          ApplicationStarted = True
        End If
      End SyncLock
    End If

    Me.OnInit(application)

  End Sub

  Public Overridable Sub OnStart(application As HttpApplication)

    EntityFramework.EntityFrameworkProfiler.Initialize()

  End Sub

  Public Overridable Sub OnInit(application As HttpApplication)

    ' put your module initialisation code here

  End Sub

End Class

Then all I needed to do was add two dll’s to the bin folder of the application.  The first being my dll with my customer HttpModule and the other being the file HibernatingRhinos.Profiler.dll.  Then I just added one line to the HttpModules section of the web.config.

<add type="CustomModule.EFProfilerModule" name="EFProfilerModule" />

I then fired up EFProf and navigated to the application and it worked!  EFProf started listing sessions and I could immediately see a number of red and grey dots signifying there were some issues found!

After just going to the login screen, signing in and taken to the home page (only two pages) the profiler picked up the following…

Navigating to the login screen only listed two object contexts.  Signing in and loading the Home page produced 16 more object contexts.

I had my evidence and analysis from a commercial product!

My team are looking to use Entity Framework for data access in our projects and to help prevent ourselves from writing sub-optimal queries I will be advocating we purchase the full commercial version of EFProf as it takes all the pain away from detecting issues with your data access code and gives you guidance to fixing any problems.