1

We have a legacy app that I am rewriting in .net. All of our databases are oracle and make use of database links. Is there any way for Entity Framework 6 to generate models based on tables located on a different database?

Currently the legacy app gets data from table like this 

SELECT * FROM emp@foo2;

where its db connection is to database foo that has a database link to the database foo2.

I would like to reproduce this using EF6. So far all I have found regarding this is this question.

Charles Driver Jr.
  • 315
  • 7
  • 17
  • Just create a view based on the link. Or, for better performance for large tables or flaky connections create a materialized view based on the table in the other database – kevinskio May 10 '16 at 17:54
  • Ok I will look into that. Could you point me to any good sources on how to do it? – Charles Driver Jr. May 10 '16 at 17:56

1 Answers1

0

You can do two things that EF 4 or higher will work with:

  • CREATE VIEW EMP as SELECT * FROM emp@foo2;
  • CREATE MATERIALIZED VIEW EMP as SELECT * FROM emp@foo2;

LOBS are not accessible across a database link without some contorted PL/SQL processing to read the LOB piece by piece.

I believe fast refresh does not work across database links so you must consider the size of the table on the linked database. If you are refreshing a million rows you may find the time to do this is an issue. Most large tables are full of tombstone data that never changes so a timestamp column with the last modified date could help you create a package that only picks out the changed data.

If you are doing complicated joins for either ensure that Oracle considers the column that would be the primary key as not null.

You can add a primary key on views and materialized view but it must be disabled. See here for details.

Community
  • 1
  • 1
kevinskio
  • 4,431
  • 1
  • 22
  • 36