Linq Entity Framework 6.0 SQL query with parameters and constants

88 Views Asked by At

I need to build an IQueryable<MyClass> in Linq and then obtain the actual SQL query (to be executed by another actor in my system).

I get to grab the SQL statement using a method extension like

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

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

That is doing the work but the actual SQL statement is often generated with a bunch of "@gp[number]" parameters (I think they're constants). I couldn't find any property or subproperty of the queryObject containing the values to be used for those "@gp"s constants. Further, sometimes the constant values are directly put it in the statement (as 'product_id in (1,2)'), sometimes as variable (like 'book_id in (@gp1, @gp2)) and I cannot guess the difference.

I could use any suggestion

Thanks

0

There are 0 best solutions below