LINQ to SQL log to debug window, file, memory or multiple writers

The Log property on a LINQ to SQL data context takes a TextWriter and streams out details of the SQL statements and parameters that are being generated and sent to the server.

Normally in examples you will see Console.Out being assigned to it which is fine for small demos and sandboxes but sooner or later you’ll want access to it in Windows or web applications. Here are some examples of how to redirect TextWriter output such as the DataContext log to other destinations.

Github has the latest version of ActionTextWriter etc.

To the output/debug window

The output/debug window mechanism actually uses a listener mechanism and so doesn’t actually directly expose a TextWriter interface however we can simply wrap up Debug.Write in something that does and use that instead:

class DebugTextWriter : System.IO.TextWriter {
   public override void Write(char[] buffer, int index, int count) {
       System.Diagnostics.Debug.Write(new String(buffer, index, count));
   }

   public override void Write(string value) {
       System.Diagnostics.Debug.Write(value);
   }

   public override Encoding Encoding {
       get { return System.Text.Encoding.Default; }
   }
}

To use it then simply:

myDataContext.Log = new DebugTextWriter();

To a file

#if DEBUG
   db.Log = new System.IO.StreamWriter("linq-to-sql.log") { AutoFlush = true };
#endif

If you wish to not overwrite the existing log file then change the constructor to include the parameter true after the filename. Bear in mind this log file can get very large and slow down your application with all that extra writing to disk and could well reveal information you’d rather wasn’t persisted there so the DEBUG conditional is recommended.

To memory

#if DEBUG
   var sw = new System.IO.StringWriter();
   db.Log = sw;
#endif

You will be able to examine sw or call ToString() on it to see the contents. Again this is not recommended for production as it will cause a lot of memory consumption as the StringWriter gets larger and larger.

To multiple writers

Here is a small useful class that lets you send the results intended for a TextWriter off into multiple writers.

class MulticastTextWriter : TextWriter {
    private readonly IList<TextWriter> textWriters;

    public MulticastTextWriter()
        : this(new List<TextWriter>()) {
    }

    public MulticastTextWriter(IList<TextWriter> textWriters) {
        this.textWriters = textWriters;
    }

    public void Add(TextWriter textWriter) {
        lock (textWriters)
            textWriters.Add(textWriter);
    }

    public bool Remove(TextWriter textWriter) {
        lock (textWriters)
            return textWriters.Remove(textWriter);
    }

    public override void Write(char[] buffer, int index, int count) {
        lock (textWriters)
            foreach (TextWriter textWriter in textWriters)
                textWriter.Write(buffer, index, count);
    }

    public override Encoding Encoding {
        get { return System.Text.Encoding.Default; }
    }
}

So if you wanted to output to a log and also to the debug window, you would use it like this (again recommended only for debugging):

   MulticastTextWriter mw = new MulticastTextWriter();
   mw.Add(new DebugTextWriter());
   mw.Add(new System.IO.StreamWriter("linq-to-sql.log") { AutoFlush = true };
   db.Log = mw;

Anything you want

To wrap things up here is a small TextWriter that lets you go off and do whatever you like with the string via the Action delegate.

class ActionTextWriter : TextWriter {
    private readonly Action<string> action;

    public ActionTextWriter(Action<string> action) {
        this.action = action;
    }

    public override void Write(char[] buffer, int index, int count) {
        Write(new string(buffer, index, count));
    }

    public override void Write(string value) {
        action.Invoke(value);
    }

    public override Encoding Encoding {
        get { return System.Text.Encoding.Default; }
    }
}

So if you wanted to output all log information to say a WinForms message box a tiny lambda expression gets you there:

   db.Log = new ActionTextWriter(s => MessageBox.Show(s));

Have fun!

[)amien

12 responses  

  1. pingback

    […] LINQ To SQL output to the ASP.NET response stream. Damien Guard has an even better post on logging to all kinds of places, including multiple writers. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it! Posted: Aug […]

    Logging LINQ To SQL Queries - Hilton Giesenow's Jumbled MindAugust 8th, 2008
  2. Some great tips, thanks! I’ll definitely make use of some of these. One small addition that you could include is logging to the ASP.NET response output stream: http://dotnet.org.za/hiltong/archive/2008/07/16/displaying-linq-to-sql-s-actual-sql-queries-in-your-asp-net-page.aspx

    Hilton GiesenowAugust 8th, 2008
  3. How do I also log the parameter values?

    Smit – November 5th, 2008
  4. pingback

    […] thanks to DamienG over at his blog! « Gearing up WCF services to […]

    LOG LINQ 2 SQL query execution to console/debug window « {Programming} & LifeDecember 30th, 2008
  5. pingback

    […] for your queries, set the DataContext.Log property to Console.Out. Alternatively, you can try a few other listeners, courtesy of Damien […]

    David DeWinter » LINQ to SQL: Updating EntitiesApril 7th, 2009
  6. These are very cool and useful. Thanks for sharing.

    Thiago leite – December 11th, 2009
  7. pingback

    […] After staring at the Sql Profiler, I still needed more information.  Why was a select being done on my entire database?  Where was this coming from?  That led me back to my DataContext which was generated from my Linq To Sql designer.  The DataContext has a great feature – “Log”.  It’s a property right off of the context which accepts a TextWriter.  When you attach a TextWriter, it will output useful information to the stream.  In my case, I attached the log to the Debug window.  I found some great examples of how to do this (and use other output mechanisms) on DamienGuard’s blog […]

    Debugging Linq To Sql | OOP - Object Oriented ProgramingMay 17th, 2010
  8. Hi,
    This is excellent article.
    I used your class ActionTextWritter to track progress of the ctx.SubmitChanges()

    http://epandzo.wordpress.com/2011/01/02/linq-to-sql-ctx-submitchanges-progress/

    EmirJanuary 2nd, 2011
  9. Yes, great article. I had spent most of yesterday playing around with SQL Profiler just to end up with the same result as this solution only less conveniently.

    Thanks very much.

    Karl – September 9th, 2011
  10. Another great article, specially the ActionTextriter tip.

    To improve it I would like to suggest you to prefix sample classes with [public] clause – a minor catch to newbies ;-)

    Júlio Nobre – September 12th, 2012
  11. pingback

    […] also easy to log to memory, file, etc. by using different kinds of TextWriter. Damien Guard wrote a post on this for the LINQ to SQL Log property which also applies to the new property in […]

    EF6 SQL Logging – Part 1: Simple Logging | One UnicornMay 8th, 2013
  12. pingback

    […] LINQ to SQL log to debug window. […]

    Debugging Linq queries | Raj Aththanayake's BlogMay 18th, 2014

Respond to this