It’s possible to log all SQL Query 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();
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 come 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 will look 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 are written like the type, and the size of the SQL parameter.
To conclude, using the Log property is interesting to get the SQL without using a SQL Profiler. Of course, a good practice would be to not log all the queries in production environment.