查询时生成不正确的SQL [英] Incorrect SQL generation on query

问题描述

Dim records = From record In db.table
              Where record.Level = "Level 1"
              Where record.NewNumber IsNot Nothing
              Select record.Number

当我调用records.ToString()时，我得到这样的输出:

When I call records.ToString() I get an output like so:

选择 [Extent1].[Number] AS [Number] 源自[dbo].[表格] AS [范围1] 在[Extent1].[Level] = @ p__linq__0

SELECT [Extent1].[Number] AS [Number] FROM [dbo].[table] AS [Extent1] WHERE [Extent1].[Level] = @p__linq__0

缺少NewNumber的where子句.如果将linq更改为NewNumber为空，则得到以下信息:

The where clause for NewNumber is not null is missing. If I change the linq to where NewNumber is nothing I get the following:

选择 CAST(NULL AS varchar(1))AS [C1] FROM(SELECT 1 AS X)AS [SingleRowTable1] 1 = 0

SELECT CAST(NULL AS varchar(1)) AS [C1] FROM ( SELECT 1 AS X ) AS [SingleRowTable1] WHERE 1 = 0

这是怎么回事?数字字段是我的主键，而newnumber只是另一个字段.我只更改了该帖子的一些名称/值.用And/AndAlso在同一行上执行两个where子句没有区别.

What is going on here? The number field is my primary key and newnumber is just another field. I've only changed some of the names/values for this post. Executing both where clauses on the same line with And/AndAlso makes no difference.

字段NewNumber就是这样配置的.

The field NewNumber is configured as such.

[属性](函数(x)x.NewNumber).HasMaxLength(20).HasColumnName("Newnumber").IsOptional()

[Property](Function(x) x.NewNumber).HasMaxLength(20).HasColumnName("Newnumber").IsOptional()

推荐答案

EF知道NewNumber不能为null/nothing，并且由于谓词(IsNot Nothing)始终为真而忽略了谓词(IsNot Nothing)，或者当它始终为假(Is Nothing)时，它将变为易于为查询优化器评估的内容.

EF knows that NewNumber can't be null/nothing and either ignores the predicate (IsNot Nothing) because it's always true, or when it's always false (Is Nothing) it turns it into something that's easier to evaluate for the query optimizer.

这篇关于查询时生成不正确的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！