Implementieren der SQL Merge-Funktionalität in Entity Framework Core

Im vorangegangenen Artikel Extending Entity Framework Core haben wir uns mit der Erweiterung von Entity Framework Core (EF core) befasst, um automatisch benutzerdefinierte Anweisungen zu generieren. Wir schlossen den vorherigen Artikel mit einer grundlegenden Lösung ab, auf der Sie aufbauen können, um Ihren eigenen benutzerdefinierten Code zu erstellen, der generiert werden soll. In diesem Artikel werden wir eine SQL-Merge-Anweisung in EF Core implementieren. Indem Sie EF core nutzen, können Sie es vermeiden, den SQL-Merge-Code für jedes Objekt zu schreiben, für das Sie ihn verwenden möchten. Die SQL-Merge-Anweisung ist eine Alternative zur Insert-, Update- und Delete-Anweisung, wenn Sie mit großen Datensätzen arbeiten. In Kombination mit EF core kann die SQL-Merge-Anweisung die Anzahl der erforderlichen Roundtrips zur Datenbank auf ein Minimum reduzieren.

SQL-Zusammenführung

Die SQL-Merge-Anweisung wird verwendet, um zwei verschiedene Datensätze effektiv zu synchronisieren. Ein Beispiel hierfür ist das Data Warehousing, bei dem häufig Aktualisierungen größerer Datensätze vorgenommen werden. Mit der SQL-Merge-Anweisung kann eine Zieltabelle mit einer Quelltabelle synchronisiert werden. Die Stärke der Merge-Anweisung besteht darin, dass sie die Anweisungen Insert, Update und Delete in einer einzigen atomaren Anweisung kombiniert. Auf diese Weise ist es einfacher, die Integrität und Konsistenz der Daten zu wahren. Die Merge-Anweisung besteht aus den folgenden Elementen:

• Ziel- und Quelltabelle
• Bedingung, nach der die Tabellen verknüpft werden sollen (z.B. Primärschlüssel)
• Die auszuführende Aktion (Einfügen, Aktualisieren, Löschen)

Die Ziel- und Quelltabellen können SQL-Tabellen oder z.B. ein Tabellentyp sein. Die Kombination aus einer Tabelle und einem Tabellentyp ist das, was wir in diesem Beispiel verwenden werden. Der Tabellentyp, den wir deklarieren müssen, ist quasi eine Kopie der Tabelle in der Datenbank. Er enthält eine zusätzliche Spalte, die für die mögliche Löschung von Daten verwendet wird. Die nächsten Elemente sind die Bedingung und die zu ergreifende Aktion.

MERGE INTO TargetTable AS Target
USING SourceTable AS Source
ON Target.Id = Source.Id
-- Update existing rows
WHEN MATCHED AND Source.MustDelete = 0 THEN
UPDATE SET
Target.Counter = Source.Counter,
Target.CreationDate = Source.CreationDate
-- Delete rows from target if MustDelete is true
WHEN MATCHED AND Source.MustDelete = 1 THEN
DELETE
-- Insert new rows from source to target
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Counter, CreationDate)
VALUES (Source.Id, Source.Counter, Source.CreationDate);

Dieses Beispiel zeigt die folgende Bedingung: Auf Target.Id = Source.Id. Dies ist die Bedingung, anhand derer die Merge-Anweisung versucht, die Daten abzugleichen. In diesem Fall ist es der Primärschlüssel. Der nächste Teil des Codes sind die Aktionen. Diese bestehen aus When Matched (Potential additional condition) und When Not Matched. Sie sehen, dass der gefundene Datensatz aktualisiert wird, wenn die ID abgeglichen werden kann und die zusätzliche Bedingung Muss löschen falsch ist. Wenn die Bedingung wahr ist, wird die Zeile aus der Zieltabelle gelöscht. Die letzte Aktion ist das Einfügen eines Datensatzes, wenn die Bedingung nicht erfüllt werden kann. Um sicherzustellen, dass die Merge-Anweisung zusammen mit EF Core verwendet werden kann, wird auch ihre Ausgabe benötigt. Die Ausgabe enthält mögliche erstellte IDs oder andere von der Datenbank generierte Felder. Dies können Sie mit der Anweisung OUTPUT in SQL erreichen.

