Invalid SQL when filtering on a boolean property that is assigned with .Any()

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
mbouwhui
Posts: 8
Joined: Fri 24 Apr 2020 12:25

Invalid SQL when filtering on a boolean property that is assigned with .Any()

Post by mbouwhui » Fri 24 Jul 2020 10:12

When we use this linq query:

Code: Select all

from car in _carsContext.Cars
select new CarDto()
{
    ID = car.ID,
    Brand = car.NAME,
    HasModels = (from model in _carsContext.Models
    		where model.CAR_ID == car.ID
    		select model).Any()
}
We use OData to filter on the property "HasModels" with this URL: ?$filter=HasModels eq true
In that case we get the SQL error "ORA-00933: SQL command not properly ended"

The generated SQL is (value of p__TypedProperty_0 is 0):

Code: Select all

SELECT "c".ID, "c".NAME "Brand", (CASE
    WHEN (
        SELECT COUNT(*)
        FROM MODELS "m"
        WHERE "m".CAR_ID = "c".ID) > 1 THEN 1
    ELSE 0
END) "HasMoreThenOneModel", (CASE
    WHEN EXISTS (
        SELECT 1
        FROM MODELS "m0"
        WHERE "m0".CAR_ID = "c".ID) THEN 1
    ELSE 0
END) "HasModels", 1 "isCar"
FROM CARS "c"
WHERE EXISTS (
    SELECT 1
    FROM MODELS "m1"
    WHERE "m1".CAR_ID = "c".ID) = :p__TypedProperty_0
We use version Devart.Data.Oracle.EFCore version 9.12.1054.
Last edited by mbouwhui on Fri 24 Jul 2020 10:38, edited 1 time in total.

mbouwhui
Posts: 8
Joined: Fri 24 Apr 2020 12:25

Re: Invalid SQL when filtering on a boolean property that is assigned with .Any()

Post by mbouwhui » Fri 24 Jul 2020 10:35

I have send a reproduction project via e-mail

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

Re: Invalid SQL when filtering on a boolean property that is assigned with .Any()

Post by Shalex » Wed 05 Aug 2020 14:42

Thank you for your report. We have reproduced the issue and will notify you when it is fixed.

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

Re: Invalid SQL when filtering on a boolean property that is assigned with .Any()

Post by Shalex » Mon 21 Sep 2020 15:55

The bug with generating invalid SQL when filtering on a boolean property that is assigned with .Any() in EF Core 3.1 is fixed: viewtopic.php?f=1&t=42246.

Post Reply