Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
387 views
in Technique[技术] by (71.8m points)

c# - 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 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();
}
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...