I have a View in an Oracle database. I want to use this view in my ASP.NET MVC project. I know that in order to add that view to EF, that view needs to have a primary key. I don't know how to add a primary key as a new column to that view. None of the columns in the view is unique. How can I add that? Thanks.
Asked
Active
Viewed 1,613 times
1
-
Is the a combination of columns which uniquely identify a row in the view? – APC Apr 19 '17 at 14:24
-
`ROWNUM` or `ROW_NUMBER` should be enough. – Ivan Stoev Apr 19 '17 at 14:24
-
@IvanStoev a small snippet would be great. – jason Apr 19 '17 at 14:25
-
@APC unfortunately, no – jason Apr 19 '17 at 14:28
-
`SELECT ROWNUM AS PK, ...` Just additional column to view select list, and then map it as PK in EF. – Ivan Stoev Apr 19 '17 at 14:30
-
@IvanStoev a more explanatory snippet would be great :D I'm new to Oracle so I couldn't do it myself :) – jason Apr 19 '17 at 14:35
-
@IvanStoev on the web, there is only one example with unions, but my code is with joins. – jason Apr 19 '17 at 14:38
-
Do you use the view as read only or should it be updateble as well? – Marmite Bomber Apr 19 '17 at 16:43
-
1If your starting position is that even a full column list combination is not unique **there is no view that can save you**. You'll have to dig in your DB design and find or add proper primary keys there.... – Marmite Bomber Apr 19 '17 at 17:01
1 Answers
1
Building on Ivan's comment, you could build your view like this:
SELECT rownum, *
FROM (your_current_view_query)
DCookie
- 42,630
- 11
- 83
- 92
-
This approach provides a unique key, but is not determinnistic, i.e. different queries can assign different "primary keys" to one row. This is **not what is expected from a PRIMARY KEY**. – Marmite Bomber Apr 19 '17 at 16:57