返回的EntityFramework从SQL Server视图损坏/ swaped数据 [英] EntityFramework returns corrupted/swaped data from SQL Server View
问题描述
我在SQL Server中的视图简单查询：
I have simple query from a view in SQL Server:
SELECT [PricePerM]
FROM RealtyStatParent
ORDER BY PricePerM
当我在SQL Management Studio中执行查询我得到正确的结果。 ，这意味着我得到2532行从1.00开始，由173543.6893结束
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.
当我使用实体框架，从C＃的查询时，我得到了相同的结果：
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();
一个很多PricePerM的值被重复（值1或48），而不是真正的值和。结果集是不正确排序
A lot of values of PricePerM are repeated (value 1 or 48) instead of a real value and the resultset is not ordered properly.
在EF设计行的定义很简单：
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; }
}
的 
更新
我认为，这种奇怪的行为有什么做的Entity框架返回坏数据，因为视图没有主键。 EF决定，实体键则列BusinessCategory和obec_kod，这是结合独一无二的。我希望我近了，但还是不够。
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.
推荐答案
的这种奇怪的行为的原因已经提到的here 或的此处。看来，EF内部缓存整个表，但EF选错了独特的密钥和后果的行中的数据来自不同行的数据被替换。
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.
这SELECT是从视图的。这种观点没有主键。 EF决定太聪明，选择缓存目的对自己的主键。但是EF选择错了键，我的意思是EF只选择了2四列。
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.
要修复它，去到EDMX型号，点击鼠标右键，在有问题的查看头和选择显示模型浏览器。然后检查，所有列有属性的EntityKey设置为true。这就是一切。
由于这一点，EF的我有点失望，EF等特色的。这段代码在LinqToSQL工作正常wihtout任何魔法。而且这还不是时候LinqToSql正确行为的第一个查询和EF行为至少是有问题或崩溃运行。恐怕微软正在打破得到你所期望的模式在这里。当我查询了整个看法，我只是希望简单的代码，您可以通过视图中的数据
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视图损坏/ swaped数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
