Linq to Entities joins

[rbr]

Hello,



I have two EDMs in my application that I need to somehow join.
The problem I need to solve is how to, essentially, create a inner
join between two tables in different databases on different database
servers. So, for example, I have a table of studentids in a table on
one db and I need to only return student records from a table in a
different db that have a corresponding id in the other db. Is there a
way to accomplish this in linq to entities?

Thanks in advance!

rbr

 

[Pavel Minaev]

I have two EDMs in my application that I need to somehow join.
The problem I need to solve is how to, essentially, create a inner
join between two tables in different databases on different database
servers. So, for example, I have a table of studentids in a table on
one db and I need to only return student records from a table in a
different db that have a corresponding id in the other db.  Is there a
way to accomplish this in linq to entities?

I don't think you can, in general, express a cross-database cross-
server join in SQL in the first place, so how would LINQ to Entities
handle it?

You can do such a join on plain IEnumerable instances (with LINQ to
Objects), but it would, of course, be in-memory only.

 

[Mr. Arnold]

Hello,



I have two EDMs in my application that I need to somehow join.
The problem I need to solve is how to, essentially, create a inner
join between two tables in different databases on different database
servers. So, for example, I have a table of studentids in a table on
one db and I need to only return student records from a table in a
different db that have a corresponding id in the other db. Is there a
way to accomplish this in linq to entities?

Database1Entities db1 = new Database1Entities();

Database2Entities db2 = new Database2Entities();

var results = from d1 in db2.Student

join d2 in db2.StudentRec on

d1.StudentID equals d2.StudentID

select new

{

id = d1.StudentID,

name = d2.Name

};

Hey, don't hold me to it, but I don't see why you can't do it, try it.

You should be able to use two nested 'using' statments too.

using(var db1 = new Database1Entities())

{

using(var db2 = new Database2Entities())

{

var results = from d1 in db2.Student

join d2 in db2.StudentRec on

d1.StudentID equals d2.StudentID

select new

{

id = d1.StudentID,

name = d2.Name

};

}

}




__________ Information from ESET NOD32 Antivirus, version of virus signature database 4034 (20090424) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[rbr]

Thank you for this suggestion. I will give it a shot ASAP and respond
with my results. I attempted the using statements and received an
Illegal Operation error that said something to the effect that the
query used results from another context.

As for the first response from Pavel, you can do this in SQL and I do
it quite often using linked servers. However, I have read the EF book
by Julia Lerman and it indicated that one of the focus areas of EF is
that it abstracts the db from the developer. In doing so, it makes the
source of the data irrelevant. And, as you can have multiple contexts
serving-up data it only makes sense that you should be able to relate
the different contexts. Also, in a not too distant future release of
EF, it is intended that there will be the ability to have a single
context model data from more than one datasource. Now this would be of
tremendous value to the enterprise market that I consult to!

Thank you both for your responses.

Best regards,

rbr

 

[Mr. Arnold]

Thank you for this suggestion. I will give it a shot ASAP and respond
with my results. I attempted the using statements and received an
Illegal Operation error that said something to the effect that the
query used results from another context.

If that doesn't work, then use a List<t>.

Just pull all the records from both database tables.

You then make a collection of students.

var students = new List<Model.Student>();

var results = After you get the results back from the query for all records,
then you foreach loop the results.

foreach(var student in results)
{
students.Add(stud);
}

You do the same thing for StudentRec.

Now that you have two collections populated. You should be able to query the
collections.

var tehresults = from c1 in students

join c2 in studentrecs on

c1.StudentID equals c2.StudentID

select new

{

id = c1.StudentID,

name = c2.Name

};



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4035 (20090425) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[Pavel Minaev]

If that doesn't work, then use a List<t>.

Just pull all the records from both database tables.

You then make a collection of students.

var students = new List<Model.Student>();

var results = After you get the results back from the query for all records,
then you foreach loop the results.

foreach(var student in results)
{
   students.Add(stud);

}

You do the same thing for StudentRec.

Don't do this. There's no need to do an in-memory copy just to
circumvent the LINQ to SQL query evaluator. You merely need to cast
your entity sets (which are IQueryable<T>) to IEnumerable<T> using
AsEnumerable() method before doing the query, and then it'll be
performed by LINQ to Objects (and hopefully it will avoid memoizing at
least one of the tables).

At the very least, when creating an instance of List<T> from an
IEnumerable<T>, the best way is to use Enumerable.ToList(), and not an
explicit loop.