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..