Join sub query in NHibernate (join on multiple columns)

Question

How to express the query

    Select * from
        (select name, Max(date) as date from items group by name) as latest
    inner join items as i
        on i.name=latest.name and i.date = latest.date

in NHibernate's QueryOver syntax?

As a result I expect to get records with maximum dates for each name from items table.

Each item in table correspond to the ItemEnity class:

[Class(Table = "items")]
public class ItemEnity
{
    [Id(Name = "Id")]
    [Generator(1, Class = "native")]
    public virtual long Id { get; set; }

    [Property(NotNull = true)]
    public virtual string Name { get; set; }

    [Property(NotNull = true)]
    public virtual DateTime Date { get; set; }

    // other columns ... 
}

I've managed to express subquery in a strongly typed form:

public class ItemLatest
{
    public string Name { get; set; }
    public DateTime Date { get; set; }
}

// ...

ItemLatest latest = null;

var subquery = QueryOver.Of<ItemEntity>().SelectList(list => list
    .SelectGroup(i => i.Name).WithAlias(() => latest.Name)
    .SelectMax(i => i.Date).WithAlias(() => latest.Date))
    .TransformUsing(Transformers.AliasToBean<ItemLatest>());

var result = subquery.GetExecutableQueryOver(session).List<ItemLatest>();

But I have no clue how to write join because I can't find out how to use JoinQueryOver() to express connection between two entities (ItemLatest and ItemEnity in my case) that has no relation property referencing from one to another.

Can't you write it using Query<T>() in a Linq syntax? It's under the NHibernate.Linq namespace — Andre Calil
– Andre Calil, Commented Aug 21, 2013 at 17:03
Don't know what to pass as an "inner" IEnumerable parameter of the Join() method. In other words, I have only one IQueryable<T> produced by Query<T>() — Ilya Serbis
– Ilya Serbis, Commented Aug 21, 2013 at 18:06

Radim Köhler · Accepted Answer · 2013-08-21 18:19:44Z

1

The JOIN is in the ORM world (including NHibernate) defined by mapping. So, if you would like to use criteria and JOIN, it must be over the Referenced property.

What we could for example do, is to create a new object e.g. ItemEnityMaxDate and map it to view similar to your SELECT MAX .. GROUP BY. We can extend it like this for example:

SELECT main.Id, main.Name, main.Date
  FROM items  main
    INNER JOIN 
    (
      SELECT i.Name, Max(i.Date) AS Date FROM items i GROUP BY i.Name 
    ) AS latest
    ON main.Name = latest.Name AND main.Date = latest.Date

The C# ItemEnityMaxDate would be similar as ItemEntity (well we will need only Id), mapped to the above view. Then we can use it as a subquery

// inner select
var subquery = DetachedCriteria.For<ItemEnityMaxDate>()
    .SetProjection(Projections.Property("Id"));

// the ItemEntity
var query = session.CreateCriteria<ItemEntity>()
    .Add(Subqueries.PropertyIn("Id", subquery));
var list = query.List<ItemEntity>();

This is probably not the only way. But this way, having your "special query" represented as an object ... would work

answered Aug 21, 2013 at 18:19

Radim Köhler

124k48 gold badges243 silver badges343 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilya Serbis Over a year ago

Do you suggest to create view in DB and then extract it's results to C# object? Because if I would be able to write such a query on NHibernate, I would definitely be able to extract the whole ItemEntity object (by adding more columns to the resulting SELECT clause)

Radim Köhler Over a year ago

Not absolutely sure if I understand your comment. But, yes, I thing, that you can create new View in DB and new C# object. Both mapp in NHibernate, as read-only. Use the new ItemEntityMaxDate to filter the ItemEntity.

Collectives™ on Stack Overflow

Join sub query in NHibernate (join on multiple columns)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related