CODEWITHSUNDEEP
entity-framework
Carmelo
Carmelo

Reputation: 13

Multiple database in EF6

We are involved in quite a new development in which we are remaking our current web shop platform. In the current platform we do not use EF6 neither other ORM but store procedures to access to the db, but in the new building is what we do.

We have a doubt regarding database design of the new platform. In the current platform we use several different databases depending on the content of them. For example, we have dedicated databases to store information for products catalogs other dedicated db for handling orders. Currently all data access is done through stored procedures, so we have no problem with the links between different databases. The problem appears to us now when we have started to use EF6. In this case each DB is associated with a context and it is not possible to know data from one context to another unless we implement directly in the source code these relationships using various contexts. It looks like these means we will lose the power of EF6.

The questions we have are: Is it a bad design maintaining different databases for the same application using EF6? in case this is a poor design and choosing for a single database, is the performance going to be optimum even driving hundreds of tables (almost 1000) with several TBytes of information? in the other hand, in the case of opting for the design in which several bbdd appear (it would be much better in our case), what is the best way to handle them EF6?

Thank you very much for your help!

Upvotes: 1

Views: 112

Answers (1)

bubi
bubi

Reputation: 6491

First of all EF is not written to be cross database. You can't write cross database (cross context) queries, lazy load does not work and so on. This is a big limitation in your case. EF could work with several schema (actually I don't use it and I don't like it but is just my opinion).

You can use your stored procedures with EF but as I understand you are thinking to stop to use them.

In my experience I wrote several applications with more than one database but the use of the different databases was very limited. In this cases I use cross database views (i.e. one database per company and some common tables with views in company databases that selects data in common tables). In your case, if the tables are sharded everywhere I don't think this is a way you can choose.

So, in my opinion you could change the approach.
If you have backups problems you could shard the huge tables (I think facts tables and tables with pictures) and create cross database views. BTW, also, cross database referential integrity is not supported in SQL Server so you need to write triggers to check it.
If you need to split different application functions (i.e. WMS, CRM and so on) you can use namespaces without bothering about how tables are stored in the DB.

Upvotes: 1

Related Questions