Hello, I'm new to LLBLGen Pro (still using demoversion).

I'd like to do some filtering on related fields and I can't get it to work. I'm using Selfservicing, LLBLGen 2.5, .NET 2.5.

I have 3 tables (with their relevant fields): tPerson (PersonID) tGroupPerson (PersonID, GroupID) tGroup (GroupID) - added for clarity but not used in example

I like to find all persons which are in group1 AND group2 AND group3.

So far I came up with the next (not working) simplefied code:

            PredicateExpression filter = new PredicateExpression();
            myRelations = new RelationCollection();
            myRelations.Add(TPersonEntity.Relations.TGroupPersonEntityUsingPersonId);
            TPersonCollection persons = new TPersonCollection();

            filter.AddWithAnd(TGroupPersonFields.GroupId == 1);
            filter.AddWithAnd(TGroupPersonFields.GroupId == 2);
            filter.AddWithAnd(TGroupPersonFields.GroupId == 3);

            persons.GetMulti(filter, myRelations);

This ends up with zero persons, which is logical if you think of a database join.

I think I need cascading filtering of the persons Collection. So, first filter on GroupId =1, then filter the result on GroupId=2, etc. But I can't get this to work.

Can anybody give me a hint on how to do this?

This ends up with zero persons, which is logical if you think of a database join.

First think of it in terms of SQL.

Post the required SQL statement here, so we can help you formulate it in LLBLGen Pro code.

So the tPerson table has 3 joins (but this could be more) with the tGroupPerson table.

SELECT tPerson.PersonID FROM tGroupPerson INNER JOIN tPerson ON tGroupPerson.PersonID = dbo.tPerson.PersonID INNER JOIN tGroupPerson AS tGroupPerson_1 ON tPerson.PersonID = tGroupPerson_1.PersonID INNER JOIN tGroupPerson AS tGroupPerson_2 ON dbo.tPerson.PersonID = tGroupPerson_2.PersonID WHERE (dbo.tGroupPerson.GroupID = 1) AND (tGroupPerson_1.GroupID = 2) AND (tGroupPerson_2.GroupID = 3)

Hi arjen,

You should proceed on this using aliases. Here is an approximate code:

PredicateExpression filter = new PredicateExpression();
myRelations = new RelationCollection();
myRelations.Add(TPersonEntity.Relations.TGroupPersonEntityUsingPersonId, "GP1");
myRelations.Add(TPersonEntity.Relations.TGroupPersonEntityUsingPersonId, "GP2");
myRelations.Add(TPersonEntity.Relations.TGroupPersonEntityUsingPersonId, "GP3");

TPersonCollection persons = new TPersonCollection();

filter.AddWithAnd(TGroupPersonFields.GroupId.SetObjectAlias("GP1") == 1);
filter.AddWithAnd(TGroupPersonFields.GroupId.SetObjectAlias("GP2") == 2);
filter.AddWithAnd(TGroupPersonFields.GroupId.SetObjectAlias("GP3") == 3);

persons.GetMulti(filter, myRelations);

Hope helpful

This is what I was looking for. It worked perfectly. So much to learn still about LLBLGen

Thanks daelmo and Walaa.