Issue while performing null check in where clause in linq for a non-nullable string property

[PrgOrcl]

To reproduce this issue , you may use the code i m pasting below. This application queries a table using sqlServer which contains a json column.

table structure -

Author {
    id int ,
    name varchar,

    Contact: {
          phone no,
          Address: {
                city,
                    Country,
                    PostCode,
                FlatNum
            }
        }
    }

The address field within the json column contains 4 sub properties -

string City nullable string Country int PostCode nullable int FlatNum

In this application i try to query the table based on whether the given four fields are null. I have 4 linq for that in here.

using System;
using Microsoft.EntityFrameworkCore;
using System.Linq;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using System.Reflection.Emit;
using System.Numerics;
using static System.Net.Mime.MediaTypeNames;
using static System.Runtime.InteropServices.JavaScript.JSType;

namespace SqlServerEFCore
{
  class Program
  {
    static void Main(string[] args)
    {
      try
      {
        using (var db = new MyContext())
        {
          db.Database.EnsureDeleted();
          db.Database.EnsureCreated();

          var data1 = new Author
          {

            Name = "aaa",
            Contact = new ContactDetails
            {
              Address = new Address
              {
                City = "acity",
                Country = "UK",
                postCode = 100,
                flatNum = 1001,

              },
              Phone = "01632 12345"
            }
          };

          var data2 = new Author
          {

            Name = "bbb",
            Contact = new ContactDetails
            {
              Address = new Address
              {
                City = null,
                Country=null,
                postCode=200,
                flatNum = null,
              },
              Phone = "01632 12346"
            }
          };

          db.Author.Add(data1);
          db.Author.Add(data2);

          db.SaveChanges();

          var cityQuery = db.Author.Where(author => author.Contact.Address.City == null).ToList();

          Console.WriteLine("-------------------------------------------------------------------------------------------------");

          var CountryQuery = db.Author.Where(author => author.Contact.Address.Country == null).ToList();

          Console.WriteLine("-------------------------------------------------------------------------------------------------");

          var PostCodeQuery = db.Author.Where(author => author.Contact.Address.postCode == null).ToList();    // this  gives  a warning saying the expression in where is always false

          Console.WriteLine("-------------------------------------------------------------------------------------------------");

          var FlatNumQuery = db.Author.Where(author => author.Contact.Address.flatNum == null).ToList();

          Console.WriteLine("-------------------------------------------------------------------------------------------------");
          foreach (var item in cityQuery)        // paste in whichever query you want
          {
            Console.WriteLine(item.Name + " --   " + item.Contact.Phone + " --   " + (item.Contact.Address != null ? item.Contact.Address.Country : null));
          }

        }
      }
      catch (Exception e)
      {
        Console.WriteLine(e.Message);
      }
    }
  }

  class MyContext : DbContext
  {
    public DbSet<Author> Author { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
      optionsBuilder.UseSqlServer("Data Source = (localdb)\\ProjectModels; Initial Catalog = MySql;")
        .LogTo(Console.WriteLine);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
      modelBuilder.Entity<Author>().OwnsOne(
          author => author.Contact, ownedNavigationBuilder =>
          {
            ownedNavigationBuilder.ToJson();
            ownedNavigationBuilder.OwnsOne(contactDetails => contactDetails.Address);
          });
    }
  }

  public class ContactDetails
  {
    public Address Address { get; set; } = null!;
    public string? Phone { get; set; }
  }

  public class Address
  {

    public string City { get; set; }                // string

    public string? Country { get; set; }           // nullable string 

    public int postCode { get; set; }             // int

    public int ? flatNum { get; set; }            // nullable int
  }

  public class Author
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public ContactDetails Contact { get; set; }
  }
}

The following sql is generated for the linqs:

for city == null  ( non- nullable string)

      SELECT [a].[Id], [a].[Name], [a].[Contact]
      FROM [Author] AS [a]
      WHERE 0 = 1

for country == null ( nullable string)

      SELECT [a].[Id], [a].[Name], [a].[Contact]
      FROM [Author] AS [a]
      WHERE JSON_VALUE([a].[Contact], '$.Address.Country') IS NULL

for postcode == null ( non-nullable int)

      SELECT [a].[Id], [a].[Name], [a].[Contact]
      FROM [Author] AS [a]
      WHERE 0 = 1

for flatNum == null ( nullable int)

      SELECT [a].[Id], [a].[Name], [a].[Contact]
      FROM [Author] AS [a]
      WHERE CAST(JSON_VALUE([a].[Contact], '$.Address.flatNum') AS int) IS NULL

In case of value types like int, If the property is nullable, we get the sql that checks for null values using IS NULL. This is fine.

If the property is non-nullable , the sql produced returns no rows ( ...where 0=1) . This makes sense as we even get a warning when writing this linq saying - CS0472 the expression is always false since its a non- nullable value type and can never be null. The result produced are also correct. This is fine as well.

In case of string, if nullable then, we get the sql that checks the field using IS NULL as seen before.

However, in case of non-nullable string, we get the sql which returns no rows (..where 0 = 1). Since, string is a reference type, we can assign it null. And we don't get a warning either like we did incase of non-nullable int. Therefore, even though we have rows with that field as null, no rows are returned.

For ref, here is the table content-

you may see that the non-nullable string "city" has null. But no rows are returned as the generated sql's where clause is always false.

Could you please tell me if this is expected behaviour ?

Include provider and version information

EF Core version: 8 ( i have tried with 9 rc2 as well) Database provider: (Microsoft.EntityFrameworkCore.SqlServer) Target framework: (NET 8.0) Operating system: windows IDE: (e.g. Visual Studio 2022 17.4)

[PrgOrcl]

@roji @ajcvickers