NHibernate: How to filter on primitive collections

I am using NHibernate with a client and I keep hitting the same issue. I have entities with basic collections of strings. I want to search for entities on the basis of filtering criteria expressed against the elements of the collection.

The easy solution is to treat the collection elements as entities but this is not ideal. That really complicates the domain. In some cases, the elements are simply references to foreign entities outside the scope of NHibernate, for example in a remote service or configuration file.

In SQL, I can pose the query as a correlated subquery or as an (outer) join. This has the advantage of being efficient and does not result in the loading of the collection.

An example would be searching for a Cat that is only black when each Cat has a collection of Colours, perhaps represented by an RGB triad. It would be true normal form to extract the colours into their own table but it would also be ridiculous to do so because a foreign key already exists – the RGB triad! Another example would be finding all Cats that are partly black or partly white. There is no reason why the criteria cannot be arbritrarily complex.

I have not found a way in HQL or the Criteria API and my scenarios require the filtering to take place in the database. The result is that I am using SQL directly with NHibernate’s ISession.

Any better solutions? Does ADO.NET Entity Framework also lack this concept?

Advertisements

One thought on “NHibernate: How to filter on primitive collections

  1. ivowiblo

    Hi,
    In hql you have the elements word that should help you.

    from the documentation (https://www.hibernate.org/hib_docs/nhibernate/html/queryhql.html#queryhql-expressions):

    select mother from Eg.Cat as mother, Eg.Cat as kit
    where kit in elements(mother.Kittens)

    select p from Eg.NameList list, Eg.Person p
    where p.Name = some elements(list.Names)

    from Eg.Cat cat where exists elements(cat.Kittens)

    from Eg.Player p where 3 > all elements(p.Scores)

    from Eg.Show show where ‘fizard’ in indices(show.Acts)

    Hope it helped!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s