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

How do you know that the query is returning the results you mention? Are you viewing it in the debugger? Or are generating output? If output, then lets see the code you're using to display it. — Erik Funkenbusch
– Erik Funkenbusch, Commented Nov 6, 2012 at 16:28
The results I mention as output are from the debugger. To clarify a bit - the problem is that the context.CharacterView.ToList() contains Shows that are not present in the dbo.CharacterView - that is EF does not reflect the DB correctly. — Martin
– Martin, Commented Nov 6, 2012 at 16:44
Thank you for the question and the answers below. The EF Designer gives you a "bad" hint: warning 6002: The table/view 'yourView' does not have a primary key defined. The key has been inferred and the definition was created as a read-only table/view. I added the message for people searching for this warning. — Alex
– Alex, Commented Aug 15, 2014 at 16:15

Debbie A · Accepted Answer · 2014-01-15 21:36:44Z

10

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)

answered Jan 15, 2014 at 21:36

Debbie A

4334 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

balbelias Over a year ago

Calling .AsNoTracking() also helps.

Milind Thakkar Over a year ago

@balbelias: It indeed worked by adding .AsNoTracking(). But fail to understand how? If it's because of not having PK then how AsNoTracking helps? Any idea?

balbelias Over a year ago

@milind-thakkar I assume EF uses key-valued storage for storing loaded entities in context and it obviously uses PK as key. Having not unique PK the loaded data became invalid after inserting in those storage (some rows are missed). Using .AsNoTracking() you make EF not to use those storage but just load and map data to POCO classes. Solution in answer above works because it adds unique PK (row number) and key-value storage starts working properly.

trees_are_great Over a year ago

Adding .AsNoTracking() as an answer. @balbelius, feel free to add the answer and I will delete mine

trees_are_great · Accepted Answer · 2019-02-18 12:01:08Z

8

Adding .AsNoTracking() as @balbelias said in comment fixed this for me.

answered Feb 18, 2019 at 12:01

trees_are_great

3,9113 gold badges34 silver badges66 bronze badges

1 Comment

Debbie A Over a year ago

Good article on how to use .AsNoTracking(). I had never heard of it. c-sharpcorner.com/UploadFile/ff2f08/…

Collectives™ on Stack Overflow

Getting a SQL View via Entity Framework returns incorrect result

Create view query

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Create view query

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related