Help with Aurelius Table Joins

[Stevens_Mark2 (Stevens Mark)]

I'm new to XData and Aurelius but am struggling a bit to get a table join to work.

I'm working on a simplified version for what I want to do but here are the basics:

I have two tables in a SQL Server database: Persons and Followers. The classes are created by the Aurelius automatic generate.

Persons:

Followers:

It's a one to many relationship. Foreign keys are setup to link Followers and Followees to Persons.

What I want to do is to retrieve Persons where either FollowerID = x or FolloweeID = y. I tried this but it doesn't like the "Followers":

Persons := TXDataOperationContext.Current.GetManager.Find
.Where(Linq['Followers/FollowerID'] = 2)
.List;

Any help would be greatly appreciated.

Thanks

[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)

[Stevens_Mark2 (Stevens Mark)]

Many thanks. That's just what I needed.

Incidentally, from what I've seen, I can appreciate the power and simplicity of Aurelius. Once I get my head around the setting up and syntax, I'm going to find it very useful.