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?
6 Answers
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
Once you get the Command you can print the CommandText and then loop through the Parameters collection and print all the individual parameters.
Also there is the linq-to-sql debug visualizer which does the same in debug mode.
A really nice tool to view the queries as they are happening is the Linq-to-sql profiler
(SqlCommand)dataContext.GetCommand(query)
will give you access to Parameters collection.
I'm using Datacontext.Log property to get the generated SQL Statement (it includes the statement text, and parameters).
Just set YourDataContext.Log = SomeTextWriter.
It can be written to a file (Log = new StreamWriter(@"c:\temp\linq.log")) or to debug window, see this post
In the latest version of EF Core 5 ToQueryString,
query.ToQueryString()
When viewing the IQueryable in the Locals pane, you can access DebugView > Query, which will contain the SQL statement.
