EF4 returns incorrect values when selecting from summary view

Question

I am using EF4 to read data from a view .This is a summary view with group by etc and is readonly-it does not have a primary key. So inorder to make it import into EF4 I used an often suggested trick -ie use ISNULL(column,-11) as the first column in the view. This helps EF4 to infer primary key and import the view.

However when I select from the view in EF4 very strange thing happens - I get incorrect data as compared to when I directly select from the view in Management studio. I could not believe this so I tried a few times and the error persisted. Just to make sure I created a table in SQL Server by select all from the view and accessed the table in EF4 .But then in returns correct data!

The view is not very complicated except that it is a summary view (group by with joints) and it uses a SQL Common table expression(ie the with clause).

When I select straight in database using select * from vw_responserate I get 

coltext       SurveyId  rowtext1    rowtext2            cnt
Anwender    2   Angestellte/r   Französische Schweiz    1
Anwender    2   Angestellte/r   Italienische Schweiz    1
IT Spezialist   2   Angestellte/r   Deutsche Schweiz    1
IT Spezialist   2   Mittleres Management / Senior Management    Italienische Schweiz    1

When I select from EF4 I get

coltext rowtext1 rowtext2 cnt
Anwender      Angestellte/r Französische Schweiz 1
Anwender      Angestellte/r Französische Schweiz 1
IT Spezialist Angestellte/r Deutsche Schweiz 1
IT Spezialist  Angestellte/r Deutsche Schweiz 1

Any help would be appreciated

thanks

Answer 1

I had the same issue, the problem is that the key defined for the view does not uniquely identify a single row.

Looking at the sample data, your problem should be solved by setting coltext, rowtext1 and rowtext2 as the key.

This can be done through the designer by right clicking each property and selecting 'Entity Key'.