LINQ select on a SQL View gets wrong answer

Question

I have a SQL View that produces a response with 8 columns. Its a rather complicated so I won't list it here and it won't add much to the issue I'm trying to understand.

When I query the view in SQL Manager directly using this query

SELECT * FROM [GPPS].[dbo].[PartIndex]
WHERE CategoryNameId = 182 AND CycleId = 13 AND BasketId = 304 AND MarketId = 8
ORDER BY ProductNameId

I get the expected result of (First two lines are important) and the ProductNameId column is 7th in the results

                            vvvvv
                            =====   
218   13    8   304 182 124 32575   162.84
218   13    8   304 182 124 32576   184.08
218   13    8   304 182 125 32577   156.13
218   13    8   304 182 127 32578   605.84
218   13    8   304 182 130 32579   141.51

When I perform the following LINQ against the view

PartIndexes.Where(x => x.CategoryNameId == 182 
                       && x.CycleId == 13 
                       && x.BasketId == 304 
                       && x.MarketId == 8)
           .ToList()
           .OrderBy(x => x.ProductNameId);

I actually get is:

                            vvvvv
                            ===== 
218   13    8   304 182 124 32576   184.08
218   13    8   304 182 124 32576   184.08
218   13    8   304 182 125 32577   156.13
218   13    8   304 182 127 32578   605.84
218   13    8   304 182 130 32579   141.51

as you can see the first two entries are identical and the distinction of the ID (32575 and 32576) has been lost.

looking at SQL profiler when I run the LINQ query on the view produces the following SQL

SELECT 
[Extent1].[SetNameId] AS [SetNameId], 
[Extent1].[CycleId] AS [CycleId], 
[Extent1].[MarketId] AS [MarketId], 
[Extent1].[BasketId] AS [BasketId], 
[Extent1].[CategoryNameId] AS [CategoryNameId], 
[Extent1].[ProductNameId] AS [ProductNameId], 
[Extent1].[PartId] AS [PartId], 
[Extent1].[Total] AS [Total]
FROM (SELECT 
  [PartIndex].[SetNameId] AS [SetNameId], 
  [PartIndex].[CycleId] AS [CycleId], 
  [PartIndex].[MarketId] AS [MarketId], 
  [PartIndex].[BasketId] AS [BasketId], 
  [PartIndex].[CategoryNameId] AS [CategoryNameId], 
  [PartIndex].[ProductNameId] AS [ProductNameId], 
  [PartIndex].[PartId] AS [PartId], 
  [PartIndex].[Total] AS [Total]
  FROM [dbo].[PartIndex] AS [PartIndex]) AS [Extent1]
WHERE (182 = [Extent1].[CategoryNameId]) AND (13 = [Extent1].[CycleId]) AND (304 =  [Extent1].[BasketId]) AND (8 = [Extent1].[MarketId])

and when I then execute that directly in SQL manager I get the desired result of:

218   13    8   304 182 124 32575   162.84
218   13    8   304 182 124 32576   184.08
218   13    8   304 182 125 32577   156.13
218   13    8   304 182 127 32578   605.84
218   13    8   304 182 130 32579   141.51

As anyone got any idea what might be happening here and why executing the LINQ request returns a different result that in SQL but when executing the SQL generated by the LINQ query it returns the desired result?

What is SQL doing when used directly that LINQ does not do when presenting back correctly?

Answer 1

Your problem is similar to this: Using a view with no primary key with Entity

Specify keys that makes your row unique. You can specify those keys on your entity mapping via attributes:

public class YearlySalesOnEachCountry
{        
    [Key, Column(Order=0)] public int CountryId { get; set; }
    public string CountryName { get; set; }
    [Key, Column(Order=1)] public int OrYear { get; set; }

    public long SalesCount { get; set; }      
    public decimal TotalSales { get; set; }
}

Or you can do it via code approach:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);    
    modelBuilder.Entity<YearlySalesOnEachCountry>()
           .HasKey(x => new { x.CountryId, x.OrYear });     
}

Answer 2

If the key that the entity framework chooses for the view is not unique, then results may not be returned correctly. For some views, a proper key (with all non-null columns) cannot be defined and provides no benefit to consuming the view.

For these cases, consider manually defining the key using the EF Edmx interface as:

 1) Any existing non-null field or 

 2) A separately added column "key" such as:

     select 1 as EfKey -- Must use with AsNoTracking()

Both approaches require the use of "AsNoTracking()" for each query (link).

Using AsNoTracking() signals EF to bypass its record caching mechanism which is based on the key. Without AsNoTracking(), the results may be corrupted containing duplicate rows.

An advantage of using (2) is that if AsNoTracking() is forgotten, then the results should be so bad that it is easily noticed.

Avoid using any variant of row_number() as it often prevents efficient use of predicates within the SQL Engine. This can be verified by viewing the SQL Actual Plan with a predicate. (Apologies as it was the advice I had originally posted.)

   -- Avoid!
   select row_number() over (order by (select null)) as RowId,
          ...

Hopefully the EF Team would consider having a option for views that allows disabling of Key requirement and automatic use of AsNoTracking() with each query.

Answer 3

Actually the questions from @stanke gave me an idea.

I actually altered the view slightly to include another column so that each record could be identified uniquely.

I don't actually need the columns value in my resulting table but it did help LINQ keep the records unique when querying. It appears that SQL does this just fine on its own but LINQ needed a bit of a helping hand to keep the records distinct.

It now works as expected in both SQL and LINQ