3

I am using SQL Server 2008.

It seems as though in EF I need a different entity context for each database. Joining tables between databases means having to allow for this which is inconvenient and makes it more likely that I would simply put everything in one database.

Am I missing something? Surely EF can work better than this?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
arame3333
  • 9,887
  • 26
  • 122
  • 205
  • 1
    AFAIK, it's not generally a good idea to join tables in separate databases, which is why EF doesn't support it. – jrummell Jul 18 '12 at 15:17
  • The implication of that is that you should only have 1 database. That does not seem right to me. – arame3333 Jul 18 '12 at 15:31

2 Answers2

6

You could map your entities to a view that returns joined tables, so your EF model doesn't know about all this technical DB stuff.

ken2k
  • 48,145
  • 10
  • 116
  • 176
3

I don't think it's properly supported, but this guy has managed to get it to work.

Basically, she set up Synonyms for the databases, and hacked the edmx file.

Joe Ratzer
  • 18,176
  • 3
  • 37
  • 51
  • 2
    That's living dangerously! :) – jrummell Jul 18 '12 at 15:18
  • 3
    Indeed, I wouldn't really recommend it but it has been done! :) – Joe Ratzer Jul 18 '12 at 15:19
  • 3
    I'm "this guy", and it wasn't really a hack :) I simply merged two edmx files, so the final copy contained the definitions for both databases, and setup Synonyms on the first database that pointed to the 2nd database's objects. I actually have a question [here](http://stackoverflow.com/q/6036357/302677) asking if this is a bad idea or not, and the answer I got was from someone I trust in the EF field :) – Rachel Jul 18 '12 at 18:05
  • Thanks for the comment! :) What do you think about ken2k's alternative? – Joe Ratzer Jul 18 '12 at 18:43
  • @JoeR I'm a lazy person, and it seemed like too much work ;) I was working with a fairly large database at the time, and creating updatable Views for every database table, along with indexes, keys, links, etc and finding/updating the Defining Query in the edmx seemed like a huge hassle. – Rachel Jul 18 '12 at 19:33
  • Although I would definitely use a simple View like ken2k suggested if I only wanted to read the data, or I'd use Stored Procedures if I only had a small number of insert/update/delete statements I wanted to run on the 2nd database. – Rachel Jul 18 '12 at 19:45