Query using HierarchyId

[muzieh]

Hi,
I'm trying to build Linq query using HierarchyId.IsDescendantOf() against Data Access Model.

int folderTreeId = 123;

var parentId = (from folder in context.FolderInfos

                where folder.FolderTreeID == folderTreeId

                select folder.FolderHierarchyID).Single();

 

var folders = from folder in context.FolderInfos

                where folder.FolderHierarchyID.IsDescendantOf(parentId).Value == true

                select folder;

First query works fine and returns SqlHierarchyId type. Second returns an exception because generated SQL contains invalid WHERE clause.
(FROM [iRooms].[FolderInfo] a WHERE a.[FolderHierarchyID].IsDescendantOf(?) = (1=1))

Is it possible to write such queries with Linq and HierarchyId? If not, what would be other option to write similar query using Data Access.
Regards,
Marcin

[Doroteya]

Hello Marcin,

Thank you for contacting us and I am sorry for the inconvenience you experience.

Indeed, the support for SqlHierarchyId of Telerik Data Access does not offer server side execution of the IsDescendentOf() extension method. To workaround this, you need to perform the filtering on the client side. In other words, you need to load all the records from the FolderInfo table in-memory and to build the IsDescendentOf() clause in a following statement. For example:

int folderTreeId = 123;
var parentId = (from folder in context.FolderInfos
                where folder.FolderTreeID == folderTreeId
                select folder.FolderHierarchyID).Single();

var allFolders = (from folder in context.FolderInfos
                    select folder).ToList();

var folders = (from f in allFolders
                where f.FolderHierarchyID.IsDescendantOf(parentId).Value == true
                select f).ToList();

I hope this helps. Let us know, if you need further information.


Regards,
Doroteya
Telerik

 

 

[muzieh]

Hi Doroteya,
Thank you very much for your answer. Unfortunately or fortunately for me I was able to trick Telerik Data Access with the following Linq expression. And it seems it gives proper SQL statement.

1.var folders = from folder in context.FolderInfos
2.    where folder.FolderHierarchyID.IsDescendantOf(parentHierarchyId).Equals(1)
3.    select folder;

Kind Regards

[Phil]

Thanks Muzieh,

You are a god-send. We have multi-'000,000 row recordsets, so you can imagine what we thought of the 'work-around' to just do it client side. Not sure what would be the point of supporting sqlHierarchyID if you don't support IsDescendantOf(). Isn't that the main use of a hierarchyID?

Thank you so much for your contribution!

[Doroteya]

@Muzieh,

Thank you for getting back to us.

I tested your suggestion, and let me confirm that it is a workaround that allows you to execute the query in question on the server side. Built this way, Telerik Data Access generates the necessary SQL.

@Phil,

I am sorry for the inconvenience you experienced.

I am glad you found a solution that fits better in your scenario, despite the fact that currently our support of HierarchyID is limited.


Do let us know if you need further information.


Regards,
Doroteya
Telerik