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

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

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

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.