nhibernate
LINQ uses ternary expressions to generate error SQL
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.
under NET8
Does it work correctly with lower versions? Yes, I tried .NET Core 3.1 and got the same error SQL as above..
It's not wrong per se. NHibernate just decides that it would rather handle the projection client side, but it can't do that with the OrderBy. The best correction would be to detect that the projection is used in the OrderBy and then prefer the server side execution.
