Incorrect SQL generation on query

Question

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

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

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

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

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

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.

The field NewNumber is configured as such.

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

AndAlso instead of the second Where ?

Steve
– Steve

2018-03-28 14:53:21 +00:00
Commented Mar 28, 2018 at 14:53 — Steve
– Steve, Commented Mar 28, 2018 at 14:53
No difference unfortunately.

David
– David

2018-03-28 14:55:46 +00:00
Commented Mar 28, 2018 at 14:55 — David
– David, Commented Mar 28, 2018 at 14:55

Gert Arnold · Accepted Answer · 2018-03-28 15:20:35Z

1

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.

answered Mar 28, 2018 at 15:20

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

David Over a year ago

Newnumber is a nullable nvarchar(20) so it shouldn't be optimizing like this.

Gert Arnold Over a year ago

Well, EF doesn't know it's nullable. It's probably mapped as required.

David Over a year ago

I thought the same right after I posted that and explicitly marked it as optional with no change to the SQL. Tho it says in the description that string properties are nullable by default.

Gert Arnold Over a year ago

Just to exclude the odd case: you do add the configuration class to the modelBuilder, do you?

Ivan Stoev Over a year ago

Definitely the field is required from EF point of view. OP has to find why. Since string is optional by default, either data type is different, or the field is FK for required relationship etc.

|

Collectives™ on Stack Overflow

Incorrect SQL generation on query

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related