Devart Forums

Thanks Andrey. For anyone reading this thread, the workaround I plan to use is to split the view into two separate views and perform the union in my C# code.

Hello,

At my company we have DevArt OraDirect .NET, apparently the precursor to dotConnect for Oracle. We purchased it last year and have been using it in several applications to map to tables, views, and stored procedures in our Oracle database.

Recently I tried to add a new view to an entity model. This has never been a problem before. However, I am not able to add the view to the model. When I choose to refresh from database, the view appears in the list of available views. I check it and click OK, and Visual Studio works away for a while as per usual, but at the end the view is not present in the model. No error message is given.

The only difference I can see with this view is that it contains a UNION ALL statement. The full text of the view's code follows:

CREATE OR REPLACE FORCE VIEW participant.service_credit_view(employment_id,
service_type,
service_amount,
benefit_flag,
eligibility_flag,
vesting_flag
) AS
SELECT "EMPLOYMENT_ID",
"SERVICE_TYPE",
"SERVICE_AMOUNT",
"BENEFIT_FLAG",
"ELIGIBILITY_FLAG",
"VESTING_FLAG"
FROM (SELECT employment_id,
'MBR' AS service_type,
participant_pkg.calculate_membership_service
(service_credit_view_pkg.get_retirement_system_id,
'MBR',
employment_id,
service_credit_view_pkg.get_effective_date
) AS service_amount,
'Y' AS benefit_flag,
'Y' AS eligibility_flag,
'Y' AS vesting_flag
FROM employment
WHERE retirement_system_id =
service_credit_view_pkg.get_retirement_system_id AND
(service_credit_view_pkg.get_employment_id IS NULL OR
employment_id =
service_credit_view_pkg.get_employment_id
)
UNION ALL
SELECT amount.employment_id,
amount.service_type,
amount.service_amount,
st.benefit_flag,
st.eligibility_flag,
st.vesting_flag
FROM (SELECT employment_id,
service_type,
SUM(service_amount) AS service_amount
FROM service_transaction
WHERE retirement_system_id =
service_credit_view_pkg.get_retirement_system_id AND
(service_credit_view_pkg.get_employment_id IS NULL OR
employment_id =
service_credit_view_pkg.get_employment_id
) AND
effective_date <=
service_credit_view_pkg.get_effective_date
GROUP BY employment_id, service_type) amount
JOIN
service_type st ON st.service_type = amount.service_type
)
ORDER BY employment_id ASC, service_type ASC;

Why would I not be able to add this view to my model? If there is something about OraDirect .NET that prevents this, would I be able to do it in dotConnect for Oracle?

Thanks in advance for any help,
Ken

Hi huanghexd, thanks for taking the time to reply. I had found that tip in an earlier post and tried it before posting this, but it gives me the following compile-time error:

Error 5: The 'StoreGeneratedPattern' attribute is not allowed.

Any idea what might cause this?

Hello,

I am using OraDirect.NET with the Entity Framework. Many of our tables have identity columns, the values of which are set by triggers which use sequences. We need to be able to retrieve the values of these columns after calling SaveChanges().

In SQL Server, we would simply set the table's identity column, which would be automatically updated in the entity after calling SaveChanges(). However, Oracle doesn't have an equivalent.

If there was a way to get the sequences NEXTVAL and supply this to the table, that would be another way to approach the problem. However, it appears that the Entity Framework does not support sequences, so this approach is not an option.

Thanks in advance for any help!