Translate LINQ to sql statement

Question

Hi,

I want to translate LINQ expression tree to SQL statement and I don't want to write my own code for this.

Example: var query = from c in Customers where c.Country == "UK" && c.City == "London" select c);

To SELECT ... FROM Customers AS c WHERE c.Country = "UK" AND c.City = "London"

I know DataContext.Log, but I want to use: query.ToSqlStatementString()

Thanks

Answer 1

CustomDataContext dc = new CustomDataContext();
IQueryable<Customer> query =
  from c in dc.Customer
  where c.Country == "UK"
  select c;
//
string command = dc.GetCommand(query).CommandText;

Answer 2

David B's answer gets you what you want, but there is the hidden cost of requiring a database connection. The reason this is done is to determine the SQL server version by asking the server itself. To avoid this, you should use this code snippet:

/// <summary>
/// Through reflection (HACK) this sets the MS impl of LINQ-to-SQL to not attempt connection to the database just
/// to determine the SQL server version running to tailor the SQL query to.
/// </summary>
private static void hack_SetLINQ2SQLProviderMode(CustomDataContext db)
{
    object provider = hack_GetLINQ2SQLProvider(db);

    provider
        .GetType()
        .GetField("mode", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance)
        .SetValue(provider, 2);
}

private static object hack_GetLINQ2SQLProvider(CustomDataContext db)
{
    return db
        .GetType()
        .GetProperty("Provider", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.NonPublic)
        .GetValue(_db, new object[0]);
}

Call hack_SetLINQ2SQLProviderMode(db) where db is your DataContext derived class.

That will set the mode field of MS's implementation of LINQ-to-SQL's IQueryProvider to tell it you mean to generate SQL code for MS SQL Server 2005, indicated by SetValue(provider, 2). Use 1 for MS SQL Server 2000 or 3 for MS SQL Server 2008.

This means that since the mode field is set, the implementation no longer needs to open the SQL connection to the database and you can now work completely offline.

Please note that this is using full-trust reflection, from what I understand. You should only use this approach in an environment that you control and that fully trusts your assembly.

Answer 3

Hi, All you need is Linq to Entity debug visualizer. You can view native SQL of linq query irrespective of Db connection or any database (MS SQL, DB2, Oracle, Sybase...). Download the free visualizer thru http://visualstudiogallery.msdn.microsoft.com/en-us/99468ece-689b-481c-868c-19e00e0a4e69

Regards

Venkat