-- Capture the output of the merge operation
OUTPUT
$action,
INSERTED.Id AS NewId,
DELETED.Id AS OldId,
INSERTED.Counter,
INSERTED.CreationDate;

Das Ausgabebeispiel enthält die eingefügten Felder, die potenzielle Löschung und ein $action. $action ist ein Feld, das aus der Merge-Anweisung resultiert und uns sagt, ob es sich um eine Einfügung, Aktualisierung oder Löschung handelt. Dies kann für die Logik in Ihrer Ausgabeanweisung nützlich sein. Ich ziehe es vor, hier zusätzliche Logik zu vermeiden und den C#-Code jede zusätzliche Logik auflösen zu lassen, die ich ausführen möchte. Eine Merge-Anweisung hat nun einige Vorteile. Die einzelne atomare Aktion ist in Kombination mit EF Core ein noch größerer Vorteil, da Sie nur einen einzigen Round Trip zur Datenbank benötigen. Dies führt zu einer noch besseren Leistungssteigerung. Mit jedem Vorteil kommt ein Nachteil. Die Zusammenführung ist nicht nur vorteilhaft. Ein großes Risiko bei der Verwendung großer Datensätze ist das Sperren der Tabelle. Insbesondere wenn die Join-Bedingung nicht richtig indiziert ist, kann die Leistung stark leiden, was wiederum das Risiko von Deadlocks erhöht. Ein weiteres Problem ist die Portabilität. Die Syntax und die Arbeitsweise von Merge-Anweisungen unterscheiden sich in der Regel zwischen verschiedenen Datenbanktypen.

Die Implementierung der Merge-Anweisung in EF Core.

Um eine Merge-Anweisung in EF COre zu implementieren. Die folgenden Klassen müssen angepasst werden: CSharpMergeMigrationOperationGenerator und die SQLServerMergeMigrationSQLGenerator. Die CSharpMergeMigrationOperationGenerator ist für die Migrationsdatei zuständig. Die SQLServerMergeMigrationSQLGenerator ist für das SQL-Skript verantwortlich. Im vorigen Artikel wurde bereits das Grundgerüst für diese Generatoren geliefert, dies wird nun weiter ausgebaut. Die Schwierigkeit liegt hauptsächlich im Migrationsgenerator. Der Migrationsgenerator erstellt die Datei migration.cs und sollte die Merge-Anweisungen mit allen notwendigen Informationen enthalten. Zum Beispiel Spaltennamen und Tabellennamen. Das bedeutet, dass der Migrationscode die Anweisung zum Erstellen der Spalten mit ihren spezifischen Informationen enthalten sollte. Um dies zu erreichen, kann eine Funktion verwendet werden. Die Funktion enthält als Ausgabe einen Spaltenersteller und als Eingabe die Spalteninformationen.

Func<ColumnsBuilder, TColumn> columns

In der Migrationsdatei sieht das dann wie folgt aus:

migrationBuilder.CreateMerge(
name: "Forecasts",
columns: table => new {
Id = table.Column<system.guid>(type: "uniqueidentifier", nullable: false),
Date = table.Column<system.datetime>(type: "datetime2", nullable: false),
Summary = table.Column<system.string>(type: "nvarchar(max)", nullable: false),
TemperatureC = table.Column<system.int32>(type: "int", nullable: false)
});</system.int32></system.string></system.datetime></system.guid>

Die migrationBuilder-Erweiterungsmethode 'CreateMerge' wird wie folgt aussehen:

