Retrieve LINQ to sql statement (IQueryable) WITH parameters

Question

I'm trying to figure out if there's a way to retrieve the (full) sql statement that gets executed on the database server.
I found something already, but it does not exactly what I would like:

IQueryable<SomeType> someQuery = ... string command = dataContext.GetCommand(query).CommandText; 

In my case this gives me a command string something like:

SELECT TOP (50) [t0].[ID], .... FROM [dbo].[someTable] AS [t0] WHERE ([t0].[someColumn] IS NOT NULL) AND (([t0].[someColumn]) IN (@p0)) 

On database there's executed:

exec sp_executesql N'SELECT TOP (50) [t0].[ID], ... FROM [dbo].[someTable] AS [t0] WHERE ([t0].[someColumn] IS NOT NULL) AND (([t0].[someColumn]) IN (@p0, @p1))',N'@p0  int,@p1 int',@p0=401,@p1=201 

Is there a way to retrieve this 'full' statement (so also the parameter values) from C# code?

Answer 1

You can also see the generated sql query if you have an instance of IQueryable<T> and call the .ToString() method.
For Example:

var db = new DbContext(); IQueryable<Blog> query = db.Blog.Where(tt=> tt.Id > 100).OrderByDescending(tt=>tt.Id); var sqlString = query.ToString(); Console.WriteLine(sqlString); 

This will generate an output of:

SELECT [Extent1].[Id] AS [Id],  [Extent1].[Title] AS [Title],  [Extent1].[Author] AS [Author],  [Extent1].[Text] AS [Text],  [Extent1].[CreatedAt] AS [CreatedAt],  [Extent1].[UpdatedAt] AS [UpdatedAt] FROM [dbo].[Blogs] AS [Extent1] WHERE [Extent1].[Id] > 100 ORDER BY [Extent1].[Id] DESC