Getting SQL view through Entity Framework returns incorrect result

Question

I have a very simple view in SQL Server that looks something like this: Where Show is the result of a LEFT JOIN with a Symbol table:

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

When I get this table through the Entity Framework context.CharacterView.ToList() in my application, the result is as follows:

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

However, in the CharacterView database, it is just as it should be.

---

Create view request

 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, D.Mac. You solved the problem that we had for several weeks, and we just learned how to research it. We had an idea where EF incorrectly “outputted” the primary key, which was not unique, and this caused data duplication in the same way as you see in the Martin SHOW column above. Thanks.

The SQL Server fix is ​​to add ROW_NUMBER to the view as a unique identifier, for example D.Mac: SELECT ISNULL (CAST ((ROW_NUMBER () OVER (order T.PRODUCT_NAME)) as int), 0) as an identifier, etc.

Entity Framework AUTOMATICALLY now includes an identity column as the ENTITY key for VIEW. (Right-click on the view in the EDMX diagram and you will see the identifier marked as the ENTITY key)