Parameterized Query from an Expression Tree in Entity Framework Core

.net-core c# entity-framework-core linq-to-sql

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?

1
0
3/3/2020 8:42:33 PM

Accepted Answer

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

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

        var predicateBody = Expression.Equal(
            property,
            value
        );
        var wf = Expression.Lambda<Func<T, bool>>(predicateBody, new ParameterExpression[] { pe });


        //var v = (int)propertyValue;
        //Expression<Func<Accounts,bool>> wf = (Accounts a) => a.Actid == v;

        wf.Dump();

        var whereCallExpression = Expression.Call(
            typeof(Queryable),
            "Where",
            new[] { typeof(T) },
            query.Expression,
            wf
        );

        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, property.Type)
    var value = Expression.Convert(Expression.PropertyOrField(Expression.Constant(holdpv), "v"), property.Type);

    // p.propertyName == Convert.ChangeType(holdpv.v, property.Type)
    var predicateBody = Expression.Equal(
        property,
        value
    );
    // p => p.propertyName == Convert.ChangeType(holdpv.v, property.Type)
    var wf = Expression.Lambda<Func<T, bool>>(predicateBody, new ParameterExpression[] { pe });

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

    // query.Where(p => p.propertyName == Convert.ChangeType(holdpv.v, property.Type))
    return query.Provider.CreateQuery<T>(whereCallExpression);
}
0
3/3/2020 11:01:47 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow