Query: optimize query sql patterns like `(x || y) && !y` into `x && !y` when y is non-nullable

[lbljms]

Include your code

public class Test
{
    public Guid Id { get; set; }

    public Guid? UserId { get; set; }
}

public partial class DemoContext : DbContext
{
    public DemoContext()
    {
    }

    public DemoContext(DbContextOptions<DemoContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Test> Tests { get; set; }
}

var services = new ServiceCollection();
services.AddDbContext<DemoContext>();
var serviceProvider = services.BuildServiceProvider();

var demoContext = serviceProvider.GetRequiredService<DemoContext>();
var userId = Guid.NewGuid();
var sql = demoContext.Tests.Where(a => a.UserId != userId && a.UserId.HasValue).ToQueryString();
Console.WriteLine(sql);

Expected Behavior

DECLARE @__userId_0 uniqueIdentifier = 'd7deb76b-b725-4285-a736-2477dedb927b';

SELECT [t].[Id], [t].[UserId]
FROM [Tests] AS [t]
WHERE [t].[UserId] IS NOT NULL AND ([t].[UserId] <> @__userId_0)

Actual Behavior

DECLARE @__userId_0 uniqueIdentifier = '8286edc4-2a82-49d7-b50c-1832964b94e5';

SELECT [t].[Id], [t].[UserId]
FROM [Tests] AS [t]
WHERE (([t].[UserId] <> @__userId_0) OR [t].[UserId] IS NULL) AND [t].[UserId] IS NOT NULL

Include provider and version information

EF Core version:6.0.0-rc.1.21452.10 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system:Win10 IDE: Visual Studio 2022 17.0

[roji]

EF doesn't always optimize your query in every possible way, since the optimization complexity can quickly get out of hand; as a result, it's sometimes up to users to tune their LINQ queries. However, note that if you inverse the order, you'll get exactly the SQL you're looking for:

var sql = ctx.Blogs.Where(a => a.UserId.HasValue && a.UserId != userId).ToQueryString();

SQL:

WHERE [b].[UserId] IS NOT NULL AND ([b].[UserId] <> @__userId_0)

/cc @maumar if we want to do the same nullability tracking in the other direction.

[lbljms]

I mean is that such a nullability judgment condition is meaningless

OR [t].[UserId] IS NULL) AND [t].[UserId] IS NOT NULL

[maumar]

We could recognize the pattern (and similar ones?):

(x || IsNull(y)) && IsNotNull(y)

and simplify it to

x && IsNotNull(y)

here is the truth table (just to be sure ;)):

A - y is null
B - x || y is null
C - y is not null
D - (x || y is null) && y is not null
E - x && y is not null


x y A B C D E

0 0 0 0 1 0 0
0 1 0 0 1 0 0
0 N 1 1 0 0 0
1 0 0 1 1 1 1
1 1 0 1 1 1 1
1 N 1 1 0 0 0
N 0 0 N 1 N N
N 1 0 N 1 N N
N N 1 1 0 0 0

D and E yield the same result

[ranma42]

@maumar another way to interpret this is that | and & distribute over each other:

• (x | y) & z = (x & z) | (y & z)
• (x & y) | z = (x | z) & (y | z)

for the special case in which z = !y you have

• (x | y) & !y = (x & !y) | (y & !y) = x & !y
• (x & y) | !y = (x | !y) & (y | !y) = x | !y

EDIT: #23181 contains the case in which z = y instead ;)