one query - 2 Databases

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
lumpimax
Posts: 5
Joined: Mon 02 May 2011 12:00

one query - 2 Databases

Post by lumpimax » Wed 16 Nov 2011 10:47

Hello!

I don't know if I am right in this forum - but I have a question:

Is it somehow possible to access 2 tables in DIFFERENT DATABASES (1xOracle, 1xPostgres).

For Example I would need:

I have an Oracle Table (validUsers) with 2 columns:
Oracle.validUsers.UID
Oracle.validUsers.valid


I have a Postgres Table (Users) with 2 columns:
PGsql.Users.UID
PGsql.Users.Username


Now I need to select all PGsql.Users.Username where the Oracle.validUsers.valid is set to 'True' - and best would be to do this with one - database independent - query?

Is this somehow possible? (BTW: It IS possible in MS Access with linked tables.... but we want to omit Access...)

Thank you!

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Fri 18 Nov 2011 15:37

You can try creating a database link from your Oracle server to PostgreSQL to perform such joins at the server side. However, Entity Developer does not support Oracle database links, thus you can perform such selects via the ExecuteQuery method only.

lumpimax
Posts: 5
Joined: Mon 02 May 2011 12:00

Post by lumpimax » Wed 23 Nov 2011 06:39

Ok thank you for your answer... altough it was not what I wanted to hear :wink:

Anyway - in this case I will try to fill some special preparation dataset and query in there....

Maybe a good idea to have some kind of 'linked tables' like access?

StanislavK
Devart Team
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Post by StanislavK » Wed 23 Nov 2011 11:49

The request on linked tables is actually more related to the DBMS than to the ORM solution: to load data from both sources via a single query, it is necessary that the DBMS itself redirects the query to the 'second' data source. And this is the case for Oracle database links; however, Oracle does not provide the metadata for database links in the same way as for 'common' database objects. This is the reason why Entity Developer cannot create a model from the objects on the linked server.

If a feature like Oracle database links is not used, you have to perform two different queries (e.g., one to the Oracle server and one to the PostgreSQL server). To do this with LinqConnect, you can, e.g., create two context instances with different mappings, perform two queries and join their results via LINQ to Objects.

Feel free to contact us if anything is unclear.

Post Reply