OData v8.2.5: Selecting and Filtering Complex Dictionary Properties

[sarangp93]

Hello,

I have a JSON property in the database that I use with EF Core for Serialize/Deserialize conversion. I've tried many solutions to select and filter this property with OData (with version 8.2.5), but all of them have failed. I found this #1224, and using LocalizableString, I can select data from this property, but filtering still results in errors. I also found a solution for filtering by using .ToList() at the end of the query in my endpoint, but I want the query to be IQueryable, so I cannot use .ToList().

I tried all these queries, but they all failed:

$filter=metadata/any(c: c/key eq 'some string')
$filter=metadata/any(p: contains(p/key, 'some text'))
$expand=metadata($filter=metadata/any(c: c/key eq 'some string'))
$filter=metadata/key eq '1'
$filter=contains(metadata/key, 'some text')

First Question: Is there any other solution to this issue to be able to select and filter this property?
Second Question: Will this issue be fixed in future versions?

In the end, I created an OData function to filter this complex property using raw SQL, and it works. However, I believe there should be a better solution for this issue!

Thank you.

[julealgon]

I also found a solution for filtering by using .ToList() at the end of the query in my endpoint, but I want the query to be IQueryable, so I cannot use .ToList().

Could you share more details on how that looked like?

Also, I believe you'll need to provide a repro for your overall issue as well so folks can try to help you out a bit better.

[anasik]

@sarangp93, could you please create a proper bug report following the correct format so that it's possible for contributors to recreate and possibly fix said bug?

You can follow this link for the bug report template

[habbes]

Hello @sarangp93 is this JSON property mapped to a complex property in your OData model? What kind of error you getting? Are you getting an error from the DB driver, EF Core or OData library? What database driver are you using? What version of EF Core?

Could you share a project that reproduces this issue to help with the investigation?

[sarangp93]

Thank you for your messages,

Yes, I am using EF core v8 and also using the same repo like this one #1224, instead of having static data I get the data from Postgres and using this conversion for Serialize/Deserialize in my dbcontext:

 modelBuilder.Entity<MyEntity>()
            .Property(x => x.Metadata)
            .HasConversion
            (
                v => JsonSerializer.Serialize(v, (JsonSerializerOptions?)null),
                v => JsonSerializer.Deserialize<Dictionary<string, string>>(v, (JsonSerializerOptions?)null),
                new ValueComparer<Dictionary<string, string>>(false)
             );

This is the error that I got from these queries:
$filter=metadata/key eq '1'
$filter=contains(metadata/key, 'some text')

Error: System.ArgumentException: 'Method 'System.Object get_Item(System.String)' declared on type 'System.Collections.Generic.IDictionary`2[System.String,System.Object]' cannot be called with instance of type 'System.Object''

As I said before I use metadata as LocalizableString in my Edm model!

public sealed class LocalizableString
{
    private IDictionary<string, string> _values;

    public LocalizableString()
    {
        _values = new Dictionary<string, string>();
    }

    public LocalizableString(IDictionary<string, string>? dictionary)
    {
        _values = dictionary != null ? new Dictionary<string, string>(dictionary) : new Dictionary<string, string>();
    }

    public string this[string key]
    {
        get
        {
            if (key != null)
            {
                return _values[key];
            }
            return null!;
        }
        set
        {
            if (key != null)
            {
                _values[key] = value;
            }
        }
    }

    public IDictionary<string, object> ExtendedProperties
    {
        get
        {
            return _values.ToDictionary(kvp => kvp.Key, kvp => (object)kvp.Value);
        }
        set
        {
            if (value != null)
            {
                _values = value.ToDictionary(kvp => kvp.Key, kvp => kvp.Value.ToString());
            }
        }
    }
}

and this is the Edm model:

  modelBuilder.EntitySet<OdataModel>("OData");
        var model= modelBuilder.EntityType<OdataModel>();
        model.HasKey(x => x.Id);
        model.HasMany(x => x.Items);
        model.ComplexProperty(c => c.Metadata);


 services.AddControllers()
            .AddOData(options =>
            {
                options.AddRouteComponents("odata", modelBuilder.GetEdmModel())
                .Select().Expand().Filter().OrderBy().SetMaxTop(1000).Count().SkipToken();
                options.RouteOptions.EnableQualifiedOperationCall = false;
            });

and this is the endpoint:

    [EnableQuery]
    public IActionResult Get()
    {
        var response = _repo.AsQueryable<MyEntity>().ToOdataModel();
        return Ok(response);
    }