Dynamic LINQ accent insensitive string queries

Question

I am developing a C# .Net MVC application and trying to implement a generic search method for entity fields. As our pages are growing i don't want to code a search method each time a new page is added.

For that, i am using Dynamic.Core LINQ Queries : Check it at : https://dynamic-linq.net/basic-simple-query the way i implemented it works the following way : at user input in the view, the app sends an ajax request to the controller telling it to search that specific value and then display the new list where the previous one.

The problem is : i could make it case insensitive but not accent insensitive and was wondering if anyone could help me with that.

Here is my code :

public static List<T> SearchEntityList<T>(this IQueryable<T> entityList, string searchBy, List<string> fieldsToCheck)
        {

            if (searchBy == null)
                return entityList.ToList();

            searchBy = searchBy.ToLower().RemoveDiacriticsUtil();
            // Dynamic LINQ Library

            string query = "";
            foreach (string str in fieldsToCheck)
            {
                query += str + ".ToString().ToLower().Contains(@0) ||";
            }

            if (query != null)
            {
                // Removes the last "OR" inserted on the foreach here on top
                query = query.Substring(0,query.Length - 3);
                try
                {
                    entityList = entityList.Where(query, searchBy);
                }
                catch (Exception e)
                {
                    // query is wrong, list wont be filtered.
                    return entityList.ToList();
                }
            }
                
            List<T> filteredList = entityList.ToList(); ;
            return filteredList;
        }

the method receives a list of string representing the fields to check, for example : "Username" then a string query is built and checked with the database.

This code works as expected and is case insensitive, now i want to add accent insensitive to it.

i modify this line

query += str + ".ToString().ToLower().Contains(@0) ||";

with this one

query += str + "Collate(" + str + ".toString(), \"SQL_Latin1_General_CP1_CI_AI\").Contains(@0) ||";

and now i cannot make it work. Got this error :

"No applicable method 'Collate' exists in type '...'"

I tested a lot of other stuff such as RemoveDiacritics, etc.. but they dont work with dynamic string linq queries...

Was wondering if anyone already had the same problem. Thanks !

Answer 1

You're on the right track. You need to register your custom extension methods for dynamic linq before us use it:

    // Registration for the default custom type handler
    [DynamicLinqType]
    public static class MyExtensions
    {
        // taken from: https://stackoverflow.com/questions/359827/ignoring-accented-letters-in-string-comparison/368850
        public static string RemoveDiacritics(this string text)
        {
            return string.Concat(
                text.Normalize(NormalizationForm.FormD)
                    .Where(ch => CharUnicodeInfo.GetUnicodeCategory(ch) !=
                                 UnicodeCategory.NonSpacingMark)
            ).Normalize(NormalizationForm.FormC);
        }
    }

    public static class Entry
    {
        public static void Main(string[] args)
        {
            // your input
            var query = "éè";

            // clean it using your extension method
            var cleanQuery = query.RemoveDiacritics();

            // a test set for this demo
            IQueryable<string> testSet = new EnumerableQuery<string>(new List<string>()
            {
                "soméè", "tèèst", "séét", "BBeeBB", "NoMatchHere"
            });
            
            var results = testSet.Where("it.RemoveDiacritics().Contains(@0)", cleanQuery);
            Debug.Assert(results.Count() == 4);
        }
    }