Introduction
If you create a database view that does not have a discernable primary key, entity framework will be unable to map it. One technique is to use the TSQL "ROW_NUMBER() OVER", but when I did this, I had several performance issues.
Using the Code
My original database view was a union of three separate queries, and none had a primary key. Entity Framework's .edmx model was unable to map it. To resolve, I wrapped my query like this in the view:
SELECT a.*, ISNULL(ROW_NUMBER() OVER(ORDER BY ORDERITEMID DESC),0) AS RowID FROM (xxx xxx xxx) as a
WHERE in the above "xxx xxx xxx" were my three Unioned SQL Statements.
Entity Framework was able to map it OK, but when using it with 1500 rows of data, it was taking an hour or longer to process!
I spent hours trying to optimize the queries, until I realized (by removing the ROW_NUMBER wrapper) that that was the performance bottleneck.
To resolve, I instead re-coded the view to:
SELECT a.*, NEWID() AS RowID FROM (xxx xxx xxx) as a
I deleted the view from Entity Framework's .EDMX map and updated the model. But then, when using the view in C# code, nothing was returned. What I had to do was change the RowID column in the Entity model properties from Int64 to a Guid and mark it as an Entity Key. It then worked fine and my C# query to that view now only takes 7 minutes to process! FYI, I am doing a ton of processing while looping through the results.
