Entity Framework Ef.Functions.Like VS String.Contains

Entity framework EF.Functions.Like vs string.Contains

Like query supports wildcard characters and hence very useful compared to the string extension methods in some scenarios.

For ex: If we were to search all the 4 lettered names with 'ri' as the middle characters we could do EF.Functions.Like(c.Name, "_ri_");

or to get all the customers from cities which start with vowels:

var customers = from c in context.Customers 
                   where EF.Functions.Like(c.City, "[aeiou]%")
                   select c;

(Please read @Tseng's answer on how they are translated differently into SQL queries)

EF.Functions.Like in a compiled query could not be translated to SQL equivalent

You seems to be hitting yet another EF Core limitation. The problem is not the EF.Functions.Like method, but the usage of the interpolated string inside, which as you found works inside regular query, but not inside compiled query definition.

The solution/workaround is to use string concatenation in place of string interpolation:

private static Func<DbContext, string, IEnumerable<Class1>> Search =
    EF.CompileQuery((DbContext context, string query) => context.Class1
    .Where(c => EF.Functions.Like(c.param1, "%" + query + "%") // <-- 
     && c.param2== 1 
     && c.param3!= 1)
    );

The resulting SQL query is a bit different, but at least you got a translation.

How to do a LIKE in Entity Framework CORE (not full .net)

The LIKE function has moved under EF.Functions in Core:

from c in dc.Organization
where EF.Functions.Like(c.Boss, "%Jeremy%")

Can Entity Framework handle like with underscore?

Entity Framework Core's SQL Server provider has mapping for DbFunctionsExtensions.Like method. Something like .Where(e => EF.Functions.Like(e.SomeColumn, "_p")) should do the trick.

Entity Framework core - Contains is case sensitive or case insensitive?

It used to be the case for older versions of EF core. Now string.Contains is case sensitive, and for exemple for sqlite it maps to sqlite function `instr()' ( I don't know for postgresql).

If you want to compare strings in a case-insensitive way, you have DbFunctions to do the jobs.

context.Counties.Where(x => EF.Functions.Like(x.Name, $"%{keyword}%")).ToList();

UPDATE to @Gert:

A part of the assumption in the question is incorrect. string.Contains does NOT convert into a LIKE expression even though it USED to be the case in ef core versions <= 1.0 (I think).

• In SQLServer string.contains converts into CHARINDEX(), in oracle and sqlite into instr() which are case sensitive by default UNLESS db or column collation is defined otherwise ( Again, I don't know for postgresql ).
• In all cases EF.Functions.Like() converts into a SQL LIKE expression which is case-insensitive by default unless db or column collation is defined otherwise.

So yes it all goes down to collation but - correct me if I'm wrong - in a way the code can have an influence on the case-sensitive/insensitive search depending on which one of the above method you use.

Now, I might not be completely up to date but I don't think EF core migrations deal with DB collation naturally and unless you've already created the table manually you will end up with the default collation (case-sensitive for sqlite and I honestly don't know for the others).

Getting back to the original question you have at least 2 options to perform this case-insensitive search if not 3 in a future release :

1. Specify the column collation on creation using DbContext.OnModelCreating() using this trick
2. Replace your string.Contains by EF.Functions.Like()
3. Or wait for a promising feature still in discussion : EF.Functions.Collate() function

EF.Functions.Like() for an array of string

Try the following query:

var configurations = _dbContext.Configurations
    .Include(x => x.ChildTable)
    .Where(x => x.Id == Id);

if (!string.IsNullOrEmpty(request.Filter))
{
    configurations = configurations
        .Where(x => x.ChildTable.Any(c => EF.Functions.Like(c.Name, '%' + request.Filter + '%')));
}

EF Core query using String.Contains

A different variations of this question often appear on SO, and the problem is always one and the same - even at the most current version (5.x) EF Core does not support operators on in-memory collections other than simple Contains with primitive value (or Any that can be turned into Contains like x => memValues.Any(v => v == SomeExpr(x)), with == operator being the essential).

The workaround is also one and the same - building dynamically expression - || (or) based for Any and && (and) based for All.

This case requires ||, and is similar to How to simplify repetitive OR condition in Where(e => e.prop1.contains() || e.prop2.contains() || ...) but with value and field roles exchanged, so following is the helper method I would use:

public static partial class QueryableExtensions
{
    public static IQueryable<T> WhereAnyMatch<T, V>(this IQueryable<T> source, IEnumerable<V> values, Expression<Func<T, V, bool>> match)
    {
        var parameter = match.Parameters[0];
        var body = values
            // the easiest way to let EF Core use parameter in the SQL query rather than literal value
            .Select(value => ((Expression<Func<V>>)(() => value)).Body)
            .Select(value => Expression.Invoke(match, parameter, value))
            .Aggregate<Expression>(Expression.OrElse);
        var predicate = Expression.Lambda<Func<T, bool>>(body, parameter);
        return source.Where(predicate);
    }
}

Note that this works only for top level query expressions. If you need something like this for something which is part of a query expression tree (like collection navigation property), you'd need different type of helper function or some library which allows expression injection.

Luckily that's not the case here, so the above helper method can be used directly by passing the trimmedTags and the condition for each tag value, e.g.

return products.WhereAnyMatch(trimmedTags, (p, t) => 
    ("," + p.Categories + ",").Contains("," + t + ",") ||
    ("," + p.Categories + ",").Contains(", " + t + ",") ||
    ("," + p.Categories + ",").Contains("," + t + " ,") ||
    ("," + p.Categories + ",").Contains(", " + t + " ,"));

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

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

How to make EF.Functions.Like case-insensitive?

According to the docs:

Note that if this function is translated into SQL, then the semantics of the comparison will depend on the database configuration. In particular, it may be either case-sensitive or case-insensitive. If this function is evaluated on the client, then it will always use a case-insensitive comparison.

Therefore, it's not the function that is case-sensitive, but your database/column collation settings. You can use the Collate function to specify a different, case-insensitive collation. More on MySQL collations.

---

Related Topics