Can I get the T-SQL query generated from a LinqDataSource?

Question

I´m using the LinqDataSource to populate a grid. But now I need the SQL query that the LinqDataSource generates, to pass around throught methods (no, I can't modify the methods to not need a SQL query).

Is there a way to obtain the generated SQL query from a instantiated and configured LinqDataSource?

Answer 1

The Sql will only be generated by the Linq to Sql infrastructure at runtime.

I think there are some tools to see generated Sql in the debugger, but if you don't plan to use linq to generate your Sql dynamicaly, shouldn't you probably look for a simple Sql designer ?

I Found a Linq To Sql Debug visualizer on Scottgu's blog.

Answer 2

You can run SQL Profiler while running your application and that should give it to you.

Answer 3

Take a look at LinqPad for debugging and to understand how it works. But if you want it at run-time, I think you're out of luck.

Answer 4

Hope this helps.

using the function below will return a SqlQueryText you can rebuild the query from that object.

• to get the sql text you can use use the .Text Property

• to get the passed parameters you can use the .Params property

      public static SqlQueryText GetFullQueryInfo(DataContext dataContext, IQueryable query)
      {
          DbCommand dbCommand = dataContext.GetCommand(query);
  
  
      var result = new SqlQueryText();
  
  
      result.Text = dbCommand.CommandText;
      int nParams = dbCommand.Parameters.Count;
      result.Params = new ParameterText[nParams];
      for (int j = 0; j < nParams; j++)
      {
          var param = new ParameterText();
          DbParameter pInfo = dbCommand.Parameters[j];
          param.Name = pInfo.ParameterName;
          param.SqlType = pInfo.DbType.ToString();
          object paramValue = pInfo.Value;
          if (paramValue == null)
          {
              param.Value = null;
          }
          else
          {
              param.Value = pInfo.Value.ToString();
          }
          result.Params[j] = param;
      }
      return result;
  }
  
  
  

  here is an example

  var results = db.Medias.Where(somepredicatehere); ClassThatHasThisMethod.GetFullQueryInfo(yourdatacontexthere, results);

EDIT:

Sorry forgot to include the SqlQueryText data structures

public struct SqlQueryText
{
    public ParameterText[] Params;
    public string Text;
}

public struct ParameterText
{
    public string Name;
    public string SqlType;
    public string Value;
}