EntityFramework returns corrupted/swaped data from SQL Server View

Question

I have simple query from a view in SQL Server:

SELECT [PricePerM]
FROM RealtyStatParent
ORDER BY PricePerM

When I execute the query in SQL Management Studio I get correct results. It means I get 2532 rows starting from 1.00 and ending by 173543.6893.

When I make a query from C# using entity framework, I got the same results:

var justDecimals = context.RealtyStatParents                
    .OrderBy(item => item.PricePerM)
    .Select(item => item.PricePerM)
    .ToArray();

Until now nothing special. But what I really don't understand is following query. I select entire rows first and then I select the price (decimal).

var entireRows = context.RealtyStatParents                
    .OrderBy(item => item.PricePerM)        
    .ToArray();

var decimalFromRows = entireRows 
    .Select(item => item.PricePerM)
    .ToArray();

A lot of values of PricePerM are repeated (value 1 or 48) instead of a real value and the resultset is not ordered properly.

The definition of row in EF designer is simple:

public partial class RealtyStatParent
{
    public Nullable<decimal> PricePerM { get; set; }
    public int BusinessCategory { get; set; }
    public decimal obec_kod { get; set; }
    public Nullable<int> ParentCategoryId { get; set; }
}

UPDATE

I Believe that this strange behavior has something to do with Entity Framework returning bad data, because the view has no primary key. EF decided that Entity Key is on Column BusinessCategory and obec_kod, which is combined unique. I hope I am closer, but still not enough.

Answer 1

The cause of this strange behavior is already mention here or here. It seems that the EF internally cache the entire table, but EF selected wrong unique key and in consequence the the data in the row get replaced by data from different rows.

This SELECT is made from a view. This view has no primary key. EF decided to be too clever and choose the primary key on his own for caching purposes. But EF has chosen wrong key, I mean EF has chosen only 2 columns of four.

To fix it, go to the EDMX model, right click to the header of problematic View and choose "Show in model browser". Then check, that all columns has the property EntityKey set to true. That is all.

Because of this and other "features" of EF I am bit disappointed by EF. This code worked fine in LinqToSQL wihtout any magic. And it is not the first query when LinqToSql behave correctly and EF behavior is at least problematic or crashing runtime. I am afraid Microsoft is breaking "get what you expect pattern" here. When I query the entire view, I just expect simple code which get the data from the view.