Data Hirarkis pada EF Core Provider SQL Server

Note

Fitur ini ditambahkan dalam EF Core 8.0.

Azure SQL dan SQL Server memiliki jenis data khusus yang disebut hierarchyid yang digunakan untuk menyimpan data hierarkis. Dalam hal ini, "data hierarkis" pada dasarnya berarti data yang membentuk struktur pohon, di mana setiap item dapat memiliki induk dan/atau anak. Contoh data tersebut adalah:

  • Struktur organisasi
  • Sistem file
  • Sekumpulan tugas dalam proyek
  • Taksonomi istilah bahasa
  • Grafik tautan antar halaman Web

Database kemudian dapat menjalankan kueri terhadap data ini menggunakan struktur hierarkisnya. Misalnya, kueri dapat menemukan leluhur dan dependen item tertentu, atau menemukan semua item pada kedalaman tertentu dalam hierarki.

Menggunakan HierarchyId di .NET dan EF Core

Pada tingkat terendah, paket NuGet Microsoft.SqlServer.Type menyertakan jenis yang disebut SqlHierarchyId. Meskipun jenis ini mendukung nilai hierarchyid yang berfungsi, jenis ini agak sulit digunakan di LINQ.

Pada tingkat berikutnya, paket Microsoft.EntityFrameworkCore.SqlServer.Abstractions baru telah diperkenalkan, yang mencakup jenis tingkat HierarchyId yang lebih tinggi yang dimaksudkan untuk digunakan dalam jenis entitas.

Tip

Jenis HierarchyId ini lebih selaras dengan norma .NET daripada SqlHierarchyId, yang sebaliknya dimodelkan berdasarkan cara jenis .NET Framework diatur dalam mesin database SQL Server. HierarchyId dirancang untuk bekerja dengan EF Core, tetapi juga dapat digunakan di luar EF Core di aplikasi lain. Paket Microsoft.EntityFrameworkCore.SqlServer.Abstractions ini tidak mereferensikan paket lain, sehingga berdampak minimal pada ukuran dan dependensi aplikasi yang disebarkan.

Penggunaan HierarchyId untuk fungsionalitas EF Core seperti kueri dan pembaruan memerlukan paket Microsoft.EntityFrameworkCore.SqlServer.HierarchyId . Paket ini membawa Microsoft.EntityFrameworkCore.SqlServer.Abstractions dan Microsoft.SqlServer.Types sebagai dependensi transitif, dan seringkali satu-satunya paket yang diperlukan.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Setelah paket diinstal, penggunaan HierarchyId diaktifkan dengan memanggil UseHierarchyId sebagai bagian dari panggilan aplikasi ke UseSqlServer. Contohnya:

options.UseSqlServer(
    connectionString,
    x => x.UseHierarchyId());

Hierarki pemodelan

Jenis HierarchyId dapat digunakan untuk properti jenis entitas. Misalnya, asumsikan kita ingin memodelkan pohon keluarga paternal dari beberapa paruh fiksi. Dalam jenis entitas untuk Halfling, properti HierarchyId dapat digunakan untuk menemukan setiap halfling di silsilah keluarga.

public class Halfling
{
    public Halfling(HierarchyId pathFromPatriarch, string name, int? yearOfBirth = null)
    {
        PathFromPatriarch = pathFromPatriarch;
        Name = name;
        YearOfBirth = yearOfBirth;
    }

    public int Id { get; private set; }
    public HierarchyId PathFromPatriarch { get; set; }
    public string Name { get; set; }
    public int? YearOfBirth { get; set; }
}

Tip

Kode yang ditunjukkan di sini dan dalam contoh di bawah ini berasal dari HierarchyIdSample.cs.

Tip

Jika diinginkan, HierarchyId cocok untuk digunakan sebagai jenis properti kunci.

Dalam hal ini, pohon keluarga berakar pada patriarki keluarga. Setiap halfling dapat dilacak dari patriark ke bawah pohon silsilah menggunakan propertinya PathFromPatriarch. SQL Server menggunakan format biner yang ringkas untuk jalur ini, tetapi umum untuk mengurai jalur tersebut ke dan dari representasi string yang dapat dibaca manusia saat bekerja dengan kode. Dalam representasi ini, posisi di setiap tingkat dipisahkan oleh / karakter. Misalnya, pertimbangkan pohon keluarga dalam diagram di bawah ini:

Pohon keluarga halfling

