My musings about .NET and what not

Quick and Dirty LINQ to SQL Debugging in ASP.NET

Sometimes, for debugging purposes, it's important to determine the T-SQL that's being issued to the database when a LINQ to SQL query executes. Probably the easiest way to do this is to fire up SQL Server Profiler. That's fine, as long as you're using a full or Developer edition of SQL Server. Unfortunately, however, SQL Server Profiler is not available to SQL Express Edition users.

Another way is to use the DataContext’s Log property. This usually involves adding a few extra lines to your method. Let's say we have some articles, and we want to get a count of all the articles belonging to a specific category:

   1: public int GetArticleCount(int categoryID)
   2: {
   3:    using (ArticlesDataContext context =
   4:       new ArticlesDataContext())
   5:    {
   6:       System.Text.StringBuilder sb = new System.Text.StringBuilder();
   7:       context.Log = new System.IO.StringWriter(sb);
   8:       int count = (from article in context.Articles
   9:                    where article.CategoryID == categoryID
  10:                    select article).Count();
  11:       System.Web.HttpContext.Current.Trace.Warn(sb.ToString());
  12:      return count;
  13:    }
  14: }

The Log property is of type System.IO.TextWriter. Whenever a query is executed by the DataContext, you can use its Log property to write the command to an object. Here, you assign it to a new System.IO.StringWriter object, specifying that Log should write to a System.Text.StringBuilder.

Of course, you then need a place to display the contents of the StringBuilder. A good place to do that is in the trace output of the current page. You just have to make sure to enable tracing in the page as follows:

<%@ Page Language=”C#” Trace=”true” %>

Then, you’ll be able to see the printed T-SQL command clearly highlighted in red text at the bottom of your page. Voila!

Subscribe to this blog for more cool content like this!

kick it on DotNetKicks.com

shout it on DotNetShoutOut.com

vote it on WebDevVote.com

Bookmark / Share

    » Similar Posts

    1. About Disposing the DataContext
    2. Nested ListViews and More - Working with Databound Controls Inside the ListView
    3. Resetting the Page Index in a ListView

    » Trackbacks & Pingbacks

    No trackbacks yet.
    Trackback link for this post:
    http://leedumond.com/trackback.ashx?id=21

    » Comments

    There are no comments. Kick things off by filling out the form below.

    » Leave a Comment