Joining tables from two databases using entity framework

Question

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?

Answer 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

}

Answer 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);
}