EF returning different values than query

Question

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.

Answer 1

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.