Observing LINQ to SQL Queries with a Custom Log

LINQ to SQL allows you to capture the generated SQL as it is being sent to a local database. This can be achieved by creating a custom TextWriter that outputs to the Visual Studio console window using Debug.WriteLine. An instance of the custom TextWriter is assigned to the Log property of the DataContext class.

John Gallardo demonstrates how to do this on his blog at http://bit.ly/mJYY74. The custom TextWriter, called DebugStreamWriter, is shown in Listing 29.9.

LISTING 29.9. DebugStreamWriter Class

Click here to view code image

public class DebugStreamWriter : TextWriter
{
    readonly int bufferSize = 256;
    readonly StringBuilder stringBuilder;

    public DebugStreamWriter(int bufferSize = 256)
    {
        this.bufferSize
            = ArgumentValidator.AssertGreaterThan(0, bufferSize, "bufferSize");
        stringBuilder = new StringBuilder(bufferSize);
    }

    public override Encoding Encoding
    {
        get
        {
            return Encoding.UTF8;
        }
    }

    public override void Write(char value)
    {
        stringBuilder.Append(value);
        if (stringBuilder.Length >= bufferSize)
        {
            Flush();
        }
    }

    public override void WriteLine(string value)
    {
        Flush();
        using (var reader = new StringReader(value))
        {
            string line;
            while ((line = reader.ReadLine()) != null)
            {
                Debug.WriteLine(line);
            }
        }
    }

    protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            Flush();
        }
    }

    public override void Flush()
    {
        if (stringBuilder.Length > 0)
        {
            Debug.WriteLine(stringBuilder);
            stringBuilder.Clear();
        }
    }
}

To send all LINQ to SQL logging output to the DebugStreamWriter, it is assigned to the Log property of the DataContext class. I have chosen to do this in the TwitterDataContext class, as shown:

Click here to view code image

public class TwitterDataContext : DataContext
{
    public TwitterDataContext(string connection) : base(connection)
    {
        Log = new DebugStreamWriter();
    }
...
}

With this in place, we are able to view the activity of the DataContext via the Visual Studio Output pane (see Figure 29.9).

FIGURE 29.9 The Output pane displays the LINQ to SQL query.

Observing the generated SQL can help you gain an understanding of how LINQ to SQL works, as well as giving you the opportunity to optimize query complexity.