0

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?

Ruchir Shah
  • 547
  • 1
  • 4
  • 6
  • 2
    "Any suggestions?" - rethink your design. Creating tables on-the-fly is usually a smell. Changing column names on the fly? Why? – Mat Apr 13 '15 at 09:37
  • Not changing column name but the new tables with different column names – Ruchir Shah Apr 13 '15 at 09:38
  • Why the hell do you need some dynamic tables? What is the expected lifetime of the data that you put in these dynamic tables? (Why are you putting data in tables that may not exist again when you rerun the program?) – jhominal Apr 13 '15 at 10:05
  • We're not going to delete the tables. It's kinda a new table for each new year. In that columns can be different. – Ruchir Shah Apr 13 '15 at 10:08

1 Answers1

2

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.

gbjbaanb
  • 48,354
  • 6
  • 102
  • 172
  • Thanks for the response. But in our dynamic tables, we've columns with numeric values and we need to perform aggregate function on them as well. So in that case I'm not sure storing data in single column as blob or XML will work. – Ruchir Shah Apr 13 '15 at 11:11