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?

If the SQL from the LINQ query returns the expected results, then it isn't the SQL. Are you doing something with the PartIndexes variable before running the Where condition on it? Is that even a variable or is it dbContext.PartIndexes? — Aaron Hawkins, Jan 08 '13 at 14:59
Have you tried to reverse the order in your LINQ query? First. `OrderBy(x => x.ProductNameId)` and then `ToList();`? — Alex Filipovici, Jan 08 '13 at 15:05
No I'm just doing using the LINQ as described, with the WHERE applied directly to the View. PartIndexes is actually dbContext.PartIndexes — Code Uniquely, Jan 08 '13 at 15:08
I've tried with and without order and with and without list and the same thing happens. — Code Uniquely, Jan 08 '13 at 15:22
I count 9 columns in the SQL query and 8 in your results. I appreciate you trying to avoid cluttering the question, but could you tell us which column is which? BTW, this bit alone earned a +1 from me: `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.` That was refreshing. — Justin Morgan - On strike, Jan 08 '13 at 15:26
The first 5 columns are obtained by applying the 4 filter criteria (the 5th column being the only matched type). The interest lies in the last three: ProductNameId, PartId and Total. I use the view to build a catalogue of all the parts that go into all products and the price of all those parts. Sometimes a part may be used more than once and parts are sourced from existing stock they may have been purchased at different times and for different prices. Mostly they are the same price, but this is not always true - hence listing the actual price. — Code Uniquely, Jan 08 '13 at 15:42

score 8 · Answer 1 · edited May 23 '17 at 12:04

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

Cool, Thanks for the info. I typically let VS to generate the model and I don't tend to mess around with the EDMX once it's done so. But the attributes would certainly help if I did. I added an additional column to the view to create a uniqueness and pulled that through into the EDMX to allow LINQ to allow it to do the same job. Nice answer though +1 — Code Uniquely, Jan 08 '13 at 15:47

crokusek · Answer 2 · 2018-05-11T17:58:10.010

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.

+1 for `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.` — Jacob Stamm, Apr 04 '19 at 20:07

score 2 · Accepted Answer · answered Jan 08 '13 at 15:19

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

score 2 · Answer 4 · edited Nov 03 '19 at 10:48

2

I added

ISNULL(CONVERT(VARCHAR(50), NEWID()), '') AS Pkid

as my first column in my view to address this problem.

edited Nov 03 '19 at 10:48

Hakan Fıstık

16,800
14
110
131

answered Aug 08 '17 at 18:32

mkdave99

835
10
15

score 1 · Answer 5 · answered Jan 08 '13 at 14:52

1

At 6th column of first two rows you have same value - 124, which can lead for one row to be filtered if this is some foreign key in this view. I had similar situation using data table Load function, because it applies constraints while loading retrieved data to data table. Try to remove key from linq to sql schema.

answered Jan 08 '13 at 14:52

stanke

276
2
13

This column is actually the critical part of the returned data unfortunately. Its what I need to use to group and total the individual part totals. However it does give me some thoughts about just how I need to sum those totals. And this has given me pause for thought on the actual uniqueness of each record – Code Uniquely Jan 08 '13 at 15:13

score 1 · Answer 6 · answered Feb 06 '15 at 15:58

1

Assume you're using EF (entity framework). If so, please use .AsNoTracking() on the IQueryable, otherwise you may get cached results.

answered Feb 06 '15 at 15:58

baHI

1,510
15
20

score 0 · Answer 7 · edited Feb 01 '13 at 11:38

0

I don't see any issues with your LINQ.

How are you populating PartIndexes (ORM, SqlCommand, etc.)?

Perhaps your logic in your DAL or ORM mappings is messed up which is messing up what's stored in PartIndexes.

edited Feb 01 '13 at 11:38

wickedone

542
1
6
18

answered Jan 08 '13 at 14:56

sgeddes

62,311
6
61
83

Directly from a model via LINQ to Entities. created using Update Model from database – Code Uniquely Jan 08 '13 at 15:09

LINQ select on a SQL View gets wrong answer

7 Answers7

Linked