How to extract the Sql Command from a Complied Linq Query

Question

In normal (not compiled) Linq to Sql queries you can extract the SQLCommand from the IQueryable via the following code:

SqlCommand cmd = (SqlCommand)table.Context.GetCommand(query);

Is it possible to do the same for a compiled query?

The following code provides me with a delegate to a compiled query:

        private static readonly Func<Data.DAL.Context, string, IQueryable<Word>> Query_Get =
        CompiledQuery.Compile<Data.DAL.Context, string, IQueryable<Word>>(
            (context, name) =>
                from r in context.GetTable<Word>()
                where r.Name == name
                select r);

When i use this to generate the IQueryable and attempt to extract the SqlCommand it doesn't seem to work. When debugging the code I can see that the SqlCommand returned has the 'very' useful CommandText of 'SELECT NULL AS [EMPTY]'

        using (var db = new Data.DAL.Context())
        {
            IQueryable<Word> query = Query_Get(db, "word");
            SqlCommand cmd = (SqlCommand)db.GetCommand(query);
            Console.WriteLine(cmd != null ? cmd.CommandText : "Command Not Found");
        }

I can't find anything in google about this particular scenario, as no doubt it is not a common thing to attempt...

So.... Any thoughts?

Answer 1

There must be a way from .NET, but you can profile your SQL Server database and capture the SQL sent to it. You can do this from SQL Management Studio.

Answer 2

You could use the DataContext log:

db.Log = Console.Out;