Joining tables from two databases using entity fra

问题:

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?

回答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

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

}

回答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.

The solution is to use IQueryable, which can be called with LINQ - this produces SQL which is much faster.

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

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 stackoverflow question she asked.

HAPPY CODING!!! :)

回答4:

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.