Databases
StackOverflow2013
Postsdbo
POLINQ select on a SQL View gets wrong answer
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLINQ select on a SQL View gets wrong answer
    primarykey
    data
    text
    <p>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.</p> <p>When I query the view in SQL Manager directly using this query</p> <pre><code>SELECT * FROM [GPPS].[dbo].[PartIndex] WHERE CategoryNameId = 182 AND CycleId = 13 AND BasketId = 304 AND MarketId = 8 ORDER BY ProductNameId </code></pre> <p>I get the expected result of (First two lines are important)</p> <pre><code>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 </code></pre> <p>When I perform the following LINQ against the view</p> <pre><code>PartIndexes.Where(x =&gt; x.CategoryNameId == 182 &amp;&amp; x.CycleId == 13 &amp;&amp; x.BasketId == 304 &amp;&amp; x.MarketId == 8) .ToList() .OrderBy(x =&gt; x.ProductNameId); </code></pre> <p>I actually get </p> <pre><code>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 </code></pre> <p>as you can see the first two entries are identical and the distinction of the ID (32575 and 32576) has been lost.</p> <p>looking at SQL profiler when I run the LINQ query on the view produces the following SQL</p> <pre><code>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]) </code></pre> <p>and when I then execute that directly in SQL manager I get the desired result of:</p> <pre><code>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 </code></pre> <p>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?</p> <p>What is SQL doing when used directly that LINQ does not do when presenting back correctly? </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCode Uniquely
    1. USCode Uniquely
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLINQ select on a SQL View gets wrong answer
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLINQ select on a SQL View gets wrong answer
      1. USSebastian K
      1. VTFavorite
    3. VO
      singulars
      1. POLINQ select on a SQL View gets wrong answer
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWhat's the Primary Key ?
      singulars
      1. POLINQ select on a SQL View gets wrong answer
      1. USfred
    2. COIf 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?
      singulars
      1. POLINQ select on a SQL View gets wrong answer
      1. USAaron Hawkins
    3. COHave you tried to reverse the order in your LINQ query? First. `OrderBy(x => x.ProductNameId)` and then `ToList();`?
      singulars
      1. POLINQ select on a SQL View gets wrong answer
      1. USAlex Filipovici
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload