How to get the Commandtext from a Iqueryable?

Asked

Viewed 320 times

4

I’m trying to catch the sql generated by IQueryable, but is returning:

SELECT NULL AS [EMPTY]

I expected something like:

SELECT i FROM lista WHERE i > 20;

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

There’s some other way to do it?

c# linq-to-sql

  • what is the "i" ?

  • and another question, why are you creating a connection to the database, if you want to access the contents of the list?

  • @sir_ask Because it’s the only way I could find to get the sql code.

1 answer

0

There is a here an example that can 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>;
        }
    }
}

Browser other questions tagged c# linq-to-sql

You are not signed in. Login or sign up in order to post.