DbFunctions compared to NULL are ignored and break the query builder

[rogerfar]

DbFunctions that are comparing to NULL are ignored in the query and even break other (valid) conditions.

For example:

    public class DataContext : DbContext
    {
        private static readonly LoggerFactory LoggerFactory =
            new LoggerFactory(new[]
            {
                new DebugLoggerProvider()
            });

        public DbSet<Course> Courses { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Data Source=.;Initial Catalog=Test;Integrated Security=True;MultipleActiveResultSets=True;");
            optionsBuilder.UseLoggerFactory(LoggerFactory);
            optionsBuilder.EnableDetailedErrors();
            optionsBuilder.EnableSensitiveDataLogging();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.HasDbFunction(typeof(DataContext).GetMethod(nameof(JsonValue)))
                        .HasTranslation(args =>
                        {
                            var arguments = args.ToList();
                            var a = (String) ((SqlConstantExpression) arguments[0]).Value;
                            arguments[0] = new SqlConstantExpression(Expression.Constant(a), RelationalTypeMapping.NullMapping);

                            return SqlFunctionExpression.Create("JSON_VALUE", arguments, typeof(String), null);
                        });
        }

        public static String JsonValue(String column, String path)
        {
            throw new NotSupportedException();
        }
    }

    public class Course
    {
        [Key]
        public Guid CourseId { get; set; }

        public String CourseName { get; set; }
        public String MetaData { get; set; }
    }

    public class MetaData
    {
        public String TestParam { get; set; }
    }

When I run:
context.Courses.FirstOrDefault(m => DataContext.JsonValue("MetaData", "$.TestParam") == "Test 123");
Works as expected, but when I run:
context.Courses.FirstOrDefault(m => DataContext.JsonValue("MetaData", "$.TestParam") == null);
it doesn't work, the generated query is odd as well:

SELECT TOP(1) [c].[CourseId], [c].[CourseName], [c].[MetaData]
FROM [Courses] AS [c]
WHERE CAST(0 AS bit) = CAST(1 AS bit)

When chaining multiple conditions, like this:
context.Courses.FirstOrDefault(m => m.CourseName == "Test 1" && DataContext.JsonValue("MetaData", "$.TestParam") == null);
It gives the same result.

Here is a demo repo:
test-repo.zip

[smitpatel]

See #11295 (comment) on how to write translation for JSON_VALUE.

Further, don't pass Metadata as string rather use m.Metadata. That will make sure to use exact column rather than trying to guess that query has Metadata column. It will also solve your issue of writing custom translation like that.

[rogerfar]

That seems to work yes, except in our actual application MetaData is a class and uses a converter:

builder.Property(e => e.MetaData)
                   .HasConversion(v => JsonConvert.SerializeObject(v, new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore}),
                                  v => JsonConvert.DeserializeObject<MetaData>(v, new JsonSerializerSettings {NullValueHandling = NullValueHandling.Ignore}));

This is why I had to rewrite your suggested solution, it doesn't know what MetaData is and how to deal with that..

Anyway, is there a reason that my solution breaks though? I don't see what causes the issue and breaking the whole query builder.

[smitpatel]

You declare your JsonValue method in C# to take object of your class and use HasParameter("column").HasStoreType("nvarchar(max)");

The solution breaks due to a bug. We assume that if FUNC(A,B) == null only when either A or B is null. In your case since you passed constant value none of them is null so we simplified it to false. That is one more reason to use actual metadata then string column name via constant expression as by using constant you lose all the metadata EF knows about. (In complex query it will generate invalid SQL too if column gets aliased.)

[rogerfar]

I tried setting it to Object but then I run into this:

The parameter 'column' for the DbFunction 'DataContext.JsonValue' has an invalid type 'object'. Ensure the parameter type can be mapped by the current provider.'

Also when I set it to the MetaData type it gives the same error. I thought I could solve that with the HasTranslation but it doesn't even get that far.

My solution is just a workaround until full JSON support lands, I'm only using it sparingly where needed, and for now I removed the conditions on the DB level and do them in memory instead.

[smitpatel]

Hence you need to use this

HasParameter("column").HasStoreType("nvarchar(max)");

[rogerfar]

Ah OK now I see, that makes sense, works perfectly!