Different Read/Update Views/Tables

[Alladin]

Hi there,

Is it possible to specify different table/view names for SELECT and DML operations for Entity Framework?

The reason:
1) I have a normal table USERS with data (TS column used for concurrency check, filled in trigger on insert and update - StoreGeneratedPattern - Computed).
2) I have a view TUSERS, that exposes USERS table and adds few calculated columns.
3) I have instead of trigger on TUSERS view, that intercepts changes to calculated columns and performs additional business logic.
4) EF fails to update TUSERS because of RETURNING statement.

Possible solutions:
A) Make DevArt EF to generate valid SQL for DML (e.g. WITHOUT RETURNING, but with Entity Keys). Estimated time?

B) Add empty columns to USERS table, define normal trigger just for those columns, implement business logic inside the trigger. In this case USERS and TUSERS have the same structure, so TUSERS can be used for SELECT and USERS for DML. So RETURNING will work properly. Now the question, how to tell DevArt EF to use different table names for SQL generation?

UPDATE1.

C) As a variant, possibility to intercept DbCommand before execution (aka EF6) and modify CommandText. How realistic?

D) Any other solutions? maybe there is an typical solution I miss?

Thank you in advance,
Lex

[Shalex]

A) Make DevArt EF to generate valid SQL for DML (e.g. WITHOUT RETURNING, but with Entity Keys). Estimated time?

A similar functionality ("a select statement instead of a returning clause") was requested at http://devart.uservoice.com/forums/1051 ... toregenera. We will investigate the possibility of implementing the corresponding option and post here about the results. There is no timeframe at the moment.

B) Add empty columns to USERS table, define normal trigger just for those columns, implement business logic inside the trigger. In this case USERS and TUSERS have the same structure, so TUSERS can be used for SELECT and USERS for DML. So RETURNING will work properly. Now the question, how to tell DevArt EF to use different table names for SQL generation?

No way. If you create a defining query in SSDL for a particular table (view) in SSDL, the DML statements will be generated basing on this defining query using the name of the same table (view).

C) As a variant, possibility to intercept DbCommand before execution (aka EF6) and modify CommandText. How realistic?

We did not check this Entity Framework functionality.

D) Any other solutions? maybe there is an typical solution I miss?

1. Add the TUSERS view to your model and generate a class basing on it.
2. Create three (Insert/Update/Delete) stored procedures for applying the corresponding action to the USERS table (!) when you will be working with the TUSERS model entity: Tools > Entity Developer > Model Explorer > Model.Store > Stored Procedures > Add > New Command Text.
3. Right click on the class in CSDL > Stored Procedure Mapping and map Insert/Update/Delete commands to the stored procedures created on the step 2.

[Alladin]

Using Stored Procedures especially for Update is really bad idea, because it requires to load entities before update to get all original column values. It is bad idea because of:

1) Simple attachment of entity to ObjectContext and marking some properties as modified corrupts the data.

2) Roundtrip of unchanged data is just very bad design. (What if you have blobs?)

And generally rely on existence of ROWID, knowing that ORACLE DOES NOT SUPPORT it everywhere is just bad design decision for LinqConnect/EF.

[Shalex]

We are investigating the issue.

[Shalex]

The config.DmlOptions.UseReturningClause configuration option (default value is True) is added to provide the possibility to turn off generation of RETURNING clause when obtaining database-generated values with INSERT/UPDATE commands. We will post here when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

New version of dotConnect for Oracle 7.9 is released!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://forums.devart.com/viewtopic.php?f=1&t=27875.