Issue with Filtering in AspNetCoreOData After Expanding the Object

[anjanasagathiya]

Subject: Issue with Filtering in AspNetCoreOData After Expanding the Object

Problem Description:

We are currently using AspNetCoreOData version 7.20.0 and have encountered an issue with filtering after expanding objects. The problem manifests as a discrepancy in the data returned for specific OData queries, leading to incorrect results.

OData Query 1:

{{Base_URL}}/finance/odata/v2/payments/PaymentOperation.v2.GetByVendorType?vendor_type='publisher'&$filter=id eq 28965 and vendor/id eq 128493&$count=true&orderby=paid_at desc&$expand=bills($expand=offer;$count=true)

Response :
{
    "@odata.count": 1,
    "value": [
        {
            "id": 28965,
            "paid_at": "2023-10-10T00:11:05.113Z",
            "amount": 700.0000,
            "vendor": {
                "id": 128493
            },
            "bills@odata.count": 4,
            "bills": [
                {
                    "paid_amount": 300.0000,
                    "offer": null
                },
                {
                    "paid_amount": 100.0000,
                    "offer": null
                },
                {
                    "paid_amount": 200.0000,
                    "offer": {
                        "campaign_name": "Default",
                        "completed_at": null,
                        "finance_settled_at": null
                    }
                },
                {
                    "paid_amount": 100.0000,
                    "offer": {
                        "campaign_name": "Default",
                        "completed_at": "2023-10-09T03:09:33Z",
                        "finance_settled_at": "2023-10-09T03:09:33Z"
                    }
                }
            ]
        }
    ]
}

OData Query 2:

{{Base_URL}}/finance/odata/v2/payments/PaymentOperation.v2.GetByVendorType?vendor_type='publisher'&$filter=id eq 28965 and vendor/id eq 128493&$count=true&orderby=paid_at desc&$expand=bills($expand=offer;$count=true;$filter=offer/campaign_name eq 'Default')

Response :
{
    "@odata.count": 1,
    "value": [
        {
            "id": 28965,
            "paid_at": "2023-10-10T00:11:05.113Z",
            "amount": 700.0000,
            "vendor": {
                "id": 128493
            },
            "bills@odata.count": 2,
            "bills": [
                {
                    "paid_amount": 200.0000,
                    "offer": {
                        "campaign_name": "Default",
                        "completed_at": null,
                        "finance_settled_at": null
                    }
                },
                {
                    "paid_amount": 100.0000,
                    "offer": {
                        "campaign_name": "Default",
                        "completed_at": "2023-10-09T03:09:33Z",
                        "finance_settled_at": "2023-10-09T03:09:33Z"
                    }
                }
            ]
        }
    ]
}

Issue:

In the first query, we apply the filter id eq 28965 and vendor/id eq 128493, which correctly returns four records in the bills object with a total sum of 700 of paid_amount in amount field.

However, in the second query, when we add a filter to the expanded offer object ($expand=bills($expand=offer;$count=true;$filter=offer/campaign_name eq 'Default')), it returns only two records in the bills object. Despite this, the amount field at the root level incorrectly remains 700 instead of 300, reflecting the sum of the filtered records.

Request for Assistance:

We seek guidance on how to correctly calculate and display the amount field in the API response when using AspNetCoreOData v7.20.0, given the current constraints. Specifically, we need:

1. Any alternative options or approaches to achieve the desired result.
2. Code examples or snippets demonstrating the correct implementation for object expansion and filtering.

We appreciate any insights or solutions that can help resolve this issue efficiently.