Query: optimize database null-semantics propagation

[ranma42]

We can optimize some case block for nullability by letting expressions take care of it instead of doing explicit checks.

db.Set<NullSemanticsEntity1>().Select(
    x => x.NullableStringA != null && x.NullableStringB != null
        ? x.NullableStringA + x.NullableStringB
        : default)

translates to

SELECT CASE
    WHEN "e"."NullableStringA" IS NOT NULL AND "e"."NullableStringB" IS NOT NULL
        THEN "e"."NullableStringA" || "e"."NullableStringB"
    ELSE NULL
END
FROM "Entities1" AS "e"

but ideally it should translate to

SELECT "e"."NullableStringA" || "e"."NullableStringB"
FROM "Entities1" AS "e"

This is in a sense the dual of pushing nullability information from clause test to clause result, but it also relies on clause/else matching; see #25977 for more cross-clause optimizations.

[ranma42]

This could be a way to achieve database null semantics for some operations (see https://github.com/dotnet/efcore/issues/33616 for an issue related to this). When compared to a flag, it has the obvious disadvantage that null checks are required in the C# expressions; OTOH it can be used locally (even in subqueries) and it is a valid optimization regardless of that (specifically, it would be quite effective whenever a developer is manually trying to replicate the SQL null semantics for an operation).

[ranma42]

Related to #16050

[roji]

Just noting that the original query is "badly written", i.e. the user could just write:

db.Set<NullSemanticsEntity1>().Select(x => x.NullableStringA + x.NullableStringB)

We generally don't go too far into "correcting" badly-written queries. We do go into this when there's good reason to believe that such a query could be a result of previous transformations that we do in the EF pipeline, of course.

[ranma42]

Just noting that the original query is "badly written", i.e. the user could just write:

db.Set<NullSemanticsEntity1>().Select(x => x.NullableStringA + x.NullableStringB)

#33616 is about this... if the translation of string concatenation is not settled, I can use a different operation for the tests.

We generally don't go too far into "correcting" badly-written queries. We do go into this when there's good reason to believe that such a query could be a result of previous transformations that we do in the EF pipeline, of course.

I believe some of these checks are added by the pipeline, but those in the tests are definitely introduced manually. Note that several operations cannot be easily expressed in C# without handling the null case in some way. What is in general the idiomatic way to request null propagation for those cases?

[roji]

Note that several operations cannot be easily expressed in C# without handling the null case in some way. What is in general the idiomatic way to request null propagation for those cases?

Can you provide more context on what you're asking? I'm not sure I understand exactly.

[ranma42]

Note that several operations cannot be easily expressed in C# without handling the null case in some way. What is in general the idiomatic way to request null propagation for those cases?

Can you provide more context on what you're asking? I'm not sure I understand exactly.

A simple example is getting the length of a (nullable) string:

1. Select(b => b.NullableString?.Length) cannot be used in expressions 😭
2. Select(b => b.NullableString == null ? default(int?) : b.NullableString.Length)
3. Select(b => b.NullableString.Length) causes a warning when #nullable enable; fails at runtime (basically because the type is int instead of int?)
4. Select(b => b.NullableString!.Length) could be problematic as in other contexts ! is often considered undesirable (and possibly even linted against); fails at runtime (again int vs int?)
5. Select(b => MyLengthFunction(b.NullableString)) would work, but requires defining MyLengthFunction as UDF (and typing it so that it returns an int?, which iiuc is not idiomatic either).

Option 1 cannot be used at all (does not even compile). Option 3/4 work if no actual null is being propagated, but then it's not really relevant for null propagation. Option 2 works, but iiuc might be considered a bad way to express this, which hopefully means that there is a better way to compute this. Option 5 works, but it quickly diminishes the value of the ORM.

[ranma42]

I am not sure if this is noteworthy, but on EFCore 8.0.6

db.Blogs.Select(x => x.NullableString == null ? default(int?) : x.NullableString.Length ).ToList();

translates to

      SELECT length("b"."NullableString")
      FROM "Blogs" AS "b"

while

db.Blogs.Select(x => x.NullableString == null ? default(int?) : 1 + x.NullableString.Length ).ToList();

translates to

      SELECT CASE
          WHEN "b"."NullableString" IS NULL THEN NULL
          ELSE 1 + length("b"."NullableString")
      END
      FROM "Blogs" AS "b"