12

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?

Phuc Thai
  • 718
  • 7
  • 17
John John
  • 1
  • 72
  • 238
  • 501
  • The main idea, is that we chose to work with Entity Framework as our data access layer is that it provides the following main features:- 1. the data access code are complied and tested as part of the whole application. more testable and provides fast development time comparing to stored procedures.2, EF provides a layer above the database , and our product can work on different database vendors. and as i mentioned that the only functionality we currently have which depends directly on the database is a stored procedure for performing advance search – John John Oct 22 '14 at 16:08
  • but if i want to start creating views for different functionality, then i will be losing the advantages that EF provides. so let me re-construct my original question is my current approach of retrieving the related data from the two databases using two separate sql statements and then join the data on the application server considered a very bad design ? Although i will have two separate queries but they are less expensive comparing to a single join sql statement ... – John John Oct 22 '14 at 16:09
  • I might have a solution for you, but I have a question: What are you selecting in your `query` variable? Are you taking data from both contexts, or only from one of them (i.e. only take the items in `tech` that exist in `resources`)? – Corey Adler Oct 23 '14 at 18:21
  • Also, what are the return types for `AllFindTechnolog` and `GetResources`? – Corey Adler Oct 23 '14 at 19:14
  • @IronMan84 i will name my contexts as context1 & context2.and in context1 i will return Technology objects, while in context2 i will return Resource objects. what i am doing is that i will return the first context1.Technology 100 records from context1 which start with the passed term. then inside each Technology object there is a field named IT360 which contains the ID of the record in context2. so basically i will select 100 context1.Technology objects in the first repository call, select their IT360 ids , then i will query context2.Resoure based on the selected 100 IT360 ids. then join them – John John Oct 24 '14 at 00:17
  • No, you misunderstand what I'm asking. The last line of you query says `select new //code goes here`. What are you selecting with that? – Corey Adler Oct 24 '14 at 14:38
  • i am selecting properties from the context1.Technology such as Name,Type, CPU + context2.Resource such as customer, location etc ... – John John Oct 24 '14 at 15:33
  • Ok, fine. And as far as the `AllFindTechnolog` and `GetResources` methods go: Are they returning a `List` of objects, or an `IEnumerable`/`IQueryable`? – Corey Adler Oct 24 '14 at 18:42
  • It is not possible using linq. – Vojtěch Dohnal Oct 29 '14 at 09:48

4 Answers4

4

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

}
CSharper
  • 5,420
  • 6
  • 28
  • 54
  • 3
    But to prevent an EF exception telling that you can't use two contexts in one query you should do `db.Table1.AsEnumerable()` and `db2.Table2.AsEnumerable()`. – Gert Arnold Nov 19 '15 at 19:49
  • 17
    Very bad idea in my opinion: each time you call 'AsEnumerable()' on one table, EF will retrieve the whole content of the table and load it in RAM. – Olivier Leneveu May 09 '17 at 16:25
4

@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);
}
chakeda
  • 1,551
  • 1
  • 18
  • 40
0

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!!! :)

Community
  • 1
  • 1
Gjohn
  • 1,261
  • 1
  • 8
  • 12
  • what i was searching for is a more out-of-the-box approach to support joining tables inside the DB, from two different databases using entity framework.. – John John Oct 22 '14 at 16:06
  • @johnG - You cannot join the two database tables across two different contexts without doing what you are doing currently which is selecting each of them and joing the resultsets. Only way you can do that is if both tables are being selected under the same context in someway - which is where the use of a Synonym comes in. – Gjohn Oct 22 '14 at 16:18
  • so will doing the join inside my application server and passing two database queries to each database considered a very bad design ? i mean is there real situations where these kind of limitation is solved by doing similar workarounds ? – John John Oct 23 '14 at 14:34
  • 1
    @johnG I think eventually it will depend on the application itself and are you willing to live with the limitation of having to do two contexts that will need to be executed, what impact will this have on the performance of your system. I have seen it be done both ways and it's just picking your poison. My one argument for a synonym would be if the second database you are selecting from were to be moved in one instance you need to compile and release your application, with a synonym you just change the synonym. – Gjohn Oct 23 '14 at 15:31
0

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.

CyberNinja
  • 872
  • 9
  • 25