Can I Use EF Across Multiple DBs in One SQLServer Instance?

I have been searching the blogs and articles but I have not found much support for this scenario. I have been poking around EF and realized that I could create views that contained data from 开发者_JS百科multiple databases and then build the EF object model off of those views. Although it works I am not sure about the usual issues of performance, scalability, maintainability. The way I am achieving the connection between databases is by creating associations in the EF model.

Does anyone have any information about this type of implementation? Either another solution or commentary on this proposed solution?

Thanks,

Tom

With regards to making views all in one database based on data in multiple databases, I have not seen any particular performance differences compared to having everything in the same database.

Your single largest direct impact will be security. Assuming you don't allow chaining, the users will have to have permissions on the underlying cross-database objects at the level which your cross-database view works (i.e. if the top-level view refers to a table, they need to have the appropriate SELECT on the columns you use, if it refers to a view, they need the same on the view - but not perhaps on anything beneath) - so they have to be allowed in some role in that database as a user and have the rights.

Structurally, the multiple databases poses certain challenges and limitations, so for maintainability, yes, that can be a factor - aliases may be an option here.

Obviously for scalability with SQL Server, assuming you are not scaling up (which is the normal the typical SQL Server route), you're going to have difficulties with sharding because now each database is really two databases or whatever. Or multiple databases joining to one central database via linked servers. This doesn't scale well unless you have particularly light controlled access. Or multiple databases joining to a replicated database in each instance might be an option. It really all depends on your domain and data issues; there are a ton of possibilities.

So consolidation might be an option in anticipation of needing to scale, and using separate schemas within a database might be an option. You could start with views now and then when the data moves into a single database, your views would no longer be across databases.

Also, you cannot guarantee SCHEMABINDING on objects based on objects in other databases.