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?

score 2 · Accepted Answer · answered Mar 22 '19 at 02:10

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

Thank you for your time, sir. Much appreciated. – Justin Nimmo Mar 22 '19 at 14:03 — Justin Nimmo, Mar 22 '19 at 14:03

score 0 · Answer 2 · answered Mar 21 '19 at 21:14

0

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

answered Mar 21 '19 at 21:14

Ashkan Mobayen Khiabani

33,575
33
102
171

1

Please explain why I'd break EF.Functions.Like(m.Name, searchPerson.Name) into 3 separate lines when I'm trying to reduce the amount of code I'm trying to write? – Justin Nimmo Mar 21 '19 at 21:21
Your final function may reduce lots of code, but inside it, you should implement all possibilities, so that it could function correctly, in the other words, you write more code in your component so that later it could do a lot with less code – Ashkan Mobayen Khiabani Mar 21 '19 at 21:23
2

Based on [this](https://stackoverflow.com/a/46489164/5070182) answer, as of 2.0 EF Core has the EF.Functions.Like which implements the same functionality with fewer lines of code. – Justin Nimmo Mar 21 '19 at 21:28
I'm with @Justin - their code is perfectly valid. This "answer" has nothing in common with the asked question. – Ivan Stoev Mar 22 '19 at 01:11

Entity Framework Search Like or Equal

2 Answers2