public static OperationBuilder<createmergeoperation> CreateMerge<tcolumns>(
this MigrationBuilder migrationBuilder,
string name,
Func<ColumnsBuilder, TColumns> columns)
{
var operation = new CreateMergeOperation(name, new List<addcolumnoperation>());
var builder = new ColumnsBuilder(operation);
var columnsObject = columns(builder);
foreach (var property in typeof(TColumns).GetTypeInfo().DeclaredProperties)
{
var addColumnOperation = ((AddColumnOperation)property.GetMethod!.Invoke(columnsObject, null)!);
addColumnOperation.Name = property.Name;
operation.Columns.Add(addColumnOperation);
}
migrationBuilder.Operations.Add(operation);
return new OperationBuilder<createmergeoperation>(operation);
}</createmergeoperation></addcolumnoperation></tcolumns></createmergeoperation>

Der ColumnsBuilder enthält eine Column-Methode, die hier über eine Reflection-Implementierung aufgerufen wird. Die Column-Methode gibt die AddColumnOperation zurück, die alle notwendigen Informationen zum Erstellen einer neuen Spalte enthält. In unserem Fall speziell für den Tabellentyp und die Merge-Anweisung. Bei der Migration der Datenbank erhalten wir nun die MergeOperations, die SQLServerMergeMigrationSQLGenerator in ein SQL-Skript umwandeln kann.

Implementieren des SQL-Generators.

Dies ist der einfachere Teil des Prozesses. Im vorigen Artikel haben wir diesen Generator so angepasst, dass er die MergeOperations verarbeiten kann. Jetzt, da die Merge-Operation in der Migration implementiert ist, können wir den SQL-Generator tatsächlich implementieren. Mit dem Tabellennamen und den Spalten aus der Merge-Operation haben wir alle Informationen, um die gespeicherte Prozedur zu erstellen. Im Folgenden finden Sie ein kurzes Beispiel, wie der SQL-Generator weiter implementiert werden kann.

builder.AppendLine("CREATE STORED PROCEDURE [dbo].[Merge_" + operation.TableName + "]");
using (builder.Indent())
{
builder.AppendLine($"@SourceTable dbo.{operation.TableName}Type READONLY");
}
builder.AppendLine("AS");
builder.AppendLine("BEGIN");
using (builder.Indent())
{
builder.AppendLine("MERGE INTO " + operation.TableName + " AS Target");
builder.AppendLine("USING @SourceTable AS Source");
builder.AppendLine("ON Target.Id = Source.Id");
builder.AppendLine("WHEN MATCHED AND Source.ShouldDelete = 0 THEN");
builder.AppendLine("UPDATE SET");
………
builder.AppendLine();
builder.AppendLine("OUTPUT $action, Inserted.Id, Deleted.Id;");
}

builder.EndCommand();

Aufrufen der Zusammenführung während der Laufzeit.

Die SQL Merge Statement-Erweiterung, die wir gerade in EF Core implementiert haben, wird während des Entwurfszeitbefehls erstellt: (dotnet ef migrations add merge). Anschließend, wenn Sie Ihre Datenbank migrieren, werden diese neuen SQL-Merge-Anweisungen zu Ihrer Datenbank hinzugefügt. Wenn Sie es vorziehen, zuerst einen Blick auf das SQL-Skript zu werfen, das generiert wird, können Sie zunächst dotnet ef migrations script aufrufen. Bei der aktuellen Implementierung ergibt dies die folgende Ausgabe:

IF TYPE_ID('dbo.ForecastsType') IS NOT NULL
BEGIN
DROP TYPE dbo.ForecastsType;
GO

IF OBJECT_ID('dbo.Merge_Forecasts') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.Merge_Forecasts;
GO

CREATE TYPE dbo.ForecastsType AS TABLE
(
Id uniqueidentifier NOT NULL,
Date datetime2 NOT NULL,
Summary nvarchar(max) NOT NULL,
TemperatureC int NOT NULLShouldDelete bit
);
GO

