'ODATA ASP .net core $Filter with Any or All
We are building an API to expose our account data for entities using ODATA REST API. One of our downstream system wants to consume our API wants to filter data for all Internal Accounts only.
The Query used is $Filter=accounts/all(account: account/Type eq 'Internal')
They get all the Entities with accounts that have 'Internal' but few missing entities. One such missing entity in output payload is "EntityId": 140630.
The only difference between this entity '140630' and other entities that make up to the payload is that, has accounts that belong to both types 'Internal and External'.
But the expectation is that downstream wants all entities that have accounts with type 'Internal' invariably whether the entity has only Internal accounts or both (Internal and External).
Based on our initial analysis, ODATA 'All' filter considers entities that have only Account/Type that has 'Internal' and skips entities with accounts that have both 'Internal' and 'External'.
With 'Any' Filter, entities that have account type 'Internal' + entities that have account type 'Internal' and 'External' are fetched. In this case we see the payload having Entity 140630 with accounts both Internal and External. But as per requirement, the downstream system should not be exposed with 'External' accounts.
Need support to write a fitting ODATA Filter that brings Entities that contains internal accounts. Whether they have only internal accounts or have both internal and External accounts.
From the sample payload provided below the filtered output should bring entities "EntityId": 140278, and accounts (Only have internal accounts) "EntityId": 440365, and accounts (Only have internal accounts) "EntityId": 140630, and accounts "Id": 228643,"Id": 228647, (Only internal accounts from the list of accounts they have)
Any directions to achieve the above requirement will be much appreciated.
The json payload with out any filter looks like
{
"@odata.context": "https://abc.myteamtest.webdomain.com/TAcctService/API/$metadata#TAccount(Accounts(CIS(),accountRel()))",
"value": [
{
"EntityId": 140278,
"Accounts": [
{
"Id": 229016,
"Type": "Internal",
"TradingSourceSystem": "ABC",
"TradingCode": "18951",
"ShortName": "MyBank ABC 124543",
"FullName": "MyBank ABC (OU 124543)",
"TradingBusiness": "ASL",
"Status": "Active",
"accrualCode": "ACC",
"CreatedDt": "2017-11-18T04:29:31.552+05:30",
"UpdatedDt": "2022-01-31T13:06:12.053+05:30",
"UpdatedBy": "admin",
"EntityRole": "CUSTR",
"CIS": {
"TradingCodeAttributesID": 229016,
"id": "69776",
"description": "MyBank HOLDINGS INC.",
"CorpCode": "NY001",
"TradingLocation": "YORK SITE",
"OrgunitId": "124543",
"OrgunitDescription": "ABC - FUNDING",
"BusinessUnit": "OTHER",
"Location": "USA",
"ProfitCenter": "124543",
"CustomerGroup": "07956",
"CISLegalEntityId": "07956",
"CISLegalEntityName": "MyBank HOLDINGS INC."
},
"accountRel": {
"TradingCodeAttributesID": 229016,
"RelatedEntityId": null,
"Relationshiptype": null,
"Restrictedpartyname": null,
"Confidentialityagreementind": null
}
}
]
},
{
"EntityId": 440365,
"Accounts": [
{
"Id": 228919,
"Type": "Internal",
"TradingSourceSystem": "ABC",
"TradingCode": "21700",
"ShortName": "MyBank 69002",
"FullName": "MyBank Limited (69002)",
"TradingBusiness": "ASL",
"Status": "Active",
"accrualCode": "MTM",
"CreatedDt": "2017-11-18T04:29:33.228+05:30",
"UpdatedDt": "2022-01-31T13:06:12.053+05:30",
"UpdatedBy": "admin",
"EntityRole": "CUSTR",
"CIS": {
"TradingCodeAttributesID": 228919,
"id": "69002",
"description": "MyBank LIMITED",
"CorpCode": "WGINT",
"TradingLocation": "UnKnown",
"OrgunitId": "69002",
"OrgunitDescription": "MyBank LIMITED",
"BusinessUnit": "MANAGED",
"Location": "WES",
"ProfitCenter": "69002",
"CustomerGroup": "69002",
"CISLegalEntityId": "69002",
"CISLegalEntityName": "MyBank LIMITED"
},
"accountRel": {
"TradingCodeAttributesID": 228919,
"RelatedEntityId": null,
"Relationshiptype": null,
"Restrictedpartyname": null,
"Confidentialityagreementind": null
}
}
]
},
{
"EntityId": 140630,
"Accounts": [
{
"Id": 219926,
"Type": "External",
"TradingSourceSystem": "MSS",
"TradingCode": "MARINE",
"ShortName": null,
"FullName": "My Bank of Testing",
"TradingBusiness": null,
"Status": "Active",
"accrualCode": null,
"CreatedDt": "2021-01-26T19:24:03.327+05:30",
"UpdatedDt": "2021-01-26T19:19:21+05:30",
"UpdatedBy": null,
"EntityRole": "CUSTR",
"CIS": {
"TradingCodeAttributesID": 0,
"Boaid": null,
"Boadescription": null,
"CorpCode": null,
"TradingLocation": null,
"OrgunitId": null,
"OrgunitDescription": null,
"BusinessUnit": null,
"Location": null,
"ProfitCenter": null,
"CustomerGroup": null,
"CISLegalEntityId": null,
"CISLegalEntityName": null
},
"accountRel": {
"TradingCodeAttributesID": 0,
"RelatedEntityId": null,
"Relationshiptype": null,
"Restrictedpartyname": null,
"Confidentialityagreementind": null
}
},
{
"Id": 219927,
"Type": "External",
"TradingSourceSystem": "MSS",
"TradingCode": "PRESET",
"ShortName": null,
"FullName": "My Bank of Testing",
"TradingBusiness": null,
"Status": "Active",
"accrualCode": null,
"CreatedDt": "2021-02-23T15:50:46.013+05:30",
"UpdatedDt": "2021-02-23T15:50:45+05:30",
"UpdatedBy": null,
"EntityRole": "CUSTR",
"CIS": {
"TradingCodeAttributesID": 0,
"Boaid": null,
"Boadescription": null,
"CorpCode": null,
"TradingLocation": null,
"OrgunitId": null,
"OrgunitDescription": null,
"BusinessUnit": null,
"Location": null,
"ProfitCenter": null,
"CustomerGroup": null,
"CISLegalEntityId": null,
"CISLegalEntityName": null
},
"accountRel": {
"TradingCodeAttributesID": 0,
"RelatedEntityId": null,
"Relationshiptype": null,
"Restrictedpartyname": null,
"Confidentialityagreementind": null
}
},
{
"Id": 228643,
"Type": "Internal",
"TradingSourceSystem": "ABC",
"TradingCode": "24633",
"ShortName": "AB124 94189",
"FullName": "AB124 CRT",
"TradingBusiness": "MSL",
"Status": "Active",
"accrualCode": null,
"CreatedDt": "2017-11-18T04:29:34.409+05:30",
"UpdatedDt": "2022-01-31T13:06:12.053+05:30",
"UpdatedBy": null,
"EntityRole": "CUSTR",
"CIS": {
"TradingCodeAttributesID": 0,
"Boaid": "09902",
"Boadescription": "OFFICE",
"CorpCode": "ABCC1",
"TradingLocation": "York",
"OrgunitId": "94189",
"OrgunitDescription": "MAP",
"BusinessUnit": "CIO",
"Location": "PAN",
"ProfitCenter": "94189",
"CustomerGroup": "09902",
"CISLegalEntityId": "29997",
"CISLegalEntityName": "My PARENT BANK"
},
"accountRel": {
"TradingCodeAttributesID": 0,
"RelatedEntityId": null,
"Relationshiptype": null,
"Restrictedpartyname": null,
"Confidentialityagreementind": null
}
},
{
"Id": 228647,
"Type": "Internal",
"TradingSourceSystem": "ABC",
"TradingCode": "7574",
"ShortName": "ABC1234 COLL13122",
"FullName": "ABC1234 Collateral Pool",
"TradingBusiness": "MSL",
"Status": "Active",
"accrualCode": null,
"CreatedDt": "2017-11-18T04:29:35.234+05:30",
"UpdatedDt": "2022-01-31T13:06:12.053+05:30",
"UpdatedBy": null,
"EntityRole": "CUSTR",
"CIS": {
"TradingCodeAttributesID": 0,
"Boaid": "09602",
"Boadescription": "P&T",
"CorpCode": "ABC12342",
"TradingLocation": "YORK",
"OrgunitId": "13122",
"OrgunitDescription": "FIC",
"BusinessUnit": "MARKETS",
"Location": "PAN",
"ProfitCenter": "13122",
"CustomerGroup": "09602",
"CISLegalEntityId": "29997",
"CISLegalEntityName": "ABC1234 PARENT BANK"
},
"accountRel": {
"TradingCodeAttributesID": 0,
"RelatedEntityId": null,
"Relationshiptype": null,
"Restrictedpartyname": null,
"Confidentialityagreementind": null
}
}
]
}
]
}
Solution 1:[1]
I'm assuming that you're using EntityFramework in this scenario. If so, I suggest you try this:
public IQueryable<Entity> Get()
{
return _databaseContext.Entities
.Include(entity => entity.Accounts.Where(account => account.Type = "Internal"))
.AsNoTracking();
}```
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | IkeMtz |