Di pohon ini:

  • Balbo berada di akar pohon, diwakili oleh /.
  • Balbo memiliki lima anak, yang diwakili oleh /1/, , /2//3/, /4/, dan /5/.
  • Anak pertama Balbo, Mungo, juga memiliki lima anak, yang diwakili oleh /1/1/, , /1/2//1/3/, /1/4/, dan /1/5/. Perhatikanlah bahwa HierarchyId untuk Mungo (/1/) adalah awalan untuk semua anak-anaknya.
  • Demikian pula, anak ketiga Balbo, Ponto, memiliki dua anak, diwakili oleh /3/1/ dan /3/2/. Sekali lagi masing-masing anak ini diawali oleh HierarchyId untuk Ponto, yang diwakili sebagai /3/.
  • Dan seterusnya ke bawah dalam struktur hierarki...

Kode berikut menyisipkan pohon keluarga ini ke dalam database menggunakan EF Core:

await AddRangeAsync(
    new Halfling(HierarchyId.Parse("/"), "Balbo", 1167),
    new Halfling(HierarchyId.Parse("/1/"), "Mungo", 1207),
    new Halfling(HierarchyId.Parse("/2/"), "Pansy", 1212),
    new Halfling(HierarchyId.Parse("/3/"), "Ponto", 1216),
    new Halfling(HierarchyId.Parse("/4/"), "Largo", 1220),
    new Halfling(HierarchyId.Parse("/5/"), "Lily", 1222),
    new Halfling(HierarchyId.Parse("/1/1/"), "Bungo", 1246),
    new Halfling(HierarchyId.Parse("/1/2/"), "Belba", 1256),
    new Halfling(HierarchyId.Parse("/1/3/"), "Longo", 1260),
    new Halfling(HierarchyId.Parse("/1/4/"), "Linda", 1262),
    new Halfling(HierarchyId.Parse("/1/5/"), "Bingo", 1264),
    new Halfling(HierarchyId.Parse("/3/1/"), "Rosa", 1256),
    new Halfling(HierarchyId.Parse("/3/2/"), "Polo"),
    new Halfling(HierarchyId.Parse("/4/1/"), "Fosco", 1264),
    new Halfling(HierarchyId.Parse("/1/1/1/"), "Bilbo", 1290),
    new Halfling(HierarchyId.Parse("/1/3/1/"), "Otho", 1310),
    new Halfling(HierarchyId.Parse("/1/5/1/"), "Falco", 1303),
    new Halfling(HierarchyId.Parse("/3/2/1/"), "Posco", 1302),
    new Halfling(HierarchyId.Parse("/3/2/2/"), "Prisca", 1306),
    new Halfling(HierarchyId.Parse("/4/1/1/"), "Dora", 1302),
    new Halfling(HierarchyId.Parse("/4/1/2/"), "Drogo", 1308),
    new Halfling(HierarchyId.Parse("/4/1/3/"), "Dudo", 1311),
    new Halfling(HierarchyId.Parse("/1/3/1/1/"), "Lotho", 1310),
    new Halfling(HierarchyId.Parse("/1/5/1/1/"), "Poppy", 1344),
    new Halfling(HierarchyId.Parse("/3/2/1/1/"), "Ponto", 1346),
    new Halfling(HierarchyId.Parse("/3/2/1/2/"), "Porto", 1348),
    new Halfling(HierarchyId.Parse("/3/2/1/3/"), "Peony", 1350),
    new Halfling(HierarchyId.Parse("/4/1/2/1/"), "Frodo", 1368),
    new Halfling(HierarchyId.Parse("/4/1/3/1/"), "Daisy", 1350),
    new Halfling(HierarchyId.Parse("/3/2/1/1/1/"), "Angelica", 1381));

await SaveChangesAsync();

Tip

Jika diperlukan, nilai desimal dapat digunakan untuk membuat simpul baru di antara dua simpul yang ada. Misalnya, /3/2.5/2/ berjalan antara /3/2/2/ dan /3/3/2/.

Melakukan kueri pada hierarki

HierarchyId mengekspos beberapa metode yang dapat digunakan dalam kueri LINQ.

Method Description
GetAncestor(int n) Mendapatkan tingkat simpul n ke atas pohon hierarkis.
GetDescendant(HierarchyId? child1, HierarchyId? child2) Mendapatkan nilai node turunan yang lebih besar dari child1 dan kurang dari child2.
GetLevel() Mengambil tingkat pada simpul ini di pohon hierarkis.
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot) Mendapatkan nilai yang mewakili lokasi simpul baru yang memiliki jalur dari newRoot sama dengan jalur dari oldRoot ke ini, secara efektif memindahkan ini ke lokasi baru.
IsDescendantOf(HierarchyId? parent) Mendapatkan nilai yang menunjukkan apakah simpul ini adalah turunan dari parent.

Selain itu, operator ==, , !=<, <=, > dan >= dapat digunakan.

Berikut ini adalah contoh penggunaan metode ini dalam kueri LINQ.

