照合順序と大文字と小文字の区別Collations and Case Sensitivity

注意

この機能は EF Core 5.0 で導入されています。This feature is introduced in EF Core 5.0.

データベースでのテキスト処理は複雑になる可能性があり、それには問題があると考えられるより多くのユーザーの注意が必要です。Text processing in databases can be a complex, and requires more user attention that one would suspect. 1つの点として、データベースはテキストの処理方法に大きく異なります。たとえば、一部のデータベースでは既定で大文字と小文字が区別されますが (例: Sqlite, PostgreSQL)、他のデータベースでは大文字と小文字が区別されません (SQL Server、MySQL)。For one thing, databases vary considerably in how they handle text; for example, while some databases are case-sensitive by default (e.g. Sqlite, PostgreSQL), others are case-insensitive (SQL Server, MySQL). また、インデックスの使用により、大文字と小文字の区別や類似した側面がクエリのパフォーマンスに大きく影響する可能性があります。これにより、大文字と小文字を区別するデータベースで大文字と小文字を区別し string.Lower ない比較を強制的に実行でき、アプリケーションでインデックスを使用できなくなる可能性があります。In addition, because of index usage, case-sensitivity and similar aspects can have a far-reaching impact on query performance: while it may be tempting to use string.Lower to force a case-insensitive comparison in a case-sensitive database, doing so may prevent your application from using indexes. このページでは、大文字と小文字の区別を構成する方法、一般的な照合順序、およびクエリのパフォーマンスを低下させずに効率的に行う方法について詳しく説明します。This page details how to configure case sensitivity, or more generally, collations, and how to do so in an efficient way without compromising query performance.

照合順序の概要Introduction to collations

テキスト処理の基本的な概念は 照合順序です。これは、テキスト値の順序と等価性を比較する方法を決定する一連のルールです。A fundamental concept in text processing is the collation, which is a set of rules determining how text values are ordered and compared for equality. たとえば、大文字と小文字を区別しない照合順序では、等値比較の目的で大文字と小文字の違いが無視されますが、大文字と小文字を区別する照合順序では区別されません。For example, while a case-insensitive collation disregards differences between upper- and lower-case letters for the purposes of equality comparison, a case-sensitive collation does not. ただし、大文字と小文字の区別はカルチャに依存しているため (たとえば i 、と I はトルコ語で異なる文字を表します)、大文字と小文字を区別しない照合順序が複数存在し、それぞれに独自のルールセットがあります。However, since case-sensitivity is culture-sensitive (e.g. i and I represent different letter in Turkish), there exist multiple case-insensitive collations, each with its own set of rules. 照合順序のスコープは、文字データのその他の側面にも大文字と小文字の区別を加えたものです。たとえば、ドイツ語では、 ä とを ae 同一として扱うことが望ましい場合があります (常にではありません)。The scope of collations also extends beyond case-sensitivity, to other aspects of character data; in German, for example, it is sometimes (but not always) desirable to treat ä and ae as identical. 最後に、照合順序では、テキスト値の 順序付け方法も定義されています。の後、ドイツ語の ä a 場合、スウェーデン語はアルファベットの最後に配置されます。Finally, collations also define how text values are ordered: while German places ä after a, Swedish places it at the end of the alphabet.

データベース内のすべてのテキスト操作では、照合順序を使用して、明示的にも暗黙的にも、操作で文字列を比較および順序付けする方法を決定します。All text operations in a database use a collation - whether explicitly or implicitly - to determine how the operation compares and orders strings. 使用可能な照合順序とその名前付けスキームの実際の一覧は、データベースに固有のものです。さまざまなデータベースの関連ドキュメントページへのリンクについて は、以下のセクション を参照してください。The actual list of available collations and their naming schemes is database-specific; consult the section below for links to relevant documentation pages of various databases. データベースでは、通常、データベースレベルまたは列レベルで既定の照合順序を定義したり、クエリの特定の操作に使用する照合順序を明示的に指定したりすることができます。Fortunately, database do generally allow a default collation to be defined at the database or column level, and to explicitly specify which collation should be use for specific operations in a query.

データベース照合順序Database collation

