CODEWITHSUNDEEP
sqlsql-serverfluent-nhibernate
Vili
Vili

Reputation: 908

Fluent NHibernate QueryOver fails on subquery

I have a table that has datetime field and nvarchar field. I am trying to do a query that fetches only newest rows for each nvarchar field using Fluent Nhibernate QueryOver.

Table is here:

CREATE TABLE [dbo].[DataItem] (
    [Id]           INT           IDENTITY (1, 1) NOT NULL,
    [Source]       NVARCHAR (50) NULL,
    [Target]       NVARCHAR (50) NULL,
    [SendDateTime] DATETIME      NULL,
    [Version]      INT           NULL
);

QueryOver code is here:

DataItem dataItemAlias = null;

        var c = QueryOver.Of<DataItem>(() => dataItemAlias);

        c.WhereRestrictionOn(x => x.Source).IsInsensitiveLike("A");

        DataItem maxSendDateTimeAlias = null;

        var subQuery = QueryOver.Of<DataItem>(() => maxSendDateTimeAlias)
            .Select(Projections.ProjectionList()
            .Add(Projections.Max(() => maxSendDateTimeAlias.SendDateTime))
            .Add(Projections.Group(() => maxSendDateTimeAlias.Target)))
            .Where(() => dataItemAlias.Source == maxSendDateTimeAlias.Source);

        c.WithSubquery.WhereProperty(p => p.SendDateTime).In(subQuery);
        var result = c.GetExecutableQueryOver(Session).List<DataItem>();

This is the SQL query:

SELECT this_.ID as ID0_0_, this_.Source as Source0_0_, this_.Target as Target0_0_, this_.SendDateTime as SendDate4_0_0_, this_.Version as Version0_0_ FROM [DataItem] this_ 
WHERE lower(this_.Source) like 'a' and this_.SendDateTime in (SELECT max(this_0_.SendDateTime) as y0_, this_0_.Target as y1_ 
    FROM [DataItem] this_0_ WHERE this_.Source = this_0_.Source GROUP BY this_0_.Target)

If I remove the

this_0_.Target as y1_

from

SELECT max(this_0_.SendDateTime) as y0_, this_0_.Target as y1_ FROM [DataItem] this_0_...

It is correct query and I get the correct results.

This is the error I get:

"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

Here are the sources

Upvotes: 0

Views: 480

Answers (1)

Vili
Vili

Reputation: 908

So yeah the bug is real and here is a workaround

var subQuery =
QueryOver.Of<DataItem>()
    .Select(
        Projections.ProjectionList()
            .Add(Projections.SqlGroupProjection("max(SendDateTime) as maxSendDateTimeAlias", "Target",
                new string[] { "maxAlias" }, new IType[] { NHibernate.NHibernateUtil.Int32 })));

Got the answer from this post

Upvotes: 0

Related Questions