R – Order by collection count using ICriteria & NHibernate

icriterianhibernate

Using the standard NHibernate example of Cats and Kittens, how would I use ICriteria to sort Cats based on Kitten count? For example, I want to do something like:

ICriteria crit = Session.CreateCriteria(typeof(Cat));
return crit.Order(Order.Asc("**Kittens.Count**"));

Anyone know how to achieve this?

Best Answer

In HQL you can do it like this:

select cat
from Eg.Cat cat
join cat.Kittens kitten
group by cat
order by count(kitten) asc

Related Solutions

R – Joins in fluent nhibernate

If you're looking for a full tutorial on how to do this, I recommend the FNH wiki or one of the many blog postings which can be found through Google.

However, you're trying to implement a many-to-many relationship here, and that seems to throw a lot of people off. Here's a rough guide:

On your Customer class, you'll need a collection like:

IList<Product> Products { get; private set; }

And similarly, on your Product class:

IList<Customers> Customers { get; private set; }

You start off a many-to-many map with the HasManyToMany function:

public class CustomerMap : ClassMap<Customer>
{
    public CustomerMap()
    {
        // other mappings

        HasManyToMany<Product>(x => x.Products)
            .WithTableName("Cust_Product")  // Specifies the join table name
            .WithParentKeyColumn("CustomerId") // Specifies the key joining back to this table (defaults to [class]_id, Customer_id in this case)
            .WithChildKeyColumn("ProductId")
            .FetchType.Join(); // Instructs NHibernate to use a join instead of sequential select
    }
}

Then repeat the process for the other side of the relationship (the Customers property on the Product class).

C# – Hibernate/NHibernate : how to persist subclass as instance of superclass

Old question, I guess I would have to use interfaces instead.

Best Answer

Related Solutions

R – Joins in fluent nhibernate

C# – Hibernate/NHibernate : how to persist subclass as instance of superclass

Related Topic