EntityFramework从SQL Server视图返回损坏/转换的数据 [英] EntityFramework returns corrupted/swaped data from SQL Server View

问题描述

  SELECT [PricePerM] 
 FROM RealtyStatParent 
 ORDER BY PricePerM 
  

当我在SQL Management Studio中执行查询时，我得到正确的结果。这意味着我从1.00开始并从173543.6893开始获得2532行。

当我使用实体框架从C＃进行查询时，我得到了相同的结果：

  var justDecimals = context.RealtyStatParents 
 .OrderBy（item => item.PricePerM）
 .Select（item = > item.PricePerM）
 .ToArray（）; 
  

直到现在没什么特别的。但是我真的不明白的是查询。我先选择整行，然后选择价格（十进制）。

  var wholeRows = context.RealtyStatParents 
。 OrderBy（item => item.PricePerM）
 .ToArray（）; 
 
 var decimalFromRows = wholeRows 
 .Select（item => item.PricePerM）
 .ToArray（）; 
  

PricePerM的很多值被重复（值1或48）而不是实际值，结果集未正确订购。

EF设计器中行的定义很简单：

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

更新

我相信这个奇怪的行为与

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.

解决方案

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.

这篇关于EntityFramework从SQL Server视图返回损坏/转换的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！