LINQ uses ternary expressions to generate error SQL #3591
Description
I ran into a puzzle that using LINQ in a query expression under NET8 and NHibernate5.5.2 uses a ternary expression, and the resulting SQL doesn't look right, the code is as follows:
var query = (from t in session.Query<Test>()
select new
{
t.Id,
NameSeq = (t.Name == "One" ? 1 : t.Name == "Tow" ? 2 : 0)
}).OrderBy(o => o.NameSeq)
.ToList();
By normal logic it should generate the sql:
test0_."Id" as col_0_0_,
(case
when test0_."UserName"=:p0 then :p3
else (case
when test0_."UserName"=:p1 then :p5
else :p6
end)
end) as col_1_0_
from
"Test" test0_
order by
(case
when test0_."UserName"=:p0 then :p3
else (case
when test0_."UserName"=:p1 then :p5
else :p6
end)
end) asc;
But in reality, it is like this:
select
test0_."Id" as col_0_0_,
case
when test0_."UserName"=:p0 then TRUE
else FALSE
end as col_1_0_,
case
when test0_."UserName"=:p1 then TRUE
else FALSE
end as col_2_0_
from
"Test" test0_
order by
(case
when test0_."UserName"=:p0 then :p3
else (case
when test0_."UserName"=:p1 then :p5
else :p6
end)
end) asc;
I want to know how to generate SQL correctly because I need deduplication and I can't do that with my current SQL, which is very thankful.