Entity Framework Search Like or Equal

Question

I'm using EntityFrameworkCore and am trying to create a simplified instance of searching for either 'equal to' or 'like' based whether the search object contians the wildcard character. Here's the base of what I'm working with

public class Person
{
    public string Name;
    public string MothersName;
    public string FathersName;

}

public class SearchPerson
{
    public string Name;
}

public class Program
{
    public void FindPerson(SearchPerson searchPerson)
    {
        if (!string.IsNullOrEmpty(searchPerson.Name))
        {
            if (searchPerson.Name.Contains("%"))
            {
                EFPersonObject.Where(m => EF.Functions.Like(m.Name, searchPerson.Name));
            }
            else
            {
                EFPersonObject.Where(m => m.Name == searchPerson.Name);
            }
        }
    }
}

If my SearchPerson class extends to 5 or 10 or 15 possible search params, there is a lot of repeated code. I should be able to implement some reflection in an extension and using Jim C's response here, get and pass the name of the property and simplify a lot of it down to one line

public static class SearchExtension
{
        public static void FindLike<T>(this DbSet<T> model, PropertyInfo info, string searchValue) where T : class
    {
        if (!string.IsNullOrEmpty(searchValue))
        {
            if (searchValue.Contains("%"))
            {
                model.Where(m => EF.Functions.Like(typeof(T).GetProperty(info.Name).GetValue(model, null).ToString(), searchValue));
            }
            else
            {
                model.Where(m => typeof(T).GetProperty(info.Name).GetValue(model, null).ToString() == searchValue);
            }
        }
    }
}

Usage:

EFPersonObject.FindLike(typeof(Person).GetProperty(RemoteMgr.GetPropertyName(()=>typeof(Person).Name)), searchPerson.Name);

(I haven't tested it yet, but if it isn't right, it should be close), but I'm going to assume I'm going to take a performance hit. Is there another way to implement this where reflection isn't needed to avoid the performance hit?

Answer 1

Using reflection (and other non SQL translatable) calls inside the query expression tree is not a good idea. In EF Core 1x and 2.x it will cause client evaluation, and EF Core v3+ will throw exception similar to EF 6.

LINQ to Entities best work with expressions. And once you need expression, you'd better make your custom extension method receive lambda expression directly rather than PropertyInfo obtained via lambda expression as in the linked topic.

Here is a sample implementation of the above:

public static partial class QueryableExtensions
{
    public static IQueryable<T> WhereMatch<T>(this IQueryable<T> source, Expression<Func<T, string>> expr, string searchValue)
    {
        if (string.IsNullOrEmpty(searchValue))
            return source;
        else if (searchValue.Contains("%"))
            return source.Where(expr.Map(value => EF.Functions.Like(value, searchValue)));
        else
            return source.Where(expr.Map(value => value == searchValue));
    }

    static Expression<Func<TSource, TTarget>> Map<TSource, TIntermediate, TTarget>(this Expression<Func<TSource, TIntermediate>> source, Expression<Func<TIntermediate, TTarget>> target)
        => Expression.Lambda<Func<TSource, TTarget>>(Expression.Invoke(target, source.Body), source.Parameters);
}

The main method is WhereMatch. It uses a small Expression helper method called Map for composing lambda expressions from other lambda expressions.

Sample usage would be:

// SearchPerson searchPerson
// DbContext db
var query = db.Set<Person>()
    .WhereMatch(p => p.Name, searchPerson.Name)
    .WhereMatch(p => p.MothersName, searchPerson.MothersName)
    .WhereMatch(p => p.FathersName, searchPerson.FathersName);

Answer 2

For Equality comparison you should use ==:

EFPersonObject.Where(m => m.Name == searchPerson.Name);

For LIKE :

like 'something%': (StartsWith Method)

EFPersonObject.Where(m => m.Name.StartsWith(searchPerson.Name));

like '%something': (EndsWith Method)

EFPersonObject.Where(m => m.Name.EndsWith(searchPerson.Name));

like '%something%': (Contains Method)

EFPersonObject.Where(m => m.Name.Contains(searchPerson.Name));