SQL Server EF Core 공급자의 계층적 데이터

아티클
09/30/2024

참고 항목

이 기능은 EF Core 8.0에서 추가되었습니다.

Azure SQL 및 SQL Server에는 hierarchyid를 저장하는 데 사용되는 라는 특수 데이터 형식이 있습니다. 이 경우 "계층적 데이터"는 기본적으로 각 항목에 부모 및/또는 자식이 있을 수 있는 트리 구조를 형성하는 데이터를 의미합니다. 이러한 데이터의 예는 다음과 같습니다.

조직 구조
파일 시스템
프로젝트의 태스크 집합
언어 용어의 분류
웹 페이지 간 링크의 그래프

그런 다음 데이터베이스는 계층 구조를 사용하여 이 데이터에 대해 쿼리를 실행할 수 있습니다. 예를 들어 쿼리는 지정된 항목의 상위 항목과 종속 항목을 찾거나 계층 구조의 특정 깊이에서 모든 항목을 찾을 수 있습니다.

.NET 및 EF Core에서 HierarchyId 사용

가장 낮은 수준에서 Microsoft.SqlServer.Types NuGet 패키지에는 SqlHierarchyId라는 형식이 포함됩니다. 이 형식은 작업 hierarchyid 값을 지원하지만 LINQ에서 작업하는 것은 약간 번거롭습니다.

다음 수준에서는 엔터티 형식에서 사용하기 위한 상위 수준 형식을 포함하는 새 HierarchyId 패키지가 도입되었습니다.

팁

HierarchyId 형식은 SqlHierarchyId보다 .NET의 표준에 더 특화되어 있으며, 대신 .NET Framework 형식이 SQL Server 데이터베이스 엔진 내에서 호스트되는 방식을 모델링합니다. HierarchyId은(는) EF Core에서 작동하도록 설계되었지만 다른 애플리케이션에서는 EF Core 외부에서도 사용할 수 있습니다. Microsoft.EntityFrameworkCore.SqlServer.Abstractions 패키지는 다른 패키지를 참조하지 않으므로 배포된 애플리케이션 크기 및 종속성에 최소한의 영향을 줍니다.

쿼리 및 업데이트와 같은 EF Core 기능에 HierarchyId을(를) 사용하려면 Microsoft.EntityFrameworkCore.SqlServer.HierarchyId 패키지가 필요합니다. 이 패키지는 Microsoft.EntityFrameworkCore.SqlServer.Abstractions 및 Microsoft.SqlServer.Types를 전이적 종속성으로 제공하므로 종종 필요한 유일한 패키지입니다.

.NET Core CLI
Visual Studio

dotnet add package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

도구 > NuGet 패키지 관리자 > 패키지 관리자 콘솔

다음 명령을 실행합니다.

Install-Package Microsoft.EntityFrameworkCore.SqlServer.HierarchyId

팁: 프로젝트를 마우스 오른쪽 단추로 클릭하고 NuGet 패키지 관리를 선택하여 패키지를 설치할 수도 있습니다.

패키지가 설치되면 애플리케이션의 HierarchyId 호출의 일부로 UseHierarchyId를 호출하여 UseSqlServer를 사용할 수 있습니다. 예시:

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

계층 모델링

HierarchyId 형식은 엔터티 형식의 속성에 사용할 수 있습니다. 예를 들어 가상의 아이 부계 가계도를 모델링하려고 합니다. Halfling에 대한 엔터티 형식에서 HierarchyId 속성을 사용하여 가계도에서 각 아이를 찾을 수 있습니다.

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

팁

여기에 표시된 코드와 아래 예제는 HierarchyIdSample.cs에서 온 것입니다.

팁

원하는 경우 HierarchyId은(는) 키 속성 형식으로 사용하기에 적합합니다.

이 경우, 가계도는 가족의 가장에 뿌리를 두고 있습니다. 각 아이는 PathFromPatriarch 속성을 사용하여 트리 아래 가장에서부터 추적할 수 있습니다. SQL Server는 이러한 경로에 대해 컴팩트한 이진 형식을 사용하지만 코드를 사용할 때 일반적으로 사람이 읽을 수 있는 문자열 표현으로 파싱합니다. 이 표현에서 각 수준의 위치는 / 문자로 구분됩니다. 예를 들어 아래 다이어그램에서 가계도를 고려합니다.

미성년의 가계도

이 트리에서는 다음과 같습니다.

Balbo는 /(으)로 나타나는 트리의 루트에 있습니다.
Balbo에게는 다섯 명의 자녀가 있으며 /1/, /2/, /3/, /4/, /5/로 표시됩니다.
Balbo의 첫 아이인 Mungo도 자녀가 다섯 명 있으며 /1/1/, /1/2/, /1/3/, /1/4/, /1/5/로 표시됩니다. Mungo(HierarchyId)에 대한 /1/는 모든 자식의 접두사임에 유의하세요.
마찬가지로 Balbo의 셋째 아이 Ponto에게는 두 명의 자녀가 있으며 /3/1/ 및 /3/2/(으)로 표시됩니다. 다시 아이들 각각은 Ponto에 대해 HierarchyId이(가) 접두사로 붙어 /3/(으)로 나타납니다.
트리 아래에서도 이와 같이 계속됩니다...

다음 코드는 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();

팁

필요한 경우 10진수 값을 사용하여 두 기존 노드 사이에 새 노드를 만들 수 있습니다. 예를 들어 /3/2.5/2/은(는) /3/2/2/ 및 /3/3/2/ 사이에 옵니다.

계층 구조 쿼리

HierarchyId은(는) LINQ 쿼리에서 사용할 수 있는 여러 메서드를 노출합니다.

