Colações e Sensibilidade a Maiúsculas

O processamento de texto em bases de dados pode ser complexo e requer mais atenção do utilizador do que se poderia imaginar. Por um lado, as bases de dados variam consideravelmente na forma como tratam o texto; por exemplo, enquanto algumas bases de dados são sensíveis a maiúsculas minúsculas por defeito (por exemplo, Sqlite, PostgreSQL), outras são insensíveis a maiúsculas minúsculas (SQL Server, MySQL). Além disso, devido ao uso de índices, a sensibilidade a maiúsculas minúsculas e aspetos semelhantes podem ter um impacto profundo no desempenho das consultas: embora possa ser tentador usar string.ToLower para forçar uma comparação insensível a maiúsculas e minúsculas numa base de dados sensível a maiúsculas e minúsculas, isso pode impedir que a sua aplicação utilize índices. Esta página detalha como configurar a sensibilidade a maiúsculas e minúsculas ou, mais genericamente, as colações, e como configurá-las de forma eficiente sem comprometer o desempenho da consulta.

Introdução às colações

Um conceito fundamental no processamento de texto é a colação, que é um conjunto de regras que determinam como os valores do texto são ordenados e comparados para igualdade. Por exemplo, enquanto uma colação insensível a maiúsculas e minúsculas ignora as diferenças entre letras maiúsculas e minúsculas para efeitos de comparação de igualdade, uma colação sensível a maiúsculas não o faz. 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 colações insensíveis a maiúsculas e minúsculas, cada uma com o seu próprio conjunto de regras. O âmbito das colações também vai além da sensibilidade a maiúsculas e minúsculas, abrangendo outros aspetos dos dados de caracteres; em alemão, por exemplo, por vezes (mas nem sempre) é desejável tratar ä e ae como idênticos. Finalmente, as colações também definem como os valores do texto são ordenados: enquanto o alemão coloca ä depois ade , o sueco coloca-o no final do alfabeto.

Todas as operações de texto numa base de dados utilizam uma colação – seja explícita ou implícita – para determinar como a operação se compara e ordena as cadeias de caracteres. A lista efetiva de colações disponíveis e os seus esquemas de nomenclatura é específica para cada base de dados; consulte a secção abaixo para as ligações para páginas de documentação relevantes de várias bases de dados. Felizmente, as bases de dados geralmente permitem definir uma colação padrão ao nível da base de dados ou da coluna, e indicar explicitamente qual a colação que deve ser usada para operações específicas numa consulta.

Agrupamento da base de dados

Na maioria dos sistemas de bases de dados, uma colação por defeito é definida ao nível da base de dados; a menos que seja substituída, essa colação aplica-se implicitamente a todas as operações de texto que ocorrem nessa base de dados. A colação da base de dados é normalmente definida no momento da criação da base de dados (através da CREATE DATABASE instrução DDL) e, se não especificada, assume por padrão um valor ao nível do servidor determinado no momento da configuração. Por exemplo, a colação padrão ao nível do servidor no SQL Server para o idioma de localização da máquina "English (United States)" é SQL_Latin1_General_CP1_CI_AS, que é uma colação insensível a maiúsculas e sensível aos acentos. Embora os sistemas de base de dados normalmente permitam alterar a colação de uma base de dados existente, fazê-lo pode causar complicações; recomenda-se escolher uma colação antes da criação da base de dados.

Ao utilizar migrações EF Core para gerir o esquema da sua base de dados, o seguinte no método do seu modelo OnModelCreating configura uma base de dados SQL Server para usar uma colação sensível a maiúsculas/minúsculas:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Colação de colunas

As colações também podem ser definidas em colunas de texto, substituindo o padrão da base de dados. Isto pode ser útil se certas colunas precisarem ser insensíveis a maiúsculas e minúsculas, enquanto o resto da base de dados tiver de ser sensível a maiúsculas e minúsculas.

Ao utilizar migrações EF Core para gerir o esquema da sua base de dados, o seguinte configura a coluna da Name propriedade para ser insensível a maiúsculas e minúsculas numa base de dados que, de outra forma, está configurada para ser sensível a maiúsculas e minúsculas:

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

Colação explícita numa consulta

Em alguns casos, a mesma coluna precisa ser consultada usando diferentes colações por diferentes consultas. Por exemplo, uma consulta pode necessitar de realizar uma comparação sensível a maiúsculas e minúsculas numa coluna, enquanto outra pode exigir uma comparação insensível a maiúsculas e minúsculas na mesma coluna. Isto pode ser conseguido especificando explicitamente uma colaçã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();

Isto gera uma cláusula COLLATE na consulta SQL, que aplica uma colação sensível à diferença entre maiúsculas e minúsculas, independentemente da colação definida ao nível da coluna ou da base de dados.

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

Colações e índices explícitos

Os índices são um dos fatores mais importantes no desempenho da base de dados – uma consulta que corre eficientemente com um índice pode parar completamente sem esse índice. Os índices herdam implicitamente a colação da sua coluna; isto significa que todas as consultas na coluna são automaticamente elegíveis para usar índices definidos nessa coluna – desde que a consulta não especifique uma colação diferente. Especificar uma colação explícita numa consulta geralmente impede que essa consulta use um índice definido nessa coluna, uma vez que as colações deixariam de coincidir; Por isso, recomenda-se ter cautela ao utilizar esta funcionalidade. É sempre preferível definir a colação ao nível da coluna (ou base de dados), permitindo que todas as consultas usem implicitamente essa colação e beneficiem de qualquer índice.

Note que algumas bases de dados permitem definir a colação ao criar um índice (por exemplo, PostgreSQL, Sqlite). Isto permite que múltiplos índices sejam definidos na mesma coluna, acelerando as operações com diferentes regras de ordenação (por exemplo, comparações que diferenciam maiúsculas de minúsculas e comparações que não diferenciam maiúsculas de minúsculas). Consulte a documentação do fornecedor da sua base de dados para mais detalhes.

Tradução de operações de strings incorporadas em .NET

Em .NET, a igualdade de strings é sensível a maiúsculas e minúsculas por padrão: s1 == s2 realiza uma comparação ordinal que exige que as strings sejam idênticas. Como a colação padrão das bases de dados varia, e como é desejável que a igualdade simples utilize índices, o EF Core não tenta traduzir a igualdade simples para uma operação sensível a maiúsculas e minúsculas no contexto da base de dados: a igualdade em C# é traduzida diretamente para igualdade em SQL, que pode ou não ser sensível a maiúsculas e minúsculas, dependendo da base de dados específica em uso e da sua configuração de colação.

Além disso, o .NET fornece sobrecargas de string.Equals aceitando um StringComparison enum, o que permite especificar a sensibilidade a maiúsculas e minúsculas, e uma cultura para a comparação. Por design, o EF Core abstém-se de traduzir estas sobrecargas para SQL, e tentar usá-las resultará numa exceção. Por um lado, o EF Core não sabe qual colação sensível ou insensível a maiúsculas deve ser usada. Mais importante ainda, aplicar uma colação impediria, na maioria dos casos, o uso do índice, afetando significativamente o desempenho de uma construção .NET muito básica e comum. Para forçar uma consulta a usar diferenciação entre maiúsculas e minúsculas ou sem diferenciação entre maiúsculas e minúsculas, especifique uma colação explicitamente através EF.Functions.Collate, como detalhado acima.

Recursos adicionais

Informações específicas da base de dados

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

Outros recursos

• Sessão do .NET Data Community Standup, apresentando as colações e explorando os aspetos de desempenho e indexação.