Dane hierarchiczne w dostawcy programu SQL Server EF Core
Uwaga
Ta funkcja została dodana w programie EF Core 8.0.
Usługi Azure SQL i SQL Server mają specjalny typ danych, hierarchyid który jest używany do przechowywania danych hierarchicznych. W takim przypadku "dane hierarchiczne" zasadniczo oznaczają dane, które stanowią strukturę drzewa, gdzie każdy element może mieć element nadrzędny i/lub podrzędny. Przykłady takich danych to:
- Struktura organizacyjna
- System plików
- Zestaw zadań w projekcie
- Taksonomia terminów językowych
- Wykres łączy między stronami sieci Web
Baza danych może następnie uruchamiać zapytania względem tych danych przy użyciu jego struktury hierarchicznej. Na przykład zapytanie może znaleźć elementy nadrzędne i zależne od danych elementów lub znaleźć wszystkie elementy w określonej głębokości w hierarchii.
Używanie identyfikatora HierarchyId na platformie .NET i platformie EF Core
Na najniższym poziomie pakiet NuGet Microsoft.SqlServer.Types zawiera typ o nazwie SqlHierarchyId. Chociaż ten typ obsługuje działające wartości hierarchyid, praca z linQ jest nieco kłopotliwa.
Na następnym poziomie wprowadzono nowy pakiet Microsoft.EntityFrameworkCore.SqlServer.Abstractions , który zawiera typ wyższego poziomu HierarchyId przeznaczony do użycia w typach jednostek.
Napiwek
Typ HierarchyId jest bardziej idiotyczny do norm platformy .NET niż SqlHierarchyId, który zamiast tego jest modelowany po tym, jak typy programu .NET Framework są hostowane wewnątrz aparatu bazy danych programu SQL Server.
HierarchyId jest przeznaczony do pracy z programem EF Core, ale może być również używany poza programem EF Core w innych aplikacjach. Pakiet Microsoft.EntityFrameworkCore.SqlServer.Abstractions nie odwołuje się do żadnych innych pakietów, dlatego ma minimalny wpływ na rozmiar i zależności wdrożonej aplikacji.
Użycie funkcji HierarchyId programu EF Core, takich jak zapytania i aktualizacje, wymaga pakietu Microsoft.EntityFrameworkCore.SqlServer.HierarchyId . Ten pakiet wprowadza zależności Microsoft.EntityFrameworkCore.SqlServer.AbstractionsMicrosoft.SqlServer.Types przechodnie i tak często jest jedynym wymaganym pakietem.
dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId
Po zainstalowaniu pakietu użycie polecenia HierarchyId jest włączone przez wywołanie w ramach wywołania UseHierarchyId aplikacji do UseSqlServermetody . Na przykład:
options.UseSqlServer(
connectionString,
x => x.UseHierarchyId());
Hierarchie modelowania
Typ HierarchyId może służyć do właściwości typu jednostki. Załóżmy na przykład, że chcemy modelować drzewo rodzinne ojcowskie niektórych fikcyjnych półlingów. W typie jednostki dla HalflingHierarchyId elementu właściwość może służyć do lokalizowania każdego półlinga w drzewie rodzinnym.
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; }
}
Napiwek
Kod przedstawiony tutaj i w poniższych przykładach pochodzi z HierarchyIdSample.cs.
Napiwek
W razie potrzeby HierarchyId nadaje się do użycia jako typ właściwości klucza.
W tym przypadku drzewo rodzinne jest zakorzenione patriarchą rodziny. Każdy półling można prześledzić od patriarchy w dół drzewa przy użyciu jego PathFromPatriarch właściwości. SQL Server używa kompaktowego formatu binarnego dla tych ścieżek, ale często jest powszechne analizowanie z i do czytelnej dla człowieka reprezentacji ciągu znaków podczas pracy z kodem. W tej reprezentacji pozycja na każdym poziomie jest oddzielona znakiem / . Rozważmy na przykład drzewo rodzinne na poniższym diagramie:
W tym drzewie:
- Balbo znajduje się u korzenia drzewa reprezentowanego przez
/element . - Balbo ma pięcioro dzieci reprezentowane przez
/1/, ,/2//3/,/4/i/5/. - Pierwsze dziecko Balbo, Mungo, ma również pięcioro dzieci reprezentowane przez
/1/1/, ,/1/2//1/3/,/1/4/i/1/5/. Zwróć uwagę, że dlaHierarchyIdMungo (/1/) jest prefiksem dla wszystkich jego dzieci. - Podobnie trzecie dziecko Balbo, Ponto, ma dwoje dzieci, reprezentowane przez
/3/1/i/3/2/. Ponownie każdy z tych elementów podrzędnych jest poprzedzony prefiksemHierarchyIddla Ponto, który jest reprezentowany jako/3/. - I tak dalej w dół drzewa...
Poniższy kod wstawia to drzewo rodziny do bazy danych przy użyciu programu 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();
Napiwek
W razie potrzeby wartości dziesiętne mogą służyć do tworzenia nowych węzłów między dwoma istniejącymi węzłami. Na przykład /3/2.5/2/ przechodzi między /3/2/2/ i /3/3/2/.
Wykonywanie zapytań dotyczących hierarchii
HierarchyId Uwidacznia kilka metod, które mogą być używane w zapytaniach LINQ.
| Metoda | opis |
|---|---|
GetAncestor(int n) |
Pobiera poziomy węzłów n w górę drzewa hierarchicznego. |
GetDescendant(HierarchyId? child1, HierarchyId? child2) |
Pobiera wartość węzła podrzędnego, który jest większy niż child1 i mniejszy niż child2. |
GetLevel() |
Pobiera poziom tego węzła w drzewie hierarchicznym. |
GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot) |
Pobiera wartość reprezentującą lokalizację nowego węzła, który ma ścieżkę z równej ścieżce od newRootoldRoot do tej, skutecznie przenosząc tę wartość do nowej lokalizacji. |
IsDescendantOf(HierarchyId? parent) |
Pobiera wartość wskazującą, czy ten węzeł jest elementem podrzędnym parent. |
Ponadto można użyć operatorów ==, !=, <<=, > i >= .
Poniżej przedstawiono przykłady użycia tych metod w zapytaniach LINQ.
Pobieranie jednostek na danym poziomie w drzewie
Następujące zapytanie używa GetLevel metody , aby zwrócić wszystkie półlingi na danym poziomie w drzewie rodziny:
var generation = await context.Halflings.Where(halfling => halfling.PathFromPatriarch.GetLevel() == level).ToListAsync();
Przekłada się to na następujący kod SQL:
SELECT [h].[Id], [h].[Name], [h].[PathFromPatriarch], [h].[YearOfBirth]
FROM [Halflings] AS [h]
WHERE [h].[PathFromPatriarch].GetLevel() = @__level_0
Uruchomienie tego w pętli pozwala uzyskać półlingi dla każdej generacji:
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
Pobieranie bezpośredniego przodka jednostki
Następujące zapytanie używa GetAncestor metody do znalezienia bezpośredniego przodka półlinga, biorąc pod uwagę nazwę halflinga:
async Task<Halfling?> FindDirectAncestor(string name)
=> await context.Halflings
.SingleOrDefaultAsync(
ancestor => ancestor.PathFromPatriarch == context.Halflings
.Single(descendent => descendent.Name == name).PathFromPatriarch
.GetAncestor(1));
Przekłada się to na następujący kod SQL:
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)
Uruchomienie tego zapytania dla półlingu "Bilbo" zwraca wartość "Bungo".
Pobieranie bezpośrednich elementów potomnych jednostki
Następujące zapytanie używa również metody GetAncestor, ale tym razem w celu znalezienia bezpośrednich elementów potomnych półlinga, biorąc pod uwagę nazwę halflinga:
IQueryable<Halfling> FindDirectDescendents(string name)
=> context.Halflings.Where(
descendent => descendent.PathFromPatriarch.GetAncestor(1) == context.Halflings
.Single(ancestor => ancestor.Name == name).PathFromPatriarch);
Przekłada się to na następujący kod SQL:
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)
Uruchomienie tego zapytania dla półlingu "Mungo" zwraca wartości "Bungo", "Belba", "Longo" i "Linda".
Pobieranie wszystkich elementów podrzędnych jednostki
GetAncestor jest przydatna do wyszukiwania w górę lub w dół pojedynczego poziomu, a nawet określonej liczby poziomów. Z drugiej strony jest IsDescendantOf przydatna do znajdowania wszystkich przodków lub zależności. Na przykład następujące zapytanie używa IsDescendantOf metody , aby znaleźć wszystkie elementy podrzędne półlinga, biorąc pod uwagę nazwę halflinga:
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());
Ważne
IsDescendantOf zwraca wartość true dla siebie, dlatego jest on filtrowany w powyższym zapytaniu.
Przekłada się to na następujący kod SQL:
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
Uruchomienie tego zapytania dla półlingu "Bilbo" zwraca wartości "Bungo", "Mungo" i "Balbo".
Pobieranie wszystkich elementów potomnych jednostki
Następujące zapytanie używa również elementu IsDescendantOf, ale tym razem do wszystkich elementów potomnych półlinga, biorąc pod uwagę nazwę halflinga:
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());
Przekłada się to na następujący kod SQL:
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()
Uruchomienie tego zapytania dla półlingu "Mungo" zwraca "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" i "Poppy".
Znajdowanie wspólnego przodka
Jednym z najczęstszych pytań zadawanych na temat tego konkretnego drzewa rodzinnego jest "kto jest wspólnym przodkiem Frodo i Bilbo?" Możemy użyć IsDescendantOf polecenia , aby napisać takie zapytanie:
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();
Przekłada się to na następujący kod SQL:
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
Uruchomienie tego zapytania z "Bilbo" i "Frodo" informuje nas, że ich wspólny przodk jest "Balbo".
Aktualizowanie hierarchii
Do aktualizowania kolumn można używać mechanizmów śledzenia normalnych zmian i hierarchyid.
Ponowne nadrzędne pod hierarchię
Na przykład, jestem pewien, że wszyscy pamiętamy skandal SR 1752 (np. "LongoGate"), gdy badania DNA wykazały, że Longo nie był w rzeczywistości synem Mungo, ale rzeczywiście synem Ponto! Jednym z opadów z tego skandalu było to, że drzewo rodzinne musi być ponownie napisane. W szczególności Longo i wszyscy jego potomkowie musieli zostać ponownie rodzicami z Mungo do Ponto.
GetReparentedValue może służyć do tego celu. Na przykład najpierw "Longo" i wszystkie jego elementy potomne są odpytywane:
var longoAndDescendents = await context.Halflings.Where(
descendent => descendent.PathFromPatriarch.IsDescendantOf(
context.Halflings.Single(ancestor => ancestor.Name == "Longo").PathFromPatriarch))
.ToListAsync();
Następnie GetReparentedValue służy do aktualizowania HierarchyId elementu dla longo i każdego malejącej, a następnie wywołania metody :SaveChangesAsync
foreach (var descendent in longoAndDescendents)
{
descendent.PathFromPatriarch
= descendent.PathFromPatriarch.GetReparentedValue(
mungo.PathFromPatriarch, ponto.PathFromPatriarch)!;
}
await context.SaveChangesAsync();
Spowoduje to następującą aktualizację bazy danych:
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;
Przy użyciu następujących parametrów:
@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)
Uwaga
Wartości parametrów właściwości HierarchyId są wysyłane do bazy danych w kompaktowym formacie binarnym.
Po aktualizacji zapytanie dotyczące elementów potomnych "Mungo" zwraca wartość "Bungo", "Belba", "Linda", "Bingo", "Bilbo", "Falco" i "Poppy", podczas wykonywania zapytań o potomków "Ponto", "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony" i "Angelica".
Mapowania funkcji
| .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(nadrzędny) | @hierarchyId.IsDescendantOf(@parent) |
| HierarchyId.Parse(input) | hierarchyid::P arse(@input) |
| hierarchyId.ToString() | @hierarchyId.ToString() |
