SQL Server EF Core Sağlayıcısında Hiyerarşik Veriler

Azure SQL ve SQL Server, hiyerarşik verileri depolamak için kullanılan adlı hierarchyid özel bir veri türüne sahiptir. Bu durumda, "hiyerarşik veriler", temelde her öğenin bir üst ve/veya alt öğeye sahip olabileceği bir ağaç yapısı oluşturan veriler anlamına gelir. Bu tür verilere örnek olarak şunlar verilebilir:

• Kuruluş yapısı
• Dosya sistemi
• Projedeki görev kümesi
• Dil terimlerinin taksonomisi
• Web sayfaları arasındaki bağlantıların grafiği

Veritabanı daha sonra hiyerarşik yapısını kullanarak bu verilere karşı sorgu çalıştırabilir. Örneğin, bir sorgu belirli öğelerin üst öğelerini ve bağımlılarını bulabilir veya hiyerarşide belirli bir derinlikteki tüm öğeleri bulabilir.

.NET ve EF Core'da HierarchyId Kullanma

En düşük düzeyde, Microsoft.SqlServer.Types NuGet paketi adlı SqlHierarchyIdbir tür içerir. Bu tür çalışma hiyerarşisi değerlerini desteklese de LINQ ile çalışmak biraz zahmetlidir.

Sonraki düzeyde, varlık türlerinde kullanılması amaçlanan daha üst düzey bir tür içeren yeni HierarchyId paketi kullanıma sunulmuştur.

HierarchyId sorgular ve güncelleştirmeler gibi EF Core işlevleri için kullanımı Için Microsoft.EntityFrameworkCore.SqlServer.HierarchyId paketi gerekir. Bu paket ve öğesini geçişli bağımlılıklar olarak getirir Microsoft.EntityFrameworkCore.SqlServer.AbstractionsMicrosoft.SqlServer.Types ve genellikle gereken tek paket de budur.

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

Paket yüklendikten sonra, uygulamasının HierarchyId çağrısının bir parçası olarak çağrısı UseHierarchyIdUseSqlServeryapılarak kullanımı etkinleştirilir. Örneğin:

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

Modelleme hiyerarşileri

Türü HierarchyId bir varlık türünün özellikleri için kullanılabilir. Örneğin, bazı kurgusal yarımlamaların babalık aile ağacını modellemek istediğimizi varsayalım. için Halflingvarlık türünde, aile ağacındaki her yarıyı bulmak için bir HierarchyId özellik kullanılabilir.

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; }
}

Bu durumda, aile ağacının kökü ailenin patriğiyle birlikte oluşturulur. Her yarımlama, kendi özelliği kullanılarak PathFromPatriarch ağaçta patrikten izlenebilir. SQL Server bu yollar için kompakt bir ikili biçim kullanır, ancak kodla çalışırken insan tarafından kolayca okunabilen bir dize gösterimine dönüştürmek ve buradan ayrıştırmak yaygın bir durumdur. Bu gösterimde, her düzeydeki konum bir / karakterle ayrılır. Örneğin, aşağıdaki diyagramda yer alan aile ağacını göz önünde bulundurun:

Bu ağaçta:

• Balbo, tarafından /temsil edilen ağacın kökündedir.
• Balbo'nun , , /1//2//3/, ve /4/ile /5/temsil edilen beş çocuğu vardır.
• Balbo'nun ilk çocuğu Mungo'nun da , , /1/1//1/2/, /1/3/ve /1/4/ile /1/5/temsil edilen beş çocuğu vardır. Tüm çocuklarının ön ekinin HierarchyId Mungo (/1/) için olduğuna dikkat edin.
• Benzer şekilde Balbo'nun üçüncü çocuğu Ponto'nun ve /3/1/ile /3/2/ temsil edilen iki çocuğu vardır. Yine bu çocukların her biri, olarak temsil edilen HierarchyId Ponto için ön ekini /3/alır.
• Ve ağacın aşağısı...

Aşağıdaki kod, EF Core kullanarak bu aile ağacını bir veritabanına ekler:

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

Hiyerarşileri sorgulama

HierarchyId LINQ sorgularında kullanılabilecek çeşitli yöntemleri kullanıma sunar.

Ayrıca , , ==!=, <ve <=> işleçleri >=kullanılabilir.

Aşağıda, LINQ sorgularında bu yöntemleri kullanma örnekleri verilmiştir.

Ağaçta belirli bir düzeydeki varlıkları alma

