Getting a SQL View via Entity Framework returns incorrect result

Question

I have a very simple View in SQL Server which looks something like this, Where the Show is a result of LEFT JOIN with Character table:

+---------+----------+----------------------+  
|  Name   | Surname  |         Show         |  
+---------+----------+----------------------+  
| Enoch   | Thompson | The Boardwalk Empire |  
| Anthony | Soprano  | The Sopranos         |  
| Walter  | White    | Breaking Bad         |  
+---------+----------+----------------------+  

When I get this table via Entity Framework's context.CharacterView.ToList() in my application, the result looks like this:

+---------+----------+----------------------+  
|  Name   | Surname  |         Show         |  
+---------+----------+----------------------+  
| Enoch   | Thompson | The Boardwalk Empire |  
| Anthony | Soprano  | The Boardwalk Empire |  
| Walter  | White    | The Boardwalk Empire |  
+---------+----------+----------------------+  

However, in DB the CharacterView is as it should be.

---

Create view query

CREATE VIEW CharacterView AS
SELECT c.Name AS [Name], 
       c.Surname AS [Surname], 
       s.Name AS [Show]

FROM   [dbo].[Characters] AS c LEFT OUTER JOIN
       [dbo].[Shows] AS scen ON c.ShowId = s.Id

---

Answer 1

Right on, D.Mac. You solved a problem we have had for several weeks and we just got around to researching it. We had a view where EF incorrectly "inferred" a primary key that wasn't unique and that caused data duplication just as you see above in Martin's SHOW column. Thanks.

SQL Server fix - to add the ROW_NUMBER to the view as a unique ID as D.Mac suggested: SELECT ISNULL(CAST((ROW_NUMBER() OVER (order by T.PRODUCT_NAME)) as int), 0) as ID, etc.

Now Entity Framework AUTOMATICALLY includes the ID column as an ENTITY KEY for the VIEW. (Right-click on the view in the EDMX diagram and you should see the ID flagged as an ENTITY KEY)