Entity Framework: duplicate rows in resultset from a view

http://jepsonsblog.blogspot.com/2011/11/enitity-framework-duplicate-rows-in.html

Entity Framework: duplicate rows in resultset from a view

After including a view I have in my database in an Entity Framework data model, I noticed that for some strange reason, the result was showing duplicates. When a set should have for instance 10 different rows, it might contain only 4 different rows, some of which were duplicated so it was still a total of 10 rows.
The view itself showed the correct results, but when called from code using the Entity Framework, it once again showed the wrong results.
After some testing I found out that the rows that were duplicated, were rows that shared certain values with the rows that were replaced by it. After checking this in my Entity Model, I soon found at that the Entity Model has a strange way of handling rows with equal primary key values.

I'll try and explain my findings:

If multiple rows share the same value in the primary column(s), only the first of these seems to be retrieved from the database and copied into these other rows. For instance, if a set has 2 columns, with the first being marked as the primary column, and the correct result should be:

A, 1
A, 2
B, 4
B, 6
B, 7
C, 5


The actual result in code with Entity Framework would become:


A, 1
A, 1
B, 4
B, 4
B, 4
C, 5


When including a view in your Entity Model, the model seems to simply use the first not-nullable columns as primary key (as all columns used in the primary key should be non-nullable).
To fix the problem, make sure that the primary key columns are chosen correctly. If you cannot create the correct primary key because of null-values or simply not having set of columns that differs for each row, try adding a column to your view that always contains a unique value for each row. After you add it, make sure it is set as the primary key in your Entity Model.


If I would set both rows to be the Primary Columns in the above example, I would get the desired result.

http://social.msdn.microsoft.com/Forums/en/linqprojectgeneral/thread/e25695d0-be93-47e2-beec-1dbb017e4dfd

The root cause you pointed out I think is correct, the problem is on the application side EF mapping, as EF has different object mapping rules with database. when the query results have been returned from database, the EF will do the mapping on application memory according to its own designed logic.

It's important to take these logic into account when you desingn your view query in your database side. I think you should do some adjustment on your view query.

I am not sure whether you have sorted the problem, if not please provide the database structure related to this issue and the view query you have written.

Thanks
Binze

Resolution:

Go to the view in edmx, set one of columns "Entity Key" as true if this column is unqiue in the result.