How can I Check how many queries a LinqToSql DataContext ran?

Is there any way I can check how many DB queries a DataContext ran?

I know I can run an sql profiler but I want to analyze a big project with hundreds of queries.

If not, is there any useful event that I can register to and count them?

I'm looking for something like this:

using(DataContext ctx = new DataContext(connectionString))
{
    // Add load options
    // execute a query 

    // I want information about the number of actual sql queries that ran using this context. 
    // Adding "1 to many" loadoptions or complex queries can create multiple sub-queries and that's why I want this info.
}

Solution

In LINQ to SQL you have the option to log the generated SQL to a TextWriter. For instance you can log to the console:

ctx.Log = Console.Out;

This will not give you a count of queries but it will provide you with the actual SQL executed which probably gives you a better insight into what is going on.

If you want to provide an aggregated view of say the number of select statements you can log to a StringWriter and then use regular expressions to count the number of occurences of the word SELECT:

var stringWriter = new StringWriter();
ctx.Log = stringWriter;

// Use DataContext referenced by ctx ...

var regex = new Regex(@"\bSELECT\b");
var selectCount = regex.Matches(stringWriter.ToString()).Count;

Obviously, you may have to also count other words like INSERT, UPDATE and DELETE and you may run into counting problems if you have say a WHERE clause containing the word SELECT.

Instead of trying to "understand" the SQL to make a count you can count the number of SQL statements executed. Each statement ends with a line that in my tests have the following format:

-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.34209

You can create a regular expression to match this string:

var regex = new Regex(@"^-- Context: ", RegexOptions.Multiline);
var statementCount = regex.Matches(stringWriter.ToString()).Count;