Devart Forums

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

This view is unacceptable for Microsoft Entity Data Model Wizard.
Entity Framework itself requires entity to contain Entity Key.
Entity Data Model Wizard builds Entity Key either using primary key or using NOT NULL columns.
The view containing UNION ALL can contain neither primary key nor NOT NULL columns.
But the upcoming Devart Entity Developer 2.0 will include the possibility of adding such view to the model.
Entity Key will be generated over all columns in this case.

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.

There is an alternative way.
You can create an entity from one of your separate views and add your initial view's definition as the
DefiningQuery tag for the generated EntitySet.