LINQ To SQL Profiler And How To Swing It

[warning: technical post]

My friend showed me a cool profiler the other day by the smart chaps at Hibernating Rhinos which lets you see what is going on behind the scenes of the magic black box known as "LINQ To SQL" (L2S). I've been using LINQ to SQL for almost 2 years now and while it's far from perfect it makes my job (life!) much easier. Unfortunately by hiding the inner workings away from the developer by the time you come to look at the performance of your website (or desktop app) you're pretty much left in the dark. That's where L2SProf comes in. It hooks into your datacontext and gives you a great view of what's happening back there...


Now, the set up is pretty straightforward with L2SProf and involves adding a reference to a single DLL into your app (web or otherwise) then copying and pasting a single line of code into your startup method (e.g. Application_Start in global.asax for a website). Once you've done that you restart your application, crack open the included L2SProf.exe and voila, your LINQ to SQL datacontext's secrets are no longer secret... unless you're me. I got a big fat nothing.

After trying to sort it out with my friend and failing I jumped onto the support forums and posted a quick message asking for help. There was a bit of to-ing and fro-ing over email with not much accomplished. Then Oren Eini (AKA Ayende Rahien) popped onto the scene (then Skype) to give me some help.

First we tried writing the profiler data to a text file (a slight modification to that single line of code I mentioned earlier) and while the log file was created, nothing was written to it. Next we created a brand new web application from scratch and everything worked with the profiler showing my single datacontext call and its corresponding SQL code. Oren looked over the web.config from my original website and decided the problem wasn't in there. After fiddling around some more we came across a little bit of code I'd put in place to stop Visual Studio from trying to read my connection string from the data model project's app.config. This was our "aha" moment.

Normally you'd either have your DBML file (LINQ to SQL datacontext) in your website or if it's out in another project in your solution, you'd let Visual Studio control the database connections by dropping them into the app.config, that's not how I had it set up. This is the method I had in place in my DataContext.cs...

public MyDataContext() : base(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)
{
OnCreated();
}

I'll let Oren explain what was going on here...

"The problem with doing it this way is that you are forcing L2S to build a new model every time that you create a data context, that is quite expensive, from a perf point of view. L2S have to use reflection to get the values each and every time. A side affect of that is that it also kills the way L2S Prof works. Here is how to make it both performance and work with L2S Prof:"

public MyDataContext() : base(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString, mappingSource)
{
OnCreated();
}

Once we did this everything fell into place, data access information pouring out of my website. So there you go, once again proving that if you don't do what Visual Studio wants you to do you can get into trouble.

Thanks a lot to Oren for his help and patience in helping me solve this problem. Now I need to go see how badly my website is performing.