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!
