NHibernate - Mixing subquery and an eager left join

Question

Consider this database model:

Product         Payment         Info          
-------         -------         --------
Id              Id              Id
Name            Value           Year
                Date            Description
                ProductId       ProductId

My wish is to query all products (by their name) from database, eager load their payments and also

add a description from Info by some parameter. The (Postgre) SQL would look something like this:

Select product.Id, product.Name, payment.Value, payment.Date,
   (select inf.Description from Info inf 
    where inf.ProductId = product.Id where inf.Year = 2010 limit 1) as Description
from product left outer join payment on product.Id = payment.ProductId
where product.Name like ?

However, I have two major problems:

How to do the query in NHibernate (no matter whether it's in SQL, HQL, Criteria API, QueryOver, LINQ etc) ?

I suspect I have to project the Category.Description, so I came up with this:

var subquery = DetachedCriteria.For<Info>("inf")
    .Add(Restrictions.EqProperty("inf.Product.Id", "p.Id"))
    .Add(Restrictions.Eq("Year", 2010)).
.SetProjection(Projections.Property("inf.Description"));

var criteria = session.CreateCriteria<Product>("p")
    .Add(Restrictions.Eq("Product.Id", 12345678))
    .SetProjection(
        Projections.Property("p.Id"),
        Projections.Property("p.Name"),
        Projections.Property("p.Payments"),
        Projections.Alias(Projections.SubQuery(subquery), "p.Description"));

criteria.SetFetchMode("p.Payments", FetchMode.Eager);

However, this doesn't work. How can I create the subquery and still eager fetch the payments ?

How to create entities (beans) from the result set ?

I wish to create the following entities from the results:

public class Product
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
    public virtual IList<Payment> Payments { get; set; }
}

public class Payment
{
    public virtual int Id { get; set; }
    public virtual Product Product { get; set; }
    public virtual double Value { get; set; }
    public virtual DateTime Date { get; set; }
}

As I'm using projections, this looks like a case for AliasToBeanResultTransformer, however, it doesn't seem to recognize my left join (eagerly loaded Payments).

Please advise.

Thanks!

Since you already have a working SQL query, and you don't care if it's SQL, HQL or anything else, why don't you use the SQL query you have? — JB Nizet, Jan 10 '12 at 22:22
Yes, I would, but I still need to create entities from the result and I don't know how to do that. — user315648, Jan 11 '12 at 00:46

score 2 · Accepted Answer · edited May 23 '17 at 12:21

2

You can use NHibernate formula to get the value of description

<property name="Description" type="Type,Namespace" formula="(select inf.Description from Info inf 
where inf.ProductId = product.Id where inf.Year = 2010 limit 1)"/>

Ref : How to map a property with formula in NHibernate?

edited May 23 '17 at 12:21

Community

1
1

answered Jan 11 '12 at 04:56

Anand

717
6
20

NHibernate - Mixing subquery and an eager left join

1 Answers1