在LINQ和SQL看似等价查询返回不同的结果 [英] Seemingly equivalent queries in LINQ and SQL returns different results

问题描述

的不知道为什么它得到了联作为一个傻瓜。现在的问题是不同的方式。答案是不同的。不知道是什么改变。如果有人看到我错过了什么，请让我知道... 的

我收到不同数量的使用这两个查询的结果。之后进行分析之后几个小时，我需要认输，承认我不能当场意义的差异。由于我的方法库是空的，我寻求帮助。

LINQ

 列表＆LT;欧诺＆GT;结果= context.Unos
  。加入（context.Duos，UNO = GT; uno.CommonId，二人= GT; duo.CommonId，
    （UNO，哆）=＆GT;新乌诺{=乌诺，铎铎=}）
  。凡（项目=＆GT; item.Uno.Amount大于0
    ＆功放;＆安培; item.Duo.Type == 2）
  。选择（项目=＆GT; item.Uno）
  .ToList（）;
 

SQL

  SELECT * FROM欧诺为u
加入铎为d的d.CommonId = u.CommonId
其中，u.Amount＆GT; 0
  和d.Type = 2
 

问题排名第一的是如果以上两种说法确实是相当的，或者如果我失去了一些东西。问题编号二是我缺少的是什么（如果有的话），或者如何故障拍它（如果我没有）。

1. 的呼叫针对同一数据库进行。


2. 得出的数字是大大分开（142和1437）。


3. 同样的结果集是为获取内部连接。


4. 双方欧诺的和的双核的有看法，并不表。

更重要的是，我可以证实？

修改

来自社区的一些赫然大力支持后，我们建立了LINQ查询可以用下面的命令来SQL'ized。

  VAR WTD = context.Unos
  。加入（context.Duos，UNO = GT; uno.CommonId，二人= GT; duo.CommonId，
    （UNO，哆）=＆GT;新乌诺{=乌诺，铎铎=}）
  。凡（项目=＆GT; item.Uno.Amount大于0
    ＆功放;＆安培; item.Duo.Type == 2）
  。选择（项目=＆GT; item.Uno）
  的ToString（）;
 

疯狂的事情是，在执行是字符串中的SQL Manager会生成142的结果（就如同在例如上面的查询中，SQL版本），并从它的差别仅不明显。但是，在执行LINQ查询本身产生1437的结果。我太糊涂了，甚至开始哭......

  

选择\\ r \\ n [Extent1]。[CommonId] AS [CommonId] \\ r \\ n [Extent1]。[X] AS [X] \\ r \\ n个（选择\\ n [欧诺]。[ CommonId] AS [CommonId] \\ n [欧诺]。[X] AS [X]的\\ n [财经]。[欧诺] AS [欧诺]）AS [Extent1] \\ r \\ n INNER JOIN（SELECT \\ n [朵]。[CommonId] AS [CommonId] \\ n [双核] [Y] AS [Y] \\ n [双核] [Z] AS [Z]，\\ n [双核] [类型] AS [类型]，\\ n [双核] [U] AS [U]，\\ n [双核] [V] AS [V]的\\ n [财经]。[铎] AS [铎]）AS [Extent2] ON [Extent1]。[CommonId] = [Extent2]。[CommonId] \\ r \\ n其中（[Extent1] [X]>铸（0为十进制（18）））与（[Extent2]。[类型] = @ p__linq__0）

解决方案

这是什么看法时，在实体框架映射经常发生。不同于常规数据库表，视图往往没有明确唯一键值。当遇到EF具有相同的主键值行，它只是复制属于它已经知道了关键的行。在加入，这可能会导致EF产生更多的子记录，因为这首脍炙人口的行可能比真正的数据库行更多的孩子。

解决方法是确保该视图有一个字段（或字段组合）唯一标识一行。而且，当然，在EF这应被映射为一个（可能是复合）主键

我不知道为什么EF显示此行为。我认为这应该是可能的扔它，它物化重复的实体异常。这个功能总是会引起很多混乱。

Not sure why it got linked as a dupe. The question is way different. The answer is different. Not sure what to change. If someone sees what I'm missing, please let me know...

I'm getting different number of results using those two queries. After having analyzed it for a few hours I need to throw in the towel, admitting that I can't spot the difference of significance. Since my arsenal of approaches is emptied, I'm asking for help.

LINQ

List<Uno> result = context.Unos
  .Join(context.Duos, uno => uno.CommonId, duo => duo.CommonId,
    (uno, duo) => new { Uno = uno, Duo = duo })
  .Where(item => item.Uno.Amount > 0
    && item.Duo.Type == 2)
  .Select(item => item.Uno)
  .ToList();

SQL

select * from Uno as u
join Duo as d on d.CommonId = u.CommonId
where u.Amount > 0
  and d.Type = 2

Question number one is if the above two statements are indeed equivalent or if I'm missing something. Question number two is what I'm missing (if anything) or how to trouble-shoot it (if I'm not).

1. Calls are made against the same database.
2. The numbers of results are vastly apart (142 and 1437).
3. The same result sets are fetched for inner join.
4. Both Uno and Duo are views, not tables.

What more can I verified?

Edit

After some awesomely great support from the community, we established that the LINQ query can be SQL'ized by the following command.

var wtd = context.Unos
  .Join(context.Duos, uno => uno.CommonId, duo => duo.CommonId,
    (uno, duo) => new { Uno = uno, Duo = duo })
  .Where(item => item.Uno.Amount > 0
    && item.Duo.Type == 2)
  .Select(item => item.Uno)
  .ToString();

The crazy thing is that executing that string in SQL Manager produces 142 results (just as the query in the example above, the SQL version) and it differs only insignificantly from it. However, executing the LINQ query itself produces 1437 results. I'm too confused to even start crying...

"SELECT \r\n [Extent1].[CommonId] AS [CommonId], \r\n [Extent1].[X] AS [X]\r\n FROM (SELECT \n [Uno].[CommonId] AS [CommonId], \n [Uno].[X] AS [X]\n FROM [Finance].[Uno] AS [Uno]) AS [Extent1]\r\n INNER JOIN (SELECT \n [Duo].[CommonId] AS [CommonId], \n [Duo].[Y] AS [Y], \n [Duo].[Z] AS [Z], \n [Duo].[Type] AS [Type], \n [Duo].[U] AS [U], \n [Duo].[V] AS [V]\n FROM [Finance].[Duo] AS [Duo]) AS [Extent2] ON [Extent1].[CommonId] = [Extent2].[CommonId]\r\n WHERE ([Extent1].[X] > cast(0 as decimal(18))) AND ([Extent2].[Type] = @p__linq__0)"

解决方案

This is something that often happens when views are mapped in Entity Framework. Unlike regular database tables, views often don't have clearly unique key values. When EF encounters rows with identical primary key values, it just duplicates the row that belongs to the key it already knows. In joins, this may cause EF to produce more child records because this first-known row may have more children than the real database row.

The fix is to make sure that the view has a field (or combination of fields) that uniquely identifies a row. And, of course, in EF this should be mapped as a (possibly composite) primary key.

I don't know why EF displays this behavior. I think it should be possible for it to throw an exception that it materializes duplicate entities. This "feature" always causes a lot of confusion.

这篇关于在LINQ和SQL看似等价查询返回不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！