Get SQL Query from Linq to SQL

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 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.

Extract sql query from LINQ expressions

Edit: Wait, you're talking about LINQ to Objects? No, that is impossible¹. There is no way to convert the expression tree for a LINQ to Object query to an expression tree that represents querying some database.

Edit: Don't write you're own ORM. There are proven solutions to this problem. You're wasting value by trying to solve this problem again. If you're going to use your own ORM, to translate an expression to a SQL statement, yes you will have to write your own provider. Here's a walkthrough on MSDN doing that.

But seriously, do not write your own ORM. Five years ago before NHibernate and LINQ to SQL came along and matured, fine. But not now. No way.

The rest of this answer assumed that you were asking about LINQ to SQL.

There are two ways that I know of.

First:

Set the DataContext.Log property to Console.Out (or another System.IO.TextWriter of your choice):

var db = new MyDataContext();
db.Log = Console.Out;

This will print the SQL statements to the console as they are executed. For more on this feature see MSDN. Elsewhere there are examples of TextWriters that let you send the output to the debugger output window.

Second:

Use the LINQ to SQL Debug Visualizer from Scott Guthrie. This allows you to see the SQL statements through the debugger in Visual Studio. This option has the advantage that you can see the SQL statement without executing the query. You can even execute the query and see the results in the visualizer.

¹: Perhaps not impossible, but certainly very hard.

Where to find translated Linq to Entity query to Sql

You can call the ToString() method on objs. This will result in a call to the ToTraceString method that returns the executed SQL:

string sql = objs.ToString();

get the data using contains() in linq to sql query

Here you can find list of LINQ limitations. Your query falles under 'The left side of the clause must be an attribute' rule...

If customVendorIDs are also retrieved with LINQ query, you can work around this limitation by joining tables with vendor filter to get only desired plus_visitSet records.

Please note that with crm.plus_visitSet. ** tolist() ** ... query you are retreiving all plus_visit records from database and then filtering this out. It could be significant hit on performance....

---

Related Topics