We've a project that has some tables are defined and some will be generated runtime, means dynamic and no pre-defined structure.
We generally use Entity Framework to communicate to the database(in our case MS SQL).
But for this kind of requirement, what should we do for database communication as the database will be dynamic and column names are also dynamic.
Tables and columns of database are generated dynamically so in case of Entity Framework, I'm not sure it might work or not?
If we're choosing Entity Framework with Code First, in that case we need to create classes runtime and update database as well, which I guess not possible.
If we're choosing Entity Framework with Model First,in that scenario we also need to update model file (.edmx) runtime, which I guess not possible.
So the last option come is to use ADO.Net from which we can dynamic query the database and get result without defining model as well.
So I need help here to design the architecture and a good way to archive it.
We're ready to go with NoSql but the thing "dynamic database".
Is there a way so we can use Entity Framework?
Best Answer
I've seen dynamically changing DB systems- driven by configuration where the customer adds columns "on the fly" for metadata that they want stored in addition to a few fixed columns.
In all cases, you're now driving your application using configuration, and that means your queries have to be calculated at runtime - this rules out all systems that determine the DB structure at compile time, such as pretty much every ORM there is. So you'll be using SQL queries built in code, that you have to generate yourself. Then use normal SQL client commands to call the DB - SQLClient or ODBC.
You can redesign such a system to place the dynamic data in a single column as a blob of XML but then you lose the ability to search or query on that data - NoSQL DBs resolve this, but if you need to use a combination of both Postgresql allows you to place dynamic 'nosql' data in a column that can be indexed by the DB engine, giving you the best of both worlds.