Boolean filter fails on JSON column due to invalid SQL syntax

[imranmomin]

Query fails when trying to filter on a Json column, where the property in the document is of type boolean

Minimal code to reproduce the error:

public class UserPreference
{
    public Guid UserId { get; set; }
    public ChannelPreference Preference { get; set; }
}

public class ChannelPreference
{
    public PreferenceOption Email { get; set; }
    public PreferenceOption Mobile { get; set; }
}

public class PreferenceOption
{
    public bool Enabled { get; set; }
    // other properties removed for simplicity
}

public class UserPreferenceConfigurations : IEntityTypeConfiguration<UserPreference>
{
    public void Configure(EntityTypeBuilder<UserPreference> builder)
    {
        builder.ToTable("tbl_preferences", "user");
        builder.HasKey(x => x.UserId);

        builder.Property(x => x.UserId)
            .HasColumnName("user_id")
            .IsRequired();

        builder.OwnsOne(x => x.Preference, preferenceNavigationBuilder =>
        {
            preferenceNavigationBuilder.ToJson("preferences");
            preferenceNavigationBuilder.OwnsOne(x => x.Email);
            preferenceNavigationBuilder.OwnsOne(x => x.Mobile);
        });
    }
}

Now when I want to find if a user has the email preference enabled

db.UserPreferences.Any(x => x.UserId == userId && x.Preference.Email.Enabled);

Below is the SQL statement generated

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [user].[tbl_preferences] AS [t]
        WHERE [t].[user_id] = @__userId AND CAST(JSON_VALUE([t].[data],'$.Email.Enabled') AS bit)) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

Exception

Microsoft.Data.SqlClient.SqlException (0x80131904): An expression of non-boolean type specified in a context where a condition is expected, near ')'.
   at Microsoft.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__208_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.<>c.<.cctor>b__273_0(Object obj)
   at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)

If we examine the query, it is incomplete and missing the equality compare for CAST(JSON_VALUE([t].[data],'$.Email.Enabled') AS bit)

It should have been CAST(JSON_VALUE([t].[data],'$.Email.Enabled') AS bit) = CAST(1 AS bit)

SELECT CASE
    WHEN EXISTS (
        SELECT 1
        FROM [user].[tbl_preferences] AS [t]
        WHERE [t].[user_id] = @__userId AND CAST(JSON_VALUE([t].[data],'$.Email.Enabled') AS bit) = CAST(1 AS bit)) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END

EF Core version: 7.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0

[imranmomin]

Changing the property type to bool? generates a valid SQL query

[maumar]

dupe of #29572, which has been fixed in EF 8.0 Preview 1

[imranmomin]

Thank you @maumar @ajcvickers