Question: configure EF where clause for open type $filter

[engenb]

Hello,

I have a model with an open type, represented as IDictionary<string, object> Data { get; set; }. In EF, this property has a converter defined, to deserialize/serialize the property as JSON when it is written/read to/from the database as such:

public void Configure(EntityTypeBuilder<Record> builder)
{
    ...
    builder
        .Property(x => x.Data)
        .HasConversion(
            x => ConvertTo(x),
            x => ConvertFrom(x));
}

private static string ConvertTo(IDictionary<string, object> data) =>
    JsonConvert.SerializeObject(data, IntegrationJsonSerializerSettings.Default);

private static IDictionary<string, object> ConvertFrom(string data) =>
    JsonConvert.DeserializeObject<IDictionary<string, object>>(data, IntegrationJsonSerializerSettings.Default);

Generally speaking, this is working great, except a new requirement requires me to fetch records based on a dynamic property, if it exists.

OData isn't going to know how to translate the $filter clause for this because it doesn't know I'm storing this property as JSON. I was wondering if there's a point where I can configure how the EF where clause is generated for the $filter param.

My ideal scenario:
A point to configure how to filter open types. Fall back on default behavior unless the filtered property doesn't match any static properties in the model. If the property isn't static, I'd like to generate my own where clause that utilizes raw SQL so I can utilize MSSQL JSON_VALUE in the where clause for that property.

Not-so-ideal scenario:
I extend ODataQueryOptions to have more control over how the $filter is applied to the IQueryable. I'm hoping this isn't the way I need to go as this class has a lot of logic in it, so I'm hoping someone more experienced with this project will have a better idea.

Thanks!

[KanishManuja-MS]

@engenb You can try looking at the FilterBinder code and inject your own FilterBinder through dependency injection that just handles the open properties differently.

[Danielku15]

In your case you likely want to extend the BindDynamicPropertyAccessQueryNode method of the FilterBinder and register your custom variant . This method translates the access to a dynamic property of an open type to an Expression. In your case you likely want to translate it to a custom function call that unwraps a property. e.g. a $filter=DynProp eq 1 would need to become .Where(x => JsonExtensions.AccessPropertyAsInt("DynProp") == 1) This function call expression is what you need to generate.

OData translates individual parts of the $filter to C# expressions on the IQueryable using expression trees. There is no SQL generated that could simply be adjusted. This means you need to build a C# expression tree, and then use EF features to translate it to your desired SQL.

Your custom function call in the expression tree then needs to be translated via a IMethodCallTranslator via DbFunction.

dotnet/efcore#11295
https://weblogs.thinktecture.com/pawel/2019/07/entity-framework-core-custom-functions-using-imethodcalltranslator.html

[ngregrichardson]

@Danielku15 I know this is an old ticket, but there doesn't seem to be many examples overriding the FilterBinder class. Once you've created your custom variant, how would you register it?

[Danielku15]

Phew, never tried it actively, and it's been a long time (we're not using this lib anymore 😅 ). But the FilterBinder is registered within the dependency injection container of OData:

WebApi/src/Microsoft.AspNet.OData.Shared/Extensions/ContainerBuilderExtensions.cs

Line 90 in 0338075

builder.AddService<FilterBinder>(ServiceLifetime.Transient);

This OData lib comes with an own DI system due to the compatibility between ASP.net Web API and ASP.net core variants.
https://github.com/OData/odata.net/blob/master/src/Microsoft.OData.Core/IContainerBuilder.cs

So it might depend how you register it correctly but in ASP.net core ()Microsoft.Extensions.DependencyInjection) classical way you would just register something like services.AddTransient<FilterBinder, CustomFilterBinder>() or services.Replace(new ServiceDescriptor(typeof(FilterBinder), typeof(CustomFilterBinder), ServiceLifetime.Transient)