CODEWITHSUNDEEP
linqnhibernate
yonexbat
yonexbat

Reputation: 3012

LINQ NHibernate, latest related entity

How do I convert this in a LINQ for NHibernate statement or queryover? Is it possible at all? Seems like a standard thing (latest related object must meet some criteria).

select item, tag
from MyItem item
    join item.Tags tag
where tag.Id = (                                    
    select  max(tag2.Id)  
    from MyItem item2
        join item2.Tags tag2
    where item2.Id = item.Id
    group by item2.Id     
)

Best regards,

Upvotes: 0

Views: 65

Answers (1)

yonexbat
yonexbat

Reputation: 3012

Ok, answering my own question again. It is that simple with LINQ.

var resi = from x in session.Query<MyItem>()
                           let maxTagId = x.Tags.Max(tag => tag.Id)
                           select new { Item = x, Tag = x.Tags.Single(tag => tag.Id == maxTagId)};

The generated sql looks like

SELECT myitem0_.id                                                        AS
       col_0_0_,
       (SELECT mytag2_.id
        FROM   myitem_mytag tags1_,
               [mytag] mytag2_
        WHERE  myitem0_.id = tags1_.myitemid
               AND tags1_.mytagid = mytag2_.id
               AND mytag2_.id = (SELECT Cast(Max(mytag4_.id) AS INT)
                                 FROM   myitem_mytag tags3_,
                                        [mytag] mytag4_
                                 WHERE  myitem0_.id = tags3_.myitemid
                                        AND tags3_.mytagid = mytag4_.id)) AS
       col_1_0_,
       myitem0_.id                                                        AS
       Id0_,
       myitem0_.name                                                      AS
       Name0_
FROM   [myitem] myitem0_

and seems semantically correct.

Upvotes: 1

Related Questions