How to log with Linq to Sql?

Posted on: 2011-08-27

It's possible to log all SQL queries generated by Linq to Sql with C# code. This can be useful when debugging or to optimize how the Linq is done.

 DataClasses1DataContext db = new DataClasses1DataContext(); 
 StringWriter sw = new StringWriter(); 
 db.Log = sw;

this.gridLinqToSql.DataSource = db.Customers.Where(c => c.CustomerID.StartsWith("A")); 
this.gridLinqToSql.DataBind(); //Here is when the Linq query will be executed.

sqlLinqToSql.Text = sw.GetStringBuilder().ToString(); // Here is the output of the SQL commands

Linq to Sql use a DataContext object that is generated with the .dbml file.

For example, in this test project, the .dbml file name is DataClasses1.dbml.

From there, this object contain a Log property that takes a TextWriter.

The TextWriter class comes from the System.IO namespace and it's an abstract class.

The namespace contain two concrete classes that inherit from this class.

The first one is StringWriter that has been used in the example above. This class has a StringBuilder and when using the property GetStringBuilder() of the StringWriter let you get a reference on it. From there, using the ToString() method give a String with all the SQL used to get the information.

The result looks like this:

 SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle] 
 FROM [dbo].[Customers] AS [t0] 
 WHERE [t0].[CustomerID] LIKE @p0

-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [A%] 
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 4.0.30319.1 

The Log return the SQL statement (or many SQL statement if many calls has been done) with a parameter way (@p...). After the SQL Statement, each parameter details is written. For example, you can find the type and the size of a SQL parameter.

To conclude, using the Log property is interesting to get the SQL detail without using a SQL Profiler.

Of course, a good practice would be to not log all the queries in production environment.