How to See Generated SQL from a Linq Query

How to view LINQ Generated SQL statements?

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;
    }
}

How do I view the SQL generated by the Entity Framework?

You can do the following:

IQueryable query = from x in appEntities
             where x.id == 32
             select x;

var sql = ((System.Data.Objects.ObjectQuery)query).ToTraceString();

or in EF6:

var sql = ((System.Data.Entity.Core.Objects.ObjectQuery)query)
            .ToTraceString();

or in EF6.3+:

var sql = ((dynamic)flooringStoresProducts).Sql;

That will give you the SQL that was generated.

how to see generated sql from a linq query

With Linq2Sql

dc.GetCommand(query).CommandText

see http://msdn.microsoft.com/en-us/library/system.data.linq.datacontext.getcommand.aspx for more info.

But I usually use LinqPad

Seeing the SQL that LINQ generates

Yes, that's a correct way, but of course, there are others:

var context = new DataClasses1DataContext();

var sb = new StringWriter();
context.Log = sb;

var query = (from a in context.Persons select a.Name);

string s = query.ToString();
string command = context.GetCommand(query).CommandText;

//The log requires the query to actually hit the database
query.ToList();
string log = sb.ToString();

And also Linqpad:

How to view Generated SQL statements by LINQ when querying object?

I am pretty sure that you cannot use SQL against 'linq2objects', so getting SQL from those is not possible.

Linq 2 objects works, basically, as table-scan in sql. When executed, it reads lines of source one by one, and applies filters/selectors on each of them. See, for exmaple, http://blogs.msdn.com/b/wesdyer/archive/2007/01/03/how-linq-to-objects-queries-work.aspx.

I assume you are interested in debugging query, and there is another topic on stack overflow answering this: How to debug a LINQ Statement

How to trace SQL statement from Linq query with Where clause?

Due to Deferred execution, your query on its own will not produce any SQL.

However, If you are in your debugger and you have a statement like

var query = context.Files.Where(f => ids.Contains(f.FileId))

then

query.ToList();

will execute your query and hence you should see the generated SQL in Sql Profiler etc.

Alternatively

Query.ToString();

will not execute the query, but will return the generated SQL statement (but does not show the values of the parameters involved).

---

Related Topics