使用实体框架连接两个数据库中的表

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

我正在做一个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 .edmx files).

目前，在两个不同的数据库(例如，我有两个.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?

所以我正在寻找一个解决方案，在那里我可以使用实体框架进行连接，并产生一个数据库连接?

3 个解决方案

#1

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

如果您想使用单个数据库调用来完成此操作，您将必须在数据库中创建一个视图，该视图将两个表与单独的db表连接在一起。创建视图后，可以将其作为单个对象添加到EF中，您可以进一步操作并查询。视图将基本上是一个表，它将很容易进行主操作，并且很容易绑定到强类型模型

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.

另一种方式，类似于您已发布的内容，您可以查询单独的.edmx文件，然后加入它们。是的，对数据库有两个调用，但它不应该那么昂贵，而且可能不会注意到差异。

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

}

#2

@CSharper's answer is close. As @Oliver mentioned in the comments, IEnumerable loads 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);
}

#3

Might I suggest that you look into using a synonym in 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.

我建议您考虑在数据库中使用同义词。例如，您可以为您的tech表所在的数据库中的resources表创建同义词。这将确保您不需要维护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 post that was put out by a programmer who got it to work. Here is the original * question she asked.

更新:请注意，如果您正在使用同义词，您将需要对EDMX文件做一些额外的工作，以使它在实体框架中工作。这是一个程序员写的一篇博客文章。这是她提出的最初的*问题。

HAPPY CODING!!! :)

编码快乐! ! !:)

#1

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

}

#2