Filtering data with two many valued associations

BIZ TMS Aurelius
1

Hello,
I have these classes in my structure:

 [Entity]
  [Table('AnagrContatto')]
  [Sequence('Seq_ID_Contat')]
  [Id('FID', TIdGenerator.IdentityOrSequence)]
  TAnagrContatto = class
  private
    [Column('ID', [TColumnProp.Required])]
    FID: Integer;

    [Column('Posizione', [])]
    FPosizione: Nullable<Integer>;

    [Column('Cognome', [], 50)]
    FCognome: Nullable<string>;

    [Column('Nome', [], 50)]
    FNome: Nullable<string>;

    [Column('CF', [], 16)]
    FCF: Nullable<string>;

    [Column('Telefono', [], 50)]
    FTelefono: Nullable<string>;

    [Column('Cellulare', [], 50)]
    FCellulare: Nullable<string>;

    [Column('email', [], 100)]
    Femail: Nullable<string>;

    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('ID_Anagr', [TColumnProp.Required], 'ID')]
    FAnagr: Proxy<TAnagrFiscale>;

    function GetAnagr: TAnagrFiscale;
    procedure SetAnagr(const Value: TAnagrFiscale);
  public
    constructor Create;
    destructor Destroy; override;

    function GetIDTpTitolo: Integer;
    function GetIDTpContatto: Integer;
    function GetIDTpRuolo: Integer;
    function tostring: string;

    property Id: Integer read FID write FID;
    property Posizione: Nullable<Integer> read FPosizione write FPosizione;

    property Cognome: Nullable<string> read FCognome write FCognome;
    property Nome: Nullable<string> read FNome write FNome;
    property CF: Nullable<string> read FCF write FCF;
    property Telefono: Nullable<string> read FTelefono write FTelefono;
    property Cellulare: Nullable<string> read FCellulare write FCellulare;
    property email: Nullable<string> read Femail write Femail;

    property Anagr: TAnagrFiscale read GetAnagr write SetAnagr;
  end;
  
  [Entity]
  [Table('AnagrIndirizzo')]
  [Sequence('Seq_ID_Indir')]
  [Id('FID', TIdGenerator.IdentityOrSequence)]
  TAnagrIndirizzo = class
  private
    [Column('ID', [TColumnProp.Required])]
    FID: Integer;

    [Column('Posizione', [])]
    FPosizione: Integer;

    [Column('Denomiazione', [], 255)]
    FDenomiazione: string;

    [Column('Indirizzo1', [], 100)]
    FIndirizzo1: string;

    [Column('Indirizzo2', [], 100)]
    FIndirizzo2: string;

    [Column('CAP', [], 10)]
    FCAP: string;

    [Column('Citta', [], 100)]
    FCitta: string;

    [Column('Provincia', [], 2)]
    FProvincia: string;
	
    [Association([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAll - [TCascadeType.Remove])]
    [JoinColumn('ID_Anagrafica', [TColumnProp.Required], 'ID')]
    FAnagr: Proxy<TAnagrFiscale>;
	
    function GetAnagr: TAnagrFiscale;
    procedure SetAnagr(const Value: TAnagrFiscale);
  public
    property Id: Integer read FID write FID;
    property Posizione: Integer read FPosizione write FPosizione;
    property Denomiazione: string read FDenomiazione write FDenomiazione;
    property Indirizzo1: string read FIndirizzo1 write FIndirizzo1;
    property Indirizzo2: string read FIndirizzo2 write FIndirizzo2;
    property CAP: string read FCAP write FCAP;
    property Citta: string read FCitta write FCitta;
    property Provincia: string read FProvincia write FProvincia;
    property Anagr: TAnagrFiscale read GetAnagr write SetAnagr;
  end;

[Entity]
[Table('AnagrFiscale')]
[Sequence('Seq_ID_Anagr')]
[Id('FID', TIdGenerator.IdentityOrSequence)]
TAnagrFiscale = class
private
	[Column('ID', [TColumnProp.Required])]
	FID: Integer;

	[Column('Ragione_Sociale', [], 255)]
	FRagione_Sociale: string;

	[ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAllRemoveOrphan, 'FAnagr')]
	[OrderBy('POSIZIONE ASC')]
	FAnagrContattoList: Proxy<TList<TAnagrContatto>>;

	[ManyValuedAssociation([TAssociationProp.Lazy, TAssociationProp.Required], CascadeTypeAllRemoveOrphan, 'FAnagr')]
	[OrderBy('POSIZIONE ASC')]
	FAnagrIndirizzoList: Proxy<TList<TAnagrIndirizzo>>;

	function GetAnagrContattoList: TList<TAnagrContatto>;
	function GetAnagrIndirizzoList: TList<TAnagrIndirizzo>;
public
	constructor Create;
	destructor Destroy; override;

	property Id: Integer read FID write FID;
	property Ragione_Sociale: string read FRagione_Sociale write FRagione_Sociale;
	property AnagrIndirizzoList: TList<TAnagrIndirizzo> read GetAnagrIndirizzoList;
	property AnagrContattoList: TList<TAnagrContatto> read GetAnagrContattoList;

end;

Now I'd like to apply a filter where I have to find a TAnagrFiscale having one or more AnagrIndirizzo.Indirizzo1 like a value AND one or more AnagrContatto.Telefono equal to another value. If I have to apply a filter only on one of the two classes I don't see any trouble, I can use something like ObjectManager.Find.CreateAlias(....).where(Linq['Indirizzo1'].like('%%%%'), but if I have to apply this filter in both classes, how can I do? And, if it's possible, how can I integrate it with XData?

Thanks in advance.

2

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}'))