Kollationen und Beachtung der Groß- und Kleinschreibung

Die Textverarbeitung in Datenbanken kann komplex sein und erfordert mehr Aufmerksamkeit, als man vermuten würde. Zum einen unterscheiden sich Datenbanken erheblich bei der Handhabung von Text, während bei einigen Datenbanken die Groß-/Kleinschreibung standardmäßig beachtet wird (z. B. Sqlite, PostgreSQL), wird bei anderen Datenbanken die Groß-/Kleinschreibung nicht beachtet (SQL Server, MySQL). Darüber hinaus kann die Verwendung von Indizes und ähnliche Aspekte wie die Groß-/Kleinschreibung weitreichende Auswirkungen auf die Abfrageleistung haben: Während es verlockend sein mag, durch string.ToLower einen fallunempfindlichen Vergleich in einer fallunempfindlichen Datenbank zu erzwingen, kann dies verhindern, dass Ihre Anwendung Indizes verwendet. Auf dieser Seite wird erläutert, wie Sie die Groß- und Kleinschreibung konfigurieren können, oder allgemeinere Zuordnungen effizient durchführen, ohne die Abfrageleistung zu beeinträchtigen.

Einführung in Kollationen

Ein grundlegendes Konzept bei der Textverarbeitung ist die Sortierung, bei der es sich um eine Reihe von Regeln handelt, die bestimmen, wie Textwerte sortiert und für Gleichheit verglichen werden. Zum Beispiel ignoriert eine die Groß- und Kleinschreibung ignorierende Sortierung Unterschiede zwischen Groß- und Kleinbuchstaben bei Gleichheitsvergleichen, während eine die Groß- und Kleinschreibung berücksichtigende Sortierung dies nicht tut. Da die Groß-/Kleinschreibung jedoch kultursensitiv ist (z. B. i und I unterschiedliche Buchstaben in Türkisch darstellen), gibt es mehrere Sortierungen, bei denen die Groß-/Kleinschreibung nicht beachtet wird, jeweils mit eigenen Regeln. Der Umfang der Sortierungen erstreckt sich auch über die Groß-/Kleinschreibung hinaus auf andere Aspekte von Zeichendaten; im Deutschen ist es zum Beispiel manchmal (aber nicht immer) wünschenswert, ä und ae als identisch zu betrachten. Schließlich definieren Sortierungen auch, wie Textwerte sortiert werden: Während Deutsch ä nach a platziert, wird es im Schwedischen am Ende des Alphabets platziert.

Alle Textvorgänge in einer Datenbank verwenden eine Sortierung – ob explizit oder implizit – um zu bestimmen, wie der Vorgang vergleicht und Zeichenfolgen sortiert. Die tatsächliche Liste der verfügbaren Sortierungen und deren Benennungsschemas ist datenbankspezifisch; Links zu den entsprechenden Dokumentationsseiten verschiedener Datenbanken finden Sie im Folgenden . Glücklicherweise erlauben Datenbanken in der Regel, dass eine Standardsortierung auf Datenbank- oder Spaltenebene definiert wird, und explizit angeben, welche Sortierung für bestimmte Vorgänge in einer Abfrage verwendet werden soll.

Datenbanksortierung

In den meisten Datenbanksystemen wird eine Standardsortierung auf Datenbankebene definiert; Sofern diese Sortierung nicht außer Kraft gesetzt wird, gilt diese Sortierung implizit für alle Textvorgänge, die in dieser Datenbank auftreten. Die Datenbanksortierung wird in der Regel zur Erstellungszeit der Datenbank (über die CREATE DATABASE DDL-Anweisung) festgelegt, und wenn nicht angegeben, wird standardmäßig auf einen zum Zeitpunkt der Einrichtung festgelegten Serverebenenwert zurückgegriffen. Die Standardsortierung auf Serverebene in SQL Server für das Gebietsschema "Englisch (USA)" lautet beispielsweise SQL_Latin1_General_CP1_CI_AS, bei dem es sich um eine Sortierung ohne Beachtung der Groß- und Kleinschreibung, jedoch mit Berücksichtigung der Akzentsetzung handelt. Obwohl Datenbanksysteme in der Regel eine Änderung der Sortierung einer vorhandenen Datenbank ermöglichen, kann dies zu Komplikationen führen; es wird empfohlen, vor der Datenbankerstellung eine Sortierung zu wählen.

Bei Verwendung von EF Core-Migrationen zum Verwalten Ihres Datenbankschemas konfiguriert die folgende Methode in Ihrem Modell eine SQL Server-Datenbank für die Verwendung einer groß-/kleinschreibungssensitiven Sortierung.

modelBuilder.UseCollation("SQL_Latin1_General_CP1_CS_AS");

Spaltenkollation

Sortierungen können auch für Textspalten definiert werden, wobei der Datenbankstandard außer Kraft gesetzt wird. Dies kann hilfreich sein, wenn bestimmte Spalten groß-/kleinschreibungsunabhängig sein sollen, während der Rest der Datenbank groß-/kleinschreibungsabhängig bleibt.

