0

So I have mulled over many of the questions asked here on stackoverflow pertaining to multiple databases and one context. My question is more about querying multiple databases as opposed to migration. this question is probably closest to my case:

Using entity framework on multiple databases

The responders mention EF6 having better support for this but despite several days of looking through blogs, posts, and whitepapers I cannot see how to query on multiple databases.

The scenario is like his. Multitenant database structure with one central database which we want to use for shared resources, common data across all tenants. So for example our categorization may be stored in the global (Since it is the same for all databases) but the tenant's data would be stored in his specific database.

What I Want to do is simple and I have been able to do it in linqpad:

var query= (from s in GlobalDatabase.Category
join  TP  in  TenantProduct on s.catid equals  UG.catid
select new { s.catid, TP.Productid}
);
query.Dump();

LINQPad Screenshot

Is this possible in EF6\C# ?
I guess the question is how can I transform this into something that would work as a linq statement in a project? Considering that I used Linqpad's capability of joining 2 connections together is linq pad simply using 2 different contexts?

What I really wanted to end up with was one connection (one login) that has access to both databases?

Community
  • 1
  • 1
EstebanSmits
  • 91
  • 2
  • 8
  • Click the SQL tab to find out. – Robert McKee Oct 02 '15 at 14:55
  • the sql tab renders out an output no problem; would be the equivalent to this: `SELECT [t0].[catid], [t1].[Productid] FROM [Globaldatabase].[dbo]. Category] AS [t0] INNER JOIN [TenantProduct] AS [t1] ON ([t0].[catid]) = [t1].[catid]` – EstebanSmits Oct 02 '15 at 15:45
  • If it's just queries (not updates), you could create a view that queries both databases. – Steve Greene Oct 02 '15 at 18:37
  • Agreed that views and synonyms are both ways to expose the tables from the centralized table to the tenant database. Ultimately i see these as workarounds though. The connection string nor the credentials need to change to see the other databases that the user has access, ergo this isn't a new connection nor user ; it is an existing connection to another database. – EstebanSmits Oct 02 '15 at 20:13
  • @SteveGreene, you can do updates through views, depending on the procedural code... – Brett Caswell Oct 06 '15 at 15:03
  • Sure, there are lots of things you can do. But now you're gravitating toward dependency on your database. Views and stored procedures are handled differently in Oracle and other database engines. – Steve Greene Oct 06 '15 at 16:27

0 Answers0