select managed users recursively

Posts   
 
    
hotchill avatar
hotchill
User
Posts: 180
Joined: 22-Jan-2007
# Posted on: 26-Sep-2012 10:42:29   

Hi.

I am on llblgen v3.5/sql server 2008 using llblgen framework with adapter template.

I have a table User with PK UserId and FK to self ManagerUserId.

I want to select all users managed by a user, recursively. This can be done in sql like this:

WITH ManagadUsers AS (
   SELECT u.* FROM [User] u WHERE u.ManagerUserId = '18E30484-32E6-498C-A5E3-1A1EF8F2AD67' and u.UserId <> u.ManagerUserId
   UNION ALL
   SELECT u2.* FROM [User] u2 JOIN ManagadUsers mu ON mu.UserId = u2.ManagerUserId)
SELECT mu2.* FROM ManagadUsers mu2

How do I fetch an entity collection of managed users recursively in LLBLGen?

Thanks, Tore.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 26-Sep-2012 20:02:26   

Could not be done unless you need to grab definitive number if levels deep. If so using PrefetchPaths should take care of it.

Otherwise you may just select the entire table, and do the recursive filtering at the client side.