Where expression with Contains on nullable enum with value conversion generates invalid query

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
laurensb
Posts: 13
Joined: Thu 06 Jun 2019 07:44

Where expression with Contains on nullable enum with value conversion generates invalid query

Post by laurensb » 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.

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));
These queries generate the following SQL statements

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
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
Last edited by laurensb on Thu 29 Aug 2019 11:02, edited 1 time in total.

Shalex
Site Admin
Posts: 9105
Joined: Thu 14 Aug 2008 12:44

Re: Where expression with Contains on nullable enum with value conversion generates invalid query

Post by Shalex » Sat 03 Aug 2019 14:07

Thank you for your report. We will notify you when the issue is fixed.

Shalex
Site Admin
Posts: 9105
Joined: Thu 14 Aug 2008 12:44

Re: Where expression with Contains on nullable enum with value conversion generates invalid query

Post by Shalex » Sat 14 Sep 2019 11:13

The bug with applying a value converter to .Where expression with .Contains on nullable enum in EF Core 2 is fixed: viewtopic.php?f=1&t=39337.

Post Reply