Linq to Entities : using ToLower() on NText fields

by

Never use .ToLower() to perform a case-insensitive comparison. Here’s why:

  1. It’s possibly wrong (your client collation could be, say, Turkish, and your DB collation not).
  2. It’s highly inefficient; the SQL Emitted is LOWER instead of = with a case-insensitive collation.

Instead, use StringComparison.OrdinalIgnoreCase or StringComparison.CurrentCultureIgnoreCase:

var q = from f in Context.Foos
        where f.Bar.Equals("hi", StringComparison.OrdinalIgnoreCase)
        select f;

But for Contains() there’s a problem: Unlike Equals, StartsWith, etc., it doesn’t have an overload for a StringComparison argument. Why? Good question; ask Microsoft.

That, combined with SQL Server’s limitation on LOWER means there’s no simple way to do what you want.

Possible workarounds might include:

  • Use a full text index, and do the search in a procedure.
  • Use Equals or StartsWith instead, if possible for your task
  • Change the default collation of the column?

More Related Contents:

  1. How to get the date of a day in a month in certain year? [closed]
  2. How do parameterized queries help against SQL injection?
  3. Dynamic where clause (OR) in Linq to Entities
  4. How to create LINQ Expression Tree to select an anonymous type
  5. The cast to value type ‘Int32’ failed because the materialized value is null
  6. “A lambda expression with a statement body cannot be converted to an expression tree”
  7. Bulk-deleting in LINQ to Entities
  8. Entity framework left join
  9. SqlDataSourceEnumerator.Instance.GetDataSources() does not locate local SQL server 2008 instance
  10. linq to entities doesn’t recognize a method
  11. Only parameterless constructors and initializers are supported in LINQ to Entities
  12. How to do a Bulk Insert — Linq to Entities
  13. MultipleActiveResultSets=True or multiple connections?
  14. “The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities” – stumped!
  15. “NOT IN” clause in LINQ to Entities
  16. Simple Automapper Example
  17. How should I multiple insert multiple records?
  18. C# SqlCommand – cannot use parameters for column names, how to resolve?
  19. Received an invalid column length from the bcp client for colid 6
  20. Linq to Entity Join table with multiple OR conditions
  21. How to get first record in each group using Linq
  22. How can I convert ticks to a date format?
  23. ‘Date’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported
  24. String.IsNullOrWhiteSpace in LINQ Expression
  25. Dynamic query with OR conditions in Entity Framework
  26. Calling a SQL User-defined function in a LINQ query
  27. linq to entities vs linq to objects – are they the same?
  28. big issue in converting string to datetime using linq-to-entities
  29. LINQ to Entities group-by failure using .date
  30. Is Injection Possible through Dynamic LINQ?

Leave a Comment