Stack Overflow
Stack Overflow

Questions tagged [hierarchyid]

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

152 questions
0
votes
0 answers

EF Core Transactions, select a value before updating; Moving a HierarchyId Node to a different parent

The suggested SQL code by Microsoft to move a HierarchyId node is: CREATE PROCEDURE MoveOrg(@oldMgr nvarchar(256), @newMgr nvarchar(256) ) AS BEGIN DECLARE @nold hierarchyid, @nnew hierarchyid SELECT @nold = OrgNode FROM…
Aktaeon
  • 189
  • 2
  • 14
0
votes
1 answer

How could I serialize hierarchyid in Microsoft.AspNetCore.OData 8.x

Using Microsoft.AspNetCore.OData 8.2.0, I'm trying to return a class with a HierarhyId property public class Account { [Key] public int Id { get; set; } public HierarchyId Level { get; set; } = null!; ... } I added the support for…
cflorenciav
  • 419
  • 1
  • 6
  • 16
0
votes
0 answers

HierarchyId in Clean Architecture

I want to migrate an existing .NET project to the Clean Architecture model but the current EF Core entities are using the HierarchyId data type (from the EntityFrameworkCore.SqlServer.HierarchyId package). This type provides features that are useful…
daltonwide
  • 83
  • 8
0
votes
2 answers

Select records from structure excluding below specific records

I have the need to select records from a SQL database but skip all records below records with specific values in the column. Example data from multiple joined tables: POSITION CODE VALUE 1 A001 No 2 A002 …
TravelDuck
  • 29
  • 3
0
votes
0 answers

HierarchyId and Alphabetical order of new record

I am playing with the HierarchyId columns in SQL Server in order to decide whether to use them in a new project. I read some articles, watched some tutorials, I did some tests and the basic concepts are clear to me. However, I have a problem…
skysurfer
  • 721
  • 2
  • 5
  • 23
0
votes
1 answer

Display full Product Category Path using HierarchyID and Recursive CTE in SQL Server

I have only found this using a classic ParentID foreign key method. I wonder how to display full hierarchy path using the HierarchyID data type in SQL Server 2019. I have this setup (simplified for this example): CREATE TABLE tbl2ProductCategories…
ThomassoCZ
  • 73
  • 6
0
votes
1 answer

How do I implement HierarchyId in EF Core 6 in C#

I am developing a simple project management system and needed to manage the WBS (basicly a hierarchy of tasks). I spent several days combing through blog after blog after blog to figure out how to setup the HierarchyId using EF Core and SQL Server.…
USMC6072
  • 268
  • 2
  • 14
0
votes
1 answer

Use hierarchyid to store address of a customer

I have a table named 'AddressDemo' to store address of a customer with the following fields, CREATE TABLE [dbo].[AddressDemo]( [AddressID] [int] IDENTITY(1,1) NOT NULL, [State] [nvarchar](50) NULL, [District] [nvarchar](50) NULL, [Taluk]…
Rauf
  • 12,326
  • 20
  • 77
  • 126
0
votes
1 answer

Sql hierarchyID - finding the lowest child (last node) value

Lets say i have this data in my db / /1/ /1/1/ /1/2/ /1/2/1/ /2/1/ /2/1/1/ /2/2/1/ I want to get for each row the last child within the hirarchyId I have tried to use the getdecendent and getancestor but it wont gives me what i need I have…
Y.G.J
  • 1,098
  • 5
  • 19
  • 44
0
votes
1 answer

.NET Core 3.1/5 and SQL Server HierarchyId

How I can generate HierarchyId automatically/programmatically in .NET Core, I set a Category as another's child and expect the HierarchyId field to be updated automatically but nothing happens. Do I need a method to calculate and update it…
Mertez
  • 1,061
  • 3
  • 14
  • 38
0
votes
1 answer

Get leaf node in a tree built using HierarchyId in SQL Server

I am using the HierarchyId data type in SQL Server. I must write some queries. The table consists of countries, regions and provinces (or districts). I must write two queries: I must return all nearest descendants. I must return all leafs. I…
Simone
  • 2,304
  • 6
  • 30
  • 79
0
votes
1 answer

Making child hierarchyid from parent path using Entity Framework

I have an object like below: roleNode = //child hierarchyid, code = model.code, name = model.name, created = now, createdById = Convert.ToInt32(HttpContext.User.FindFirst(CrmClaimTypes.UserId).Value), updated = now, updatedById =…
Whiskey Truong
  • 1
0
votes
1 answer

How to insert a new record into a table with hierarchyid

I’m learning how to deal with hierarchical data using hierarchyid. Have read a tutorial. The demo data is composed of precalculated hierarchyids. I’m familiar with parent/child tables using IDENTITY (1, 1) in the primary key. I understand that I…
JoePR
  • 3
  • 1
0
votes
1 answer

How to to copy hierarchyid subtree

I want to copy a subtree e.g. into the same table, at the next free most-right spot (in this case at /5/...). Is there any stored procedure which may help me do this? If not: how would I do it? I'm relatively new to SQL so any help will be…
fmi21
  • 485
  • 3
  • 15
0
votes
0 answers

"InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlHierarchyId" in .net core 3.1

I try to upgrade my project from .net core sdk 2.2 to .net core sdk 3.1. My model class include this "public SqlHierarchyId id". In .net core sdk 2.2, SqlHierarchyId works fine with "using Microsoft.SqlServer.Types". namespace Entity { using…
xangkcl
  • 57
  • 1
  • 7
1 2 3
…
10 11