LINQ Where Ignore Accentuation and Case

by

To ignore case and accents (diacritics) you can first define an extension method like this: 

    public static string RemoveDiacritics(this String s)
    {
        String normalizedString = s.Normalize(NormalizationForm.FormD);
        StringBuilder stringBuilder = new StringBuilder();

        for (int i = 0; i < normalizedString.Length; i++)
        {
            Char c = normalizedString[i];
            if (CharUnicodeInfo.GetUnicodeCategory(c) != UnicodeCategory.NonSpacingMark)
                stringBuilder.Append(c);
        }

        return stringBuilder.ToString();
    }

(Modified from Ignoring accented letters in string comparison)

Now you can run your query:

string queryText = filter.ToUpper().RemoveDiacritics();

var result = from p in People
         where p.Name.ToUpper().RemoveDiacritics() == queryText
         select p;

This is fine if you are just iterating over a collection in C#, but if you are using LINQ to SQL it is preferable to avoid non-standard methods (including extension methods) in your LINQ query. This is because your code cannot be converted into valid SQL and hence run on SQL Server with all its lovely performance optimization.

Since there doesn’t seem to be a standard way of ignoring accents within LINQ to SQL, in this case I would suggest changing the field type that you want to search to be case- and accent-insensitive (CI_AI).

With your example:

ALTER TABLE People ALTER COLUMN Name [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AI

Your query should now ignore accentuation and case.

Note that you will need to temporarily remove any unique constraints on the field before running the above query, e.g.

ALTER TABLE People DROP CONSTRAINT UQ_People_Name

Now your LINQ query would simply be:

var result = from p in People
         where p.Name == filter
         select p;

See related question here.

More Related Contents:

  1. LINQ – Left Join, Group By, and Count
  2. Am I misunderstanding LINQ to SQL .AsEnumerable()?
  3. Operation could destabilize the runtime?
  4. How to select only the records with the highest date in LINQ
  5. DISTINCT() and ORDERBY issue
  6. Max or Default?
  7. How do I do a left outer join with Dynamic Linq?
  8. Get sum of two columns in one LINQ query
  9. Last and LastOrDefault not supported
  10. Linq where column == (null reference) not the same as column == null
  11. Prevent duplicates from array, based on condition [closed]
  12. Return anonymous type results?
  13. How do I apply OrderBy on an IQueryable using a string column name within a generic extension method?
  14. What guarantees are there on the run-time complexity (Big-O) of LINQ methods?
  15. How to do a subquery in LINQ?
  16. How to get the index of an element in an IEnumerable?
  17. LINQ: Not Any vs All Don’t
  18. How to check if IEnumerable is null or empty?
  19. LINQ: combining join and group by
  20. What is the Efficiency and Performance of LINQ and Lambda Expression in .Net?
  21. Why is LINQ .Where(predicate).First() faster than .First(predicate)?
  22. MVC 4 how pass data correctly from controller to view
  23. How to take all but the last element in a sequence using LINQ?
  24. Cannot implicitly convert type ‘System.Collections.Generic.IEnumerable’ to ‘System.Collections.Generic.List
  25. LINQ Expression to return Property value?
  26. Max return value if empty query
  27. How to do an “in” query in entity framework?
  28. Keep Getting ‘The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities’ Exception
  29. How to include() nested child entity in linq
  30. LINQ Query to Convert string to datetime

Leave a Comment