How do I get the CommandText of an IQueryable?

Question

How do I get the CommandText of an IQueryable?

Navigation

#1 by (0 votes)

4

I'm trying to get the sql generated by IQueryable , but it's returning:

SELECT NULL AS [EMPTY]

I expected something like:

SELECT i FROM lista WHERE i > 20;

How I'm doing:

IDbConnection conn = new MySqlConnection("server=127.0.0.1;uid=root;pwd=;database=teste");
DataContext db = new DataContext(conn);

List<int> lista = new List<int>();
lista.AddRange(new int[] { 10, 25, 50, 35, 16, 100, 30, 22, 5 });

var query = from i in lista where i > 20 select i;
var dc = db.GetCommand(query.AsQueryable());

Console.WriteLine("Command Text: \n{0}", dc.CommandText);

Do you have any other way to do this?

c# linq-to-sql

asked by anonymous 10.11.2017 / 14:10

1 answer

Trace lines from a specific point and angle Two different bases with spring data jpa

score 0 · Answer 1

here an example that might help or even solve your problem:

using System;
using System.Data.Entity.Core.Objects;
using System.Linq;
using System.Linq.Expressions;
using System.Text;

namespace Fenton.Example
{
    public static class IQueryableExtensions
    {
        /// <summary>
        /// For an Entity Framework IQueryable, returns the SQL with inlined Parameters.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <returns></returns>
        public static string ToTraceQuery<T>(this IQueryable<T> query)
        {
            ObjectQuery<T> objectQuery = GetQueryFromQueryable(query);

            var result = objectQuery.ToTraceString();
            foreach (var parameter in objectQuery.Parameters)
            {
                var name = "@" + parameter.Name;
                var value = "'" + parameter.Value.ToString() + "'";
                result = result.Replace(name, value);
            }

            return result;
        }

        /// <summary>
        /// For an Entity Framework IQueryable, returns the SQL and Parameters.
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <returns></returns>
        public static string ToTraceString<T>(this IQueryable<T> query)
        {
            ObjectQuery<T> objectQuery = GetQueryFromQueryable(query);

            var traceString = new StringBuilder();

            traceString.AppendLine(objectQuery.ToTraceString());
            traceString.AppendLine();

            foreach (var parameter in objectQuery.Parameters)
            {
                traceString.AppendLine(parameter.Name + " [" + parameter.ParameterType.FullName + "] = " + parameter.Value);
            }

            return traceString.ToString();
        }

        private static System.Data.Entity.Core.Objects.ObjectQuery<T> GetQueryFromQueryable<T>(IQueryable<T> query)
        {
            var internalQueryField = query.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_internalQuery")).FirstOrDefault();
            var internalQuery = internalQueryField.GetValue(query);
            var objectQueryField = internalQuery.GetType().GetFields(System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.Instance).Where(f => f.Name.Equals("_objectQuery")).FirstOrDefault();
            return objectQueryField.GetValue(internalQuery) as System.Data.Entity.Core.Objects.ObjectQuery<T>;
        }
    }
}