Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq WHERE EF.Functions.Like - Why direct properties work and reflection does not?

I try to perform a simple LIKE action on the database site, while having query building services based on generic types. I found out while debugging however, that performing EF.Functions.Like() with reflection does not work as expected:

The LINQ expression 'where __Functions_0.Like([c].GetType().GetProperty("FirstName").GetValue([c], null).ToString(), "%Test%")' could not be translated and will be evaluated locally..


The code that makes the difference

That works:

var query = _context.Set<Customer>().Where(c => EF.Functions.Like(c.FirstName, "%Test%"));

This throws the warning & tries to resolve in memory:

var query = _context.Set<Customer>().Where(c => EF.Functions.Like(c.GetType().GetProperty("FirstName").GetValue(c, null).ToString(), "%Test%"));

Does the Linq query builder or the EF.Functions not support reflections?

Sorry if the questions seem basic, it's my first attempt with .NET Core :)

like image 403
Jan Pedryc Avatar asked Oct 10 '19 10:10

Jan Pedryc


4 Answers

In EF the lambdas are ExpressionTrees and the expressions are translated to T-SQL so that the query can be executed in the database.

You can create an extension method like so:

public static IQueryable<T> Search<T>(this IQueryable<T> source, string propertyName, string searchTerm)
{
    if (string.IsNullOrEmpty(propertyName) || string.IsNullOrEmpty(searchTerm))
    {
        return source;
    }

    var property = typeof(T).GetProperty(propertyName);

    if (property is null)
    {
        return source;
    }

    searchTerm = "%" + searchTerm + "%";
    var itemParameter = Parameter(typeof(T), "item");

    var functions = Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
    var like = typeof(DbFunctionsExtensions).GetMethod(nameof(DbFunctionsExtensions.Like), new Type[] { functions.Type, typeof(string), typeof(string) });

    Expression expressionProperty = Property(itemParameter, property.Name);

    if (property.PropertyType != typeof(string))
    {
        expressionProperty = Call(expressionProperty, typeof(object).GetMethod(nameof(object.ToString), new Type[0]));
    }

    var selector = Call(
               null,
               like,
               functions,
               expressionProperty,
               Constant(searchTerm));

    return source.Where(Lambda<Func<T, bool>>(selector, itemParameter));
}

And use it like so:

var query = _context.Set<Customer>().Search("FirstName", "Test").ToList();
var query2 = _context.Set<Customer>().Search("Age", "2").ToList();

For reference this was the Customer I used:

public class Customer
{
    [Key]
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public int Age { get; set; }
}
like image 115
CarlosMorgado Avatar answered Nov 09 '22 22:11

CarlosMorgado


Simple answer, no.

EntityFramework is trying to covert your where clause in to a SQL Query. There is no native support for reflection in this conversation.

You have 2 options here. You can construct your text outside of your query or directly use property itself. Is there any specific reason for not using something like following?

var query = _context.Set<Customer>().Where(c => EF.Functions.Like(c.FirstName, "%Test%"));
like image 42
Serdar Avatar answered Nov 09 '22 22:11

Serdar


Keep in mind that every ExpresionTree that you put in Where clause has to be translated into SQL query.

Because of that, ExpressionTrees that you can write are quite limited, you have to stick to some rules, thats why reflection is not supported.

Image that instead of :

var query = _context.Set<Customer>().Where(c => EF.Functions.Like(c.GetType().GetProperty("FirstName").GetValue(c, null).ToString(), "%Test%"));

You write something like:

var query = _context.Set<Customer>().Where(c => EF.Functions.Like(SomeMethodThatReturnsString(c), "%Test%"));

It would mean that EF is able to translate any c# code to SQL query - it's obviously not true :)

like image 42
Szymon Tomczyk Avatar answered Nov 09 '22 22:11

Szymon Tomczyk


I chucked together a version of the accepted answer for those using NpgSQL as their EF Core provider as you will need to use the ILike function instead if you want case-insensitivity, also added a second version which combines a bunch of properties into a single Where() clause:

public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, string propertyName, string searchTerm)
    {
        // Check property name
        if (string.IsNullOrEmpty(propertyName))
        {
            throw new ArgumentNullException(nameof(propertyName));
        }

        // Check the search term
        if(string.IsNullOrEmpty(searchTerm))
        {
            throw new ArgumentNullException(nameof(searchTerm));
        }

        // Check the property exists
        var property = typeof(T).GetProperty(propertyName);
        if (property == null)
        {
            throw new ArgumentException($"The property {typeof(T)}.{propertyName} was not found.", nameof(propertyName));
        }

        // Check the property type
        if(property.PropertyType != typeof(string))
        {
            throw new ArgumentException($"The specified property must be of type {typeof(string)}.", nameof(propertyName));
        }

        // Get expression constants
        var searchPattern = "%" + searchTerm + "%";
        var itemParameter = Expression.Parameter(typeof(T), "item");
        var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
        var likeFunction = typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { functions.Type, typeof(string), typeof(string) });

        // Build the property expression and return it
        Expression selectorExpression = Expression.Property(itemParameter, property.Name);
        selectorExpression = Expression.Call(null, likeFunction, functions, selectorExpression, Expression.Constant(searchPattern));
        return source.Where(Expression.Lambda<Func<T, bool>>(selectorExpression, itemParameter));
    }

    public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, IEnumerable<string> propertyNames, string searchTerm)
    {
        // Check property name
        if (!(propertyNames?.Any() ?? false))
        {
            throw new ArgumentNullException(nameof(propertyNames));
        }

        // Check the search term
        if (string.IsNullOrEmpty(searchTerm))
        {
            throw new ArgumentNullException(nameof(searchTerm));
        }

        // Check the property exists
        var properties = propertyNames.Select(p => typeof(T).GetProperty(p)).AsEnumerable();
        if (properties.Any(p => p == null))
        {
            throw new ArgumentException($"One or more specified properties was not found on type {typeof(T)}: {string.Join(",", properties.Where(p => p == null).Select((p, i) => propertyNames.ElementAt(i)))}.", nameof(propertyNames));
        }

        // Check the property type
        if (properties.Any(p => p.PropertyType != typeof(string)))
        {
            throw new ArgumentException($"The specified properties must be of type {typeof(string)}: {string.Join(",", properties.Where(p => p.PropertyType != typeof(string)).Select(p => p.Name))}.", nameof(propertyNames));
        }

        // Get the expression constants
        var searchPattern = "%" + searchTerm + "%";
        var itemParameter = Expression.Parameter(typeof(T), "item");
        var functions = Expression.Property(null, typeof(EF).GetProperty(nameof(EF.Functions)));
        var likeFunction = typeof(NpgsqlDbFunctionsExtensions).GetMethod(nameof(NpgsqlDbFunctionsExtensions.ILike), new Type[] { functions.Type, typeof(string), typeof(string) });

        // Build the expression and return it
        Expression selectorExpression = null;
        foreach (var property in properties)
        {
            var previousSelectorExpression = selectorExpression;
            selectorExpression = Expression.Property(itemParameter, property.Name);
            selectorExpression = Expression.Call(null, likeFunction, functions, selectorExpression, Expression.Constant(searchPattern));
            if(previousSelectorExpression != null)
            {
                selectorExpression = Expression.Or(previousSelectorExpression, selectorExpression);
            }
        }
        return source.Where(Expression.Lambda<Func<T, bool>>(selectorExpression, itemParameter));
    }
like image 23
Alex Hope O'Connor Avatar answered Nov 09 '22 23:11

Alex Hope O'Connor