Where expression with Contains on nullable enum with value conversion generates invalid query
Posted: Thu 01 Aug 2019 12:47
Continuation of the issue in this post: viewtopic.php?p=139702, but with a Contains expression in the Where clause.
When using EFCore value conversions for nullable enum properties and performing a Contains expression in a where clause, Devart ignores the value expression and generates a query using the numeric value of the enum.
These queries generate the following SQL statements
Database Provider: Devart.Data.Oracle.EFCore version 9.7.805
A repository reproducing the issue can be found here: https://github.com/LBRitsSES/devart-efc ... ug-repro-2
When using EFCore value conversions for nullable enum properties and performing a Contains expression in a where clause, Devart ignores the value expression and generates a query using the numeric value of the enum.
Code: Select all
// This works
var unicornRider = dbContext
.Set<Rider>()
.FirstOrDefault(_ => _.Mount.Value == EquineBeast.Unicorn);
// This works
var beastsWithHorns = new EquineBeast?[] {EquineBeast.Unicorn};
var hornRider = dbContext
.Set<Rider>()
.FirstOrDefault(_ => beastsWithHorns.Contains(_.Mount));
// This doesn't - Throws ORA-01722: invalid number exception
var beastsWithoutHorns = new [] {EquineBeast.Donkey, EquineBeast.Horse, EquineBeast.Mule};
var noHornRider = dbContext
.Set<Rider>()
.FirstOrDefault(_ => beastsWithoutHorns.Contains(_.Mount.Value));
Code: Select all
-- Query 1, good
SELECT
"_".ID,
"_".MOUNT
FROM RIDER "_"
WHERE "_".MOUNT = 'Unicorn'
FETCH FIRST 1 ROWS ONLY
-- Query 2, good
SELECT
"_".ID,
"_".MOUNT
FROM RIDER "_"
WHERE "_".MOUNT IN ('Unicorn')
FETCH FIRST 1 ROWS ONLY
-- Query 3, bad
SELECT
"_".ID,
"_".MOUNT
FROM RIDER "_"
WHERE "_".MOUNT IN (0, 2, 1)
FETCH FIRST 1 ROWS ONLY
A repository reproducing the issue can be found here: https://github.com/LBRitsSES/devart-efc ... ug-repro-2