OData Queries on Entities with Dynamic Fields

Question

I am using OData on a dynamic model. The MyObject data is stored in two different ways on the database - in a main MyObject table and in a secondary table of key/value pairs. The allowed field names for the secondary table are stored in a third table. Using a PIVOT, I can retrieve a list of MyObject items with both static and dynamic fields included. I am also able to use $top, $filter, and $orderby on any static fields if I assign the builder's EntitySet to the MyObject model. However, I am unable to use $filter or $orderby with any of the dynamic fields because they do not exist on the MyObject model.

I have tried creating a dynamic model using DynamicObject or ExpandoObject and using in place of MyObject, but am still unable to run $filter or $orderby on any fields that are not previously defined. In every case, the code fails before it hits the controller with a message that the field does not exist on whatever model is being assigned to the builder. Since I need to account for different users have different fields, as well as allowing new fields to be included automatically, I do not know how to set this up.

In the Startup.cs ConfigureServices, I have the following:

services.AddControllers(options =>
{
    options.ModelBinderProviders.RemoveType<DateTimeModelBinderProvider>();
})
    .AddOData(opt => 
       opt.AddRouteComponents("odata", GetEdmModel())
           .Expand()
           .Select()
           .OrderBy()
           .Filter()
           .Count()
           .SkipToken()
     )
    .AddNewtonsoftJson();

The GetEdmModel method is as follows:

var builder = new ODataConventionModelBuilder();
builder.EntitySet<MyObject>("MyObjectController");
builder.EntityType<MyObject>().HasKey(k => k.Id);
return builder.GetEdmModel();

I am not very familiar with setting up OData. Is there a way to use an EntitySet with a class that can be altered dynamically? Or is there a way to define something in the EntityType that can account for dynamic fields? I can get a list of these fields when the user calls the controller - before any queries need to be made on the MyObject entity.

Thanks in advance!

Answer 1

I managed to get this to work by removing the EnableQuery attribute on the Get method of MyObjectController. Based on the documentation, this attribute prevents malicious queries. It seems that it is also preventing using any additional dynamic fields in $filter and $orderby.

I don't know if it is a great idea to remove this attribute, so I am still open to any other suggestions on how to handle this issue.

Answer 2

You can take a look at OData's Open Types. If your entity is defined like that you can query your dynamic properties with the normal [EnableQuery] attribute.