Is there an api or extension to translate IQueryable<T> lambda expressions to SQL strings?

Question

I was reading an article on Linq to Sql and came across this:

IQueryProvider provider = new QueryProvider(database.GetCommand, database.ExecuteQuery);
IQueryable<Product> source = new Queryable<Product>(provider, database.GetTable<Product>());
IQueryable<string> results = source.Where(product => product.CategoryID == 2)
                                   .OrderBy(product => product.ProductName)
                                   .Select(product => product.ProductName)
                                   .Skip(5)
                                   .Take(10);

The author then translated the results in plain sql:

exec sp_executesql N'SELECT [t1].[ProductName]
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ProductName]) AS [ROW_NUMBER], [t0].[ProductName]
FROM [dbo].[Products] AS [t0]
WHERE [t0].[CategoryID] > @p0
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t1].[ROW_NUMBER]',N'@p0 int,@p1 int,@p2 int',@p0=2,@p1=5,@p2=10

And I thought to myself, "holy cow! wouldn't it be great if there was an extension to IQueryable that would generate these strings for you when debugging?"

Anyone ever heard of anything like this, and if so, could you point me in the right direction?

Thanks!

Answer 1

With Linq to Sql you can call query.Provider.ToString() and this will return you text of query (btw you can watch same property in Visual Studio when debugging).

UPDATE: (complex part) How it is implemented?

Actual string generation is done by System.Data.Linq.SqlClient.SqlProvider class. It has hidden method string IProvider.GetQueryText(Expression query), which builds SQL query text based on passed expression. This method is hidden by internal interface IProvider, so it's not trivial thing to call it.

Answer 2

I think Linqpad can be used to translate between SQL and Linq, hope that helps.

Answer 3

You can set the DataContext.Log property to a TextWriter (e.g. Console.Out) to see the SQL as it is generated, but I don't think this lets you output the SQL without executing it.