Filtering / Grouping on dynamic properties does not translate to valid SQL (exception)

[nathanvj]

Assemblies affected ASP.NET Core OData 8.0.11

Context I'm using EF with PostgreSQL And have an entity that needs to support 'custom fields'. I followed this guide from Microsoft and managed to get the following result for example:

{
    "@odata.context": "https://localhost:9001/v1/$metadata#Tickets",
    "value": [
        {
            "subject": "This is a test ticket.",
            "createdAt": "2022-08-31T11:25:07.206657Z",
            "updatedAt": "2022-09-03T20:29:19.836242Z",
            "id": "006ad14f-8f5e-b3aa-df9f-a193f9f34d6e",
            "language": "English"
        }
    ]
}

Describe the bug When I try to filter or groupby on a dynamic property, I get the following exception:

The LINQ expression 
'DbSet<Ticket>().Where(t => (string)t.CustomFields.ContainsKey("language") ? t.CustomFields["language"] : null == __TypedProperty_0)' 
could not be translated. 
Additional information: Translation of method 'System.Collections.Generic.Dictionary<string, object>.ContainsKey' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. 
Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Entity Model

public class Ticket : WorkspaceEntity
{
    [Required]
    public string Subject { get; set; }

    public DateTime? CreatedAt { get; set; }

    public DateTime? UpdatedAt { get; set; }

    public Dictionary<string, object> CustomFields { get; set; }
}

EDM (CSDL) Model

<EntityType Name="Ticket" OpenType="true">
<Key>
<PropertyRef Name="id"/>
</Key>
<Property Name="subject" Type="Edm.String" Nullable="false"/>
<Property Name="createdAt" Type="Edm.DateTimeOffset"/>
<Property Name="updatedAt" Type="Edm.DateTimeOffset"/>
<Property Name="id" Type="Edm.Guid" Nullable="false"/>
</EntityType>

Request GET https://localhost:9001/v1/tickets?$filter=language%20eq%20%27French%27

Response See exception above.

Expected behavior To get all tickets where language is equal to 'French'.

Screenshots N/A

[corranrogue9]

From triage: we should find what the underlying data store expecting the expression to look like. Then we can update our code to generate such an expression for open types.

[h-uchiy]

I am facing the same problem. (using EF Core 7 & SQL server) I implemented workarounds by overriding FilterBinder/OrderByBinder. It seems to be worked fine, but IN operator throws NRE...

[djniu]

I am facing the same problem. (using EF Core 7 & SQL server) I implemented workarounds by overriding FilterBinder/OrderByBinder. It seems to be worked fine, but IN operator throws NRE...

what is your workaround like? It is hard to make an expression with dynamic property.