CODEWITHSUNDEEP
sql-serverentity-frameworkentity-framework-coreef-core-2.2
Dan
Dan

Reputation: 693

EF generates a unwanted SQL for nullable entity

I have an entity that uses another entity that can be null

class Parent 
{
   int? ChildId;
   Child Child;
}
class Child
{
   decimal Property;
}

I am trying to do a filter of the child property but it generates a strange SQL

Parents.Select(p => new { X = p.Child.Property <> 0 ? 1 : 0  })

But when I looked at the SQL it generated:

(([p.Child].[Property] <> 0.0) OR [p.Child].[Property] IS NULL).

The SQL that I want is to have

(([p.Child].[Property] <> 0.0) OR [p.Child].[Property] IS NOT NULL)

Can this be achieved without using p.Child != null && p.Child.Property <> 0. I tried this but it still adds the IS NULL condition.

Upvotes: 1

Views: 147

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205529

This seems to be caused by bug/defect in EF Core 2 which has been fixed in EF Core 3, because it doesn't happen there.

I've tried probably all possible syntax tricks, and it continues to insert that OR IS NULL condition, regardless of the UseRelationalNulls option.

At the end I was able to get the desired

[p].[ChildId1] IS NOT NULL AND ([p.Child].[Property] <> 0.0

translation by using the super counterintuitive expression

p.Child != null && !(p.Child.Property == 0)

(as you mentioned, the natural way of writing it

p.Child != null && p.Child.Property != 0

still includes OR IS NULL condition, although it has no effect on the result)

Upvotes: 1

Related Questions