CODEWITHSUNDEEP
linq
nellbryant
nellbryant

Reputation: 3169

How to view LINQ Generated SQL statements?

How is it done using the ObjectQuery method?

Upvotes: 78

Views: 105559

Answers (9)

user1176058
user1176058

Reputation: 676

In versions >= EF Core 5.0, You can use ToQueryString method on IQueryable.

public static string ToQueryString(this IQueryable source)

Example:

var sql = context.Blogs
.OrderBy(b => b.BlogId)
.ToQueryString();

You will get result as shown below.

SELECT [b].[BlogId], [b].[Discriminator], [b].[Url], [b].[RssUrl]
FROM [Blogs] AS [b]
ORDER BY [b].[BlogId]

Upvotes: 3

Giridhar94
Giridhar94

Reputation: 39

If you are executing the linq query against a database, you can run the SQL Profiler to record the SQL query that is being executed. We do it quite often to identify any performance impact on conversion.

Upvotes: 2

user8128167
user8128167

Reputation: 7706

This is what I use when setting up the database context:

this.DbContext.Database.Log += s => Debug.WriteLine(s);

Upvotes: 6

liminal18
liminal18

Reputation: 563

just a small update you can now use an Action to log the SQL:

// test SQL logger
Action<string> SQLLogger = (message) => System.Diagnostics.Debug.Write(message);
_dB.Context().Database.Log = SQLLogger;

Upvotes: 1

gyozo kudor
gyozo kudor

Reputation: 6345

 var q = from img in context.Images
                    ...
         select img;
 string sql = q.ToString();

sql will contain the sql select query.

EDIT: disadvantage: parameters won't have any values at this time

Upvotes: 20

Tj Kellie
Tj Kellie

Reputation: 6476

You can always attach something to the .Log property of your DataContext. That will show all the SQL commands as they are sent.

I do this in my base for data access objects and output it to the Visual Studio debug console. As the objects create their DataContext I check it see if its debug and attach a TextWritter helper class like this:

dbDataContext _dB = new dbDataContext();
_dB.CommandTimeout = 5000;

#if DEBUG
    _dB.Log = new DebugTextWriter();
#endif

Here is the helper object for output to the debug console:

//utility class for output of TextWriter for the Visual Sudio Debug window
class DebugTextWriter : System.IO.TextWriter
{
    public override void Write(char[] buffer, int index, int count)
    {
        System.Diagnostics.Debug.Write(new String(buffer, index, count));
    }

    public override void Write(string value)
    {
        System.Diagnostics.Debug.Write(value);
    }

    public override Encoding Encoding
    {
        get { return System.Text.Encoding.Default; }
    }
}

Upvotes: 70

nellbryant
nellbryant

Reputation: 3169

Here is what I found using ObjectQuery Method. Using console for testing, you can do the following:

Create an Extension Method as below, then call it. Say Product product, then SQL prints out as product.ToTraceString.

public static class MyExtensions
{
    public static string ToTraceString<T>(this IQueryable<T> t)
    {
        string sql = "";
        ObjectQuery<T> oqt = t as ObjectQuery<T>;
        if (oqt != null)
            sql = oqt.ToTraceString();
        return sql;
    }
}

Upvotes: 31

guiomie
guiomie

Reputation: 5148

You could run the SQL Server Profiler.

Upvotes: 7

marc_s
marc_s

Reputation: 755411

You could have a look at the Linq-to-SQL Debug Visualizer, or just hover your mouse over your Linq-to-SQL query (tooltip should show generated SQL), or access:

context.GetCommand(query).CommandText

Upvotes: 22

Related Questions