Query using HierarchyId

5 posts, 0 answers
  1. muzieh
    muzieh avatar
    2 posts
    Member since:
    Apr 2014

    Posted 21 Aug 2014 Link to this post

    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
  2. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 27 Aug 2014 Link to this post

    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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
  3. DevCraft banner
  4. muzieh
    muzieh avatar
    2 posts
    Member since:
    Apr 2014

    Posted 28 Aug 2014 in reply to Doroteya Link to this post

    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
  5. Phil
    Phil avatar
    1 posts
    Member since:
    Feb 2014

    Posted 26 Oct 2014 Link to this post

    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!
  6. Doroteya
    Admin
    Doroteya avatar
    502 posts

    Posted 27 Oct 2014 Link to this post

    @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
     
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
     
Back to Top
DevCraft banner