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

score 4 · Answer 1 · answered Jun 06 '11 at 19:45

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:

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.

This may very well have worked, I never tried it. My DBA is very protective of his db servers and would fight me to the death before he let me (a lowly programmer) put my grubby hands on his server. Thank you for the suggestion anyway. — ean5533, Jun 07 '11 at 13:46

score 2 · Accepted Answer · edited May 23 '17 at 11:51

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) {
      ...
  }
}

I've added an edit to my question to show the code that I've tried, it's very similar to yours. I'm afraid I can't send you an entire sample project because it would contain sensitive information. However, the code example I've added above should make it clear what I'm doing. — ean5533, Jun 07 '11 at 12:32
Ah ha, there we go. I knew there had to be some sort of way to just do the join in memory. I didn't realize the .ToList() function would give me an object that can still be treated like a LINQ table. This gives me what I need, thank you. — ean5533, Jun 07 '11 at 13:42

LINQ: Join MySql and SQL Server tables

2 Answers2

Linked

Related