11

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: database 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:

Wrong data

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?

19
  • Are you sure that your cardinality is setup correctly in the EF model? What is the SQL as generated by EF? (you can capture it in debug mode or with SQL profiler) Commented Jul 1, 2014 at 14:30
  • Don't use the debugger. Actually print out the results of the query to the console or a file or something and then look at it. Commented Jul 1, 2014 at 14:31
  • @Servy, I checked the results before - this is not a debugger's bug. Commented Jul 1, 2014 at 14:34
  • @Darek, here is the EF-generated SQL code: SELECT [Extent1].[Letter] AS [Letter], [Extent1].[Language] AS [Language] FROM (SELECT [LetterLanguageView].[Letter] AS [Letter], [LetterLanguageView].[Language] AS [Language] FROM [dbo].[LetterLanguageView] AS [LetterLanguageView]) AS [Extent1] Commented Jul 1, 2014 at 14:36
  • 1
    It could be that EF is assuming items 2 and 4 are the same entity as items 1/3 - likely because the keys have not been setup correctly. Obviously it will associate duplicate items in the dataset with the same entity and most likely it's chosen the first one and ignored any dupes - you can use a composite entity key, but you must specify the order of the keys (I'm sure the designer probably does this for you) - otherwise EF is just giving you its best guess! Commented Jul 1, 2014 at 15:04

1 Answer 1

13

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/

Sign up to request clarification or add additional context in comments.

1 Comment

Thank you. Having the same problem but My headache is now in the past.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.