c# - LINQ: Join MySql and SQL Server tables

Question

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 high-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();
}

Answer 1

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.