Mendapatkan entitas pada tingkat tertentu di pohon

Kueri berikut menggunakan GetLevel untuk mengembalikan semua halfling pada tingkat tertentu di pohon keluarga:

var generation = await context.Halflings.Where(halfling => halfling.PathFromPatriarch.GetLevel() == level).ToListAsync();

Ini diterjemahkan ke SQL berikut:

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch].GetLevel() = @__level_0

Menjalankan ini dalam perulangan kita bisa mendapatkan halfling untuk setiap generasi:

Generation 0: Balbo
Generation 1: Mungo, Pansy, Ponto, Largo, Lily
Generation 2: Bungo, Belba, Longo, Linda, Bingo, Rosa, Polo, Fosco
Generation 3: Bilbo, Otho, Falco, Posco, Prisca, Dora, Drogo, Dudo
Generation 4: Lotho, Poppy, Ponto, Porto, Peony, Frodo, Daisy
Generation 5: Angelica

Dapatkan induk langsung dari entitas

Kueri berikut menggunakan GetAncestor untuk menemukan leluhur langsung dari halfling, mengingat nama halfling tersebut:

async Task<Halfling?> FindDirectAncestor(string name)
    => await context.Halflings
        .SingleOrDefaultAsync(
            ancestor => ancestor.PathFromPatriarch == context.Halflings
                .Single(descendent => descendent.Name == name).PathFromPatriarch
                .GetAncestor(1));

Ini diterjemahkan ke SQL berikut:

SELECT TOP(2) [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch] = (
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0).GetAncestor(1)

Menjalankan kueri ini untuk halfling "Bilbo" menghasilkan "Bungo".

Mendapatkan keturunan langsung dari sebuah entitas

Kueri berikut juga menggunakan GetAncestor, tetapi kali ini untuk menemukan turunan langsung dari halfling, mengingat nama halfling tersebut:

IQueryable<Halfling> FindDirectDescendents(string name)
    => context.Halflings.Where(
        descendent => descendent.PathFromPatriarch.GetAncestor(1) == context.Halflings
            .Single(ancestor => ancestor.Name == name).PathFromPatriarch);

Ini diterjemahkan ke SQL berikut:

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch].GetAncestor(1) = (
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0)

Menjalankan kueri ini untuk halfling "Mungo" mengembalikan "Bungo", "Belba", "Longo", dan "Linda".

Dapatkan semua pendahulu entitas

GetAncestor berguna untuk mencari ke atas atau ke bawah satu tingkat, atau sejumlah tingkat yang ditentukan. Di sisi lain, IsDescendantOf berguna untuk menemukan semua leluhur atau dependen. Misalnya, kueri berikut menggunakan IsDescendantOf untuk menemukan semua leluhur seorang halfling, berdasarkan namanya.

IQueryable<Halfling> FindAllAncestors(string name)
    => context.Halflings.Where(
            ancestor => context.Halflings
                .Single(
                    descendent =>
                        descendent.Name == name
                        && ancestor.Id != descendent.Id)
                .PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch))
        .OrderByDescending(ancestor => ancestor.PathFromPatriarch.GetLevel());

Important

IsDescendantOf mengembalikan true untuk dirinya sendiri, itulah sebabnya dikecualikan dalam kueri di atas.

Ini diterjemahkan ke SQL berikut:

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE (
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0 AND [h].[Id] <> [h0].[Id]).IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)
ORDER BY [h].[PathFromPatriarch].GetLevel() DESC

Menjalankan kueri ini untuk halfling "Bilbo" mengembalikan "Bungo", "Mungo", dan "Balbo".

Dapatkan semua keturunan dari sebuah entitas

Kueri berikut juga menggunakan IsDescendantOf, tetapi kali ini untuk semua turunan dari halfling, mengingat nama halfling itu:

IQueryable<Halfling> FindAllDescendents(string name)
    => context.Halflings.Where(
            descendent => descendent.PathFromPatriarch.IsDescendantOf(
                context.Halflings
                    .Single(
                        ancestor =>
                            ancestor.Name == name
                            && descendent.Id != ancestor.Id)
                    .PathFromPatriarch))
        .OrderBy(descendent => descendent.PathFromPatriarch.GetLevel());

Ini diterjemahkan ke SQL berikut:

SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch].IsDescendantOf((
    SELECT TOP(1) [h0].[PathFromPatriarch]
    FROM [Halflings] AS [h0]
    WHERE [h0].[Name] = @__name_0 AND [h].[Id] <> [h0].[Id])) = CAST(1 AS bit)
ORDER BY [h].[PathFromPatriarch].GetLevel()

Menjalankan kueri untuk halfling "Mungo" ini menghasilkan "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho", dan "Poppy".

