I have the following query which I'm trying to execute via llblgen

SELECT * FROM OrganizationPage WHERE 
    (SELECT lineage FROM OrganizationPage WHERE OrganizationPageId = 6) LIKE lineage + '%'
ORDER BY lineage

I've tried doing this in llblgen

IRelationPredicateBucket bucket = new RelationPredicateBucket();
ISortExpression sort = new SortExpression();
sort.Add(SortClauseFactory.Create(OrganizationPageFieldIndex.Lineage,SortOperator.Ascending));

bucket.PredicateExpression.Add(PredicateFactory.CompareValue(OrganizationPageFieldIndex.OrganizationPageId,ComparisonOperator.Equal,6));
bucket.PredicateExpression.Add(PredicateFactory.Like(OrganizationPageFieldIndex.Lineage,"%"));
        
EntityCollection orgPages = new EntityCollection(new OrganizationPageEntityFactory());
EntityCollection collection = OrganizationPageManager.FetchCollection(orgPages, bucket, 0, sort);

I believe it's this line that I'm having problems with

(SELECT lineage FROM OrganizationPage WHERE OrganizationPageId = 6) LIKE lineage + '%'
     foreach(OrganizationPageEntity page in collection)
            Console.WriteLine(page.Name + " " + page.Lineage);

Any suggestions? Thanks

Your query is very odd, what does it do exactly? (not that it doesn't work, it's just not clear to me). If I understand it correctly it won't be possible to perform this query, though perhaps it can be formulated differently

The query is used to create "navigation breadcrumbs" from a table. The table is a representation of a tree which consists of the following columns:

Id ParentId Name Depth Lineage

For a better explanation, check out, the following article:

Tree structures in Asp.net http://www.developerfusion.com/show/4633/6/

You will drop into the middle of the article which is where I got the previous stored procedure from.

Thanks

Ok, that query is pretty silly. Why not do:

SELECT * FROM dfTree WHERE
    lineage LIKE '%/' + name
ORDER BY lineage, name

Also, because they're interested in the NODE's, you can also do:

string[] nodeNames = lineage.Split('/');
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareRange(TreeFieldIndex.Name, nodeNames));

and then use that filter to fetch the nodes. Their query is pretty odd and IMHO pretty slow.

But perhaps my query is not working, though the comparerange predicate will work.

thanks frans, with a little manipulation to fit my needs I got the CompareRange predicate to work great. Thanks.

fyi, the first sql statement you mentioned, I could not get to work. No need though. Thanks again.

erichar11 wrote:

thanks frans, with a little manipulation to fit my needs I got the CompareRange predicate to work great. Thanks.

fyi, the first sql statement you mentioned, I could not get to work. No need though. Thanks again.

The argument for the like should get the name of the current node (which is the end of the breadcrumb or lineage) and you have to prefix that with '%/'. So pass "%/" + name as like pattern. But if the other option works, why bother .