EF返回与查询不同的值 [英] EF returning different values than query

问题描述

所以我刚刚遇到这个非常奇怪的情况，想知道有没有人知道问题是什么。我有以下EF Linq查询。

  var hierarchyies =（从ctx.PolygonHierarchyViews中的层次结构
其中hierarchy.DashboardId == dashboardId 
选择层次结构）; 
  

当我在调试器中检查该查询时显示以下SQL

  SELECT 
 [Extent1]。[DashboardId] AS [DashboardId]，
 [Extent1]。[CurrentId] AS [CurrentId]，
 [Extent1]。[PolygonTypeId] AS [PolygonTypeId]，
 [Extent1]。[DisplayName] AS [DisplayName]，
 [Extent1]。[ParentId] AS [ParentId] 
 FROM [dbo]。[PolygonHierarchyView] AS [Extent1] 
 WHERE [Extent1]。[DashboardId] = @ p__linq__0 
  

如果我在SQL Server Management Studio中运行它，值为 dashboardId 的SQL Server Management Studio取代 @ p__linq__0 。我得到这些结果。

  DashboardId CurrentId类型名称ParentId 
 4 5 1区域空值
 4 6 2市场空白
 4 7 3 SubMarket 6 
 4 8 4 ZipCode 7 
 4 6 2市场5 
 4 7 3 SubMarket 6 
 4 8 4 ZipCode 7 
  

然而，迭代EF查询的结果如下：

  DashboardId CurrentId类型名称ParentId 
 4 5 1区域空值
 4 6 2市场空值
 4 7 3 SubMarket 6 
 4 8 4 ZipCode 7 
 4 6 2市场空白
 4 7 3 SubMarket 6 
 4 8 4 ZipCo de 7 
  

请注意，第五行有一个 ParentId NULL 而不是5.这是我如何解决问题。

  var layersies =（从ctx.PolygonHierarchyViews中的层次结构
其中hierarchy.DashboardId == dashboardId 
按层次结构分组层次结构.ParentId into grp 
 select grp）.AsEnumerable（）; 
  

这里奇怪的是，这导致了一个 IGrouping 一个键值为5，但该组中单个对象的 ParentId null 。

我试图从中创建一个查找查询并想要做

  var lookup = hierarchyies.ToLookup（h => h.ParentId）; 
  

但由于实际 ParentId 不似乎总是有正确的价值，我必须做这个组，我最终不得不做以下

  var lookup = hierarchy.SelectMany（x => x.Select（y => new {x.Key，View = y}））
 .ToLookup（h => h.Key，h => h.View ）; 
  

为了使事情更陌生，如果我删除 AsEnumerable 从查询结束之前，执行 SelectMany 和 ToLookup ，它仍然会导致实体应该 ParentId 5分为 null 。

这是EF的这种类型的错误还是我刚才在这里遗漏的东西？ BTW我正在使用EF 6.1.3。

解决方案

这是由非唯一主键< STRONG>。您的查询很好。

我注意到有问题的行（＃5）几乎完全与第2行匹配。我估计你有一个/所有列的复合键，除了'ParentId'。

  DashboardId CurrentId类型名称ParentId 
行＃2 4 6 2市场空值
行＃5 4 6 2市场5 
  

无论什么原因，实体框架对于维护这些规则比sql server在数据时更加严格。

您需要做的是确保您的数据具有完整性。否则实体框架或任何其他ORM可能会导致意外的问题。 p>

So I just came across this very odd scenario and was wondering if anyone might know what the problem is. I have the following EF Linq query.

var hierarchies = (from hierarchy in ctx.PolygonHierarchyViews
                   where hierarchy.DashboardId == dashboardId
                   select hierarchy);

When I inspect that query in the debugger it shows the following SQL

SELECT 
[Extent1].[DashboardId] AS [DashboardId], 
[Extent1].[CurrentId] AS [CurrentId], 
[Extent1].[PolygonTypeId] AS [PolygonTypeId], 
[Extent1].[DisplayName] AS [DisplayName], 
[Extent1].[ParentId] AS [ParentId]
FROM [dbo].[PolygonHierarchyView] AS [Extent1]
WHERE [Extent1].[DashboardId] = @p__linq__0

If I run that in SQL Server Management Studio substituding @p__linq__0 with the value of dashboardId. I get these results.

DashboardId     CurrentId  Type  Name       ParentId
4               5          1     Region     NULL
4               6          2     Market     NULL
4               7          3     SubMarket  6
4               8          4     ZipCode    7
4               6          2     Market     5
4               7          3     SubMarket  6
4               8          4     ZipCode    7

However the results from iterating the EF query are as follows.

DashboardId     CurrentId  Type  Name       ParentId
4               5          1     Region     NULL
4               6          2     Market     NULL
4               7          3     SubMarket  6
4               8          4     ZipCode    7
4               6          2     Market     NULL
4               7          3     SubMarket  6
4               8          4     ZipCode    7

Notice that the fifth row has a ParentId of NULL instead of 5. This is how I worked around the problem.

var hierarchies = (from hierarchy in ctx.PolygonHierarchyViews
                   where hierarchy.DashboardId == dashboardId
                   group hierarchy by hierarchy.ParentId into grp
                   select grp).AsEnumerable();

The odd thing here is that this results in a IGrouping with a Key value of 5, but the ParentId of the single object in that group is null.

I'm attempting to creat a lookup from that query and wanted to just do

var lookup = hierarchies.ToLookup(h => h.ParentId);

But since the actually ParentId doesn't seem to always have the correct value and I have to do the group by I end up having to do the following

var lookup = hierarchies.SelectMany(x => x.Select(y => new { x.Key, View = y }))
                        .ToLookup(h => h.Key, h => h.View);

To make matters even stranger, if I remove the AsEnumerable from the end of the query before doing the SelectMany and ToLookup it will still result in the entity that should have a ParentId of 5 being grouped under null.

Is this some type of bug with EF or am I just missing something here? BTW I'm using EF 6.1.3.

解决方案

This is caused by a non-unique primary key. Your query is fine.

I noticed that the row in question (#5) matches almost completely with row #2. I reckon you have a composite key of some / all of the columns except 'ParentId'.

         DashboardId     CurrentId  Type  Name       ParentId
Row #2   4               6          2     Market     NULL
Row #5   4               6          2     Market     5

For whatever reason Entity framework is more strict about upholding those rules than sql server is when rendering the data.

What you need to do is make sure your data has integrity.Otherwise Entity framework, or any other ORM might cause unexpected problems.

这篇关于EF返回与查询不同的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！