使用实体框架从两个数据库连接表 [英] Joining tables from two databases using entity framework

问题描述

我工作的一个ASP.NET MVC 4 Web应用程序。我使用的实体框架作为数据访问层，数据库采用第一种方式（的.edmx 文件）。

I am working on an ASP.NET MVC 4 web application. I am using Entity Framework as the data access layer, using database first approach (.edmx file).

目前我有一个里面有两个不同的数据库中定义的连接表的问题（即我有两个的.edmx 文件）。

Currently I have a problem in join tables that are defined inside two different databases (i.e. I have two .edmx files).

例如，如果我想加入的表，我执行以下查询： -

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.

我知道一个存储过程可以让我加入来自不同数据库的表纯粹是在服务器上，但我不希望使用SP，因为那会使我的code较少的维护和更少的测试。

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.

所以我在寻找一个解决方案，我所能做的使用实体框架的加入，并导致一个单一的数据库加入？

So I am searching for a solution where I can do the join using entity framework and to result in a single database join?

推荐答案

如果你想用一个单独的数据库做叫你必须创建在连接来自不同分贝的2个表的数据库视图。一旦创建视图，您可以将其添加到EF作为一个对象，你可以进一步和查询处理掉。该视图将基本上是一个表，它会很容易maintable容易绑定到一个强类型的模型

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

另一种方式，similiar像你已经发布，可以查询独立的的.edmx 文件，然后加入他们的行列。
是的，有2调用数据库，但它不应该是昂贵的，可能不会注意到差别。

Another way ,similiar like you have posted, you can query separate .edmx files 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.

using(var db = new MyEntities())
using (var db2 = new MyEntities2())
{
   var one = db.Table1//blah blah
   var two = db2.Table2//blah blah 

   var result = from o in one
                join t in two on o.Id equals t.Id
                //blah blah

}

这篇关于使用实体框架从两个数据库连接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！