Open
Description
I'm using LINQ with the MSSQL2012 driver, NHibernate version 5.2.4. Executing below query results in the following exception:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified
public class TestEntity
{
public virtual int Id { get; set; }
public virtual string PropertyA { get; set; }
public virtual string PropertyB { get; set; }
}
var result = session.Query<TestEntity>()
.Select(x => x.PropertyA.Contains("test") ? x.PropertyA : x.PropertyB)
.OrderBy(a => a)
.Distinct()
.ToList();
The generated SQL looks is listed below. NHibernate is adding PropertyA and PropertyB in the select, but not in the order by.
SELECT DISTINCT CASE
WHEN userquery_0_.PropertyA LIKE ('%' + @p0 + '%')
THEN 1
ELSE 0
END AS col_0_0_
,userquery_0_.PropertyA AS col_1_0_
,userquery_0_.PropertyB AS col_2_0_
FROM [TestEntity] userquery_0_
ORDER BY (
CASE
WHEN userquery_0_.PropertyA LIKE ('%' + @p0 + '%')
THEN userquery_0_.PropertyA
ELSE userquery_0_.PropertyB
END
) AS
I've made a reproduction in linqpad: download