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

问题描述

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

目前我在两个不同的数据库中定义的连接表中有一个问题例如，我有两个 .edmx 文件）。

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

  public ActionResult AutoComplete（string term）
 {
 var tech = repository.AllFindTechnolog term）.Take（100）; //调用第一个数据库
 var resources = repository.GetResources（tech.Select（a => a.IT360ID.Value）.ToArray（），false）; //调用第二个数据库
 
 var query = from techItems in tech 
 join resourcesItems in resources 
 on techItems.IT360ID.Value equals resourcesItems.RESOURCEID // join based on db2ID 
 orderby techItems.PartialTag 
选择新的// c ode在这里
 
 return Json（query，JsonRequestBehavior.AllowGet）; 
} 
  

我将对数据库进行两次单独的调用，并在应用程序中连接服务器，这不是最好的性能为导向的解决方案。理想情况下，连接将完全发生在数据库引擎中。

我知道一个存储过程将允许我从纯数据库服务器上的不同数据库连接表，但是我不想要使用SP，因为它会使我的代码更少的可维护性和更少的可测试性。

所以我正在寻找一个解决方案，我可以使用实体框架进行加入，并导致单个数据库连接？

解决方案

如果要使用单个数据库调用来执行此操作，则必须在数据库中创建一个View，该视图从单独的数据库连接2个表。创建视图后，您可以将其添加到EF作为单个对象，您可以进一步操作和查询。这个视图基本上是一个表，它将很容易被maintable和容易绑定到强类型的模型

另一种类似于你已经发布的方式，你可以单独查询 .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 
 
} 
  

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

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.

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?

解决方案

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

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.AsEnumerable();
   var two = db2.Table2.AsEnumerable(); 

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

}

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