C# Entity LINQ returns wrong and duplicate values

Question

I use the following code :

List<vw_GetIMeasurements> Imeasurements = context.vw_GetIMeasurements.Where(f => f.MEMBERID == userID).Distinct().ToList();

This returns a list with 12 Values like it should but these values are always the same(even though they aren't in the DB.(Even if they were distinct should solve this)

DB :

The raw value that is returned is also wrong. What would be the cause of this issue and how can I resolve it?

Answer 1

I have had this problem before - it turned out that the view did not have unique entity keys set - make sure your primary keys for your view that is auto created by entity framework is indeed unique...

Answer 2

I had a similar issue - the database view looked fine in SQL server but when debugging, duplicate rows were appearing where rows shared a particular attribute value.

I found that one of the important unique keys was from a outer join table - therefore had the potential to be null - and the model was not assigning as unique.

I was able to fix this problem by setting this field to ISNULL(field, '') - ensuring that a value is always returned - and upon updating, the model correctly interprets the field as unique and as part of a compound key and the rows returned correctly.

I know that one might argue the view design is at fault (i.e. I should not be using an outer join), but in this instance, there was a specific need for the outer join. The fix works because the compound key is still unique even if "" is returned multiple times.