ほとんどのデータベースシステムでは、既定の照合順序はデータベースレベルで定義されています。オーバーライドしない限り、その照合順序は、そのデータベース内で発生するすべてのテキスト操作に暗黙的に適用されます。In most database systems, a default collation is defined at the database level; unless overridden, that collation implicitly applies to all text operations occurring within that database. データベースの照合順序は、通常、データベースの作成時に (DDL ステートメントを使用して CREATE DATABASE ) 設定します。指定しない場合、既定では、セットアップ時に決定されるサーバーレベルの値が既定値に設定されます。The database collation is typically set at database creation time (via the CREATE DATABASE DDL statement), and if not specified, defaults to a some server-level value determined at setup time. たとえば、SQL Server の既定のサーバーレベルの照合順序は SQL_Latin1_General_CP1_CI_AS 、大文字と小文字を区別しない、アクセントを区別する照合順序であるです。For example, the default server-level collation in SQL Server is SQL_Latin1_General_CP1_CI_AS, which is a case-insensitive, accent-sensitive collation. データベースシステムでは、通常、既存のデータベースの照合順序の変更が許可されますが、そのためには複雑になる可能性があります。データベースを作成する前に照合順序を選択することをお勧めします。Although database systems usually do permit altering the collation of an existing database, doing so can lead to complications; it is recommended to pick a collation before database creation.

EF Core 移行を使用してデータベーススキーマを管理する場合、モデルのメソッドの次の例では、 OnModelCreating 大文字と小文字を区別する照合順序を使用するように SQL Server データベースを構成します。When using EF Core migrations to manage your database schema, the following in your model's OnModelCreating method configures a SQL Server database to use a case-sensitive collation:

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

列の照合順序Column collation

また、テキスト列に対して照合順序を定義して、データベースの既定値を上書きすることもできます。Collations can also be defined on text columns, overriding the database default. これは、特定の列で大文字と小文字を区別する必要があり、データベースの残りの部分で大文字と小文字を区別する必要がある場合に便利です。This can be useful if certain columns need to be case-insensitive, while the rest of the database needs to be case-sensitive.

EF Core 移行を使用してデータベーススキーマを管理する場合、次の例では、 Name 大文字と小文字を区別するように構成されているデータベースで、プロパティの列が大文字と小文字を区別しないように構成されています。When using EF Core migrations to manage your database schema, the following configures the column for the Name property to be case-insensitive in a database that is otherwise configured to be case-sensitive:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

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

クエリ内の明示的な照合順序Explicit collation in a query

場合によっては、クエリごとに異なる照合順序を使用して同じ列を照会する必要があります。In some cases, the same column needs to be queried using different collations by different queries. たとえば、あるクエリでは、列に対して大文字と小文字を区別した比較を実行する必要がありますが、同じ列で大文字と小文字を区別しない比較を実行することが必要になる場合があります。For example, one query may need to perform a case-sensitive comparison on a column, while another may need to perform a case-insensitive comparison on the same column. これは、クエリ内で照合順序を明示的に指定することで実現できます。This can be accomplished by explicitly specifying a collation within the query itself:

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

これに COLLATE より、列レベルまたはデータベースレベルで定義されている照合順序に関係なく、大文字と小文字を区別する照合順序を適用する句が SQL クエリに生成されます。This generates a COLLATE clause in the SQL query, which applies a case-sensitive collation regardless of the collation defined at the column or database level:

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

明示的な照合順序とインデックスExplicit collations and indexes

インデックスは、データベースのパフォーマンスにおいて最も重要な要素の1つです。インデックスを使用して効率的に実行されるクエリは、そのインデックスを使用せずに停止をグリッドことがあります。Indexes are one of the most important factors in database performance - a query that runs efficiently with an index can grind to a halt without that index. インデックスは、列の照合順序を暗黙的に継承します。つまり、クエリで異なる照合順序が指定されていない場合、列に対するすべてのクエリが、その列に定義されているインデックスを使用できるようになります。Indexes implicitly inherit the collation of their column; this means that all queries on the column are automatically eligible to use indexes defined on that column - provided that the query doesn't specify a different collation. クエリで明示的な照合順序を指定すると、照合順序が一致しなくなるため、通常はそのクエリでその列に定義されているインデックスを使用できなくなります。このため、この機能を使用する場合は注意が必要です。Specifying an explicit collation in a query will generally prevent that query from using an index defined on that column, since the collations would no longer match; it is therefore recommended to exercise caution when using this feature. 常に、列 (またはデータベース) レベルで照合順序を定義することをお勧めします。これにより、すべてのクエリが暗黙的にその照合順序を使用し、任意のインデックスの恩恵を受けることができます。It is always preferable to define the collation at the column (or database) level, allowing all queries to implicitly use that collation and benefit from any index.

