LINQ: Join MySql and SQL Server tables

I have related data living on two completely separate databases, and I need information from both of these databases. One of the databases lives on a MySql server and the other one lives on a MS SQL Server. Don't ask why we have related data living on two completely different servers, it's a long story.

From a hi开发者_如何转开发gh-level perspective, I need to join MySqlTableA to SQLServerTableB, do some complex restrictions, and possibly do some GROUP BYs and counts.

I'm trying to find a way to make joining between these two databases reasonably easy. I thought LINQ might solve my problems, but I can't create a single context that has both MySql and SQL Server sources, as far as I know. I can put the two sources in different contexts -- using Devart's LinqConnect to create a MySql context -- but LINQ doesn't allow cross-context joins. (I tried the method described here, but it didn't work: Simulating Cross Context Joins--LINQ/C#)

So what are my options? Is there a way to efficiently join tables on these two different database servers (though LINQ or otherwise), or am I going to have to loop through and join the data by hand?

EDIT:

As mentioned, I've already tried the AsQueryable() workaround, but I still get a cross-context exception. Here is my code:

public static MySqlDataContext mysql = new MySqlDataContext();
public static SQLDataContext sql = new SQLDataContext();

public static void Main() {
    var rows = from a in mysql.tableA
               join b in GetTableBs() on a.col equals b.col
               select a;

    //exception gets thrown when rows is enumerated.
    //InvalidOperationException: "The query contains references to items defined on a different data context."
    foreach(var row in rows) {
        ...
    }
}

public static IEnumerable<TableB> {
    return sql.TableBs.AsQueryable();
}

---

One option is to link your MySql database through MS SQL Server. Then access both databases through your SQL Server DataContext. Here's a couple examples of how to set up the link and docs:

• http://www.ideaexcursion.com/2009/02/25/howto-setup-sql-server-linked-server-to-mysql/
• http://sql-articles.com/blogs/creating-linked-server-to-mysql-from-sql-server/
• MSDN: Linking Servers

With that complete, you may want to expose the linked tables with either stored procedures or views so they appear to be part of the SQL Server database.

Keep in mind that this approach (and any approach I can think of) won't be particularly fast. There's no magic to speed up the cost of relating data across databases and a network. It's biggest benefit is that it presents a consistent and simple view of the data to the application developer.

---

I have performed a test using latest dotConnect for MySQL and Entity Developer for SQL Server and succeeded in implementing the workaround as in the following example:

  var join = from d in GetDepts()
             from e in db1.Emps
             select new {
               e.ENAME,
               d.DNAME
             };
  join.ToList();
}
public IEnumerable<DEPT> GetDepts() {
  return db.DEPTs.AsQueryable();
}

Could you please send us a small test project illustrating the problem?
Update.The previous "cross-join" solution is actually not very appropriate.
The better solution is to materialize both collections and then perform an in-memory join of materialized objects:

public static MySqlDataContext mysql = new MySqlDataContext();
public static SQLDataContext sql = new SQLDataContext();

public static void Main() {
  var qA = mysql.tableA.ToList();
  var qB = sql.TableBs.ToList();
  var rows = from a in qA
             join b in qB on a.col equals b.col
             select a;

  foreach(var row in rows) {
      ...
  }
}