Entity Framework with Oracle Edition-based Redefinition (EBR):

[rlcarrierscsharp]

Question

We are planning to use entity framework for ORM integration with our oracle database. But we soon also plan to use Oracle Edition-based redefinition (EBR) for zero downtime deployments. In order for EBR to work, applications must only query data via editioned views (never directly against the tables).

When experimenting with this architecture we are running into problems when it comes to testing. One issue is that our views do not have a primary key and thus cannot be tracked with EF. We often get runtime exceptions saying that a certain entity cannot be tracked because it does not have a primary key.

Furthermore, we are unsure on how to build unit tests which do not assume any underlying knowledge of the database. For example, if we have some Table X that is exposed by View Y, the developer creating unit tests has to know to seed data into Table X even though the underlying code never directly interacts with that table. This becomes more complicated with views that involve multiple tables. Is there something wrong with our approach here, or some way that we can write tests which only interact with the objects which are actually exposed to the application?

Your code

Stack traces

Verbose output

EF Core version

9.0.0

Database provider

Oracle.ManagedProvider

Target framework

.NET 9.0

Operating system

Windows 11

IDE

Visual Studio 2022 (latest)

[roji]

In order for EBR to work, applications must only query data via editioned views (never directly against the tables).

I'm not sure if this is what you need, but EF has full support for entities which are read via a view but updated via a table (docs). Otherwise, if both querying and updating happens via the view, that view can be considered a regular table as far as EF is concerned, regardless of how exactly it's defined in the database (EF probably won't be able to create it in its migrations, but it sounds like that's OK for your scenario).

One issue is that our views do not have a primary key and thus cannot be tracked with EF.

How exactly would you expect this to work? In order for EF's change tracking to work, entity types must obviously have some sort of primary key in order to identify an instance/row (so that the same row can be updated after querying). How exactly are you expecting to perform updates if your rows have no primary key?

For example, if we have some Table X that is exposed by View Y, the developer creating unit tests has to know to seed data into Table X even though the underlying code never directly interacts with that table.

As written above, EF does support cases where read operations (queries) are performed against a view, whereas write operations (updates) are performed against a table; this may be sufficient for you.

Otherwise, purely for unit tests, it may be acceptable to simply seed your tables via raw SQL, and then use EF to query that data via whatever views you have on top of it.

Overall, it's a bit difficult to provide further advice without knowing exactly what you're trying to achieve and how your schema actually looks like - more details may help us help you better.