Sql methods/hqlGenerator is not properly applied when there is subquery in the linq expression

[xiaoyvr]

Version: 5.3.10

If the select clause has HQL generator method and subquery at the same time. like this:

session.Query<Person>()
    .Select(t => new { 
        name = p.Name.Like("abc*") ? 0 : 1, 
        petNames = p.Pets.Select(p => p.Name).ToArray() 
    }).ToList();

Will throws exception:

System.NotSupportedException
The NHibernate.Linq.SqlMethods.Like(string, string) method can only be used in Linq2NHibernate expressions.

But if remove the subquery part petNames = p.Pets.Select(p => p.Name).ToArray() , everything works fine.

It looks like if there is a subquery in the select clause, it will break the generator logic. This Like method shouldn't be called.

[oskarb]

No, this isn't a matter of "not applying hql generator". Your addition of the second part means that the whole thing can no longer be translated to SQL. If you look at the generated SQL, you would probably see the equivalent of simple select * from person, possibly also with a join to pets. NHibernate then tries to do the rest locally and calls the Like() method, which does nothing but throw.

What SQL do you imagine to get from the query? I think it would be something like:

SELECT (case when p.Name like 'abc*' then 0 else 1),
       (select pets.Name from person p2 joined with pets where p2.id = p1.id)  -- NOTE 1
FROM  person p1

NOTE 1: This part would try to return multiple rows in a single value, which doesn't work.

(Please excuse minor syntax errors in the SQL above, it's written quickly.)

[bahusoid]

Your addition of the second part means that the whole thing can no longer be translated to SQL

@oskarb I don't think it's true. We do support such kind of queries. Check NestedSelectsTests. For instance: https://github.com/nhibernate/nhibernate-core/blob/c3380e313b841298fa3174b80b8e519474bc2fcf/src/NHibernate.Test/Linq/NestedSelectsTests.cs#L403-L416

And generated SQL:

13:25:23,609 DEBUG SQL:230 - 
    select
        entries1_.TimesheetEntryId as col_0_0_,
        entries1_.TimesheetEntryId as col_1_0_,
        timesheet0_.TimesheetId as col_2_0_,
        timesheet0_.TimesheetId as col_3_0_,
        entries1_.TimesheetEntryId as timesheetentryid1_16_,
        entries1_.EntryDate as entrydate2_16_,
        entries1_.NumberOfHours as numberofhours3_16_,
        entries1_.Comments as comments4_16_ 
    from
        Timesheets timesheet0_ 
    left outer join
        TimesheetEntries entries1_ 
            on timesheet0_.TimesheetId=entries1_.TimesheetID 
            and (
                entries1_.NumberOfHours>=@p0
            );
    @p0 = 0 [Type: Int32 (0:0:0)]

Looks like the provided in issue query can be processed in similar way.

[oskarb]

Interesting, I didn't realize that. Cool!

It seems in the file you mention, some of the test cases use multiple "subselects", but none seem to do anything complicated for the expression that is not a collection. They just return the Id property. I wonder if the presence of the collection-valued part means the other part is not considered for SQL translation or if it really is just about the Like() call.

I'm curious to see what SQL is generated if the Like expression is replaced with just some simple operator such as ==. If that carries over into SQL or if it's evaluated locally. (Or even just p.Name + "abc" to remove the ternary operator also.)