Classements et respect de la casse

Le traitement du texte dans les bases de données peut être complexe et nécessite plus d’attention de la part des utilisateurs qu’on ne pourrait le penser. D’un côté, les bases de données varient considérablement dans la façon dont elles gèrent le texte; par exemple. Si certaines bases de données respectent la casse par défaut (par exemple, Sqlite, PostgreSQL), d’autres ne le font pas (SQL Server, MySQL). En outre, en raison de l’utilisation d’index, le respect de la casse et des élément similaires peuvent avoir un impact considérable sur les performances des requêtes : alors qu’il peut être tentant d’utiliser string.ToLower pour forcer une comparaison ne respectant pas la casse dans une base de données respectant la casse, cela peut empêcher votre application d’utiliser des index. Cette page explique comment configurer le respect de la casse, ou plus généralement, les classements, et comment le faire de manière efficace sans compromettre les performances des requêtes.

Présentation des classements

Un concept fondamental dans le traitement du texte est le classement, qui est un ensemble de règles déterminant la façon dont les valeurs texte sont ordonnées et comparées pour l’égalité. Par exemple, alors qu’un classement ne respectant pas la casse ignore les différences entre les lettres majuscules et minuscules à des fins de comparaison d’égalité, un classement respectant la casse ne le fait pas. Toutefois, étant donné que le respect de la casse dépend de la culture (par exemple i et I représentent différentes lettres en turc), il existe plusieurs classements ne respectant pas la casse, chacun avec son propre ensemble de règles. L’étendue des classements s’étend également au-delà du respect de la casse, à d’autres aspects des données caractère. En allemand, par exemple, il est parfois (mais pas toujours) souhaitable de traiter ä et ae comme identiques. Enfin, les classements définissent également la façon dont les valeurs de texte sont classées: si l’allemand place ä après a, le suédois le place à la fin de l’alphabet.

Toutes les opérations texte d’une base de données utilisent un classement ( explicitement ou implicitement) pour déterminer la façon dont l’opération compare et ordonne des chaînes. La liste effective des classements disponibles et de leurs schémas d’affectation de noms est spécifique à la base de données ; consultez la section ci-dessous pour obtenir des liens vers les pages de documentation pertinentes de différentes bases de données. Heureusement, les bases de données autorisent généralement la définition d’un classement par défaut au niveau de la base de données ou de la colonne, et spécifient explicitement quel classement doit être utilisé pour des opérations spécifiques dans une requête.

Classement de base de données

Dans la plupart des systèmes de base de données, un classement par défaut est défini au niveau de la base de données. Sauf en cas de remplacement, ce classement s’applique implicitement à toutes les opérations texte qui se produisent dans cette base de données. Le classement de base de données est généralement défini au moment de la création de la base de données (via l’instruction DDL CREATE DATABASE) et, si elle n’est pas spécifiée, la valeur par défaut est déterminée au niveau du serveur au moment de l’installation. Par exemple, le classement au niveau du serveur par défaut dans SQL Server pour les paramètres régionaux de l’ordinateur « Anglais (États-Unis) » est SQL_Latin1_General_CP1_CI_AS, qui est un classement respectant la casse et les accents. Bien que les systèmes de base de données autorisent généralement la modification du classement d’une base de données existante, cela peut entraîner des complications. Nous vous recommandons de choisir un classement avant la création d’une base de données.

Lorsque vous utilisez des migrations EF Core pour gérer votre schéma de base de données, la commande suivante dans la méthode OnModelCreating de votre modèle configure une base de données SQL Server pour qu’elle utilise un classement respectant la casse :

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Classement par colonnes

Les classements peuvent également être définis sur les colonnes de texte, remplaçant le fonctionnement de la base de données par défaut. Cela peut être utile si certaines colonnes ne doivent pas respecter la casse, tandis que le reste de la base de données doit la respecter.

Lorsque vous utilisez des migrations EF Core pour gérer votre schéma de base de données, la commande suivante la colonne pour que la propriété Name ne respecte pas la casse dans une base de données qui est configurée pour utiliser un classement respectant la casse :

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

