Error during $expand operation on to_Description: "Can't expand 'to_Description' as it has no foreign keys"

[KillianD1]

Description of erroneous behaviour

When performing a READ operation on the Products entity with an $expand on to_Description, the following error is returned:

{
    "error": {
        "code": "500",
        "message": "Can't expand 'to_Description' as it has no foreign keys"
    }
}

However, if the code that handles the READ operation is commented out, the response is returned successfully. This suggests an issue when connecting to the external API in combination with the $expand functionality.

Detailed steps to reproduce

1. Click on the GitHub link at the bottom of the page.
2. Run npm install to install dependencies.
3. Start the service using cds-ts w.
4. Make the following OData query: GET http://localhost:4004/odata/v4/test/Products?$select=Product&$count=true&$orderby=Product&$expand=to_Description($select=Language,Product,ProductDescription)&$skip=0&$top=46
5. The error 500: Can't expand 'to_Description' as it has no foreign keys is returned
6. If the READ event handler is commented out:

/*const productsAPI = await cds.connect.to('Product');
this.on('READ', Products, (req) => {
     return productsAPI.run(req.query);
});*/

the same request returns the correct data:

{
    "@odata.context": "$metadata#Products",
    "@odata.count": 7,
    "value": [
        {
            "Product": "1",
            "to_Description": [
                {
                    "Language": "EN",
                    "Product": "1",
                    "ProductDescription": "apple"
                }
            ]
        },
        {
            "Product": "1081",
            "to_Description": [
                {
                    "Language": "EN",
                    "Product": "1081",
                    "ProductDescription": "banana"
                }
            ]
        },
        {
            "Product": "150159",
            "to_Description": [
                {
                    "Language": "EN",
                    "Product": "150159",
                    "ProductDescription": "orange"
                }
            ]
        }
    ]
}

Issue with Language Setting:

Additionally, I am forced to hardcode the language to 'EN' in the schema.cds and in the service definition. Otherwise, the $expand operation on to_Description returns empty arrays.

Here is the part of the schema.cds where the language is hardcoded:

entity Products as projection on ApiProduct.A_Product {
    key Product,
        ProductType,
        CreationDate,
        CreatedByUser,
        to_Description : Association to many ProductDescriptions on to_Description.Product = $self.Product
                         and to_Description.Language = 'EN'
}

In the service.cds definition:

@readonly
entity ProductDescriptions as projection on component.ProductDescriptions
                              where Language = 'EN';

If I replace the hardcoded 'EN' with $user.locale:

@readonly
entity ProductDescriptions as projection on component.ProductDescriptions
                              where Language = $user.locale;

The response contains empty to_Description arrays for all products.

Expected Behavior:

The $expand operation should work correctly without throwing an error, even when the READ handler is active. The response should include both Products and their associated to_Description data.

Actual Behavior:

The $expand operation fails when the READ handler is in place, throwing the error: "Can't expand 'to_Description' as it has no foreign keys".

Environment:

Github Link	https://github.com/KillianD1/issueCAP
Node.js Version	v20.17.0
@cap-js/asyncapi	1.0.2
@cap-js/cds-typer	0.27.0
@cap-js/cds-types	0.6.5
@cap-js/db-service	1.13.0
@cap-js/openapi	1.0.6
@cap-js/sqlite	1.7.3
@sap/cds	8.3.1
@sap/cds-compiler	5.3.0
@sap/cds-dk (global)	8.3.0
@sap/cds-fiori	1.2.7
@sap/cds-foss	5.0.1
@sap/cds-mtxs	2.2.0
@sap/eslint-plugin-cds	3.1.0

[patricebender]

Hi and thanks for opening an issue with us, please consider our docs on how to mock associations

The problem is that we resolve the request to your service to the underlying entity, we do intentionally not try to map your on-condition in the service level to the underlying entity as this is a rather complex task (think of redirections…)

That being said, I have added a small change to your repo which will produce the desired results on your end: https://github.com/KillianD1/issueCAP/pull/1/files

BR Patrice