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?

See similar question (re: generated SQL) here http://stackoverflow.com/questions/265192/how-to-get-the-generated-sql-statment-from-a-sqlcommand-object It might provide some insight. — John K, Jun 14 '11 at 13:09

score 24 · Answer 1 · answered May 31 '17 at 16:32

24

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

answered May 31 '17 at 16:32

Nick Polyderopoulos

839
10
15

8

The question was how to retrieve the full statement but your solution doesn't include the parameters. – ViRuSTriNiTy Nov 17 '17 at 13:16
2

It will print out the variables if you use any (i dont use any on my example). It will not print the variable's value though. (but this is simple to do) – Nick Polyderopoulos Nov 17 '17 at 18:29
@phil123456 which version of EntityFramework are you using ? – Nick Polyderopoulos Feb 23 '21 at 09:57
1

@NickPolyderopoulos as stated in the question he doesn't use EF he use LinqToSql, a diffrent technology, your answer is OT IMHO – Skary Aug 03 '21 at 14:29

score 15 · Accepted Answer · edited May 11 '16 at 17:50

15

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

edited May 11 '16 at 17:50

threadster

418
5
18

answered Jun 14 '11 at 13:03

marto

4,170
1
28
39

score 15 · Answer 3 · edited Sep 01 '21 at 01:46

15

In the latest version of EF Core 5 ToQueryString,

query.ToQueryString()

edited Sep 01 '21 at 01:46

Madushan

6,977
31
79

answered May 30 '21 at 15:15

Emil

6,411
7
62
112

1

The question clearly state LinqToSql i don't understand why your answer here about EF Core 5, a completley different technology – Skary Aug 03 '21 at 14:26
3

@Skary they arent completely different technology. EF is the successor of Linq to Sql. Linq to sql is almost dead today. Many people searching answer for EF or EF Core in SO. https://www.infoq.com/news/2008/11/DLINQ-Future/ – Emil Aug 03 '21 at 14:48
they are both ORM but with a very different architecture IMHO, if not please give me a reference where microsoft state otherwhise. Personally i don't mind if it's an old dead technology or not, here the topic is LinqToSql (it's a 2011 question) not EF Core nor other ORM technology like NHibernate or LLBL – Skary Aug 03 '21 at 14:52
sorry but .ToQueryString() not returns the parameters – rChavz Jul 06 '23 at 00:43

score 12 · Answer 4 · answered Jun 14 '11 at 13:00

12

(SqlCommand)dataContext.GetCommand(query)

will give you access to Parameters collection.

answered Jun 14 '11 at 13:00

cdel

717
7
14

11

Question asks for the full SQL statement. – John K Jun 14 '11 at 13:06
2

@John When sending a parameterized query to SqlServer, there is no such thing as full SQL statement, first is sent the CommandText with placeholders for parameters and then the parameters with their type and values. – cdel Jun 14 '11 at 13:12
5

Agreed, but your answer doesn't provide this kind of useful context. If you provide a more thorough answer it will be higher quality, viewers will be able to quickly tie it to the question, and I'll remove the downvote. – John K Jun 14 '11 at 13:15
He asked for the full statement, like one you'd need to pass to a bcl command line utility to export the data for the statement. I would need the full text, including the 'exec' command as well as the stringified parameter values (a non-trivial mapping task). Basically a string you could paste directly in SSMS and run. – Triynko Dec 28 '16 at 20:57

jaraics · Answer 5 · 2020-02-25T07:03:56.213

7

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

edited Feb 25 '20 at 07:03

answered Jun 15 '11 at 07:22

jaraics

4,239
3
30
35

2

Yes, all the statements that are generated by that DataContext (including selects, inserts, updates). – jaraics Jun 15 '11 at 14:33

score 1 · Answer 6 · answered Sep 13 '21 at 23:38

1

When viewing the IQueryable in the Locals pane, you can access DebugView > Query, which will contain the SQL statement.

answered Sep 13 '21 at 23:38

June Lau

151
2
9

Retrieve LINQ to sql statement (IQueryable) WITH parameters

6 Answers6

Linked