Translate LINQ to sql statement

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

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

I know DataContext.Log, but I want to use:

query.ToSqlStatementString()

回答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;

回答2:

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