0

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

4
  • It is bad idea. EF is not designed for SQL building. What you can do is to use, for example, Remote.Linq which can serialize LINQ expression, execute query on other side (EF Core should be installed on target), serialize result, return data back, construct objects. Commented Oct 5, 2023 at 9:08
  • What is this code trying to do? There are no "inlined parameters" and what this seems to be doing is the old, bad string concatenations that cause SQL injection attacks and conversion errors. Imagine what would happen if a parameter value contained '; DROP TABLE Users;-- Commented Oct 5, 2023 at 9:16
  • I know that's a not-so-good workaround, but I need to solve a client problem really fast. There's no risk of injection because the inline parameters are beckend generated and sanitized. And to be fair, I haven't questioned whether it'as bad idea or not. Commented Oct 5, 2023 at 9:31
  • I'm trying to explain that you approached the problem from the wrong side. Anyway, you can create visitor which replace parameters (they are usually MemberExpression of ConstatntExpression) and feed EF Core with just constants. Commented Oct 5, 2023 at 9:41

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.