Collection querying via EF7 JSON column

[ravz]

Not sure if this is just not supported yet or I'm doing something wrong

I have a List of Addresses stored in database as a JSON array
[{"StreetAddress":"1615 Anzac Avenue","City":"Kallangur","State":"QLD","Postcode":"4503","Latitude":-27.24795613,"Longitude":153.01077159},{"StreetAddress":"1611 Anzac Avenue","City":"Kallangur","State":"QLD","Postcode":"4503","Latitude":-27.24779426,"Longitude":153.01015158}]

I've mapped this using the new Entity Framework 7 JSON column support
When I query it via OData the results return as expected
However when I try to use any filter on the collection I get LINQ expression errors:

e.g. $filter=Addresses/$count gt 0
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The LINQ expression 'DbSet<ApplicationODataRecord>() .Where(a => EF.Property<List<Address>>(a, "Addresses") .AsQueryable() .LongCount() > __TypedProperty_0)' could not be translated. 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.

e.g. $filter=Addresses/any(a: a/Postcode eq '4503')
System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidOperationException: The LINQ expression 'DbSet<ApplicationODataRecord>() .Where(a => EF.Property<List<Address>>(a, "Addresses") .AsQueryable() .Any(o => (string)o.Postcode == __TypedProperty_0))' could not be translated. 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.

[julealgon]

Could you show how a working query would look like if made directly against the EF dbcontext?

[xuzhg]

@ravz OData doesn't know/use the type behind the scene. OData builds the model/type using the C# classes.

for example, I have the following C# entity (School).

public class School
{
    public int SchoolId { get; set; }

    // It's not for Edm model, let's ignore when Edm model building
    public string Emails { get; set; }

    public IList<string> ContactEmails
    {
        get
        {
            return Emails is null ? new List<string>() : JsonSerializer.Deserialize<IList<string>>(Emails);
        }
        set
        {
            Emails = value is null ? string.Empty : JsonSerializer.Serialize(value);
        }
    }
}

where:

1. Emails is "string" type, meanwhile "ContactEmails" is a list of string.
2. Emails is mapped to database column, so it's a "JSON" string.
3. ContactEmails is OData side property, it's a list of strings.

I think this is the same scenario as yours. In this case, you have to convert the Linq expression built from OData.

I have an example for your reference at: https://github.com/xuzhg/mydotnetconf/blob/main/OData/OData.WebApi/Extensions/SchoolStudentFilterBinder.cs#L12

Or see my commit at: xuzhg/mydotnetconf@508d0a3 to make
'any' operator work.

[xuzhg]

@ravz @julealgon I created a new issue for EFCore at: dotnet/efcore#30132. That's the JSON array column.

[ravz]

hi @xuzhg my example is a bit more complicated since my JSON columns map to objects

public class Application
{
   public int ApplicationId { get; set; }
   public List<Applicant> Applicants { get; set; }
}

public class Applicant
{
    public string Name { get; set; }
}

I want to be able to run a query like $filter=Applicant/any(a : a/Name eq 'James')

can it be done?

[xuzhg]

@ravz Yes, you can.

Here's my sample for your reference: https://github.com/xuzhg/mydotnetconf/blob/main/README.md#updated-on-272023-make-the-basic-query-on-json-array-column

Here's the commit: xuzhg/mydotnetconf@26e01ca

Be noted, I only implemented the basic scenario with 'Tricks'. Let me know any other problems.