Help with Aurelius Table Joins

[wlandgraf (Wagner Landgraf)]

Followers can't be used in that expression because there is no Followers association in your TPersons class. In the generated mapping, the associations go in the opposite direction: TFollowers has FollowerID and FolloweeID associations pointing to TPersons

If what you want is to find persons through the Followers table, one option is to add the corresponding many-valued associations to TPersons. For example, conceptually you could have two associations, since Followers refers to Persons twice:

[ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll, 'FFollowerID')]
FFollowers: Proxy<TList<TFollowers>>;

[ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll, 'FFolloweeID')]
FFollowees: Proxy<TList<TFollowers>>;

Then you can create aliases/use criteria over those associations. When filtering through a many-valued association, remember that the join can produce duplicate TPersons rows, so RemovingDuplicatedEntities is usually needed. This is the same approach illustrated in this previous topic:

How can I get a List from the Server??

Create a ManyValuedAssociation in TItems class pointing to Tp_iteminzet: [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll, 'FItemid')]FItemzetList: Proxy<TList<Tp_iteminzet>>; Then find TItems objects using distinct but where the association is not null: Result := OM.Find<TItems>  .CreateAlias('Itemzetlist', 'p')  .Where(not TLinq.IsNull('p.p_itemzetid'))  .RemovingDuplicatedEntities  .List More info: http://www.tmssoftware.biz/business/aurelius/doc/web/index.html?manyvaluedassoci…

Alternatively, especially if you don't want to change the generated mappings, using an SQL expression with EXISTS is often simpler for this type of query. For example:

Persons := TXDataOperationContext.Current.GetManager.Find<TPersons>
  .Where(
    Linq.Sql(
      'exists (select 1 from Followers f ' +
      'where (...conditions involving f.FollowerID, f.FolloweeID and {ID}...))'
    )
  )
  .List;

{ID} in the SQL expression refers to the mapped ID property of the TPersons entity, so it can be used to correlate the subquery with the person being retrieved.

We have discussed the EXISTS approach for many-valued associations here as well:

Filtering data with two many valued associations

In this case you should use SQL expressions, using, for example, EXISTS clause. Pseudo-code: Find<TAnagrFiscale> .Where(Linq.Sql('Exists SELECT * FROM AnagrIndirizzo AI WHERE field like '%' and AI.Id = {ID}'))

The exact condition inside the EXISTS depends on what you mean by "retrieve Persons where either FollowerID = x or FolloweeID = y". For example, if FollowerID = x means that you want to return the followees of x, and FolloweeID = y means that you want to return the followers of y, then the correlated condition would be roughly:

(f.FollowerID = :x and f.FolloweeID = Persons.ID)
or
(f.FolloweeID = :y and f.FollowerID = Persons.ID)