How to get all information aggregated with nhibernate

Question

Take a look the classes below:

public class Produt
{
    public virtual int id { get; set; }
    public virtual string name { get; set; }

    [ScriptIgnore]
    public virtual Unit unit { get; set; }
}

public class Unit
{
    public virtual int id { get; set; }
    public virtual string name { get; set; }
    public virtual IList<Produt> produts { get; set; }
}

After this, the mappings:

public partial class ProdutMap : ClassMap<Produt>
{
    public ProdutMap()
    {
        Id(x => x.id).GeneratedBy.Identity();
        Map(x => x.name).Length(100).Not.Nullable();
        References(x => x.unit, "idUnit").Cascade.All().LazyLoad();
    }
}

public partial class UnitMap : ClassMap<Unit>
{
    public UnitMap()
    {
        Id(x => x.id).GeneratedBy.Identity();
        Map(x => x.name).Length(100).Not.Nullable();
        HasMany(x => x.produts).Cascade.All().KeyColumns.Add("idUnit").LazyLoad();
    }
}

Now, imagine that I want to execute this query:

SELECT produt.id, produt.name, unit.name FROM Produt, Unit WHERE produt.idUnit = unit.id

with nhibernate? How to do? Something help?

P.S. The [ScriptIgnore] is because I had problems with circular references. My classes are not only these. This is just an example.

this might be useful http://stackoverflow.com/q/10510913/1236044 — jbl, Sep 06 '13 at 12:03
Thanks for your answer jbl, but when I do this: yourNhSession.CreateSQLQuery("SELECT produt.id, produt.name, unit.name FROM Produt, Unit WHERE produt.idUnit = unit.id").List(); ... will return me a list of Produts... Where are the information of object "Unit"? — extrass, Sep 06 '13 at 16:25
NHibernate can only give you a list of a one type (not two). Thus, you must create some ProductUnit type that can contain the data of both. — Apocatastasis, Sep 07 '13 at 00:11

score 0 · Answer 1 · edited May 23 '17 at 12:11

Just fetch Products

The simplest way to do this is to just fetch a list of Products. Product already contains all of the information you need because it has a reference to Unit.

// NOTE: This fetches ALL products.  You really should limit this.
var products = session.Query<Product>();
foreach (var product in products)
    Console.WriteLine("Product: {0}, Unit: {1}", product.Name, product.Unit.Name);

On each iteration of this loop, if product.Unit points to a unit that NHibernate has not fetched yet, NHibernate will lazily execute another query to fetch that unit.

Sometimes you are not able to use lazy loading - perhaps you need to close the NHibernate session before iterating over the results. Also, for performance it would be better to reduce the number of round-trips to the database. We can fix these problems by changing our query like so:

var products = session.Query<Product>().Fetch(x => x.Unit);

Flatten your results

If for some reason you need flattened result objects where you don't have to dig through nested objects to get the data you need, you can use "projections" to do this. With LINQ, this looks like:

var productInfos = session.Query<Product>().Select(x => new
{
    ProductId = x.Id,
    ProductName = x.Name,
    UnitName = x.Unit.Name
});

This is also useful if you need to limit the columns returned by NHibernate - for example, if one of the column contains huge BLOBs that you want to avoid fetching.

Besides LINQ, NHibernate has several different ways to execute queries: native SQL, HQL, Criteria, and QueryOver. For Criteria or QueryOver, the AliasToBean result transformer will help you when executing these types of queries. See this related question for an example using AliasToBean in a Criteria query: NHibernate - Only retrieve specific columns when using Critera queries?

How to get all information aggregated with nhibernate

1 Answers1

Just fetch Products

Flatten your results