How to correctly query OData with filter in nested type based on property from parent type?

I am getting a dynamic value (_FilterDate) on the parent type that I want to use as a filter for the nested type /Trips but can't get it to work because I still get entries in the nested data that do not meet the filter. Actually, there is no difference whether I use this filter or not.

$filter=Trips/all(d:d/EndDate ge _FilterDate)

I also tried this:

$expand=Trips($filter=EndDate ge $it/_FilterDate)

but got the error: "Could not find a property named '_FilterDate' on type 'Default.Trips'."

So I'm wondering how to get the syntax right and thus kindly ask for help.

Example portion:

"value": [
       {
           "_FilterCompany": "YES",
           "_FilterLocation": "YES",
           "_FilterDate": "2020-01-08",
           "Trips": [
               {
                   "StartDate": "2019-06-24",
                   "EndDate": "2019-06-28",
               },
               {
                   "StartDate": "2020-02-07",
                   "EndDate": "2020-02-07",
               }         
                     ]
       }

Solution

There are two issues going on here:

this response is specifically regarding the OData v4 specification and the .Net ODataLib implementation.

You have correctly identified that when filtering results based on a nested collection you you must separately apply the filter within that collection if you want the fitler to apply to the items within that collection as well.
- This is because the root level $filter criteria only affects the selection of the root items, think of it as if the $expand operator is applied after the $filter has identified the top level of row the return, $expand is simply executed as a Linq Include statement.
In your second attempt, $it references the instance of Trips, which is a known bug/by design, according to the spec it is expected to work the way you have implemented it:
5.1.1.6.4 $it
Example 82: customers along with their orders that shipped to the same city as the customer's address. The nested filter expression is evaluated in the context of Orders; $it allows referring to values in the outer context of Customers.
```
http://host/service/Customers?
    $expand=Orders($filter=$it/Address/City eq ShipTo/City)
```
So knowing the $it is broken, the spec doc does specify a $root identifier that you might also be able to use, but in ODataLib 7.3 $root is still not supported OOTB either. There is an issue logged here: $it references the wrong resource #616

Workaround

If your Trips data type has a navigation property back to the Filter/root record, then you can use that navigation property as part of the $filter:

Assuming the navigation property is called Filter

$filter=Trips/all(d:d/EndDate ge _FilterDate)&$expand=Trips($filter=EndDate ge Filter/_FilterDate)

If your Trips type does not have this navigation link back to the parent record then you are stuck at this stage with these two workarounds:

Create a Function on the controller to return this filtered data specifically, as this would be simple to evaluate as a Linq query in the server-side.
Accept that the server will return extra rows in the Trips collections, and apply the filter over the results in the client-side.