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