Ordenações e sensibilidade a maiúsculas

O processamento de texto em bancos de dados pode ser complexo e requer mais atenção do usuário do que se suspeitaria. Por um lado, os bancos de dados variam consideravelmente na forma como lidam com o texto; por exemplo, enquanto alguns bancos de dados diferenciam maiúsculas de minúsculas por padrão (por exemplo, Sqlite, PostgreSQL), outros não diferenciam maiúsculas de minúsculas (SQL Server, MySQL). Além disso, devido ao uso de índice, a sensibilidade a maiúsculas e minúsculas e aspectos semelhantes podem ter um impacto de longo alcance no desempenho da consulta: embora possa ser tentador usar string.ToLower para forçar uma comparação que não diferencia maiúsculas de minúsculas em um banco de dados que diferencia maiúsculas de minúsculas, isso pode impedir o uso de índices pelo seu aplicativo. Esta página detalha como configurar a sensibilidade a maiúsculas e minúsculas ou, de forma mais geral, intercalações e como fazer isso de maneira eficiente sem comprometer o desempenho das consultas.

Introdução às colações

Um conceito fundamental no processamento de texto é a colação, que é um conjunto de regras que determina como os valores de texto são ordenados e comparados para verificar igualdade. Por exemplo, enquanto uma intercalação insensível a maiúsculas desconsidera diferenças entre letras maiúsculas e minúsculas para fins de comparação de igualdade, uma intercalação sensível a maiúsculas considera essas diferenças. No entanto, como a sensibilidade a maiúsculas e minúsculas é sensível à cultura (por exemplo, i e I representam letras diferentes em turco), existem múltiplas ordenações insensíveis a maiúsculas e minúsculas, cada uma com seu próprio conjunto de regras. O escopo das colações também vai além da sensibilidade a maiúsculas e minúsculas, para incluir outros aspectos dos dados de caractere; em alemão, por exemplo, às vezes (mas nem sempre) é desejável tratar ä e ae como idênticos. Por fim, as ordenações também definem como os valores de texto são ordenados: enquanto o alemão coloca ä depois a, o sueco o coloca no final do alfabeto.

Todas as operações de texto em um banco de dados usam uma ordenação – explicita ou implicitamente – para determinar como a operação compara e ordena cadeias de caracteres. A lista real de ordenações disponíveis e seus esquemas de nomenclatura é específica do banco de dados; consulte a seção abaixo para obter links para páginas de documentação relevantes de vários bancos de dados. Felizmente, os bancos de dados geralmente permitem que uma ordenação padrão seja definida no nível do banco de dados ou da coluna e especifique explicitamente qual ordenação deve ser usada para operações específicas em uma consulta.

Agrupamento de banco de dados

Na maioria dos sistemas de banco de dados, uma ordenação padrão é definida no nível do banco de dados; a menos que substituído, essa ordenação se aplica implicitamente a todas as operações de texto que ocorrem dentro desse banco de dados. Normalmente, a ordenação do banco de dados é definida no momento da criação do banco de dados (por meio da CREATE DATABASE instrução DDL) e, se não for especificada, usa como padrão um valor de nível de servidor determinado no momento da instalação. Por exemplo, a ordenação padrão no nível do servidor no SQL Server para a localidade do computador "Inglês (Estados Unidos)" é SQL_Latin1_General_CP1_CI_AS, que é insensível a maiúsculas e sensível a acentos. Embora os sistemas de banco de dados geralmente permitam alterar a ordenação de um banco de dados existente, isso pode levar a complicações; É recomendável escolher uma ordenação antes da criação do banco de dados.

Ao usar migrações do EF Core para gerenciar o esquema do seu banco de dados, o seguinte código no método de configuração do seu modelo ajusta um banco de dados SQL Server para usar uma ordenação sensível a maiúsculas e minúsculas:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Ordenação de coluna

As ordenações também podem ser definidas em colunas de texto, substituindo o padrão do banco de dados. Isso pode ser útil se determinadas colunas precisarem não diferenciar maiúsculas de minúsculas, enquanto que o restante do banco de dados precisa diferenciar maiúsculas de minúsculas.

