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

by

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

More Related Contents:

  1. Dynamically access table in EF Core 2.0
  2. Bulk-deleting in LINQ to Entities
  3. loading a full hierarchy from a self referencing table with EntityFramework.Core
  4. The LINQ expression could not be translated and will be evaluated locally
  5. EF Core queries all columns in SQL when mapping to object in Select
  6. Check if list contains item from other list in EntityFramework
  7. Entity Framework Core Many to Many change navigation property names
  8. Is it possible to perform joins across different databases using LINQ?
  9. Update-Database command is not working in ASP.Net Core / Entity Framework Core because object in database already exists
  10. Read a CSV to a C# object to be able to do LINQ query on it [closed]
  11. How do you perform a left outer join using linq extension methods
  12. Update all objects in a collection using LINQ
  13. Alphanumeric sorting using LINQ
  14. XElement namespaces (How to?)
  15. Row_number over (Partition by xxx) in Linq?
  16. LINQ Aggregate algorithm explained
  17. Mutating the expression tree of a predicate to target another type
  18. Why does Enumerable.All return true for an empty sequence? [duplicate]
  19. How to instantiate a DbContext in EF Core
  20. How to disable cascade delete for link tables in EF code-first?
  21. Cascade deleting with EF Core
  22. Convert anonymous type to class
  23. How to use TransactionScope in C#?
  24. C# Linq where clause as a variable
  25. Find if listA contains any elements not in listB
  26. Find all child controls of specific type using Enumerable.OfType() or LINQ
  27. LINQ to SQL using GROUP BY and COUNT(DISTINCT)
  28. Remove item from list based on condition
  29. How to refresh an Entity Framework Core DBContext?
  30. CSV to object model mapping

Leave a Comment