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:
The actual result in code with Entity Framework would become:
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.