Ao usar migrações do EF Core para gerenciar seu esquema de banco de dados, o seguinte configura a coluna para que a Name propriedade não diferencia maiúsculas de minúsculas em um banco de dados configurado para diferenciar maiúsculas de minúsculas:

modelBuilder.Entity<Customer>().Property(c => c.Name)
    .UseCollation("SQL_Latin1_General_CP1_CI_AS");

Ordenação explícita em uma consulta

Em alguns casos, a mesma coluna precisa ser consultada usando ordenações diferentes por consultas diferentes. Por exemplo, uma consulta pode precisar executar uma comparação que diferencia maiúsculas de minúsculas em uma coluna, enquanto outra pode precisar executar uma comparação que não diferencia maiúsculas de minúsculas na mesma coluna. Isso pode ser feito especificando explicitamente uma ordenação dentro da própria consulta:

var customers = await context.Customers
    .Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
    .ToListAsync();

Isso gera uma cláusula COLLATE na consulta SQL que aplica uma ordenação sensível a maiúsculas/minúsculas, independentemente da ordenação definida no nível da coluna ou do banco de dados.

SELECT [c].[Id], [c].[Name]
FROM [Customers] AS [c]
WHERE [c].[Name] COLLATE SQL_Latin1_General_CP1_CS_AS = N'John'

Ordenações e índices explícitos

Índices são um dos fatores mais importantes no desempenho do banco de dados - uma consulta que é executada com eficiência com um índice pode parar sem esse índice. Índices herdam implicitamente a ordenação de sua coluna; isso significa que todas as consultas na coluna são automaticamente qualificadas para usar índices definidos nessa coluna, desde que a consulta não especifique uma ordenação diferente. Especificar uma ordenação explícita em uma consulta geralmente impedirá que essa consulta use um índice definido nessa coluna, pois as ordenações não corresponderiam mais; Portanto, é recomendável ter cuidado ao usar esse recurso. É sempre preferível definir a ordenação no nível de coluna (ou banco de dados), permitindo que todas as consultas usem implicitamente essa ordenação e se beneficiem de qualquer índice.

Observe que alguns bancos de dados permitem que a ordenação seja definida ao criar um índice (por exemplo, PostgreSQL, Sqlite). Isso permite que vários índices sejam definidos na mesma coluna, acelerando as operações com ordenações diferentes (por exemplo, comparações sensíveis e insensíveis a maiúsculas e minúsculas). Consulte a documentação do provedor de banco de dados para obter mais detalhes.

Tradução de operações internas de cadeia de caracteres do .NET

No .NET, a igualdade de strings é sensível a maiúsculas e minúsculas por padrão: s1 == s2 executa uma comparação ordinal que exige que as strings sejam idênticas. Como a ordenação padrão de bancos de dados varia e, como é desejável que a igualdade simples use índices, o EF Core não tenta traduzir a igualdade simples para uma operação que diferencia maiúsculas de minúsculas do banco de dados: a igualdade em C# é traduzida diretamente para a igualdade de SQL, que pode ou não diferenciar maiúsculas de minúsculas, dependendo do banco de dados específico em uso e de sua configuração de ordenação.

Além disso, o .NET fornece sobrecargas que aceitam uma enumeração StringComparison, permitindo especificar a sensibilidade a maiúsculas e minúsculas e uma cultura para a comparação. Por design, o EF Core se abstém de traduzir essas sobrecargas para o SQL e tentar usá-las resultará em uma exceção. Por um lado, o EF Core não sabe qual ordenação sensível a maiúsculas ou insensível a maiúsculas deve ser usada. Mais importante, a aplicação de uma ordenação evitaria, na maioria dos casos, o uso do índice, afetando significativamente o desempenho de um constructo .NET muito básico e comumente usado. Para forçar uma consulta a usar uma comparação sensível ou insensível a maiúsculas e minúsculas, especifique a ordenação explicitamente por meio de EF.Functions.Collate como detalhado acima.

Recursos adicionais

Informações específicas do banco de dados

• Documentação do SQL Server sobre ordenações.
• Documentação do Microsoft.Data.Sqlite sobre ordenações.
• Documentação do PostgreSQL sobre ordenações.
• Documentação do MySQL sobre ordenações.

Outros recursos

• Reunião da Comunidade de Dados do .NET, apresentando ordenações e explorando aspectos de desempenho e indexação.