LINQ:连接 MySql 和 SQL Server 表 [英] LINQ: Join MySql and SQL Server tables

问题描述

我将相关数据保存在两个完全独立的数据库中，我需要来自这两个数据库的信息.其中一个数据库位于 MySql 服务器上，另一个位于 MS SQL Server 上.不要问为什么我们的相关数据存在于两个完全不同的服务器上，这是一个很长的故事.

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.

从高层次的角度来看，我需要将 MySqlTableA 加入到 SQLServerTableB，做一些复杂的限制，并可能做一些 GROUP BY 和计数.

From a high-level perspective, I need to join MySqlTableA to SQLServerTableB, do some complex restrictions, and possibly do some GROUP BYs and counts.

我正在尝试找到一种方法，使这两个数据库之间的连接变得相当容易.我认为 LINQ 可能会解决我的问题，但据我所知，我无法创建同时具有 MySql 和 SQL Server 源的单个上下文.我可以将两个源放在不同的上下文中——使用 Devart 的 LinqConnect 创建一个 MySql 上下文——但 LINQ 不允许跨上下文连接.(我尝试了这里描述的方法，但没有奏效:Simulating Cross Context Joins--LINQ/C#)

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#)

那么我的选择是什么?有没有办法有效地连接这两个不同数据库服务器上的表(通过 LINQ 或其他方式)，或者我必须手动循环并连接数据?

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?

如前所述，我已经尝试了 AsQueryable() 解决方法，但我仍然遇到跨上下文异常.这是我的代码:

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

推荐答案

我使用最新的 dotConnect for MySQL 和 Entity Developer for SQL Server 进行了测试，并成功实现了 解决方法，如下例所示:

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

这篇关于LINQ:连接 MySql 和 SQL Server 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！