CODEWITHSUNDEEP
c#nhibernatejoin
Ilya Serbis
Ilya Serbis

Reputation: 22273

Join sub query in NHibernate (join on multiple columns)

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.

Upvotes: 0

Views: 1800

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

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

Upvotes: 1

Related Questions