How to make a query through LINQ ignoring accents?

Question

I wonder if it is possible to make a query with LINQ to compare two strings and ignore their accents.

That without my Collation in the database is set as AI (Accent Insensitive) and I don’t need to make a replace in strings to get out by removing their accents.

For example, when trying to search for an item by the name "Orchid", it should return both the items that are saved in the bank as "Orchid", "Orchid", "Orchid" and any other type of variation.

I’ve tried using the

string.Compare(string1, string2, CultureInfo.GetCultureInfo("pt-BR"), 
               CompareOptions.IgnoreNonSpace | CompareOptions.IgnoreCase) == 0

within the LINQ consultation, but does not work.

Answer 1

You can only do that if you define the COLLATION column (or table, or bank) before. There are some ways to do this.

One of them is defining a baseline initializer, thus:

public class CollationInitializer : CreateDatabaseIfNotExists<MeuContexto>
{
    public override void InitializeDatabase(MeuContextocontext)
    {
        if(!context.Database.Exists())
        {
            using (SqlConnection connection = new SqlConnection("DefaultConnection"))
            {
                connection.Open();
                using(SqlCommand command = new SqlCommand(string.Format("CREATE DATABASE {0} COLLATE Latin1_General_CI_AI", "MinhaBaseDeDados"), connection))
                {
                    command.ExecuteNonQuery();
                }
            }

            SqlConnection.ClearAllPools();
         }

         base.InitializeDatabase(context);
    }
}

And:

public class MeuContexto: DbContext
{
    public MeuContexto() : base("DefaultConnection", throwIfV1Schema: false)
    {
        Database.SetInitializer(new CollationInitializer<MeuContexto>());
        if(!Database.Exists())
        {
            Database.Initialize(true);
         }
     } 
}

Another is defining a migration developer to define the COLLATION at the time the bank is being updated:

public class CreateDatabaseCollationInterceptor : IDbCommandInterceptor
{
    private readonly string _collation;

    public CreateDatabaseCollationInterceptor(string collation)
    {
        _collation = collation;
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { }
    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        // Apenas para SQL Server
        if (Regex.IsMatch(command.CommandText, @"^create database \[.*]$"))
        {
            command.CommandText += " COLLATE " + _collation;
        }
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }
    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { }
    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }
    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { }
}

Answer 2

In SQL Server it is possible to change the collation one-column "on the fly" during a Select.

Something like:

Select *
From Entidades
Where (Coluna Collate SQL_Latin1_General_CP1_CI_AI) Like 'Tê%'

This query returns all rows where the column Nome start with "You", whether or not there are accents or differences of Casing. For example: "Testing", "Téste", "téste". Note also that the parameter uses accent, I did this to illustrate that it is also disregarded.

Obviously for this you will need to write to query to execute it, but I don’t think that’s a problem.

I made an example running this query with Entity Framework.

const string collation = "SQL_Latin1_General_CP1_CI_AI";

using (var db = new Contexto())
{
    var resultado = db.Database.SqlQuery<Entidade>($"Select * From Entidades
                                                     Where (Nome Collate {collation}) 
                                                     Like '%{param}%'").ToList();
}

Answer 3

a simple way I found it was like this. utilizo Entity framework com mysql e utf8 -default collation,

using (controle_estoqueEntities entity = new controle_estoqueEntities())
        {
            return (from a in entity.clientes
                    where ( (a.nome_cliente == nome || a.nome_cliente.Contains(nome)))  
                    orderby a.nome_cliente
                    select new ClienteDTO
                    {IdCliente = a.idcliente,
                        NomeCliente = a.nome_cliente}).toList();

}