Aşağıdaki sorgu, aile ağacında belirli bir düzeydeki tüm yarımları döndürmek için kullanır GetLevel :

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

Bu, aşağıdaki SQL'e çevrilir:

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

Bunu bir döngüde çalıştırarak her nesil için halfling'leri alacağız:

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

Bir varlığın doğrudan atası alma

Aşağıdaki sorgu, bu yarımlamanın adı göz önünde bulundurularak bir yarılanmanın doğrudan atası bulmak için kullanır GetAncestor :

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

Bu, aşağıdaki SQL'e çevrilir:

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)

"Bilbo" yarımlama için bu sorgu çalıştırılırken "Bungo" döndürülüyor.

Bir varlığın doğrudan alt öğelerini alma

Aşağıdaki sorguda da kullanılır GetAncestor, ancak bu kez bu yarımlamanın adı göz önüne alındığında, bir yarıya ait doğrudan alt öğeleri bulmak için kullanılır:

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

Bu, aşağıdaki SQL'e çevrilir:

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)

"Mungo" yarımlama için bu sorgu çalıştırılırken "Bungo", "Belba", "Longo" ve "Linda" döndürülüyor.

Bir varlığın tüm atalarını alma

GetAncestor tek bir düzeyi veya gerçekten de belirli sayıda düzeyi aramak için kullanışlıdır. Öte yandan, IsDescendantOf tüm ataları veya bağımlıları bulmak için yararlıdır. Örneğin, aşağıdaki sorgu, bu yarımlamanın adı göz önünde bulundurularak bir yarımlamanın tüm atalarını bulmak için kullanır IsDescendantOf :

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

Bu, aşağıdaki SQL'e çevrilir:

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

"Bilbo" yarımlama için bu sorgu çalıştırılırken "Bungo", "Mungo" ve "Balbo" döndürülüyor.

Bir varlığın tüm alt öğelerini alma

Aşağıdaki sorguda da kullanılır IsDescendantOf, ancak bu kez bu yarımlamanın adı göz önünde bulundurulduğunda bir yarımlamanın tüm alt öğelerine kullanılır:

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

Bu, aşağıdaki SQL'e çevrilir:

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

"Mungo" yarımlama için bu sorgu çalıştırılırken "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" ve "Poppy" döndürülüyor.

Ortak bir ata bulma

Bu aile ağacı hakkında sorulan en yaygın sorulardan biri, "Frodo ve Bilbo'nun ortak atası kim?" sorusudur. Böyle bir sorgu yazmak için kullanabiliriz IsDescendantOf :

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

Bu, aşağıdaki SQL'e çevrilir:

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

Bu sorguyu "Bilbo" ve "Frodo" ile çalıştırmak, ortak atalarının "Balbo" olduğunu bildirir.

Hiyerarşileri güncelleştirme

Sütunları güncelleştirmek için normal değişiklik izleme ve SaveChanges mekanizmaları hierarchyid kullanılabilir.

Alt hiyerarşiyi yeniden üst öğe oluşturma

Örneğin, DNA testinde Longo'nun aslında Mungo'nun değil aslında Ponto'nun oğlu olduğunu ortaya çıkardığında hepimizin SR 1752 (yani "LongoGate") skandalını hatırlayacağından eminim! Bu skandaldan bir tanesi, aile ağacının yeniden yazılması gerektiğiydi. Özellikle, Longo ve tüm soyundan gelenlerin Mungo'dan Ponto'ya yeniden ebeveyn olması gerekiyordu. GetReparentedValue bunu yapmak için kullanılabilir. Örneğin, ilk "Longo" ve tüm alt öğeleri sorgulanır:

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

Ardından GetReparentedValue Longo ve her alt öğesini güncelleştirmek HierarchyId için kullanılır ve ardından öğesine bir çağrı SaveChangesAsyncyapılır:

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

await context.SaveChangesAsync();

Bu, aşağıdaki veritabanı güncelleştirmesine neden olur:

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;

Şu parametreleri kullanarak:

 @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)

Güncelleştirmeden sonra "Mungo" alt öğeleri sorgulanması "Bungo" döndürür, "Belba", "Linda", "Bingo", "Bilbo", "Falco" ve "Poppy", "Ponto" öğesinin alt öğeleri için sorgu yapılırken "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" ve "Angelica" döndürür.

İşlev eşlemeleri

Ek kaynaklar

• SQL Server'da Hiyerarşik Veriler