EF Core 3.1 Generated SQL error: Greater > and smaller < than get reversed in some cases!

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

EF Core 3.1 Generated SQL error: Greater > and smaller < than get reversed in some cases!

Post by mbouwhui » Fri 15 May 2020 12:27

We are encountering the following bug in versions 9.11.951 and 9.11.980:
We have a .Net Core 3.1 WEB API that uses Entity Framework 3.1.3

When we build a LINQ query like the following:

Code: Select all

            
DateTime checkDate = new DateTime(2015, 5, 1); 
var result = from model in carsContext.Models 
                    join v3 in carsContext.ModelVersions.Where( 
                        v => checkDate >= v.STARTDATE && (checkDate < v.ENDDATE || v.ENDDATE == null)) 
                        on model.ID equals v3.MODEL_ID into v2 
                    from v in (v2.DefaultIfEmpty()) 
                    select new ModelVersionDto() 
                    { 
                         modelName = model.DESCRIPTION, 
                         ModelVersion = v.DESCRIPTION 
                    }; 
return result; 
With version 9.10.909 this results in the following Oracle query:

Code: Select all

SELECT "m".DESCRIPTION "modelName", "t".DESCRIPTION "ModelVersion" 
FROM MODELS "m" 
LEFT JOIN ( 
    SELECT "m0".ID, "m0".DESCRIPTION, "m0".ENDDATE, "m0".MODEL_ID, "m0".STARTDATE 
    FROM MODELVERSIONS "m0" 
    WHERE (:p__checkDate_0 >= "m0".STARTDATE) AND ((:p__checkDate_1 < "m0".ENDDATE) OR "m0".ENDDATE IS NULL) 
) "t" ON "m".ID = "t".MODEL_ID 
Which is correct.

However, when upgrading package Devart.Data.Oracle.EFCore to 9.11.951 or 9.11.980, running the same query will result in the following Oracle query:

Code: Select all

SELECT "m".DESCRIPTION "modelName", "t".DESCRIPTION "ModelVersion" 
FROM MODELS "m" 
LEFT JOIN ( 
    SELECT "m0".ID, "m0".DESCRIPTION, "m0".ENDDATE, "m0".MODEL_ID, "m0".STARTDATE 
    FROM MODELVERSIONS "m0" 
    WHERE ("m0".STARTDATE >= :p__checkDate_0) AND (("m0".ENDDATE < :p__checkDate_1) OR "m0".ENDDATE IS NULL) 
) "t" ON "m".ID = "t".MODEL_ID 
As you can see, the arguments in the WHERE clause are switched, while the operators remain the same, which would only work for eg. equal or between operators.

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

Re: EF Core 3.1 Generated SQL error: Greater > and smaller < than get reversed in some cases!

Post by mbouwhui » Fri 15 May 2020 12:37

I have sent an e-mail with the reproduction for this issue.
Hope to hear from you soon!

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

Re: EF Core 3.1 Generated SQL error: Greater > and smaller < than get reversed in some cases!

Post by Shalex » Thu 21 May 2020 19:42

The bug with the opposite order of operands in case of less than, more than operators in the Where clause of LINQ query in EF Core 3 is fixed. We will notify you when the new public build is released.

Post Reply