Data Hierarkis di Penyedia Inti EF SQL Server

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 hierarkis yang berfungsi, agak rumit untuk dikerjakan 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.

Penggunaan HierarchyId untuk fungsionalitas EF Core seperti kueri dan pembaruan memerlukan paket Microsoft.EntityFrameworkCore.SqlServer.HierarchyId . Paket ini membawa dan Microsoft.EntityFrameworkCore.SqlServer.AbstractionsMicrosoft.SqlServer.Types sebagai dependensi transitif, dan begitu juga 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, HierarchyId properti dapat digunakan untuk menemukan setiap setengah di pohon 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; }
}

Dalam hal ini, pohon keluarga berakar pada patriarki keluarga. Setiap halfling dapat dilacak dari patriarki di bawah pohon 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:

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 sebagainya di bawah pohon ...

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();

Mengkueri hierarki

HierarchyId mengekspos beberapa metode yang dapat digunakan dalam kueri LINQ.

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

Mendapatkan leluhur langsung 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" mengembalikan "Bungo".

Mendapatkan turunan langsung 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".

Mendapatkan semua leluhur entitas

GetAncestor berguna untuk mencari ke atas atau ke bawah satu tingkat, atau, memang, jumlah tingkat yang ditentukan. Di sisi lain, IsDescendantOf berguna untuk menemukan semua leluhur atau dependen. Misalnya, kueri berikut menggunakan IsDescendantOf untuk menemukan semua leluhur dari halfling, mengingat nama halfling itu:

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());

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".

Mendapatkan semua turunan 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 ini untuk halfling "Mungo" mengembalikan "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 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! Satu fallout dari skandal ini adalah bahwa 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, "Longo" pertama 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)

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

Sumber Daya Tambahan:

• Data Hierarkis di SQL Server