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

问题描述

不知道为什么它被链接为骗子.问题完全不同.答案是不同的.不确定要改变什么.如果有人看到我遗漏的东西，请告诉我...

我使用这两个查询获得了不同数量的结果.在分析了几个小时之后，我需要认输，承认我无法发现显着性差异.由于我的方法库已清空，我正在寻求帮助.

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. 调用是针对同一个数据库进行的.
2. 结果数量相差很大(142 和 1437).
3. 为内连接获取相同的结果集.
4. Uno 和 Duo 都是视图，而不是表格.

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.

我还能验证什么?

经过社区的大力支持，我们确定可以通过以下命令对 LINQ 查询进行 SQL 化.

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

疯狂的是，在 SQL 管理器中执行 那个 字符串会产生 142 个结果(就像上面例子中的查询，SQL 版本)，并且它与它的区别很小.但是，执行 LINQ 查询本身会产生 1437 个结果.我太糊涂了，连哭都哭不出来……

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 [Extent1].[CommonId] AS [CommonId]， [Extent1].[X] AS [X] FROM (SELECT [Uno].[CommonId] AS [CommonId], [Uno].[X] AS [X] FROM [Finance].[Uno] AS [Uno]) AS [Extent1] INNER JOIN (SELECT [Duo].[CommonId] AS [CommonId]， [Duo].[Y] AS [Y]， [Duo].[Z] AS [Z]， [Duo].[Type] AS[类型], [Duo].[U] AS [U], [Duo].[V] AS [V] FROM [Finance].[Duo] AS [Duo]) AS [Extent2]ON [Extent1].[CommonId] = [Extent2].[CommonId] WHERE ([Extent1].[X] > cast(0 as decimal(18))) AND ([Extent2].[Type]= @p__linq__0)"

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

推荐答案

这是在实体框架中映射视图时经常发生的事情.与常规数据库表不同，视图通常没有明确唯一的键值.当 EF 遇到具有相同主键值的行时，它只是复制属于它已经知道的键的行.在连接中，这可能会导致 EF 产生更多的子记录，因为这个第一个已知行可能比真正的数据库行有更多的子记录.

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.

修复方法是确保视图具有唯一标识行的字段(或字段组合).而且，当然，在 EF 中，这应该映射为(可能是复合的)主键.

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.

我不知道为什么 EF 会显示这种行为.我认为它应该有可能抛出一个异常，它实现了重复的实体.这个功能"总是引起很多混乱.

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屋！