LINQ选择一个SQL视图得到错误的答案 [英] LINQ select on a SQL View gets wrong answer

查看:124
本文介绍了LINQ选择一个SQL视图得到错误的答案的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个SQL视图产生与8列的响应。它是一种相当复杂的,所以我不会在这里列出来,它不会增加太多我想了解这个问题。

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.

当我查询视图SQL Manager直接使用此查询

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)

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

当我执行以下LINQ对视图

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

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

你可以看到前两个条目是相同的,而ID(32575和32576)已丢失的区别。

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

在看SQL事件探查器,当我运行视图上的LINQ查询产生下面的SQL

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])

,当我再在SQL经理执行直接我得到想要的

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

由于任何人有任何想法可能在这里发生的事情,为什么执行LINQ请求返回不同的结果,在SQL,但执行由LINQ生成的SQL查询时返回所需的结果?

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?

什么是直接使用时回正确呈现LINQ的时候没有做SQL做什么?

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

推荐答案

其实从@stanke问题给了我一个想法。

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.

我实际上并不需要在我得到的表中的列值,但它确实有助于LINQ保持独特记录查询时。看来,SQL这是否只是对自己不错,但LINQ需要有点伸出援助之手,以保持记录的区别。

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.

现在按预期在SQL和LINQ

It now works as expected in both SQL and LINQ

这篇关于LINQ选择一个SQL视图得到错误的答案的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