1

I'm trying to join two tables from different servers , but it keeps throwing this exception :

This method supports LINQ to Entities infrastructure and is not intended to be used directly from your code.

here is my query :

 var myList = (from myTableA in _dbProvider.ContextOne.TableA
               join myTableB in _dbProvider.ContextOne.TableB on myTableA.ProductId equals myTableB.Oid
               join myTableC in _dbProvider.ContextTwo.TableC on myTableB.Id equals myTableC.ProductId
               where 
               select  myTableC.Name).Distinct().ToList();

what's that mean ?, knowing that I found an other solution by getting data separately from each table into lists then joining them but it's very greedy in terms of time is there any other solution ?

Sam FarajpourGhamari
  • 14,601
  • 4
  • 52
  • 56
user3708197
  • 29
  • 1
  • 10
  • can you show where you declare `_dbProvider`? – user1666620 Aug 28 '15 at 13:24
  • if your data is too large to handle with separate queries and Linq to Objects, you might be able to use a federated table to create a "local" copy of the remote table that is accessible from one context. However, I generally dislike linking DBs this way. – BJ Safdie Aug 28 '15 at 13:41

1 Answers1

2

You can't join two tables from two different servers. Definitely not from EF. Your best bet is to only fetch the data in two separate lists and then join them together using Linq to objects.

Let me make an imaginary example: You have 1000,000 invoices on one table, each one has about 10 items, a total of 10,000,000 invoice details on anther server. You need Invoices and their details for 10 first invoices created on 2015-5-4

you send a query to first DB, getting only that 10 invoices, extract their ids and use that to query about 100 rows from the other server. This is only about two times slower than making a single join query.

In some cases this becomes impossible (you have conditions on both tables) and you need to bring more rows, but in simple scenarios this is possible.

Alireza
  • 5,421
  • 5
  • 34
  • 67
  • my tables are too heavy , i can't get all data from each one then join them from lists ! I'm looking for a best solution – user3708197 Aug 28 '15 at 13:30
  • I see what you did here and it's a great solution in case of non-complex query , in my case I cannot do the same , thank you anyway , but here is someone who found it I tink : http://stackoverflow.com/questions/6256790/linq-join-mysql-and-sql-server-tables?rq=1 – user3708197 Aug 28 '15 at 14:07
  • It does the same thing as loading data from both collections and then joining them. Actually, there is no silver bullet here. If the data are on different servers, you need custom solutions based on specific problem to yield a good performance. – Alireza Aug 28 '15 at 14:42