Write a search query which is case insensitive in EF Core?

Question

I want to ask a question about SQL Server and EF Core. Collation in the database is Latin1_CI_AS and I want to write a search query that contains Turkish characters.

In the database, there is a record named "SELİM" in the 'personnel' table. When I write a query like this in EF Core:

    public async Task<IList<PersonnelGetDto>> Get(PersonnelGetPayload payload)
        {
           if (payload.Name != null)
                query = query.Where(x => x.Name.Contains(payload.Name)); 
        }

The list is empty if my search condition is "selim".

I don't have a chance to change the collation in the database to Turkish because our application is multilingual. I think there will be a problem with other languages. Or am I wrong?

I also wrote the string extension. However, when converting a LINQ query to SQL, all records come to the service layer because the LIKE operator does not assign the WHERE clause. It's very important to run this condition on the sql side. If I take all the dataset to the service layer and query it, it will cost me a lot.

I can solve the problem when I type a query in the database like this:

SELECT * FROM Personnel WHERE Name LIKE 'selim' COLLATE Turkish_CI_AS

I think if I can manipulate collate on EF Core I will solve the problem.

Answer 1

I have tested the like function but it is not resulting correct as op stated. So only one option left remains. Which is to create an interceptor and implement custom logic. I have created a sample like below :

   public class Suffixes
    {
        public const string Collate = "--Collate";
    }

    public class CollationDbCommandInterceptor : DbCommandInterceptor
    {
        private const string CollateSyntax = " collate turkish_ci_as";

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {
            var args = command.Parameters.OfType<DbParameter>()
                           .Where(t => t.DbType == DbType.String && t.Value.ToString().EndsWith(Suffixes.Collate)).ToList();
            if (args.Count <= 0)
                return base.ReaderExecuting(command, eventData, result);

            foreach (var parameter in args)
            {
                parameter.Value = parameter.Value.ToString().Replace(Suffixes.Collate, "");
                var equality = $"= {parameter.ParameterName}";

                var ixs = AllIndexesOf(command.CommandText, equality);

#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
                foreach (var eq in ixs)
                {
                    command.CommandText = command.CommandText.Insert(eq+equality.Length,CollateSyntax);

                }
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities

            }



            return base.ReaderExecuting(command, eventData, result);
        }

        private static IEnumerable<int> AllIndexesOf(string str, string value)
        {
            if (string.IsNullOrEmpty(value))
                throw new ArgumentException("the string to find may not be empty", nameof(value));
            var indexes = new List<int>();
            for (var index = 0; ; index += value.Length)
            {
                index = str.IndexOf(value, index);
                if (index == -1)
                    return indexes;
                indexes.Insert(0,index);
            }
        }
    }

Configuration :

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
               ....  
                optionsBuilder.AddInterceptors(new CollationDbCommandInterceptor());
                ....
            }
        }

Usage :

var kadayif = $"kadayıf{Suffixes.Collate}";
var william = $"Wİlliam{Suffixes.Collate}";            
var auths = ctx.Authors.Where(t =>   t.FirstName == william ||t.LastName == kadayif).ToList(); 
// returns William Shakespeare and Abuzer Kadayıf

The logic is to create an interceptor that seeks a specific suffix in sql parameters passed in the query. Injects query specific collation in to the final sql command text. I tried to cover some advanced scenarios like parameter reuse. It may require more improvements.

Please note that this example is for Entity Framework Core 3.0 which is the version that interceptors introduced. Interception in earlier ef core versions is a bit trick. You can refer to this link for further information.