一部のデータベースでは、インデックスの作成時に照合順序を定義できることに注意してください (PostgreSQL、Sqlite など)。Note that some databases allow the collation to be defined when creating an index (e.g. PostgreSQL, Sqlite). これにより、複数のインデックスが同じ列に定義され、異なる照合順序で操作が高速化されます (大文字と小文字を区別し、大文字と小文字を区別しない比較など)。This allows multiple indexes to be defined on the same column, speeding up operations with different collations (e.g. both case-sensitive and case-insensitive comparisons). 詳細については、データベースプロバイダーのドキュメントを参照してください。Consult your database provider's documentation for more details.

警告

クエリのクエリプランを常に検査し、大量のデータに対して実行されるパフォーマンスに重要なクエリで適切なインデックスが使用されていることを確認してください。Always inspect the query plans of your queries, and make sure the proper indexes are being used in performance-critical queries executing over large amounts of data. (またはを呼び出して) クエリの大文字と小文字の区別をオーバーライド EF.Functions.Collate string.ToLower すると、アプリケーションのパフォーマンスに非常に大きな影響を与える可能性があります。Overriding case-sensitivity in a query via EF.Functions.Collate (or by calling string.ToLower) can have a very significant impact on your application's performance.

組み込みの .NET 文字列操作の変換Translation of built-in .NET string operations

.NET では、文字列の等価性が既定で大文字と小文字が区別されます。では s1 == s2 、文字列が同一であることを必要とする序数の比較が実行されます。In .NET, string equality is case-sensitive by default: s1 == s2 performs an ordinal comparison that requires the strings to be identical. データベースの既定の照合順序は異なるため、インデックスを使用するには単純な等価性が必要であるため、EF Core はデータベースの大文字と小文字を区別する操作に単純な等価性を変換しようとしません。 C# の等値は、使用されている特定のデータベースとその照合順序の構成に応じて、大文字と小Because the default collation of databases varies, and because it is desirable for simple equality to use indexes, EF Core makes no attempt to translate simple equality to a database case-sensitive operation: C# equality is translated directly to SQL equality, which may or may not be case-sensitive, depending on the specific database in use and its collation configuration.

さらに、.NET は string.Equals 列挙型を受け入れるオーバーロードを提供し StringComparison ます。これにより、比較に大文字と小文字の区別とカルチャを指定できます。In addition, .NET provides overloads of string.Equals accepting a StringComparison enum, which allows specifying case-sensitivity and culture for the comparison. 仕様により、これらのオーバーロードを SQL に変換することによって EF Core し、それらを使用しようとすると、例外が発生します。By design, EF Core refrains from translating these overloads to SQL, and attempting to use them will result in an exception. 1つの点として、EF Core では、大文字と小文字を区別しない照合順序を使用する必要があります。For one thing, EF Core does know not which case-sensitive or case-insensitive collation should be used. さらに重要なこととして、照合順序を適用すると、ほとんどの場合、インデックスの使用が妨げられ、非常に基本的で一般的に使用される .NET コンストラクトのパフォーマンスに大きく影響します。More importantly, applying a collation would in most cases prevent index usage, significantly impacting performance for a very basic and commonly-used .NET construct. クエリで大文字と小文字を区別するか、大文字と小文字を区別しない比較を使用するには、前述のようにを使用して明示的に照合順序を指定し EF.Functions.Collate ますdetailed aboveTo force a query to use case-sensitive or case-insensitive comparison, specify a collation explicitly via EF.Functions.Collate as detailed above.

データベース固有の情報Database-specific information