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

问题描述

我正在开发一个 ASP.NET MVC 4 Web 应用程序.我使用 Entity Framework 作为数据访问层，使用数据库优先的方法(.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，因为它会使我的代码不易维护和测试.

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，您可以对其进行进一步操作和查询.视图基本上是一个表，它很容易维护并且很容易绑定到强类型模型

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

另一种方式，与您发布的类似，您可以查询单独的 .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.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屋！