DELETE CASCADE missing using generate database from model

vifani · Post by **vifani** » Wed 18 Aug 2010 08:39

I found another issue (the previous is http://www.devart.com/forums/viewtopic. ... 9650#59650) using "Generate database from model" EF4 features with Devart SSDLToOracle.tt.

When I set a DELETE CASCADE rule in a relation (both one to many or many to many), the DDL sql file doesn't set any action on the constraint.

The Cascade Rule is set both in CSDL and SSDL.

Using SQL Server tt to generate DDL, it generates correctly the DELETE CASCADE ACTION.

I am currently using the 5.70.146.0 licensed build.

Thank you

AndreyR · Post by **AndreyR** » Wed 18 Aug 2010 11:50

I have just performed a test and the cascade option was present in the generated SQL.
I am using the 5.70.152 build of dotConnect for Oracle.
Please make sure that your SSDL contains the necessary OnDelete actions (open the model in XML Editor and check it, the designer shows only conceptual model information).

vifani · Post by **vifani** » Wed 18 Aug 2010 15:06

I have just tried using the last version 5.70.152 build, but there is the same problem.

The SSDL contains the OnDelete action, but the generated SQL doesn't have the ON DELETE CASCADE ACTION set.

I am using Visual Studio 2010 with Entity Framework 4 and POCO Generation.

I have also another issue to signalize you using transactions.

I have two threads that performs queries. When in one thread there are both operations in a TransactionScope and operations without TransactionScope on the same data, I get an exception ORA-24776.

Performing some debugging I have found that when a transaction is disposed (calling complete or not), sqldeveloper shows that the corresponding session is not closed immediately as I expected. So when the other thread perform some operation, I receive a ORA-24776 exception (cannot start a new transaction).

I suppose the problem is due to connection pooling, so I tried to disable connection pooling in the connection string and it works. Setting Pooling=false, the transactions work as expected. I also tried to open and close the EntityConnection manually and it works also with Pooling=true.

So using both Pooling=true and managing connection automatically, I get the exception ORA-24776.

The issues exists both in Oracle 11.2.0.1 and Oracle 10.2.0.1.

Can I send you a little solution sample that shows the two issues?

AndreyR · Post by **AndreyR** » Thu 19 Aug 2010 13:24

Thank you for the project, i have reproduced both issues.
I will let you know about the results of our investigation.

vifani · Post by **vifani** » Fri 01 Oct 2010 09:30

Updating to the version 5.70.170, the ON DELETE CASCADE has been added to the generated script, but when there is no action associated to the ON DELETE, DDT template generates the script "ON DELETE NO ACTION". This is not correct and I get an error ORA-00905 Missing keyword.

When there is not action associated to the DELETE, it should not be specified in the script because it is the default behaviour.

Can you update me about the other problems?

Thank you.

AndreyR · Post by **AndreyR** » Fri 01 Oct 2010 15:01

Thank you for the report, we have reproduced the situation.
I will let you know as soon as the problem is fixed.
We are investigating the problem with TransactionScope.
I will post here about teh results of our investigation.

AndreyR · Post by **AndreyR** » Tue 26 Oct 2010 07:09

We have fixed the problem with the ON DELETE NO ACTION problem.
This fix is available in the latest 5.70.180 build.
You can download a trial version from here or get a non-trial version in Registered Users' Area if you are a registered user.

vifani · Post by **vifani** » Tue 26 Oct 2010 09:00

AndreyR wrote:We have fixed the problem with the ON DELETE NO ACTION problem.
This fix is available in the latest 5.70.180 build.
You can download a trial version from here or get a non-trial version in Registered Users' Area if you are a registered user.

Thank you very much!

vifani · Post by **vifani** » Thu 18 Nov 2010 09:34

AndreyR wrote:Thank you for the report, we have reproduced the situation.
I will let you know as soon as the problem is fixed.
We are investigating the problem with TransactionScope.
I will post here about teh results of our investigation.

Hi AndreyR, can you update me about the transaction issues? We need a solution because perfomance are really ugly using your provider with connection pooling disabled.

AndreyR · Post by **AndreyR** » Thu 18 Nov 2010 13:11

We had a similar user request, but I failed to reproduce the problem with pooling.
Could you please send us (support * devart * com, subject "Transaction Pooling Error") a small test project illustrating the issue?
Please note, however, that multi-threaded usage of DbConnection object isn't safe (because each non-static DbConnection instance is not thread-safe).

vifani · Post by **vifani** » Thu 18 Nov 2010 14:00

AndreyR wrote:We had a similar user request, but I failed to reproduce the problem with pooling.
Could you please send us (support * devart * com, subject "Transaction Pooling Error") a small test project illustrating the issue?
Please note, however, that multi-threaded usage of DbConnection object isn't safe (because each non-static DbConnection instance is not thread-safe).

I have just sent you the test project. Please, let me updated. It's very important for us to resolve this issue because we have invested on your oracle provider to use oracle dbms.

AndreyR · Post by **AndreyR** » Mon 13 Dec 2010 18:11

Could you please try installing the latest build and specifying the "Transaction Scope Local" connection string parameter to true?
This should help.

vifani · Post by **vifani** » Sat 08 Jan 2011 08:30

Hi AndreyR,

I tried and I can confirm that the issue has been solved

Thank you very much!