Open Thomas5x01 opened 4 months ago
@Thomas5x01
If you do the 'Where' clause first then do the 'Select', it should work as:
Here's the SQL (I switched to use SQLLite for my simplicity)
If you switch the order, EF core can't understand the 'type' in 'Where' clause? I am not sure the real root cause, but that's the behaviour.
My suggestion is to use one POCO class (Merge PersonDto and PersonEntity), make some properties for DB side, other properties for OData/Client side. Then, a request containing query option coming, customize the FilterBinder to use the DB side properties to build the Expression and generate the SQL to execute. You can refer to this sample: https://github.com/xuzhg/mydotnetconf/blob/main/OData/OData.WebApi/Models/School.cs
If the above cannot work for you, you can build two Edm models, one using PersonDto for client, the other using PersonEntity for DBSide, Then, a request containing query option coming, customize the FilterBinder to use the Edm Model using PersonEntity to generate the Expression and generate the SQL, etc.
Hope it can help.
Thank you, xuzhg. I'll try out you proposals.
We, team members of Thomas5x01, ascertained thas this is not a small amount of investigational work for a common way to implement an API (create an OData-API with Custom Fields and separate the DTO-Models from the Entity-Models). Can we hope that the problem will be solved in a future version, @xuzhg?
Versions used
You can find a git repo at: https://github.com/Thomas5x01/ODataEfCoreFilterDynamicProps
Describe the issue / bug
Applying a filter to a dynamic property of an open type causes an exception. E.g.
https://localhost:7054/persondtos?$filter=LikesFootball eq false
causes(long StackTrace is at the bottom)
Reproduce steps
Program.cs:21
GET https://localhost:7054/persondtos?$filter=LikesFootball eq false
Data Model
PersonEntity is used as an Entity for EfCore
PersonDto
is exposed as an EntityType for OData. While processing a request,PersonEntity
is projected intoPersonDto
.EDM (CSDL) Model
Request/Response
(see ODataDynamicProperties.http in the repo) https://github.com/Thomas5x01/ODataEfCoreFilterDynamicProps/blob/main/ODataDynamicProperties/ODataDynamicProperties.http
Request without filtering works
Request filtering a hardcoded property works
SQL-Command that is sent by EfCore:
Resulting JSON Response
==Request filtering a dynamic property fails==
System.ArgumentException: Method 'System.Object get_Item(System.String)' declared on type System.Collections.Generic.Dictionary`2[System.String,System.Object]' cannot be called with instance of type 'System.Object'
Filter by dynamic property with EfCore works
(see PersonDtosController.Get "a small test to narrow down the problem")
Expected behavior
Our OData based web Api should be able to filter DTOs based on the value of fields / properties that are unknown at compile time / dynamic properties.
As far as I have understood, the behavior of EfCore and OData is not compatible to enable the desired behavior and a "little bridge" is needed. A sample to adress this kind of problem is described here https://devblogs.microsoft.com/odata/customizing-filter-for-spatial-data-in-asp-net-core-odata-8/ I don't know if the OData team can solve this problem in general way to provide a solution of the box. That of course would be the best solution for me :).
Maybe at least some more advice how to write a custom FilterBinder would be good: Is it possible to write a query by hand that can be translated (see commented Where() calls)? Maybe based on the expression, that is produced by that query, one can write a custom FilterBinder.
Related issues and bugs
Filtering / Grouping on dynamic properties does not translate to valid SQL (https://github.com/OData/AspNetCoreOData/issues/689)
Exception when using filter associated with a Dictionary<string,object> (https://github.com/OData/AspNetCoreOData/issues/890)
This issue is quite similar but does not involve a projection from entity to dto in the screenshot of the solution. Propsal 1 to load data into memory is not an viaable option for our scenario.
Additional context