0

I have a relational database with a number of entities. Example:

sample-relational-database-schema

(source: https://www.researchgate.net/figure/A-sample-relational-database-schema_fig2_228999233)

What is the best (simple but safe) approach for the web-application to dynamically allow basic CRUD and List operations for the underlying data, without strict binding to the actual entities. So, if I define another entity in the database, my application will accommodate it with no additional coding.

I kicked off with MVC approach on ASP.NET, and now I doubt it was a good decision. So, in MVC I needed a way to read from the database the list of entities and based on that dynamically create appropriate controllers. Then I needed to read the attributes for those entities and dynamically build models, and finally my view shouldn't have been bound to the specific models, so it renders whatever dynamic model comes.

I ended up creating a type called Entity, that will have contain a dictionary of all fields that an entity has. This is the example

{
  "Id": "7655b4ae-4495-485a-8a66-3be469700726",
  "Name": "Contact",
  "Attributes": [
    {
      "Type": "string",
      "Value": "John",
      "Name": "firstname",
      "DisplayName": "First Name"
    },
    {
      "Type": "string",
      "Value": "Doe",
      "Name": "lastname",
      "DisplayName": "Last Name"
    },
    {
      "Type": "date",
      "Value": "1990-01-02T00:00:00.000Z",
      "Name": "birthdate",
      "DisplayName": "First Name"
    }
  ]
}

There are some difficulties:

  • It is hard to work with linked entities in the view
  • I had to write my own ModelBinder, my own Dynamic Controller and a bunch of View- and Editor- Templates in order to support the model. This is a lot of custom code and it is not very neat and easy to work with
  • This approach completely ignores one of the main benefits of MVC, which is data-validation done by the Model. In fact there's no data validation anymore and I have to write own validators for all possible types of fields.

I would like to hear your thoughts or ideas, or any links to further reading or projects that have already found a good solution. I am looking for ASP.NET/Core stack.

Raman Sinclair
  • 1,194
  • 17
  • 31
  • That sounds like an [EAV approach](https://stackoverflow.com/questions/17231834/if-eav-is-evil-what-to-use-for-dynamic-values). Is there a specific reason for that? Because adding a model to support a new entity in the database is usually not that painful. – user7217806 Dec 15 '19 at 11:45
  • Part 1: Well, from what I've read about EAV: it is an approach to organising the database rather than building a web application, moreover it is an approach designed for storing entities that are characterized by numerous fields, that are not fully defined at the development stage and can change. As it also says, it defeats the benefits of relational schema, which is important for me. I am looking for something that will allow me to render the database into a set of default views without hard-coding actual entities into the application. The entities itself will be well-related in the database. – Raman Sinclair Dec 16 '19 at 21:00
  • Part 2: My reason is that once I wrote the code, I don't want to change it all the time a new entity of field is required, and to the end user I am providing an interface that will allow definition of the database objects inside of the application, which will not require a re-compilation of the application. – Raman Sinclair Dec 16 '19 at 21:01
  • Ah, thanks for clarifying! Did you have a look at [Ilaro.Admin](https://github.com/psi-corps/Ilaro.Admin/)? Maybe you can find some inspiration there. – user7217806 Dec 16 '19 at 23:15
  • @user7217806 I've looked at Ilaro.Admin. It is a neat project, but really - I can't specify my classes even for once in the application code. It should be generic, based on the entities in the database, and in Ilaro one needs to declare actual classes before building it. – Raman Sinclair Jan 13 '20 at 22:30

0 Answers0