asp.net-mvc 使用实体框架连接来自两个数据库的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26446145/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Joining tables from two databases using entity framework
提问by john Gu
I am working on an ASP.NET MVC 4 web application. I am using Entity Frameworkas the data access layer, using database first approach (.edmxfile).
我正在开发一个 ASP.NET MVC 4 Web 应用程序。我使用实体框架作为数据访问层，使用数据库优先方法（.edmx文件）。
Currently I have a problem in join tables that are defined inside two different databases (i.e. I have two .edmxfiles).
目前我在两个不同的数据库中定义的连接表有问题（即我有两个.edmx文件）。
For example if I want to join tables I am performing the following query:-
例如，如果我想加入表，我将执行以下查询：-
public ActionResult AutoComplete(string term)
{
var tech = repository.AllFindTechnolog(term).Take(100);//Call to the first database
var resources = repository.GetResources(tech.Select(a => a.IT360ID.Value).ToArray(), false);//call to the second database
var query = from techItems in tech
join resourcesItems in resources
on techItems.IT360ID.Value equals resourcesItems.RESOURCEID // join based on db2ID
orderby techItems.PartialTag
select new //code goes here
return Json(query, JsonRequestBehavior.AllowGet);
}
I will have two separate calls to the database, and a join inside the application server, which is not the best performance-oriented solution. Ideally the joins will happen completely inside the database engine.
我将对数据库进行两次单独的调用，并在应用程序服务器内部进行一次连接，这不是最好的面向性能的解决方案。理想情况下，连接将完全发生在数据库引擎内部。
I know that a stored procedure will allow me to join tables from different databases purely on the server, but I do not want to use SP because it will make my code less maintainable and less testable.
我知道存储过程将允许我纯粹在服务器上连接来自不同数据库的表，但我不想使用 SP，因为它会使我的代码不易维护和测试。
So I am searching for a solution where I can do the join using entity framework and to result in a single database join?
所以我正在寻找一种解决方案，我可以在其中使用实体框架进行连接并产生单个数据库连接？
回答by CSharper
If you want to do it with a single database call you will have to create a View in the database that joins the 2 tables from separate db's. Once the view is created you can add it to EF as a single object, which you can manipulate further and Query off of. The view will basically be a table and it will be easily maintable and easy to bind to a strongly typed model
如果您想使用单个数据库调用来完成此操作，则必须在数据库中创建一个视图，该视图连接来自不同数据库的 2 个表。创建视图后，您可以将其作为单个对象添加到 EF，您可以对其进行进一步操作和查询。视图基本上是一个表，它很容易维护并且很容易绑定到强类型模型
Another way ,similiar like you have posted, you can query separate .edmxfiles and then join them.
Yes, there is 2 calls to the database but it shouldn't be that expensive and probably won't notice a difference.
另一种方式，与您发布的类似，您可以查询单独的.edmx文件，然后加入它们。是的，有 2 个对数据库的调用，但它不应该那么昂贵，并且可能不会注意到差异。
using(var db = new MyEntities())
using (var db2 = new MyEntities2())
{
var one = db.Table1.AsEnumerable();
var two = db2.Table2.AsEnumerable();
var result = from o in one
join t in two on o.Id equals t.Id
// blah blah
}
回答by chakeda
@CSharper's answer is close. As @Oliver mentioned in the comments, IEnumerableloads the table into application memory, leading to crashes if you have a large database.
@CSharper 的答案很接近。正如@Oliver 在评论中提到的，IEnumerable将表加载到应用程序内存中，如果您有一个大型数据库，则会导致崩溃。
The solution is to use IQueryable, which can be called with LINQ - this produces SQL which is much faster.
解决方案是使用IQueryable，它可以用 LINQ 调用 - 这会产生更快的 SQL。
// This is a generic method, modify to your needs
public ActionResult Details(int? id)
var one = db.Table1.AsQueryable();
var two = db2.Table2.AsQueryable();
// since you're using MVC EF, I assume you want to put this in a viewmodel
// (in this case ObjectCombined)
// assume "id" is passed as parameter
Object1 result1 = (from o in one where one.id == id select o).Single();
Object2 result2 = (from t in two where t.id == o.id select t).Single();
ObjectCombined result = new ObjectCombined(result1, result2);
return View(result);
}
回答by CyberNinja
you can create a view or a stored procedure, your sql statement can then make cross db query just make sure your credentials can DML or DDL on both db. otherwise try the nested using entities that will make sure you will not get the linq bug when you dont declare the db entity inside a using statement.
您可以创建视图或存储过程，然后您的 sql 语句可以进行跨数据库查询，只需确保您的凭据可以在两个数据库上使用 DML 或 DDL。否则请尝试嵌套 using 实体，以确保当您不在 using 语句中声明 db 实体时不会出现 linq 错误。
回答by Gjohn
Might I suggest that you look into using a synonymin your database. For instance, you can create a synonym to the resources table in the database that your tech table is located. This will ensure that you will not need to maintain 2 EDMX files. Instead you can have a single EDMX file and you can simply join your tech table to the synonym of the resource table and voila - you are on your way.
我是否建议您考虑在数据库中使用同义词。例如，您可以在您的技术表所在的数据库中创建资源表的同义词。这将确保您不需要维护 2 个 EDMX 文件。相反，您可以拥有一个 EDMX 文件，并且您可以简单地将您的技术表加入资源表的同义词中，瞧——您就在路上。
UPDATE: Please note that if you are using synonyms there is an extra bit of work you will need to do to the EDMX file to get it working in Entity Framework. Here is a blog postthat was put out by a programmer who got it to work. Here is the original stackoverflowquestion she asked.
更新：请注意，如果您使用同义词，则需要对 EDMX 文件做一些额外的工作才能使其在实体框架中工作。这是一个让它工作的程序员发布的博客文章。这是她问的原始stackoverflow问题。
HAPPY CODING!!! :)
编码快乐！！！:)
