Entity Framework returns wrong data for view with LEFT JOIN statement

Question

I'm experiencing strange behavior of Entity Framework. EF-generated DbContext object returns data different from the actual data in database.

Consider the following DB schema:

Letter data:

Id      Value   LanguageId
------- ------- ----------
1       A       1
2       A       2
3       B       1
4       B       2

Language data:

Id      Value
------- -------
1       English
2       Russian

I also have the following simple view LetterLanguageView. Note that it uses LEFT JOIN clause because Letter.LanguageId could be NULL:

SELECT dbo.Letter.Value as Letter, dbo.Language.Value as Language
FROM dbo.Letter
LEFT JOIN dbo.Language ON dbo.Letter.LanguageId = dbo.Language.Id

The result of this view is pretty straightforward:

Letter  Language
------- --------
A       English
A       Russian
B       English
B       Russian

However, when I use this view from Entity Framework, I have the following results:

As you can see, the Language property is wrong, there is no Russian language at all.

If you are wondering, here is the code snippet for reading this data:

using (var e = new TestEntities())
{
    var data = e.LetterLanguageView;
}

Nothing special, no conversions or any modifications of returned data, so it looks like the problem is in the Entity Framework itself.

Could you suggest any ideas why EF returns wrong data in this case and how could I fix this?

Answer 1

Make sure in your EF model for LetterLanguageView that you set Letter and Language as EntityKey = true.

Another trick I have used in the past is add a row Id column and make that the PK. Here is someones (not me) blog about it

http://girlfromoutofthisworld.com/entity-framework-and-setting-primary-keys-on-views/