NHibernate Linq OrderBy+Select+Distinct produces invalid SQL

Question

If I have these models mapped in NHibernate:

public class TagActions {
    public Tag Tag { get; set; }
    public DateTime ChangeTime { get; set; }
}
public class Tag {
    public string Label { get; set; }
}

And do the following:

// repo.TagActions is IQueryable<TagAction> from session.Query<TagAction>()
var stake = DateTime.UtcNow - TimeSpan.FromDays(120);
repo.TagActions.Where(ta => ta.ChangeTime > stake)
    .OrderByDescending(ta => ta.ChangeTime)
    .Select(ta => ta.Tag).Distinct().Take(10);

It produces (in the MsSql2012Dialect dialect):

    select
        distinct tag1_.Label as label1_12_,
        tag1_.Color as color2_12_
    from
        TagAction tagaction0_
    left outer join
        Tag tag1_
            on tagaction0_.Tag=tag1_.Label
    where
        tagaction0_.ChangeTime>@p0
    order by
        tagaction0_.ChangeTime desc OFFSET 0 ROWS FETCH FIRST @p1 ROWS ONLY;
    @p0 = 2020-04-02T10:40:54.0038780Z [Type: DateTime2 (8:0:0)],
    @p1 = 10 [Type: Int32 (0:0:0)]

Which is great except for the problem that ChangeTime does not appear in the SELECT clause, so MSSQL fails and rejects the query. The SQLiteDialect produces SQL with the same problem, but SQLite processes the query regardless. I am pretty sure that technically MSSQL's response is correct per the SQL spec.

Question part 1:

This has to be a bug in NHibernate, correct? I did find this:

MSSql2012Dialect generates invalid order by clause for paging distinct query

but it only shows there as applying to 4.0 (alpha) and I'm on 5.2.7. Anyone else have a known bug report on this?

Question part 2:

Any proposed workaround with session.Query<TagAction>/Linq? I am loath to inject .ToList() after OrderByDescending(...), which works, but there may be a large number of TagActions and only a small number of Tags to return. Any way to trick it into including the ChangeTime column in the SELECT clause and still do the DISTINCT? I could potentially drop down to HQL or ICriteria or something but have no experience with those yet.

When you distinct you must select the column you are ordening on. A workaround is to group by your Label and Color columns and order by Max change time — Preben Huybrechts, Jul 31 '20 at 11:02
Does this answer your question? [How to use DISTINCT and ORDER BY in same SELECT statement?](https://stackoverflow.com/questions/5391564/how-to-use-distinct-and-order-by-in-same-select-statement) — Preben Huybrechts, Jul 31 '20 at 11:06
@PrebenHuybrechts My key here is using NHibernate and preferably Linq/IQueryable... though since this method is in my repository, if there are no other options I could drop down to HQL or use QueryOver/ICriteria or some other method, if i have to. So no, I understand well the SQL problem, the question is how to get NHibernate to play ball. — S'pht'Kr, Jul 31 '20 at 11:08

Preben Huybrechts · Answer 1 · 2020-07-31T11:25:50.907

For nhibernate you can try something like this with QueryOver:

session.QueryOver<TagActions>()
.Select(
    Projections.Group<TagActions>(ta => ta.Label),
    Projections.Max<TagActions>(ta => ta.ChangeTime)
    )
.OrderBy(Projections.Max<TagActions>(ta => ta.ChangeTime)).Desc
.Take(10)
.List<object[]>();

Using Max because you are sorting descending, and you'll probably want the most recent items.

As far as I know there is no way to accomplish this with linq to nhibernate. Since you need to .Select(ta => new { ta.Tag }) you are unable to specify OrderBy on Max(ChangeTime) since it's no longer part of the object.

If you wan't to query with linq, you could create a View/Table valued function with the group by and max() and map this in NHibrenate and query the View/Table valued function with linq.

NHibernate Linq OrderBy+Select+Distinct produces invalid SQL

Question part 1:

Question part 2:

1 Answers1