Classement explicite dans une requête

Dans certains cas, la même colonne doit être interrogée avec des classements différents par différentes requêtes. Par exemple, une requête peut avoir besoin d’effectuer une comparaison respectant la casse sur une colonne, tandis qu’une autre peut avoir besoin d’effectuer une comparaison ne respectant pas la casse sur la même colonne. Pour ce faire, spécifiez explicitement un classement dans la requête elle-même :

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

Cela génère une clause COLLATE dans la requête SQL, qui applique un classement respectant la casse, quel que soit le classement défini au niveau de la colonne ou de la base de données :

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

Classements et index explicites

Les index sont l’un des facteurs les plus importants des performances des bases de données. Une requête qui s’exécute efficacement avec un index peut se bloquer sans cet index. Les index héritent implicitement du classement de leur colonne. Cela signifie que toutes les requêtes sur la colonne sont automatiquement éligibles à l’utilisation des index définis sur cette colonne, à condition que la requête ne spécifie pas un classement différent. Spécifier un classement explicite dans une requête empêche généralement cette requête d’utiliser un index défini sur cette colonne, car les classements ne correspondent plus. Il est donc recommandé d’agir avec prudence lors de l’utilisation de cette fonctionnalité. Il est toujours préférable de définir le classement au niveau de la colonne (ou de la base de données), ce qui permet à toutes les requêtes d’utiliser implicitement ce classement et de tirer parti de tous les index.

Notez que certaines bases de données permettent de définir le classement lors de la création d’un index (par exemple PostgreSQL, Sqlite). Cela permet à plusieurs index d’être définis sur la même colonne, accélérant ainsi les opérations avec différents classements (par exemple, les comparaisons respectant la casse et celles ne respectant pas la casse). Pour plus de détails, consultez la documentation de votre fournisseur de base de données.

Avertissement

Inspectez toujours les plans de requête de vos requêtes et assurez-vous que les index appropriés sont utilisés dans les requêtes critiques en termes de performances, s’exécutant sur de grandes quantités de données. Le remplacement du respect de la casse dans une requête via EF.Functions.Collate (ou en appelant string.ToLower) peut avoir un impact très significatif sur les performances de votre application.

Traduction des opérations de chaîne .NET intégrées

Dans .NET, l’égalité des chaînes respecte la casse par défaut : s1 == s2 effectue une comparaison ordinale qui nécessite que les chaînes soient identiques. Étant donné que le classement par défaut des bases de données varie et qu’il est souhaitable que l’égalité simple utilise des index, EF Core n’essaie pas de traduire l’égalité simple en opération respectant la casse de base de données : l’égalité C# est traduite directement en égalité SQL, qui peut respecter la casse ou non, selon la base de données spécifique utilisée et la configuration de son classement.

En outre, .NET fournit des surcharges de string.Equals acceptant une énumération StringComparison, ce qui permet de spécifier le respect de la casse et une culture pour la comparaison. Par conception, EF Core s’abstient de traduire ces surcharges en SQL et tenter de les utiliser entraîne une exception. D’un côté, EF Core ne sait pas quel classement respectant la casse ou ne respectant pas la casse doit être utilisé. Plus important encore, appliquer un classement empêcherait dans la plupart des cas l’utilisation des index, ce qui a un impact significatif sur les performances d’une construction .NET très simple et couramment utilisée. Pour forcer une requête à utiliser une comparaison respectant la casse ou ne respectant pas la casse, spécifiez un classement explicitement via EF.Functions.Collate comme expliqué ci-dessus.

Ressources supplémentaires

Informations spécifiques à la base de données

• Documentation SQL Server sur les classements.
• Documentation Microsoft.Data.Sqlite sur les classements.
• Documentation PostgreSQL sur les classements.
• Documentation MySQL sur les classements.

Autres ressources

• Session de standup de la communauté des données .NET, présentation des classements et exploration des aspects liés aux performances et à l’indexation.