CREATE STORED PROCEDURE [dbo].[Merge_Forecasts]
@SourceTable dbo.ForecastsType READONLY
AS
BEGIN
MERGE INTO Forecasts AS Target
USING @SourceTable AS Source
ON Target.Id = Source.Id
WHEN MATCHED AND Source.ShouldDelete = 0 THEN
UPDATE SET
Target.Id = Source.Id,
Target.Date = Source.Date,
Target.Summary = Source.Summary,
Target.TemperatureC = Source.TemperatureC
WHEN MATCHED AND Source.ShouldDelete = 1 THEN
DELETE
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id, Name, Description)
VALUES (Source.Id,Source.Date,Source.Summary,Source.TemperatureC);

OUTPUT $action, Inserted.Id, Deleted.Id;
GO

Um Komplexität zu vermeiden, lässt der Code zunächst den Tabellentyp und die gespeicherte Prozedur fallen, um komplexe alter-Anweisungen zu vermeiden. Der nächste Schritt besteht darin, die SQL-Merge-Anweisungen während der Laufzeit aufzurufen. Um dies zu erreichen, kann eine Erweiterung für den DbContext namens Merge und MergeAsync eine Lösung sein. Ich würde Ihnen davon abraten, die Methode SaveChanges zu überschreiben. Die Merge-Anweisung sollte nur dann verwendet werden, wenn der Datensatz groß genug ist, um sie sinnvoll durchzuführen. Die SaveChanges-Methode könnte überschrieben werden, um eine Zusammenführung durchzuführen, wenn der Datenbestand groß genug ist. Davon würde ich jedoch abraten. Es ist willkürlich, ab welcher Zahl eine Zusammenführung schneller ist als eine normale Einfügung, Aktualisierung oder Löschung. Ich ziehe es vor, diese Entscheidung der implementierenden Partei zu überlassen. Die aktuelle Implementierung ist den seit EF Core Version 7.0 verfügbaren ExecuteUpdate und ExecuteDelete sehr ähnlich. Die Merge-Anweisung kann jedoch weiter ausgebaut werden als die Execute-Methoden. Die Merge-Anweisung kann verkettet werden, so dass der gesamte Baum gespeichert werden kann. Dazu speichern Sie zunächst die untergeordneten Objekte, die anhand der Fremdschlüssel leicht zu erkennen sind. Aktualisieren Sie die Fremdschlüssel in den Stammobjekten und speichern Sie dann die Stammobjekte. Schließlich aktualisieren Sie die Fremdschlüssel in den untergeordneten Sammlungsobjekten und speichern dann die untergeordneten Sammlungsobjekte. Um die Fremdschlüssel korrekt zu aktualisieren, muss auch eine interne Id mit der Merge-Anweisung gesendet werden. Auf diese Weise kann eine Zuordnung beibehalten werden, um die Fremdschlüssel mit ihren korrekten Stammobjekten zu aktualisieren.

Zusammenfassung: SQL Merge trifft auf EF Core

Gut, wir haben einen langen Weg hinter uns! Wir haben nicht nur über die Erweiterung von EF Core gesprochen, sondern uns auch die Hände mit SQL Merge schmutzig gemacht. In diesem Artikel ging es darum, das Leben im Umgang mit großen Datenmengen zu erleichtern. Wir haben gezeigt, wie SQL Merge das Einfügen, Aktualisieren und Löschen von Daten in einem übersichtlichen Paket zusammenfassen kann. Das bedeutet weniger Hin und Her mit der Datenbank.

Wir haben uns damit beschäftigt, wie Sie dies in EF Core einrichten können, indem wir einige Klassen wie CSharpMergeMigrationOperationGenerator und SQLServerMergeMigrationSQLGenerator angepasst haben. Es ging darum, diese Teile dazu zu bringen, die SQL Merge-Anweisung zu generieren, damit Sie diesen Code nicht selbst schreiben müssen.

Schließlich haben wir uns damit befasst, wie das alles zur Laufzeit zusammenkommt. Es geht nicht nur darum, Dinge einzurichten, sondern sie auch effektiv zu nutzen. Wir haben einige intelligente Möglichkeiten vorgeschlagen, wie z.B. die Methoden Merge und MergeAsync in DbContext.

 

Dieser Artikel ist Teil von XPRT.#16. Laden Sie das Magazin hier herunter.