This morning my coworker and I diagnosed a case of Entity Framework 4 returning the wrong data from a view. This was not a case of “we messed up how we handle the data” or “we query the data incorrectly”, we actually found that EF was simply returning genuinely wrong data.
When you add a view to Entity Framework, it selects one or more columns that it considers the “entity key”, which determines uniqueness of the row. (How it selects them is a mystery.) If the selected columns are the same for more than one row, EF simply returns a copy of the first row matching that set, even if the rows aren’t actually the same. Thus, I had three rows that were different, but because the columns EF had selected as the “entity key” columns matched, it showed me the same results for all three rows. (Debugging proved that as I iterated over the result set, it returned the same instance to me three times.)
In my particular case, every row of the results should be considered unique. However, there was no one column which could be used as a unique entity key. There was combination of two column which, together, define uniqueness, but when I tried to use them as the entity key EF threw errors, because one of them sometimes has null values. To get around this I added a fake column to my SQL which I defined as “newId() AS rowid”. I tried making that the entity key but EF threw additional errors. I correctly guessed that perhaps EF had simply gotten too screwed up about my view for the problem to be resolved directly, so I dropped and re-added the view from EF designer (we use database-first), and this resolved the problem.
So, in summary, beware results from views in EF, they may be false if you don’t carefully check the entity key(s).