Menemukan leluhur umum

Salah satu pertanyaan paling umum yang diajukan tentang pohon keluarga khusus ini adalah, "siapa nenek moyang umum Frodo dan Bilbo?" Kita dapat menggunakan IsDescendantOf untuk menulis kueri seperti itu:

async Task<Halfling?> FindCommonAncestor(Halfling first, Halfling second)
    => await context.Halflings
        .Where(
            ancestor => first.PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch)
                        && second.PathFromPatriarch.IsDescendantOf(ancestor.PathFromPatriarch))
        .OrderByDescending(ancestor => ancestor.PathFromPatriarch.GetLevel())
        .FirstOrDefaultAsync();

Ini diterjemahkan ke SQL berikut:

SELECT TOP(1) [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE @__first_PathFromPatriarch_0.IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)
  AND @__second_PathFromPatriarch_1.IsDescendantOf([h].[PathFromPatriarch]) = CAST(1 AS bit)
ORDER BY [h].[PathFromPatriarch].GetLevel() DESC

Menjalankan kueri ini dengan "Bilbo" dan "Frodo" memberi tahu kita bahwa leluhur umum mereka adalah "Balbo".

Memperbarui struktur hierarki

Mekanisme pelacakan perubahan normal dan SaveChanges dapat digunakan untuk memperbarui hierarchyid kolom.

Mengasuh ulang sub-hierarki

Misalnya, saya yakin kita semua ingat skandal SR 1752 (alias "LongoGate") ketika pengujian DNA mengungkapkan bahwa Longo sebenarnya bukan putra Mungo, tetapi sebenarnya putra Ponto! Salah satu dampak dari skandal ini adalah pohon keluarga perlu ditulis ulang. Secara khusus, Longo dan semua keturunannya perlu diasuh kembali dari Mungo ke Ponto. GetReparentedValue dapat digunakan untuk melakukan ini. Misalnya, pertama-tama "Longo" dan semua keturunannya dikueri:

var longoAndDescendents = await context.Halflings.Where(
        descendent => descendent.PathFromPatriarch.IsDescendantOf(
            context.Halflings.Single(ancestor => ancestor.Name == "Longo").PathFromPatriarch))
    .ToListAsync();

Kemudian GetReparentedValue digunakan untuk memperbarui HierarchyId untuk Longo dan setiap keturunan, diikuti dengan panggilan ke SaveChangesAsync:

foreach (var descendent in longoAndDescendents)
{
    descendent.PathFromPatriarch
        = descendent.PathFromPatriarch.GetReparentedValue(
            mungo.PathFromPatriarch, ponto.PathFromPatriarch)!;
}

await context.SaveChangesAsync();

Ini menghasilkan pembaruan database berikut:

SET NOCOUNT ON;
UPDATE [Halflings] SET [PathFromPatriarch] = @p0
OUTPUT 1
WHERE [Id] = @p1;
UPDATE [Halflings] SET [PathFromPatriarch] = @p2
OUTPUT 1
WHERE [Id] = @p3;
UPDATE [Halflings] SET [PathFromPatriarch] = @p4
OUTPUT 1
WHERE [Id] = @p5;

Menggunakan parameter ini:

 @p1='9',
 @p0='0x7BC0' (Nullable = false) (Size = 2) (DbType = Object),
 @p3='16',
 @p2='0x7BD6' (Nullable = false) (Size = 2) (DbType = Object),
 @p5='23',
 @p4='0x7BD6B0' (Nullable = false) (Size = 3) (DbType = Object)

Note

Nilai parameter untuk HierarchyId properti dikirim ke database dalam format biner yang ringkas.

Setelah pembaruan, mengkueri turunan "Mungo" mengembalikan "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco", dan "Poppy", sementara mengkueri keturunan "Ponto" mengembalikan "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony", dan "Angelica".

Pemetaan fungsi

.NET SQL
hierarchyId.GetAncestor(n) @hierarchyId.GetAncestor(@n)
hierarchyId.GetDescendant(child) @hierarchyId.GetDescendant(@child, NULL)
hierarchyId.GetDescendant(child1, child2) @hierarchyId.GetDescendant(@child1, @child2)
hierarchyId.GetLevel() @hierarchyId.GetLevel()
hierarchyId.GetReparentedValue(oldRoot, newRoot) @hierarchyId.GetReparentedValue(@oldRoot, @newRoot)
HierarchyId.GetRoot() hierarchyid::GetRoot()
hierarchyId.IsDescendantOf(parent) @hierarchyId.IsDescendantOf(@parent)
HierarchyId.Parse(input) hierarchyid::Parse(@input)
hierarchyId.ToString() @hierarchyId.ToString()

Sumber daya tambahan

  • Last updated on