Check LINQ query against SQL Server database

Question

Is there any way to in .NET to see what LINQ query against database we are firing? For eg. I am wring a query in LINQ and I want to see that what SQL query is firing to communicate with database.

Is there any Visual Studio window or any other way?

Answer 1

If you have a DbContext on which you firing your LINQ queries you can simply set the DbContext.Database.Log property to something like this:

yourContext.Database.Log = (msg => System.Diagnostics.Debug.Write(msg, "SQL"));

After this, every SQL query shows up in the Debug console from your Visual Studio with the category SQL.

Answer 2

You can also use SQL Server Profiler, Trace or Extended Events. First two are deprecated.

https://technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx

https://msdn.microsoft.com/en-us/library/ms181091.aspx

https://technet.microsoft.com/en-us/library/ms191006(v=sql.105).aspx

Answer 3

Are you looking for something like this

var context = new MyContext();
context.Database.Log = s => Debug.WriteLine(s);

Then whenever a query is executed, you'll see an output like :

var customers = context.Customers.ToList();

Opened connection at 30-3-2015 13:48:03 +02:00

SELECT [Extent1].[Guid] AS [Guid],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[LastName] AS [LastName],
[Extent1].[Email] AS [Email],
[Extent1].[Created] AS [Created]
FROM [dbo].[Customer] AS [Extent1]

-- Executing at 30-3-2015 13:48:03 +02:00

-- Completed in 0 ms with result: SqlDataReader

Closed connection at 30-3-2015 13:48:03 +02:00

Answer 4

You can use the Log property of DataContext object. Also, it depends on the type of application you are using.

For Web Application:-

db.Log = Response.Output;

For Console Application:-

db.Log = Console.Out;

Apart from this you can also use the GetCommand method of DataContext class. Sql Server Profiler is again an obvious option.

Answer 5

You can use SQL Profiler to see how the LINQ expression is translated into SQL statement.