Hello there

I have one table A.

Table A PathIndex, OrderIndex, NodeID 1,0,Node1 1,1,Node2 1,2,Node3 2,0,Node1 2,1,Node4 2,2,Node5 3,0,Node6 3,1,Node7 3,2,Node8 4,0,Node1 4,1,Node2 4,2,Node9 5,0,Node10 5,1,Node2 5,2,Node11 6,0,Node11 6,1,Node3 6,2,Node2

I would like with one statement to filter on a certain node in OrderIndex = 1 and return the OrderIndex = 0 of the same PathIndex, and I would like this collection to be unique.

If I were to get Node2 in OrderIndex=1 I should get the following collection back. 1,0,Node1 5,0,Node10

Could someone please point me in the write direction?

Did you miss out (4,0,Node1) from the results? I'm not sure I understand your query.

Maybe if you can write the SQL Query, so we can tell you how to use LLBLGen Pro to perform this query.

I'm guessing here, I think the SQL might be:

SELECT * FROM TableA WHERE OrderIndex = 0 AND PathIndex IN (SELECT PathIndex FROM TableA WHERE OrderIndex = 1 AND NodeID = 'Node2')

If this is the correct query then you need to use a FieldCompareSetPredicate for the IN predicate.

Please refer to the LLBLGen Pro manual: "Using the generated code -> Adapter/ Slef Servicing -> Filtering & sorting -> The predicate system"

Hi Walaa

Thank you for your response.

The note you gave about missing 4,0,Node1 is not correct, I do not was 4,0,Node1 to show up, since 1,0,Node1 is already in the collection. Since 1,0,Node1 is related to x,1,Node2 I do not care about another instance linking Node1 to Node2.

I read the entry on the manual about the FieldCompareSetPredicate and IN predicate. However I was not able to grasp it. On the query you noted above could you give me example for that query? I believe with an example closer to my issue I might have a chance or taking this knowledge in.

Thank you, Bruno

Hello,

if you want to convert the sql request that Walaa gives youcan make this :

dim rpb as new RelationPredicatBucket
rpb.PredicateExpression.Add(New FieldCompareSetPredicate( _
    AFields.PathIndex, Nothing, AFields.PathIndex, Nothing, _
    SetOperator.In, _
    ((OrderIndex = 1) AND (NodeID = 'Node2'))))
rpb.predicateExpression.add(OrderIndex = 0)

Then you can apply this rpb on fetching and you will have the two conditions written by Walaa.

Thank you very much.

I ended up with this very nice piece of code that does exactly what I needed.

IRelationPredicateBucket bucket = new RelationPredicateBucket();
Guid guidNodeID = new Guid("05000000-2000-0101-0000-000000000001");

/// connect the PathIndexes then look for my NodeID and only when the NodeID equals the second possition on the path. Meaning
/// it would be one hop alway from the FCP.
bucket.PredicateExpression.Add(new FieldCompareSetPredicate(TableFields.PathIndex, null, TableFields.PathIndex, null, 
    SetOperator.In, ((TableFields.NodeID == guidNodeID)&(TableFields.OrderIndex == 2))));

/// only return entries which have the order Index greater than 2
bucket.PredicateExpression.Add(
    new PredicateExpression((TableFields.NodeID == new Guid("03000000-2000-0101-0000-000000000001")) &
        (TableFields.OrderIndex < 2)));

EntityCollection tableCollection = new EntityCollection(new SiteRoutingNodeEntityFactory());

using (DataAccessAdapter dataAccessAdapter = new DataAccessAdapter(ConnectionString.GetDatabaseServer()))
{
    dataAccessAdapter.FetchEntityCollection(tableCollection, bucket);
}

I am really amazed on the level of technical help from this Forum. I feel free here to ask any question without been called names. Very professional, a tip of the hat to you guys.

Thanks again, Bruno

Thanks Bruno!