Json (or XML) Columns

[TehWardy]

Like (i suspect) most people using WebApi with OData I back my OData models with EF Models in the storage tier.
I can have entities with computed values or as mentioned in the title a column in the DB that stores a json blob that's mapped to a complex type but isn't an "entity type with a key as such".

So my question here is ...

How can map something (either strongly typed or dynamic) that's stored in a DB row in such a way that I can retain the full OData querying functionality on it?
I ask this now because newer versions of SQL now have a JsonColumn type (much like the old XML column type which I also don't know how to map).

Here's an example of what I would like to "OData query, ideally with the expression tree that's computed running entirely in the DB" ...

public class Foo
{
       [Key]
        public int Id { get; set; }
        public string Name { get; set; }
        
        string data;
        public IDictionary<string, object> Data 
        { 
                   get {  return JsonConvert.DeserializeObject<IDictionary<string, object>>(data); } 
                   set { data = JsonCvonert.SerializeObject(value); } 
        }
}

The docs show that this data can be returned as per the open types description but i'm looking to quey the base set based on this open data too ...
https://docs.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/use-open-types-in-odata-v4

This situation will work and is perfectly map-able under EF but from what I understand if I do this I can't query OData in such a way that I could (for example) get a list of Foo objects where some value in the dictionary was a filter or sort criteria for the set I wanted to return.

The only option I have is to define this as a child entity then I'm adding a new primary, and foreignkey in to the new "type" and replacing the dictionary with a collection to join to but to my knowledge most SQL implementations can store this and query within it fine today ...

Here's what i'm looking to take advantage of through an OData query ...

https://docs.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

... whilst I do accept this is not a great example, I have a situation that's more specific to my business that I would happily discuss if you guys need more context for this question.

[KbKev78]

You need OdataModelBuilder..

For example: https://forums.asp.net/t/2002038.aspx?How+to+manually+build+odata+metadata+

[TehWardy]

That's not quite my issue ...

So lets say I have a table of GenericObjects in my EF data model defined much like the Foo object above.

now lets say i add a string property labelled something like "DataType" and based on the value of that I can do an OData query like ...

~/Api/Foo?$filter=DataType eq 'Bar' and Bar/Data/Goo eq 123

... the idea is that for a subset of Foo's "the ones labelled as Bars in data" I know they then have in Data a "Goo" Key with an integer value I can query on.

The net result is that the dynamic dictionary is defined by the process / caller that puts the data there in the first place, and that thing also knows how to query it and consume it correctly.

Update:

The issue as I see it is twofold:

1. EF Core doesn't yet support polymorphic sets & polymorphism in this case won't tell OData the item types inside the dictionary
2. The ideal case is always to run the entire query in the DB if possible, so the above implementation should be able to build an inner JSON query.

I think the problem is probably not entirely / may even be specifically an EF Core one rather than an OData one.
The reason I'm posting it here is that I figured if the framework saw an open type like this then the keys in the dictionary could be treated as dot net "dynamic" variables
and allow for type unsafe query building, the SQL should simply work if the query is valid when the underlying data provider gets hold of it.

That leaves me with the other side of the problem ... getting the EF Core team to support either JSON or XML column queries by providing the means in the model to define that and then ultimately have the expression trees translate in to JSON / XML path'ed queries.

[KanishManuja-MS]

@TehWardy We going to close this for now. You have correctly identified it as an EF core issue, WebAPI OData should be responsible for generating the LINQ query based on the data model and the LINQ provider returned by the controller action, in this case, EF core, should be responsible for translating that to the underlying storage. Please feel free to create a new issue if you find that correct query expression is not being generated by our libraries once EF core starts supporting this scenario.

[TehWardy]

Seems reasonable, thanks.