메서드	설명
`GetAncestor(int n)`	계층 트리에서 노드의 수준을 `n`단계 높입니다.
`GetDescendant(HierarchyId? child1, HierarchyId? child2)`	`child1`보다 크고 `child2`보다 작은 하위 항목 노드의 값을 가져옵니다.
`GetLevel()`	계층 트리에서 이 노드의 수준을 가져옵니다.
`GetReparentedValue(HierarchyId? oldRoot, HierarchyId? newRoot)`	`newRoot`에서 이것으로의 경로와 같은 `oldRoot`에서의 경로를 보유하여 이것을 새 위치로 이동하는 효과가 있는 새 노드의 위치를 나타내는 값을 가져옵니다
`IsDescendantOf(HierarchyId? parent)`	이 노드가 `parent`의 하위 항목인지 여부를 나타내는 값을 가져옵니다.

또한 ==, !=, <, <=, >, >= 연산자를 사용할 수 있습니다.

다음은 LINQ 쿼리에서 이러한 메서드를 사용하는 예제입니다.

트리의 지정된 수준에서 엔터티 가져오기

다음 쿼리는 GetLevel을(를) 사용하여 가계도의 지정된 수준에서 모든 아이를 반환합니다.

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

이는 다음 SQL로 변환됩니다.

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

루프에서 이 작업을 실행하면 모든 세대에 대한 아이를 가져올 수 있습니다.

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

엔터티의 직접 상위 항목 가져오기

다음 쿼리는 GetAncestor을(를) 사용하여 아이의 이름을 감안할 때 아이의 직접 상위 항목을 찾습니다.

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

이는 다음 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)

"Bilbo" 반쪽에 대해 이 쿼리를 실행하면 "Bungo"가 반환됩니다.

엔터티의 직접 하위 항목 가져오기

다음 쿼리는 GetAncestor를사용하지만 이번에는 아이의 이름을 감안할 때 아이의 직접 하위 항목을 찾습니다.

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

이는 다음 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)

"Mungo"의 아이에 대해 이 쿼리를 실행하면 "Bungo", "Belba", "Longo", "Linda"가 반환됩니다.

엔터티의 모든 상위 항목 가져오기

GetAncestor은(는) 단일 수준 또는 실제로 지정된 수준의 수를 검색하는 데 유용합니다. 반면 IsDescendantOf은(는) 모든 상위 항목 또는 종속을 찾는 데 유용합니다. 예를 들어 다음 쿼리는 아이의 이름을 감안할 때 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());

Important

IsDescendantOf은(는) 자신에 대해 true를 반환하므로 위의 쿼리에서 필터링됩니다.

이는 다음 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

"Bilbo" 반쪽에 대해 이 쿼리를 실행하면 "Bungo", "Mungo", "Balbo"가 반환됩니다.

엔터티의 모든 하위 항목 가져오기

다음 쿼리도 IsDescendantOf를 사용하지만, 이번에는 아이의 이름이 다음인 것을 감안하여 아이의 모든 하위 항목에 사용합니다.

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

이는 다음 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()

"Mungo"의 아이에 대해 이 쿼리를 실행하면 "Bungo", "Belba", "Longo", "Linda", "Bingo", "Bilbo", "Otho", "Falco", "Lotho" 및 "Poppy"가 반환됩니다.

공통 상위 항목 찾기

이 특정 가계도에 대해 묻는 가장 일반적인 질문 중 하나는 "Frodo와 Bilbo의 공통 상위 항목은 무엇입니까?"입니다. 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();

이는 다음 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

"Bilbo" 및 "Frodo"를 사용하여 이 쿼리를 실행하면 공통 상위 항목이 "Balbo"임을 알 수 있습니다.

계층 구조 업데이트

일반적인 변경 내용 추적 및 SaveChanges 메커니즘을 사용하여 hierarchyid 열을 업데이트할 수 있습니다.

하위 계층 구조의 부모/자식 관리 변경

예를 들어, 모두 SR 1752의 스캔들(일명 "LongoGate")을 기억할 것입니다. DNA 검사를 통해 Longo가 사실 Mungo의 아들이 아니라 실제로 Ponto의 아들이라는 사실이 밝혀졌었죠. 이 스캔들의 한 가지 결과는 가계도를 다시 작성해야 한다는 것이었습니다. 특히 Longo와 그의 모든 후손들은 상위 항목을 Mungo에서 Ponto로 변경해야 했습니다. GetReparentedValue를 사용하여 이렇게 할 수 있습니다. 예를 들어, 첫 번째로 "Longo"와 모든 하위 항목이 쿼리됩니다.

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

그런 다음 GetReparentedValue를 사용하여 Longo 및 각 하위 항목에 대한 HierarchyId를 업데이트한 다음 SaveChangesAsync를 호출합니다.

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

await context.SaveChangesAsync();

그러면 다음과 같은 데이터베이스 업데이트가 수행됩니다.

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;

다음 매개 변수 사용:

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

참고 항목

HierarchyId 속성에 대한 매개 변수 값은 압축된 이진 형식으로 데이터베이스로 전송됩니다.

업데이트 후 "Mungo"의 하위 항목을 쿼리하면 "Bungo"가 반환됩니다. "Belba", "Linda", "Bingo", "Bilbo", "Falco", "Poppy"는 "Ponto"의 하위 항목을 쿼리하는 동안 "Longo", "Rosa", "Polo", "Otho", "Posco", "Prisca", "Lotho", "Ponto", "Porto", "Peony", "Angelica"를 반환합니다.

함수 매핑

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

추가 리소스

SQL Server의 계층적 데이터

다음을 통해 공유

SQL Server EF Core 공급자의 계층적 데이터

.NET 및 EF Core에서 HierarchyId 사용

계층 모델링

계층 구조 쿼리

계층 구조 업데이트

함수 매핑

추가 리소스

추가 리소스