LINQ uses ternary expressions to generate error SQL

[HmgsLJ]

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.

[hazzik]

under NET8

Does it work correctly with lower versions?

[HmgsLJ]

under NET8

Does it work correctly with lower versions?
Yes, I tried .NET Core 3.1 and got the same error SQL as above..

[gliljas]

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.