It's a common enough problem: you have a view that contains no primary key identifier, or even any combination of columns that guarantees each row to be unique. And you want to use this view in Entity Framework. What do you do?
It's been suggested that you modify the view to add a generated primary key, for instance:
SELECT row_number() OVER(ORDER BY Activity) AS ID, * FROM MyView
However, because Activity is not unique, the generated identifier can and will change each time you query the view.
Consider the following scenario where you have the view:
Activity CustomerName Cost
Skiing John 81.50
Skydiving Sarah 199.99
Skydiving Robert 215.00
and you add an ID column over Activity:
ID Activity CustomerName Cost
1 Skiing John 81.50
2 Skydiving Sarah 199.99
3 Skydiving Robert 215.00
Now suppose a user requests this view on a webpage.
Shortly afterwards, someone else adds a booking for snowboarding, causing the view to become:
ID Activity CustomerName Cost
1 Snowboarding Michael 85.00
2 Skiing John 81.50
3 Skydiving Sarah 199.99
4 Skydiving Robert 215.00
The first user wants to see more details about Sarah's skydiving booking, so he selects that row from the page and sends a new request with ID = 2. But the backing view has changed since he loaded the page, and now ID = 2 refers to John's skiing booking, causing the wrong info to be loaded.
The moral of the story: row_number() doesn't reliably uniquely identify each row in a view.
(Now, arguably this is a misuse of views, and it should be pointing at a table directly, but there could be any number of reasons to use a view, such as unioning the data from multiple sources).
Is there any solution or workaround for this issue?
