LINQ select on a SQL View gets wrong answer

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

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.

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 });     
}