Description
Library name and version
- Kros.KORM 6.2.0
Description
When applying OData query options to IQueryable object, the OData self creates expressions for Where, OrderBy and other parts of a LINQ query. In some cases it produces Where expressions with ternary operator, which KORM fails to parse into SQL command.
Steps To Reproduce
var searchtext = "kro";
var test2 = _database.Query<MovementsOverviewSearchResult>()
.Where(x => (x.PartnerName == null ? null : x.PartnerName.ToLower()) == null ||
x.PartnerName.ToLower().Contains(searchtext))
.OrderByDescending(x => x.MovementDate);
I know the Where condition in example is not 'optimal', but something like that OData produces for $filter=contains(tolower(PartnerName) 'kro')
query string param.
Expected behavior
Parsed WHERE clause from example above should be like
WHERE ((CASE WHEN PartnerName IS NULL THEN NULL ELSE LOWER(PartnerName) END) IS NULL) OR (LOWER(PartnerName) LIKE '%' + @1 + '%')))
Also comparing something to NULL should be done by IS NULL
, not = NULL
. In TSQL comparing NULL = NULL
produces FALSE. See separate issue #110 .
Actual behavior
The example provided above produces this SELECT statement:
SELECT ... -- colums omitted
FROM ... -- table name omitted
WHERE ((((PartnerName = NULL)NULLLOWER(PartnerName) = NULL) OR (LOWER(PartnerName) LIKE '%' + @1 + '%'))) ORDER BY MovementDate DESC
WITH PARAMETERS (kro)
Notice NULLLOWER in the WHERE clause. That is the place where ternary operator should be - well, or rather (CASE WHEN ... THEN ... ELSE ... END).
The query fails on SqlException: Incorrect syntax near 'NULLOWER'. Incorrect syntax near 'PartnerName'.