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!
Comments
Amen to this post - I'm glad i'm not the only person who thinks that at some point something wont work as magically as it looks using Linq to SQL - superb post - I am reading up on Linq and it seems powerful and the Linq to XML API alone looks like a saviour.
Posted by: Gregor Suttie | January 21, 2008 07:52 AM
So far with my new website, I've experienced very good performance using linq to sql for my data access, ToList>()'ing results right away and then caching them. But I think the .dbml designer in VS2008 didn't wire up one of my many to many relationships properly.
Posted by: Matt Brown | January 21, 2008 11:03 AM
First they draw you in with the magic of the linq datasouce...Then you try to do disconected linq within a multi teir app...Then the smile turns flat...But it rocks for demos...
Posted by: Geoff | January 21, 2008 12:33 PM
Thank you so much for posting this info!
After playing with your technique, I decided I wanted to display the LINQ SQL via an ASP.NET Page Trace, instead a text box.
I managed to accomplish this by creating a TextWriter class. I posted the details here:
http://www.goondocks.com/blog/08-04-22/using_asp_net_page_trace_to_view_linq_sql.aspx
Hopefully this will help others. Thanks again for helping me with this Matt.
Posted by: Gabe Sumner | April 22, 2008 03:36 PM