Parameterized Query from an Expression Tree in Entity Framework Core

Question

I'm trying to implement a dynamic filter in a generic repository (.NET Core 3.1 + EF Core 3.1) by building an Expression Tree, but the generated SQL query is never parameterized (I'm verifying the generated query via "Microsoft.EntityFrameworkCore.Database.Command": "Information" in appsettings.json and have EnableSensitiveDataLogging in Startup.cs)

The code to build an Expression Tree is the following (for sake of simplicity working with string values only here):

    public static IQueryable<T> WhereEquals<T>(IQueryable<T> query, string propertyName, object propertyValue)
    {
        var pe = Expression.Parameter(typeof(T));

        var property = Expression.PropertyOrField(pe, propertyName);
        var value = Expression.Constant(propertyValue);

        var predicateBody = Expression.Equal(
            property,
            value
        );

        var whereCallExpression = Expression.Call(
            typeof(Queryable),
            "Where",
            new[] { typeof(T) },
            query.Expression,
            Expression.Lambda<Func<T, bool>>(predicateBody, new ParameterExpression[] { pe })
        );

        return query.Provider.CreateQuery<T>(whereCallExpression);
    }

The approach works, but values are always incorporated inside a generated SQL query and I afraid that it could lead to SQL injections.

Here is an example of a generated query:

Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (33ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p].[Id], [p].[Name], [p].[FirstName], [p].[Created], [p].[CreatedBy], [p].[Updated], [p].[UpdatedBy]
FROM [Persons] AS [p]
WHERE [p].[Name] = N'smith'

Found a potential answer from a EF team member (@divega): Force Entity Framework to use SQL parameterization for better SQL proc cache reuse, managed it to work with Where method, but the generated SQL is still the same.

Tried to use System.Linq.Dynamic.Core, but it has the same issue (generated SQL query is not parameterized).

Is there a way to force Entity Framework Core to generate a parameterized query from an Expression Tree?

Have you looked at LINQkit (albahari.com/nutshell/linqkit.aspx) and Predicate Builder (albahari.com/nutshell/predicatebuilder.aspx). They may provide another path for you — Flydog57
– Flydog57, Commented Mar 3, 2020 at 21:51
Thanks for the provided links - interesting reading. As my filter is fully dynamic and generic, I didn't find a way to build a predicate for properties known only at a run-time, other then working with Expression Trees directly. — Vitaly
– Vitaly, Commented Mar 4, 2020 at 18:29
I'm curious to know how you think this can be subject to an attack. If you pass in a string like ';TRUNC Table; that that last line of SQL becomes WHERE [p].[Name] = N''';DELETE FROM Table;' not WHERE [p].[Name] = N'';DELETE FROM Table; which seems safe enough, which is to say EF escapes the string properly. I'm probably missing something, so would be curious to know what you were thinking of. — Fraser Orr
– Fraser Orr, Commented Dec 3, 2022 at 18:15

NetMage · Accepted Answer · 2020-09-24 18:10:06Z

8

The link you provided explains that EF uses a SQL parameter for variable values, so instead of creating an Expression.Constant for the value passed in, if you create a variable reference (which in C# is always a field reference), then you will get a parameterized query. The simplest solution seems to be to copy how the compiler handles a lambda outer scope variable reference, which is create a class object to hold the value, and reference that.

Unlike Expression.Constant, it isn't easy to get the actual type of the object parameter, so changing that to a generic type:

public static class IQueryableExt {
    private sealed class holdPropertyValue<T> {
        public T v;
    }

    public static IQueryable<T> WhereEquals<T, TValue>(this IQueryable<T> query, string propertyName, TValue propertyValue) {
        // p
        var pe = Expression.Parameter(typeof(T), "p");

        // p.{propertyName}
        var property = Expression.PropertyOrField(pe, propertyName);
        var holdpv = new holdPropertyValue<TValue> { v = propertyValue };
        // holdpv.v
        var value = Expression.PropertyOrField(Expression.Constant(holdpv), "v");

        // p.{propertyName} == holdpv.v
        var whereBody = Expression.Equal(property, value);
        // p => p.{propertyName} == holdpv.v
        var whereLambda = Expression.Lambda<Func<T, bool>>(whereBody, pe);

        // Queryable.Where(query, p => p.{propertyName} == holdpv.v)
        var whereCallExpression = Expression.Call(
            typeof(Queryable),
            "Where",
            new[] { typeof(T) },
            query.Expression,
            whereLambda
        );

        // query.Where(p => p.{propertyName} == holdpv.v)
        return query.Provider.CreateQuery<T>(whereCallExpression);
    }
}

If you need to pass in an object instead, it is simpler to add a conversion to the proper type (which won't affect the generated SQL), rather than dynamically create the right type of holdPropertyValue and assign it a value, so:

public static IQueryable<T> WhereEquals2<T>(this IQueryable<T> query, string propertyName, object propertyValue) {
    // p
    var pe = Expression.Parameter(typeof(T), "p");
    // p.{propertyName}
    var property = Expression.PropertyOrField(pe, propertyName);

    var holdpv = new holdPropertyValue<object> { v = propertyValue };
    // Convert.ChangeType(holdpv.v, p.{propertyName}.GetType())
    var value = Expression.Convert(Expression.PropertyOrField(Expression.Constant(holdpv), "v"), property.Type);

    // p.{propertyName} == Convert.ChangeType(holdpv.v, p.{propertyName}.GetType())
    var whereBody = Expression.Equal(property, value);
    // p => p.{propertyName} == Convert.ChangeType(holdpv.v, p.{propertyName}.GetType())
    var whereLambda = Expression.Lambda<Func<T, bool>>(whereBody, pe);

    // Queryable.Where(query, p => p.{propertyName} == Convert.ChangeType(holdpv.v, p.{propertyName}.GetType()))
    var whereCallExpression = Expression.Call(
        typeof(Queryable),
        "Where",
        new[] { typeof(T) },
        query.Expression,
        whereLambda
    );

    // query.Where(query, p => p.{propertyName} == Convert.ChangeType(holdpv.v, p.{propertyName}.GetType()))
    return query.Provider.CreateQuery<T>(whereCallExpression);
}

edited Sep 24, 2020 at 18:10

answered Mar 3, 2020 at 23:01

NetMage

27.1k3 gold badges39 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Vitaly Over a year ago

Thanks a lot for the provided answer! Works perfect on strings. However for objects there was still an issue with a conversion and for example on int value system throws InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Int32'. lambda_method(Closure ).

Vitaly Over a year ago

Got around by the following code ` var propertyType = ((PropertyInfo)property.Member).PropertyType; var converter = TypeDescriptor.GetConverter(propertyType); var valueConverted = converter.ConvertFrom(propertyValue); var holdpv = new holdPropertyValue<object> { v = valueConverted }; ` that made your answer to work super smoothly.

NetMage Over a year ago

@Vitaly If you pass the wrong type (e.g. a string when the field is an Int32) then that will happen. I tested the code with an int column, but I passed an int to the WhereEquals2 method. I assumed you would pass in the type that matches the column's type.

Vitaly Over a year ago

You are totally right - your code works perfect. Was an issue on my side as I'm parsing params from a query string. Thanks again!

NetMage Over a year ago

@Vitaly Ah - perhaps the parameter should be a string instead of an object and then you convert to an object of the right type as you indicated :)

Collectives™ on Stack Overflow

Parameterized Query from an Expression Tree in Entity Framework Core

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related