EF6 returns wrong data from database

Question

I'm using SQL Server 2016 and Entity Framework 6 in my ASP.NET project. To initialize the main menu, I'm fetching a view data to a List variable in the C# language :

using (var db = new DBEntities())
{
    ImageGroupList.AddRange(db.vw_image_groups.OrderBy(x => x.id)
                                              .ThenBy(x => x.abbreviation).ToList());

But the result is not the same as I see in SQL Server Management Studio.

Here is the SQL Server view query result in SSMS:

Also, I've printed the 'ImageGroupList' values by a loop :

foreach (var item in ImageGroupList)
{
     System.Diagnostics.Debug.WriteLine(item.id + ", " + item.parent_id + ", " + item.abbreviation + ", " + item.text + ", " + item.member_count + ", " + item.type + ", " + item.order_index);

Here is the result :

As you compare both results, the data of abbreviation column in Entity Framework isn't the same as SQL Server view result!

I mixed up! Who knows what happened here?

Edited : Mapping details

Answer 1

There is a subtle problem with views when used from Entity Framework.

If you have a table, to use it with EF, you need to have a primary key to uniquely identify each row. Typically, that's a single column, e.g. an ID or something like that.

With a view, you don't have the concept of a "primary key" - the view just contains some columns from some tables.

So when EF maps a view, it cannot find a primary key - and therefore, it will use all non-nullable columns from the view as "substitute" primary key.

I don't know what these are in your case - you should be able to tell from the .edmx model.

When EF goes and reads the data, it will read the first line and create an object for that.

When EF reads the second line, it notices that the "primary key" (the collection of all non-nullable columns in your dataset) is the same as before - so it doesn't bother creating a new object with those values read, but the primary key is the same, it hence must be the same object as it has already read before, so it uses that object instead.

So the problem really is that you can't have explicit primary keys on a view.

Either you can tweak your EF model to make it clear to EF what is really the "primary key" (you need to make sure those columns are both non-nullable) - or you need to add something like a "artificial" primary key (using e.g. a ROW_NUMBER() OVER() construct) to your view.

By adding this RowNum column to your view, which just numbers the rows 1, 2, ...., n, you get a new, non-nullable column which EF will include into the "substitute PK" and since those numbers are sequential, no two rows will have the same "PK" values and therefore none will erroneously be replaced by something that's been read from the database already.

Update: @YounesJafari: your mapping exactly supports what I'm saying; your view's "primary key" is on (id, parent_id) - and in the case of the first two rows from your view, those are identical -- therefore, EF will take the first row (id=0, parent_id=-1, abbreviation=en) twice - it will consider the second row from the view (id=0, parent_id=-1, abbreviation=fa) to be identical to the first (because they have the same values for id and parent_id) .....