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 - Im glad im 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.
So far with my new website, Ive 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 didnt wire up one of my many to many relationships properly.
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...
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.
Matt, remember me? ha ha
I wrote a full middle tier on a subsystem using Linq, putting it behind a WCF service and stood it up against a full ADO middle-tier and got about 95% of the performance using NO sprocs. The DAO layer was much less code. Eager loading is turned off since the service call itself contains the data load options that articulate how the user wants the object graph loaded.
I used SQL Metal to gen the Data Transfer Objects and then used my own custom filter code to get the naming conventions I want for the Entities. This way I can regen my objects with my build process and pick up changes automatically in my database. Here is a sample DAO call that uses a left outer join.. Pretty simple to write actually. Transaction demarcation is up in the service layer. I use a Loader layer above the DAO layer to manage the data aquisition. This probably has more comments than code so don't get put off by the length
internal IList GetListCategoryByApplicationId( string applicationId )
{
Stopwatch svcTimer = Stopwatch.StartNew();
IList retval = null;
//we need a left outer join here to return all ListCategory records even if all the associated
//list topic records to not pass the where clause. syntax in Lambda is cryptic, will put the
//comprehension query in comments below.
retval = Database.ListCategories.OrderBy(lc => lc.Description).SelectMany( lc => lc.ListTopics
.Where( lt => ( lt.ActiveFg == 1 ) ).DefaultIfEmpty(), ( lc, lt ) => lc ).ToList();
svcTimer.Stop();
LogDebugAll( string.Format( "{0}.GetListCategoryByApplicationId , DAO duration was {1} seconds."
, _className
, svcTimer.Elapsed.TotalSeconds.ToString() ) );
return retval;
//*************Comprehension syntax******************
//var retval =
// from lc in Database.ListCategories
// from lt in lc.ListTopics.Where( lt => lt.ActiveFg == 1 ).DefaultIfEmpty()
// select lc;
//return retval.ToList();
}