LINQ to SQL generates SQL statements to send to SQL Server. In the case of problems with these, for example unexpected results or poor performance, it is useful to be able to capture this SQL. This can then be run and investigated separately. It is also sometimes useful to get a feel for the number and type of SQL commands issued as this is not always obvious from the LINQ to SQL code.
One way to achieve this is by attaching the SQL Profiler to the database, which also has the advantage that it will pick up any non-LINQ to SQL calls.
However it is also possible to create a specialised TextWriter (“LinqToSqlDebugWriter”) that can be attached to the Log property of the DataContext and will write the SQL to the output window of Visual Studio:
DataContext dc = new DataContext("***your connection string***");
dc.Log = new LinqToSqlDebugWriter();
The code (shown below together with some unit tests) is based on code from blog “Sending the LINQ To SQL log to the debugger output window” by Kris Vandermotten. It has been extended to substitute the parameters with the data given so that the captured SQL can be copied and run without any modification.
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
namespace TheDifficultBit.com
{
/// <summary>
/// Implements a <see cref="TextWriter"/> for writing information to the debugger log. Set breakpoint on line 169 to break when SQL executed.
/// </summary>
/// <remarks>
/// Original code from blog "Sending the LINQ To SQL log to the debugger output window" by Kris Vandermotten
/// at http://www.u2u.info/Blogs/Kris/Lists/Posts/Post.aspx?ID=11
/// This code is only used to output debug information in debug builds.
/// </remarks>
/// <seealso cref="Debugger.Log"/>
public sealed class LinqToSqlDebugWriter : TextWriter
{
private readonly int _level;
private readonly string _category;
private bool _isOpen;
private static UnicodeEncoding _encoding;
private List<string> _sqlLines = new List<string>();
/// <summary>
/// Initializes a new instance of the <see cref="LinqToSqlDebugWriter"/> class.
/// </summary>
public LinqToSqlDebugWriter()
: this(0, Debugger.DefaultCategory)
{
}
/// <summary>
/// Initializes a new instance of the <see cref="LinqToSqlDebugWriter"/> class with the specified level and category.
/// </summary>
/// <param name="level">A description of the importance of the messages.</param>
/// <param name="category">The category of the messages.</param>
public LinqToSqlDebugWriter(int level, string category)
: this(level, category, CultureInfo.CurrentCulture)
{
}
/// <summary>
/// Initializes a new instance of the <see cref="LinqToSqlDebugWriter"/> class with the specified level, category and format provider.
/// </summary>
/// <param name="level">A description of the importance of the messages.</param>
/// <param name="category">The category of the messages.</param>
/// <param name="formatProvider">An <see cref="IFormatProvider"/> object that controls formatting.</param>
public LinqToSqlDebugWriter(int level, string category, IFormatProvider formatProvider)
: base(formatProvider)
{
this._level = level;
this._category = category;
this._isOpen = true;
}
/// <summary>
/// Clear up resources.
/// </summary>
protected override void Dispose(bool disposing)
{
this._isOpen = false;
base.Dispose(disposing);
}
/// <summary>
/// Write a char to the Debugger Log.
/// </summary>
public override void Write(char value)
{
if (!this._isOpen)
{
throw new ObjectDisposedException(null);
}
Debugger.Log(this._level, this._category, value.ToString());
}
/// <summary>
/// Write a string to the Debugger Log.
/// </summary>
public override void Write(string value)
{
if (!this._isOpen)
{
throw new ObjectDisposedException(null);
}
if (value != null)
{
this.LogSql(value);
}
}
/// <summary>
/// Write a char array to the Debugger Log.
/// </summary>
public override void Write(char[] buffer, int index, int count)
{
if (!this._isOpen)
{
throw new ObjectDisposedException(null);
}
if (buffer == null || index < 0 || count < 0 || buffer.Length - index < count)
{
base.Write(buffer, index, count); // delegate throw exception to base class
}
this.LogSql(new string(buffer));
}
/// <summary>
/// Gets the current Encoding (always Unicode).
/// </summary>
public override Encoding Encoding
{
get
{
if (LinqToSqlDebugWriter._encoding == null)
{
LinqToSqlDebugWriter._encoding = new UnicodeEncoding(false, false);
}
return LinqToSqlDebugWriter._encoding;
}
}
/// <summary>
/// Gets the Level used in writing to Debugger Log.
/// </summary>
public int Level
{
get { return this._level; }
}
/// <summary>
/// Gets the Category used in writing to Debugger Log.
/// </summary>
public string Category
{
get { return this._category; }
}
private void LogSql(string content)
{
this._sqlLines.Add(content);
if (content.StartsWith("SELECT"))
{
}
if (!content.StartsWith("-- Context: "))
{
return;
}
IList<string> outputLines = LinqToSqlDebugWriter.FormatSql(this._sqlLines);
foreach (string outputLine in outputLines)
{
Debugger.Log(this._level, this._category, outputLine);
Debugger.Log(this._level, this._category, "\r\n");
}
//// >>>>> Set breakpoint here >>>>>
Debugger.Log(this._level, this._category, "\r\n");
this._sqlLines.Clear();
}
/// <summary>
/// Formats the log output so it can be used as sql.
/// </summary>
public static IList<string> FormatSql(IList<string> lines)
{
List<StringBuilder> output = lines
.Where(k => !(k.StartsWith("-- ") || string.IsNullOrEmpty(k.Trim())))
.Select(k => new StringBuilder(k))
.ToList();
if (lines.Count() == 0)
{
return output.Select(k => k.ToString()).ToList();
}
if (lines.Count() < 2)
{
throw new InvalidOperationException("Less than 2 lines found");
}
if (!lines.Last().StartsWith("-- Context: "))
{
throw new InvalidOperationException("Last line is not Context details");
}
IEnumerable<string> paramLines = lines.Where(k => k.StartsWith("-- @")).Reverse();
foreach (string line in paramLines)
{
if (string.IsNullOrEmpty(line.Trim()))
{
continue;
}
if (!line.StartsWith("-- @"))
{
throw new InvalidOperationException("Invalid parameter line found: " + line);
}
Regex paramLinePattern = new Regex("-- (?<paramName>[@][a-zA-Z_]*?[0-9]*?): [InOut]*?put (?<paramType>[a-zA-Z]*?) \\(Size = [\\-0-9]*?; Prec = [0-9]*?; Scale = [0-9]*?\\) \\[(?<paramValue>.*?)\\]", RegexOptions.Singleline);
Match match = paramLinePattern.Match(line);
if (match.Success)
{
string paramName = match.Groups["paramName"].Value;
string paramType = match.Groups["paramType"].Value;
string paramValue = match.Groups["paramValue"].Value;
if (paramType == "NVarChar" || paramType == "VarChar")
{
if (paramValue != "Null")
{
paramValue = "'" + paramValue + "'";
}
}
else if (paramType == "Bit")
{
if (paramValue == "Null")
{
}
else if (paramValue == "True")
{
paramValue = "1";
}
else if (paramValue == "False")
{
paramValue = "0";
}
else
{
throw new InvalidOperationException("Invalid value for Bit type in: " + line);
}
}
else if (paramType == "DateTime")
{
if (paramValue != "Null")
{
Regex datePattern = new Regex("(?<day>[0-9][0-9])/(?<month>[0-9][0-9])/(?<year>[0-9][0-9][0-9][0-9])(?<time> [0-9][0-9]:[0-9][0-9]:[0-9][0-9])");
Match dateMatch = datePattern.Match(line);
if (dateMatch.Success)
{
paramValue = string.Format(
"'{0}-{1}-{2}{3}'",
dateMatch.Groups["year"].Value,
dateMatch.Groups["month"].Value,
dateMatch.Groups["day"].Value,
dateMatch.Groups["time"].Value);
}
else
{
throw new InvalidOperationException("Could not match date in: " + line);
}
}
}
else if (paramType == "Int" ||
paramType == "Decimal" ||
paramType == "Timestamp" ||
paramType == "VarBinary" ||
paramType == "UniqueIdentifier" ||
paramType == "Xml" ||
paramType == "Money")
{
}
else
{
throw new InvalidOperationException("Invalid parameter type '" + paramType + "' found in line: " + line);
}
foreach (StringBuilder sqlLine in output)
{
sqlLine.Replace(paramName, paramValue);
}
}
else
{
throw new InvalidOperationException("Could not match parameter in: " + line);
}
}
return output.Select(k => k.ToString()).ToList();
}
}
}
Tests:
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace TheDifficultBit.Com.Test
{
[TestClass]
public sealed class LinqToSqlDebugWriterTest
{
[TestMethod]
public void FormatSql_OkData_ReturnsSql()
{
List<string> lines = new List<string>();
lines.Add("INSERT INTO [ZATest]([LastModifiedOn], [TestString], [TestInt], [TestDate], [TestMoney], [LastModifiedById])\r\nVALUES (@p0, @p1, @p2, @p3, @p4, @p5)");
lines.Add("\r\n");
lines.Add("SELECT [t0].[ZATestId], [t0].[LastModifiedOn], [t0].[RowVersion]\nFROM [ZATest] AS [t0]\nWHERE [t0].[ZATestId] = (SCOPE_IDENTITY())");
lines.Add("-- @p0: Input DateTime (Size = 0; Prec = 0; Scale = 0) [02/07/2009 20:47:40]");
lines.Add("-- @p1: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [TestS1]");
lines.Add("-- @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [0]");
lines.Add("-- @p3: Input DateTime (Size = 0; Prec = 0; Scale = 0) [05/05/2000 00:00:00]");
lines.Add("-- @p4: Input Decimal (Size = 0; Prec = 9; Scale = 2) [0]");
lines.Add("-- @p5: Input Int (Size = 0; Prec = 0; Scale = 0) [Null]");
lines.Add("-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926");
IList<string> sqlLines = LinqToSqlDebugWriter.FormatSql(lines);
Assert.IsNotNull(sqlLines);
Assert.AreEqual(2, sqlLines.Count);
Assert.AreEqual("INSERT INTO [ZATest]([LastModifiedOn], [TestString], [TestInt], [TestDate], [TestMoney], [LastModifiedById])\r\nVALUES ('2009-07-02 20:47:40', 'TestS1', 0, '2000-05-05 00:00:00', 0, Null)", sqlLines[0]);
Assert.AreEqual("SELECT [t0].[ZATestId], [t0].[LastModifiedOn], [t0].[RowVersion]\nFROM [ZATest] AS [t0]\nWHERE [t0].[ZATestId] = (SCOPE_IDENTITY())", sqlLines[1]);
}
[TestMethod]
public void LinqToSqlDebugWriterTests()
{
LinqToSqlDebugWriter dw;
using (dw = new LinqToSqlDebugWriter())
{
Assert.IsNull(dw.Category);
Assert.IsInstanceOfType(dw.Encoding, typeof(UnicodeEncoding));
Assert.AreEqual(0, dw.Level);
}
using (dw = new LinqToSqlDebugWriter(2, "MyCategory"))
{
Assert.AreEqual("MyCategory", dw.Category);
Assert.AreEqual(2, dw.Level);
}
using (dw = new LinqToSqlDebugWriter(3, "MyCategory2", CultureInfo.InvariantCulture))
{
Assert.AreSame(CultureInfo.InvariantCulture, dw.FormatProvider);
Assert.AreEqual("MyCategory2", dw.Category);
Assert.AreEqual(3, dw.Level);
}
using (dw = new LinqToSqlDebugWriter())
{
string testString = "xyz";
char[] testChars = { 'h', 'e', 'l', 'l', 'o' };
dw.Write('a');
dw.Write(testString);
testString = null;
dw.Write(testString);
dw.Write(testChars, 0, 4);
}
}
[TestMethod]
[ExpectedException(typeof(ObjectDisposedException))]
public void WriteCharDisposedException()
{
LinqToSqlDebugWriter dw = new LinqToSqlDebugWriter();
dw.Dispose();
dw.Write('a');
}
[TestMethod]
[ExpectedException(typeof(ObjectDisposedException))]
public void WriteStringDisposedException()
{
LinqToSqlDebugWriter dw = new LinqToSqlDebugWriter();
dw.Dispose();
dw.Write("xyz");
}
[TestMethod]
[ExpectedException(typeof(ObjectDisposedException))]
public void WriteCharsDisposedException()
{
LinqToSqlDebugWriter dw = new LinqToSqlDebugWriter();
dw.Dispose();
dw.Write(new char[] { 'a' }, 0, 1);
}
[TestMethod]
[ExpectedException(typeof(ArgumentNullException))]
public void WriteCharsNullBufferException()
{
using (LinqToSqlDebugWriter dw = new LinqToSqlDebugWriter())
{
dw.Write(null, 0, 1);
}
}
[TestMethod]
[ExpectedException(typeof(ArgumentOutOfRangeException))]
public void WriteCharsNegativeIndexException()
{
using (LinqToSqlDebugWriter dw = new LinqToSqlDebugWriter())
{
dw.Write(new char[] { 'a' }, -1, 1);
}
}
[TestMethod]
[ExpectedException(typeof(ArgumentOutOfRangeException))]
public void WriteCharsNegativeCountException()
{
using (LinqToSqlDebugWriter dw = new LinqToSqlDebugWriter())
{
dw.Write(new char[] { 'a' }, 0, -1);
}
}
[TestMethod]
[ExpectedException(typeof(ArgumentException))]
public void WriteCharsOverEndException()
{
using (LinqToSqlDebugWriter dw = new LinqToSqlDebugWriter())
{
dw.Write(new char[] { 'a' }, 0, 2);
}
}
}
}