照合順序とケース感度

データベースでのテキスト処理は複雑であり、予想以上にユーザーの注意を要します。 一つには、データベースはテキストの処理方法に大きく異なります。たとえば、既定では大文字と小文字が区別されるデータベース (Sqlite、PostgreSQL など) もあれば、大文字と小文字が区別されないデータベースもあります (SQL Server、MySQL)。 さらに、インデックスの使用により、大文字小文字の区別やその他の類似する側面がクエリのパフォーマンスに広範に影響を及ぼす可能性があります。string.ToLower を使用してケースセンシティブなデータベースで大文字小文字の区別をしない比較を強制することは魅力的ですが、それによりアプリケーションがインデックスを使用できなくなることがあります。 このページでは、大文字と小文字の区別(より一般的には照合順序)を構成する方法と、クエリのパフォーマンスを損なうことなく効率的に構成する方法について詳しく説明します。

照合順序の概要

テキスト処理の基本的な概念は 照合順序です。照合順序は、テキスト値の順序付けと等価性の比較方法を決定する一連のルールです。 たとえば、大文字と小文字を区別しない照合順序では、等価比較の目的で大文字と小文字の違いが無視されますが、大文字と小文字を区別する照合順序では違いが考慮されます。 ただし、大文字と小文字の区別はカルチャに依存するため (たとえば、 i や I はトルコ語で異なる文字を表します)、大文字と小文字を区別しない照合順序が複数存在し、それぞれに独自の規則のセットがあります。 照合順序の範囲は、大文字と小文字の区別を超えて、文字データの他の側面まで拡張されます。たとえば、ドイツ語では、 ä と ae を同一として扱うのが望ましい場合があります (ただし、常にではありません)。 最後に、照合順序ではテキスト値の順序も定義されます。ドイツ語ではäの後にaされますが、スウェーデン語ではアルファベットの末尾に配置されます。

データベース内のすべてのテキスト操作では、照合順序 (明示的または暗黙的) を使用して、操作による文字列の比較と順序付け方法を決定します。 使用可能な照合順序とその名前付けスキームの実際の一覧はデータベース固有です。さまざまなデータベースの関連ドキュメント ページへのリンクについては、 以下のセクション を参照してください。 さいわい、データベースでは通常、既定の照合順序をデータベースレベルまたは列レベルで定義し、クエリ内の特定の操作に使用する照合順序を明示的に指定できます。

データベースの照合順序

ほとんどのデータベース システムでは、既定の照合順序がデータベース レベルで定義されます。オーバーライドされない限り、その照合順序は、そのデータベース内で発生するすべてのテキスト操作に暗黙的に適用されます。 データベースの照合順序は通常、データベースの作成時に ( CREATE DATABASE DDL ステートメントを使用して) 設定されます。指定しない場合、既定では、セットアップ時に決定されるサーバー レベルの値が設定されます。 たとえば、"英語 (米国)" コンピューターロケールのSQL Serverにおける既定のサーバーレベルの照合順序はSQL_Latin1_General_CP1_CI_ASです。これは大文字小文字を区別せず、アクセントを区別する照合順序です。 通常、データベース システムでは既存のデータベースの照合順序を変更できますが、これを行うと複雑になる可能性があります。データベースを作成する前に照合順序を選択することをお勧めします。

EF Core 移行を使用してデータベース スキーマを管理する場合、モデルの OnModelCreating メソッドで、大文字と小文字を区別する照合順序を使用するように SQL Server データベースを構成します。

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

列の照合順序

照合順序はテキスト列で定義することもでき、データベースの既定値をオーバーライドします。 これは、特定の列で大文字と小文字を区別しない必要がある一方で、データベースの残りの部分では大文字と小文字を区別する必要がある場合に便利です。

EF Core 移行を使用してデータベース スキーマを管理する場合、次の例では、通常大文字と小文字が区別されるように構成されているデータベースにおいて、Name プロパティの列を大文字と小文字を区別しないように設定します。

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

クエリでの明示的な照合順序

場合によっては、異なるクエリによって異なる照合順序を使用して同じ列を照会する必要があります。 たとえば、1 つのクエリで列で大文字と小文字を区別する比較を実行する必要がある場合と、同じ列で大文字と小文字を区別しない比較を実行する必要があるクエリがあります。 これは、クエリ自体内で照合順序を明示的に指定することで実現できます。

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

これにより、SQL クエリに COLLATE 句が生成されます。これにより、列レベルまたはデータベース レベルで定義されている照合順序に関係なく、大文字と小文字が区別される照合順序が適用されます。

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

明示的な照合順序とインデックス

インデックスは、データベース パフォーマンスにおいて最も重要な要因の 1 つです。インデックスを使用して効率的に実行されるクエリは、そのインデックスなしで停止する可能性があります。 インデックスは、その列の照合順序を暗黙的に継承します。つまり、クエリで別の照合順序が指定されていない場合、列のすべてのクエリは、その列に定義されているインデックスを自動的に使用できます。 クエリで明示的な照合順序を指定すると、照合順序が一致しなくなったため、通常、そのクエリではその列に定義されているインデックスを使用できなくなります。そのため、この機能を使用する場合は注意を払うことをお勧めします。 常に、列 (またはデータベース) レベルで照合順序を定義することをお勧めします。これにより、すべてのクエリでその照合順序を暗黙的に使用し、任意のインデックスからメリットを得られます。

一部のデータベースでは、インデックスの作成時に照合順序を定義できます (PostgreSQL、Sqlite など)。 これにより、同じ列に複数のインデックスを定義できるため、照合順序が異なる操作が高速化されます (大文字と小文字を区別する比較と大文字と小文字を区別しない比較など)。 詳細については、データベース プロバイダーのドキュメントを参照してください。

組み込みの .NET 文字列操作の変換

.NET では、文字列の等価性は既定で大文字と小文字が区別されます。 s1 == s2 は、文字列を同一にする必要がある序数比較を実行します。 データベースの既定の照合順序は異なるため、単純な等価性でインデックスを使用することが望ましいため、EF Core では、単純な等価性をデータベースの大文字と小文字を区別する操作に変換しません。C# の等価性は SQL の等価性に直接変換されます。これは、使用中の特定のデータベースとその照合順序の構成によっては、大文字と小文字が区別される場合とそうでない場合があります。

さらに、.NET では、string.Equals列挙型を受け入れるStringComparisonのオーバーロードが提供されます。これにより、比較の大文字と小文字の区別とカルチャを指定できます。 設計上、EF Core はこれらのオーバーロードを SQL に変換することを控え、それらを使用しようとすると例外が発生します。 EF Core は、どのケースセンシティブまたはケースインセンシティブな照合順序を使用すべきか認識していません。 さらに重要なのは、照合順序を適用すると、ほとんどの場合、インデックスの使用が妨てられ、非常に基本的で一般的に使用される .NET コンストラクトのパフォーマンスに大きな影響を与えます。 クエリで大文字と小文字を区別する比較または大文字と小文字を区別しない比較を強制的に使用するには、EF.Functions.Collateのようにを使用して照合順序を明示的に指定します。

その他のリソース

データベース固有の情報

• 照合順序に関する SQL Server のドキュメント。
• 照合順序に関する Microsoft.Data.Sqlite のドキュメント。
• 照合順序に関する PostgreSQL ドキュメント。
• 照合順序に関する MySQL ドキュメント。

その他のリソース

• .NET Data Community Standup セッション。照合順序について導入し、パフォーマンスとインデックス作成の側面を調べます。