Join multiple times to the same table using LLBLGen

Question

I have a table, let call it Widget, with (among others) person1id and person2id. (Yes, I suppose I should have setup a N-N relation table, but so far we never have more than 2 persons on a single widget. )

Person1Id (and person2id of course) are linked to a person table with yes another link to a PersonDetail table.

How can I query a list of Widgets with 2 persons and 2 persondetails, filtering on a persondetail field? If I had just one personid in my widget I would do:

RelationCollection relationsToUse = new RelationCollection();
relationsToUse.Add(WidgetEntity.Relations.PersonEntityUsingPerson1Id);
relationsToUse.Add(PersonEntity.Relations.PersonDetailsEntityUsingDetailId);
PredicateExpression filter = new PredicateExpression(new FieldCompareValuePredicate(PersonDetailsFields.ModifiedDate, ComparisonOperator.GreaterEqual, startdate)); //whatever
var list = new WidgetCollection();
list.GetMulti(filter, relationsToUse);

So how do I get the second relation in? relationsToUse.Add(WidgetEntity.Relations.PersonEntityUsingPerson2Id); ?

I'm using LLBLgen 2.6 with .net 3.5. I did see the related question here but it's not the same.

Answer 1

You just need to specify an alias for each of the relations you add - if I remember correctly theres something in the LLBLGen doc file that talks about this.

This is taken from the LLBLGen 2.6 docs, for "advanced filtering", you can see how to join onto the same table twice and use aliases to control your filters:

...The solution is to alias the entities in the relation added to the RelationCollection, and also to use the same alias in a predicate. If you omit an alias, it is considered not aliased and if you have aliased an entity in an earlier added relation to the same RelationCollection, it will be considered a different entity in the join list. So aliassing Customer to "C" in the first relation and in the second relation you do not specify an alias for Customer, you'll get 2 times a Customer entity in the join list. So use aliassing with care.

Our example of Customer and the two Address entities with the two City predicates will result in the following code. Notice the alias usage in the predicates as well.

// C#
IRelationPredicateBucket bucket = new RelationPredicateBucket();
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingVisitingAddressID, "VisitingAddress");
bucket.Relations.Add(CustomerEntity.Relations.AddressEntityUsingBillingAddressID, "BillingAddress");
bucket.PredicateExpression.Add((AddressFields.City.SetObjectAlias("VisitingAddress")=="Amsterdam") &
     (AddressFields.City.SetObjectAlias("BillingAddress")=="Rotterdam"));
EntityCollection customers = new EntityCollection(new CustomerEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter();
adapter.FetchEntityCollection(customers, bucket);