获取ToTraceString（）为IQueryable [英] Obtain ToTraceString() for IQueryable

问题描述

我使用

 `..... 
 ..... 
 query = query.Where（criteria，fiedNames）; 
 string sql2 =（（ObjectQuery）query）.ToTraceString（）;'
  

并调整LINQ将执行的SQL代码。

当我快速观看我得到以下不明白的SQL语句。

$ [C3] AS [C1]，[UnionAll2]。[C4] AS [C2]，
[UnionAll2] ] [C5] AS [C3]，[UnionAll2]。[C6] AS [C4]，
[UnionAll2]。[C7] AS [C5]，
[UnionAll2]。[C8] AS [C6]，
[UnionAll2]。[C9] AS [C7]，
.....几个hundereds行更多.....
@ p__linq__4 AS [C2]，
.....
CASE
WHEN（[Join5]。[id] IS NULL）THEN
Cast（NULL AS INT）
WHEN（@ p__linq__0 = 1 ）

.......

显然，如果我粘贴SQL管理工作室和执行它会给我错误，如果我没有传递的价值 @ p__linq__0 ....

我的问题：如何获得可以在SQL管理上执行的跟踪SQL？

解决方案

此扩展方法可以断言：

  ///< summary> 
 ///跟踪IQueryable的DB查询/> 
 ///< / summary> 
 ///< typeparam name =TEntity> IQueryable type< / typeparam> 
 ///< param name =query> IQueryable查询< / param> 
 ///< returns> Query< / returns> 
 public static string ToTraceString< TEntity>（此IQueryable< TEntity>查询）
 {
 StringBuilder sb = new StringBuilder（）; 
 
 var objQuery =查询为ObjectQuery< TEntity> ;; 
 if（objQuery！= null）
 {
 sb.AppendLine（）; 
 if（objQuery.Parameters.Count> 0）
 {
 sb.AppendLine（Query parameters：）; 
 sb.AppendLine（----------------------------------------- ---------------------）; 
 sb.AppendLine（Name | Type | Value）; 
 sb.AppendLine（----------------------------------------- ---------------------）; 
 foreach（objQuery.Parameters中的var param）
 {
 sb.AppendFormat（{0，-20} | {1，-20} | {2，-20} {3} ，param.Name，param.ParameterType.ToString（），param.Value，Environment.NewLine）; 
} 
 sb.AppendLine（）; 
} 
 
 string partialQuery = objQuery.ToTraceString（）; 
 objQuery.Parameters.ToList（）。ForEach（p => partialQuery = partialQuery.Replace（：+ p.Name，p.Value.ToString（）））; 
 
 sb.AppendLine（partialQuery）; 
} 
 
 //替换
 
返回sb.ToString（）; 
} 
  

I use

  `.....
   .....
  query = query.Where(criterion, fiedNames);
  string sql2 = ((ObjectQuery)query).ToTraceString();'

to obtain and tweak SQL code that is going to be executed by LINQ.

When I do quick watch I get the following SQL statement which i do not understand.

SELECT [UnionAll2].[C3] AS [C1], [UnionAll2].[C4] AS [C2],
[UnionAll2].[C5] AS [C3], [UnionAll2].[C6] AS [C4], 
[UnionAll2].[C7] AS [C5], 
[UnionAll2].[C8] AS [C6], 
[UnionAll2].[C9] AS [C7],  
..... few hundereds line more.....
@p__linq__4 AS [C2], 
.....
CASE 
     WHEN ( [Join5].[id] IS NULL ) THEN 
     Cast(NULL AS INT) 
     WHEN ( @p__linq__0 = 1 )

.......

Obviously if i paste in SQL management studio and execute it will throw me errors if i do not pass the value for @p__linq__0....

My question: how can get trace SQL that can execute on SQL management?

解决方案

This extension method you can assert:

 /// <summary>
    /// Traces IQueryable's DB query />
    /// </summary>
    /// <typeparam name="TEntity">IQueryable type</typeparam>
    /// <param name="query">The IQueryable query</param>
    /// <returns>Query</returns>
    public static string ToTraceString<TEntity>(this IQueryable<TEntity> query)
    {
        StringBuilder sb = new StringBuilder();

        var objQuery = query as ObjectQuery<TEntity>;
        if (objQuery != null)
        {
            sb.AppendLine();
            if(objQuery.Parameters.Count > 0)
            {
                sb.AppendLine("Query parameters:");
                sb.AppendLine("--------------------------------------------------------------");
                sb.AppendLine("      Name          |        Type        |       Value        ");
                sb.AppendLine("--------------------------------------------------------------");
                foreach (var param in objQuery.Parameters) 
                {
                    sb.AppendFormat("{0, -20}|{1, -20}|{2, -20}{3}", param.Name, param.ParameterType.ToString(), param.Value, Environment.NewLine);
                }
                sb.AppendLine();
            }

            string partialQuery = objQuery.ToTraceString();
            objQuery.Parameters.ToList().ForEach(p => partialQuery = partialQuery.Replace(":" + p.Name, p.Value.ToString()));

            sb.AppendLine(partialQuery);
        }

        // replace 

        return sb.ToString();
    }

这篇关于获取ToTraceString（）为IQueryable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！