Filtering on a One-To-Many Relationship


How can I filter on a one-to-many relationship?

    [ManyValuedAssociation([TAssociationProp.Lazy], CascadeTypeAll)]
    [ForeignJoinColumn('ProspGUID', [TColumnProp.Required])]
    FProspectsSC: Proxy<TList<TProspectsSC>>;

  [Id('FGUID', TIdGenerator.Guid)]
  TProspectsSC = class
    [Column('"GUID"', [TColumnProp.Required])]
    FGUID: TGuid;

    [Column('ProspGUID', [])]
    FProspGUID: Nullable<TGuid>;

    [Column('ClassID', [])]
    FClassID: Nullable<Integer>;

    [Column('SubID', [])]
    FSubID: Nullable<Integer>;
    property GUID: TGuid read FGUID write FGUID;
    property ProspGUID: Nullable<TGuid> read FProspGUID write FProspGUID;
    property ClassID: Nullable<Integer> read FClassID write FClassID;
    property SubID: Nullable<Integer> read FSubID write FSubID;

I am using the filter:

ProspectsSC/ClassID eq 1 and ProspectsSC/SubID eq 4

But all of the expanded ProspectSC's are still listed e.g.

     "ProspectsSC": [
          "$id": 2,
          "GUID": "703F6B16-9172-4DE4-B0F3-A017C1691BB0",
          "ProspGUID": "34AA4C80-6545-4435-8907-7C06C8E1D8F7",
          "ClassID": 1,
          "SubID": 1
          "$id": 3,
          "GUID": "05088E0E-D7D1-4D8F-90ED-3CA5071626C3",
          "ProspGUID": "34AA4C80-6545-4435-8907-7C06C8E1D8F7",
          "ClassID": 1,
          "SubID": 2
          "$id": 4,
          "GUID": "D3CD226D-7AD6-49B9-AB5B-1B35D45AC1CF",
          "ProspGUID": "34AA4C80-6545-4435-8907-7C06C8E1D8F7",
          "ClassID": 1,
          "SubID": 3



The filter you use affects how the master (parent) records will be returned.

The child/detail lists will always be retrieved individually in separated SQL statements after the parent/master records.

So you can't filter then, unless you use the Where attribute to predefine a filter at mapping time, and then enable the filter per manager. The process is described in the chapter about global filters.

Another option is simply doing a direct query in TProspectsSC class.

Any other ideas?

I am using a 3rd party grid to display the results which uses OData so need to use a select e.g.


I don't understand how things related? What does the grid has to do with it?

Any other ideas?

The more flexible way is to create your own service operation.

The grid is doing the filter. I think I will pre-process this in a service operation and get a list of GUID's to use that in the filter

1 Like