generated sql has incorrect case when logic

[MijnUsernam3]

File a bug

I am currently upgrading or application that is made in framework 4.8 to NET8 and i have some issues with the Entity framework part where the generated sql from the linq query is incorrect. The logic with boolean seems to be the problem, I am not sure if this is a EF core issues or the provider (Devart).

Include your code

this is the linq query return await _vaDbContext.RefeLijst .Join(_vaDbContext.RefeLijst, x => new { p1 = x.Item, x.Campagne }, y => new { p1 = y.Lijst, y.Campagne }, (x, y) => new { x, y }) .Where(set => set.x.Campagne == campagne && set.x.Lijst == "BOOMSOORT") .OrderBy(set => set.y.Lijst) .ThenBy(set => set.y.Sortering.HasValue) .ThenBy(set => set.y.Sortering) .Select(set => new RefeLijstDTO { Item = set.y.Item, Lijst = set.y.Lijst, Omschrijving = set.y.Omschrijving, Sortering = set.y.Sortering, Tekst = set.y.Tekst }) .ToListAsync();

I think this is wat entity framework generate. queryContext => new SingleQueryingEnumerable<RefeLijstDTO>( (RelationalQueryContext)queryContext, RelationalCommandCache.QueryExpression( Projection Mapping: Item -> 0 Lijst -> 1 Omschrijving -> 2 Sortering -> 3 Tekst -> 4 SELECT p0.LIJSTITEM AS Item, p0.LIJST AS Lijst, p0.OMSCHRIJVING AS Omschrijving, p0.SORTERING AS Sortering, p0.LIJSTITEM_TEKST AS Tekst FROM PRI.PRI_REF_LIJST AS p INNER JOIN PRI.PRI_REF_LIJST AS p0 ON (p.LIJSTITEM == p0.LIJST) && (p.CAMPAGNE == p0.CAMPAGNE) WHERE (p.CAMPAGNE == @__campagne_0) && (p.LIJST == BOOMSOORT) ORDER BY p0.LIJST ASC, CASE WHEN p0.SORTERING IS NOT NULL == True THEN True ELSE False END ASC, p0.SORTERING ASC), null, Func<QueryContext, DbDataReader, ResultContext, SingleQueryResultCoordinator, RefeLijstDTO>, Domain.Verzamelaanvraag.Application.Dal.VaOracleDbContext, False, False, True ) and this is the sql that entity framework uses on the database. SELECT "p0".LIJSTITEM "Item", "p0".LIJST "Lijst", "p0".OMSCHRIJVING "Omschrijving", "p0".SORTERING "Sortering", "p0".LIJSTITEM_TEKST "Tekst" FROM PRI.PRI_REF_LIJST "p" INNER JOIN PRI.PRI_REF_LIJST "p0" ON "p".LIJSTITEM = "p0".LIJST AND "p".CAMPAGNE = "p0".CAMPAGNE WHERE "p".CAMPAGNE = :p__campagne_0 AND "p".LIJST = 'BOOMSOORT' ORDER BY "p0".LIJST, CASE WHEN CASE WHEN "p0".SORTERING IS NOT NULL THEN 1 ELSE 0 END THEN 1 ELSE 0 END, "p0".SORTERING

Of course this result in a exception as the sql is incorrect (the part after the order by case when logic is wrong) : Devart.Data.Oracle.OracleException (0x80004005): ORA-00920: invalid relational operator

Include provider and version information

EF Core version: 8.0.1 Database provider: Devart.Data 6.0.1.0 ; Devart.Data.Oracle 10.3.10 ; Devart.Data.Oracle.EFCore 10.3.10.8 Database: Oracle Target framework: (e.g. .NET 8.0) Operating system: Windows IDE: (e.g. Visual Studio 2022 17.4)

[ErikEJ]

@MijnUsernam3 You should address this with DevArt, this provider is not maintained by the EF Core team

[MijnUsernam3]

Ok, thank you.

I was not sure if this was a EF issues or a Devart issues, thank you.