Wenn Sie EF Core-Migrationen zum Verwalten Ihres Datenbankschemas verwenden, wird die Spalte für die Eigenschaft Name so konfiguriert, dass sie die Groß-/Kleinschreibung nicht beachtet, auch wenn die Datenbank ansonsten so konfiguriert ist, dass die Groß-/Kleinschreibung beachtet wird.

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

Explizite Sortierung in einer Abfrage

In einigen Fällen muss dieselbe Spalte mit unterschiedlichen Sortierungen von verschiedenen Abfragen abgefragt werden. Eine Abfrage muss beispielsweise einen fallabhängigen Vergleich in einer Spalte durchführen, während eine andere Abfrage einen fallunabhängigen Vergleich in derselben Spalte durchführen muss. Dies kann erreicht werden, indem explizit eine Sortierung innerhalb der Abfrage selbst angegeben wird:

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

Dadurch wird eine COLLATE-Klausel in der SQL-Abfrage generiert, die eine Fall-sensitive Sortierung anwendet, unabhängig von der Sortierung, die auf der Spalten- oder Datenbankebene festgelegt ist.

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

Explizite Kollationen und Indizes

Indizes sind einer der wichtigsten Faktoren bei der Datenbankleistung – eine Abfrage, die effizient mit einem Index ausgeführt wird, kann ohne diesen Index angehalten werden. Indizes erben implizit die Sortierung ihrer Spalte; Dies bedeutet, dass alle Abfragen für die Spalte automatisch berechtigt sind, in dieser Spalte definierte Indizes zu verwenden , vorausgesetzt, die Abfrage gibt keine andere Sortierung an. Die Angabe einer expliziten Sortierung in einer Abfrage verhindert in der Regel, dass diese Abfrage einen in dieser Spalte definierten Index verwendet, da die Sortierungen nicht mehr übereinstimmen würden. Es wird daher empfohlen, bei der Verwendung dieses Features Vorsicht walten zu lassen. Es ist immer vorzuziehen, die Sortierung auf Spaltenebene (oder Datenbankebene) zu definieren, sodass alle Abfragen diese Sortierung implizit verwenden und von jedem Index profitieren können.

Beachten Sie, dass einige Datenbanken die Sortierung beim Erstellen eines Indexes (z. B. PostgreSQL, Sqlite) definieren können. Auf diese Weise können mehrere Indizes für dieselbe Spalte definiert werden, wodurch Vorgänge mit unterschiedlichen Sortierungen beschleunigt werden (z. B. bei Vergleichen zwischen Groß- und Kleinschreibung). Weitere Informationen finden Sie in der Dokumentation Ihres Datenbankanbieters.

Übersetzung integrierter .NET-Zeichenfolgenvorgänge

In .NET wird bei der Zeichenfolgengleichstellung standardmäßig die Groß-/Kleinschreibung beachtet: s1 == s2 Führt einen Ordinalvergleich aus, bei dem die Zeichenfolgen identisch sein müssen. Da die Standardsortierung von Datenbanken unterschiedlich ist und es wünschenswert ist, für einfache Gleichheitsabfragen Indizes zu verwenden, versucht EF Core nicht, einfache Gleichheitsvergleiche in datenbankspezifische Operationen mit Groß-/Kleinschreibungsunterscheidung zu übersetzen: C#-Gleichheit wird direkt in SQL-Gleichheit übersetzt, die je nach verwendeter Datenbank und deren Sortierungskonfiguration die Groß-/Kleinschreibung berücksichtigen kann oder nicht.

Darüber hinaus bietet .NET Überladungen für string.Equals, die ein StringComparison-Enum akzeptieren, sodass die Groß-/Kleinschreibung und eine Kultureinstellung für den Vergleich angegeben werden können. Standardmäßig unterlässt EF Core die Übersetzung dieser Überladungen in SQL, und der Versuch, sie zu verwenden, führt zu einer Ausnahme. EF Core weiß zum einen nicht, welche groß- oder kleinschreibungssensitive Kollation verwendet werden sollte. Wichtiger ist, dass das Anwenden einer Sortierung in den meisten Fällen die Indexverwendung verhindert und die Leistung für ein sehr einfaches und häufig verwendete .NET-Konstrukt erheblich beeinträchtigt. Wenn sie erzwingen möchten, dass bei einer Abfrage zwischen Groß- und Kleinschreibung unterschieden wird, geben Sie eine Sortierung explizit über EF.Functions.Collatedie oben beschriebene Beschreibung an.

Weitere Ressourcen

Datenbankspezifische Informationen

• SQL Server-Dokumentation zu Sortierungen.
• Microsoft.Data.Sqlite-Dokumentation zu Sortierungen.
• PostgreSQL-Dokumentation zu Sortierungen.
• MySQL-Dokumentation zu Sortierungen.

Weitere Ressourcen

• .NET Data Community Standup-Sitzung, Einführung von Sortierungen und Untersuchung der Aspekte von Performance und Indizierung.