Use tables from 2 different databases - entity framework

Question

Hello, I have a model got from a sql 2008 database, now i need, in the same model file to create a entity that represent a user-base from another database. How I can do this in the same model and avoid creating a database view.

Answer 1

You could create a VIEW in the SSDL. I.e. not a database view but an Entity Framework view.

Take a look at the documentation for DefiningQuery. DefiningQueries have a native SQL body, just like a database View, but you don't need a view in the database.

At this point the Entity becomes readonly so you need to add Functions (to the SSDL) and ModificationFunction Mappings (to the MSL) for to make the Entity Read-Write again.

Note once the Functions are in the SSDL you should use the designer to create the modifications mappings rather than mucking around in the XML.

Generally Functions in the SSDL are simply wrappers around stored procs, but seeing as you don't want to add a database view you probably don't want to create stored procs either.

Luckily you can embed the CommandText for the SSDL functions directly in the SSDL too (i.e. native SQL again).

If you follow all these steps you can get what you want.

But I admit it is NOT easy.

Hope this helps (at least by giving you some hints on how to get started).

Alex (Entity Framework Team)