3

In the task of building a web api using OData v4, entity and C#, I got stuck in the following problem. I have a view in my DB (SQL Server) that has some yearly info as rows, such as... 

    |Year |Value |
     1985  8.7
     1986  8.8

But I need to serve them as columns: 

    |1985 |1986 |
     8.7    8.8

The years are dynamic, I couldn't find a way to create a model class that is dynamic. Because of that, I couldn't find a way to serve the data in the controller. I was able to pivot the information using C# code, but OData doesn't serve it, because there was no corresponding model.

I tried to use a library called MedallionOdata, it has a ODataEntity that was supposed to be dynamic and Queryable, but I couldn't make it work.

What is the best way to serve dynamic entities keeping the queryable aspects of OData?

Gustavo Corrêia
  • 43
  • 2
  • 8
  • I think [this post](https://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql/13377114#13377114) has the answer you're looking for. – James Crosswell Oct 25 '17 at 20:29
  • I used that post before to pivot the data on the DB, but that is not what I'm looking for, the problem is in the models... I was able to do it with "open types" in OData. – Gustavo Corrêia Oct 27 '17 at 12:15

1 Answers1

3

OData has a concept of "open types" that I think will help you to achieve what you are looking for. They essentially let you add any properties onto an object.

If you are using the ODataConventionModelBuilder, it is as simple as adding a property of type IDictionary<string, object> to your model class like this:

public IDictionary<string, object> Properties { get; set; }

You can find more details here: https://learn.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/use-open-types-in-odata-v4

TomDoesCode
  • 3,580
  • 2
  • 18
  • 34
  • That worked! Thank you very much. Now the API is able to serve the open type, and some of the filters are working. I'm still figuring out a way to $select on the open types, such as, if I wanted the API to return only the values of the year 1985... – Gustavo Corrêia Oct 27 '17 at 12:12
  • That's great, can you accept the answer if it has solved the issue :) You should be able to do `$select` and `$filter` on the dynamic properties as though they were normal properties – TomDoesCode Oct 27 '17 at 12:19
  • I'm able to select on the properties that are not dynamic, when I try to `$select` on the year, I get the following error: `{ "error": { "code": "", "message": "The query specified in the URI is not valid. An identifier was expected at position 0.", "innererror": { "message": "An identifier was expected at position 0.", "type": "Microsoft.OData.ODataException", "stacktrace": " ...` – Gustavo Corrêia Oct 27 '17 at 12:31
  • Could you create a new question for this as it is a different problem to this question and that means that there will be a lot more space to see the issue. Make sure that you post the URL that you used in the questions – TomDoesCode Oct 27 '17 at 12:45
  • Yes!!! I will do it right now. =]... Am I supposed to close this question? How do I do it? – Gustavo Corrêia Oct 27 '17 at 12:56
  • Don't forget to make this question as answered so that other people know that it is answered :) – TomDoesCode Oct 27 '17 at 13:11
  • Ok!! =] The other question is here: https://stackoverflow.com/questions/46976034/how-to-select-on-odata-open-data-type-query-not-valid-error – Gustavo Corrêia Oct 27 '17 at 13:23