LINQ:加入MySQL和SQL Server表 [英] LINQ: Join MySql and SQL Server tables
问题描述
我有相关的数据生活费两个完全独立的数据库,我需要从这两个数据库中的信息。其中的一个数据库住一个MySQL服务器和其他人住一个MS SQL Server上。不要问为什么,我们有相关的数据生活在两个完全不同的服务器上,这是一个很长的故事。
这是一个高层次的角度来看,我需要加入 MySqlTableA 为 SQLServerTableB ,做一些复杂的限制,并有可能做一些分组依据和计数。
我试图找到一种方法,使这两个数据库之间的连接相当方便。我认为LINQ可能会解决我的问题,但我不能创建具有MySQL和SQL服务器源的单一方面,据我所知。我可以把两个源在不同的语境 - 使用Devart的LinqConnect创建一个MySQL背景下 - 但LINQ不允许跨情境的连接。 (我想在这里介绍的方法,但它没有工作:模拟跨语境Joins- -LINQ / C#)
所以,我有哪些选择?有没有一种方法能够有效地加入这两个不同的数据库服务器上的表(虽然LINQ或其他方式),还是我经历有循环和手工加入数据?
编辑:
正如前面提到的,我已经试过了AsQueryable已()的解决方法,但我还是得到了跨情境例外。这里是我的代码:
公共静态MySqlDataContext的mysql =新MySqlDataContext();
公共静态SQL SQLDataContext =新SQLDataContext();
公共静态无效的主要(){
无功行=从在mysql.tableA
JOIN B在a.col GetTableBs()等于b.col
选择一个;当行枚举
//异常被抛出。
// InvalidOperationException异常:查询包含对不同数据上下文定义的项目引用。
的foreach(行VAR行){
:
}
}
公共静态的IEnumerable<表B> {
返回sql.TableBs.AsQueryable();
}
我已经进行使用测试MySQL和实体开发的SQL Server最新的dotConnect,并成功地实施解决方法如下面的例子:
从变种d在GetDepts加入=()从电子
在db1.Emps
。选择所需的新{
e.ENAME,
d.DNAME
};
join.ToList();
}
公开的IEnumerable<&DEPT GT; GetDepts(){
返回db.DEPTs.AsQueryable();
}
请你的发送给我们的说明问题的一个小的测试项目?结果
更新。先前的交叉联接其实解决办法不是很妥当结果
更好的解决方案是兑现两个集合,然后执行一个内存中加入物化的对象:
公共静态MySqlDataContext的mysql =新MySqlDataContext();
公共静态SQL SQLDataContext =新SQLDataContext();
公共静态无效的主要(){
VAR QA = mysql.tableA.ToList();
无功QB = sql.TableBs.ToList();
无功行从一个QB在QA
JOIN B上a.col =等于b.col
选择;
的foreach(行VAR行){
:
}
}
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();
}
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屋!