2

I'd like to combine multiple databases into one Entity Framework "object model". I do not need to change databases dynamically at runtime. This is a static configuration issue. It should look like this:

  1. Entity1 mapped to DB1.dbo.Entity1
  2. Entity2 mapped to DB2.dbo.Entity2

And I should be able to say:

new XyzContext().Entities1
new XyzContext().Entities2

How can I configure Entity Framework to take note of my multiple databases?

I do not want to create views or synonyms. This question is about an EF only solution. I'm using the latest EF version and can switch to any version.

boot4life
  • 4,966
  • 7
  • 25
  • 47
  • What have you tried so far to solve the problem and what have been your results? – user700390 Jun 22 '15 at 16:24
  • I have searched the web for this and only found hacks or workarounds (which do not fit what I'm looking for). I have looked at the ToTable method and did not find a way to specify the database name. – boot4life Jun 22 '15 at 16:26

1 Answers1

2

There is no way to do it directly with Entity Framework, the context is linked to a Database and you can't access tables directly outside that Database. I don't know your situation, but if I were you first of all I would try to move that tables to my Database.

If this is not possible one workaround to do it's just create views to your external table. But be careful populating views, remember every time that you modify the schema of your external table, to recreate the view, and it's something that is easy break.

Marc Cals
  • 2,963
  • 4
  • 30
  • 48
  • If you are right this is terrible. Why does EF still not have the core features that LINQ to SQL had in the first version? Is it really that uncommon to have to target multiple databases in a connected object model?! – boot4life Jun 22 '15 at 16:34
  • @boot4life Yes, because that isn't a database any more and you lose everything that is good and holy in a RDBMS (namely ACID). Can I interest you in NoSQL? – Aron Jun 22 '15 at 16:36
  • 1
    Well, I think that it's not so bad. For my a Database it's a isolated island of data, isolated from other database. And If my application I need to access to two database I use two different context. Because they contain isolated data. Think to this, Can you have relationships in tables from different databases? – Marc Cals Jun 22 '15 at 16:37
  • 1
    Why do you need two access from the same context? Can't you use two contexts? – Marc Cals Jun 22 '15 at 16:37
  • 1
    @MarcCals exactly, a DbContext is MORE than just an abstraction of a database connection. It carries with it Transactional state, which makes no sense over two databases. – Aron Jun 22 '15 at 16:39
  • I do want to use cross-database transactions. No problem with doing that normally. I want to use cross-database joins (e.g. `entity1.MyEntity2`). This works just fine in L2S and there are no conceptual problems with it. Don't define the problem away, there are real use cases. All the ORM has to do is prepend "DB2." to the table reference and everything else just works. This works for queries as well as for DML. – boot4life Jun 22 '15 at 16:42
  • 1
    The reason for this architecture is that there are some huge tables that need to be in a different database. Different recovery model, backups and storage. – boot4life Jun 22 '15 at 16:46
  • You can do it cross-database transaction with different DatabaseContext. http://stackoverflow.com/questions/21202982/one-transaction-with-multiple-dbcontexts . Cross joins I think that it's not possible to do it directly with out views. – Marc Cals Jun 22 '15 at 16:46
  • I'm not an expert in database administration, but there are ways to split a database in different files, and save it in different storage systems. By the way I don't have the full map of your system, but It doesn't seems a good idea to have related tables in separate databases, you can't not guarantee database integrity, and for me this sounds very bad and dangerous. You can use your DAL Layer, to simulate the cross joins in queries and cross-database transaction as I pointed out is easy to do. – Marc Cals Jun 22 '15 at 16:55
  • 1
    All your concerns are valid in principle but do not apply to my specific case. Thanks for pointing them out, though. The "huge tables" only reference mostly static data from the small database. Any inconsistency in case of a database restore event would be small and acceptable. I'll let this question sit for a few days and then problem accept your answer. – boot4life Jun 23 '15 at 11:20