Profiling LINQ to SQL using the DataContext.Log Property
Lately, I have been playing around with ASP.NET's new LinqDataSource. Most of my data resides in a SQL Server database, so I always configure the LinqDataSource to work with the LINQ to SQL DataContext and Entity objects. These components are still pretty new to be, so I am still kind of in awe at how it all works together so seamlessly.
I guess maybe I am a little paranoid, but I don't like that using LINQ to SQL seems like magic. Sooner or later something is not going to work, and I am going to have to figure out why that is. Maybe it's a performance problem, maybe it's a data integrity issue, but sooner or later I am going to have to understand (at least a little bit) about how LINQ to SQL works so I can effectively debug my problem.
I found that displaying the SQL statements the LINQ to SQL runtime generates will at least help me understand how LINQ to SQL interacts with my DBMS. I guess this is my starting point to better understanding LINQ to SQL. Of course you can always set up a profiler trace to view the emitted SQL, but I find the following steps are also pretty helpful ...
Step 1: Add a Control to the Page for Displaying the SQL Trace
I am going to display my SQL trace in a TEXTAREA HTML element. So I add it to my page, make it visible from the codebehind by setting the runat attribute.
Step 2: Handle the ContextCreated LinqDataSource Event
Next, I add an event handler for the LinqDataSource's ContextCreated event. This event fires after the data source creates the object specified by the ContextTypeName attribute. For this sample, I am using a DataContext created from the Northwind database, so that is the context object that is created. In this event handler, I get a reference to the DataContext instance that was created and set the Log property to an instance of a System.IO.StringWriter. The DataContext will write any SQL statements it generates to the TextWriter that is assigned to the Log property before it sends it to the database.
Step 3: Handle your DataSource Event
For my page, I am only selecting data so I added an event handler to the Selected event and grabbed the contents of the writer object I created in the ContextCreated event and moved the output to my TEXTAREA element.
Now when the page renders, the TEXTAREA will contain the statements that were used to populate my grid.
That's it. Enjoy!