Bug in generated SQL when using conditional operator and null comparison (EF6 ObjectContext)
Posted: Wed 09 Oct 2019 06:52
Hi,
We are experiencing a bug where LINQ-code like this: ...
(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:
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:
Please handle this case for EF6 (System.Data.Entity.Core.Objects.ObjectContext).
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();... 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)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