Bug in generated SQL when using conditional operator and null comparison (EF6 ObjectContext)

[KRU]

Hi,

We are experiencing a bug where LINQ-code like this: ...

Code: Select all

var result = (from i in db.CUSTOMERS
where i.NAME?.ToUpper() == (string.IsNullOrEmpty(localVariable) ? null : localVariable.ToUpper())
select i).ToList();

(where "localVariable" is set to null in C# code)

... will never return any rows for cases where "localVariable" is null (we would expect it to return the rows where "NAME" column is null in the Oracle DB), because the generated SQL ends up containing the expression "TO_CHAR(NULL)", which is non-sensical SQL.

Presumably, this happens because the other branch of the conditional operator uses ToUpper which becomes UPPER(NULL), leading your SQL-generation to believe that a cast TO_CHAR is required for the hard-coded NULL value.

The WHERE statement generated looks like this:

Code: Select all

WHERE (UPPER("Extent1".NAME)) = (CASE WHEN (:p__linq__0 IS NULL) OR ((Length(:p__linq__0)) = 0) THEN TO_CHAR(NULL) ELSE UPPER(:p__linq__1) END)

Both parameters in this case are NULL, since it is the "localVariable" variable being placed as a parameter twice.

By the way, it appears that LinqConnect (Devart.Data.Linq.DataContext) AND the EF Core (Microsoft.EntityFrameworkCore.DbContext using Devart.Data.Oracle as provider) is not having the same issue, as it generates SQL like this instead, which is clearly smarter, as it pre-evaluates the conditional operator from the C# code:

Code: Select all

WHERE UPPER(t1.NAME) IS NULL

Please handle this case for EF6 (System.Data.Entity.Core.Objects.ObjectContext).

[Shalex]

We have reproduced the issue and are investigating it. We will notify you about the result.

[Shalex]

The SQL generation in case of config.QueryOptions.UseCSharpNullComparisonBehavior=true in EF1/EF4/EF5/EF6 is improved in v9.9.887: viewtopic.php?f=1&t=39576.

With v9.9.887, specify the additional option:

Code: Select all

    var config = Devart.Data.Oracle.Entity.Configuration.OracleEntityProviderConfig.Instance;
    config.QueryOptions.UseCSharpNullComparisonBehavior = true;