NHibernate How do I query against an IList property?

Question

I am trying to query against an IList<string> property on one of my domain classes using NHibernate. Here is a simple example to demonstrate:

public class Demo
{
    public Demo()
    {
        this.Tags = new List<string>();
    }
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<string> Tags { get; set; }
}

Mapped like this:

<class name="Demo">
<id name="Id" />
<property name="Name" />
<bag name="Tags">
  <key column="DemoId"/>
  <element column="Tag" type="String" />
</bag>

And I am able to save and retrieve just fine. Now to query for instances of my domain class where the Tags property contains a specified value:

var demos = this.session.CreateCriteria<Demo>()
            .CreateAlias("Tags", "t")
            .Add(Restrictions.Eq("t", "a"))
            .List<Demo>();

Results in the error: collection was not an association: Demo.Tags

var demos = (from d in this.session.Linq<Demo>()
                     where d.Tags.Contains("a")
                     select d).ToList();

Results in the error: Objct reference not set to an instance of an object.

var demos = this.session.CreateQuery("from Demo d where :t in elements(d.Tags)")
            .SetParameter("t", "a")
            .List<Demo>();

Works fine, but as my real domain class has many many properties, and I am building a complicated dynamic query, doing ugly string manipulation is not my first choice. I'd much rather use ICriteria or Linq. I have a user interface where many different possible search criteria can be entered. The code that builds up the ICriteria right now is dozens of lines long. I'd really hate to turn that into HQL string manipulation.

score 4 · Accepted Answer · answered Jul 31 '09 at 14:13

4

So because of limitations of the Criteria API, I decided to bend my domain classes to fit.

I created an entity class for the Tag. I couldn't even create it as a value object. It had to have its own id.

I feel dirty now. But being able to construct a dynamic query without resorting to string manipulation was more important to me than staying true to the domain.

answered Jul 31 '09 at 14:13

JohnRudolfLewis

1,692
1
18
33

There is a way doing this by criteria api. I packed it in a separate answer. – Tobias Dec 05 '16 at 10:28

score 4 · Answer 2 · answered Nov 05 '14 at 11:14

As documented here:

17.1.4.1. Alias and property references

we can use:

...
A collection key             {[aliasname].key}      ORGID as {coll.key}
The id of an collection      {[aliasname].id}       EMPID as {coll.id}
The element of an collection {[aliasname].element}  XID as {coll.element}
...

there is a small bug in doc... instead of ".element" we have to use ".elements"

var demos = this.session.CreateCriteria<Demo>()
        .CreateAlias("Tags", "t")

        // instead of this
        // .Add(Restrictions.Eq("t", "a"))

        // we can use the .elements keyword
        .Add(Restrictions.Eq("t.elements", "a"))

        .List<Demo>();

score 3 · Answer 3 · answered Jul 30 '09 at 21:48

3

You need to use SubCriterias not alias. This should work:

var demos = this.session.CreateCriteria<Demo>()
            .CreateCriteria("Tags")
            .Add(Restrictions.Eq("Tag", "a"))
            .List<Demo>();

answered Jul 30 '09 at 21:48

zoidbeck

4,091
1
26
25

That also results in the error message: collection was not an association: Demo.Tags – JohnRudolfLewis Jul 30 '09 at 21:52
You haven't specified any table for your bag. I don't know this kind of mapping. Is this intended? – zoidbeck Jul 30 '09 at 22:27
Makes no difference if I specify the table name, or let it use the default (same as the property name in this case). – JohnRudolfLewis Jul 30 '09 at 22:33
Okay, finally saw what your problem is. Tags is a List, not a List. You can not do this using CriteriaAPI. See this thread: http://www.mail-archive.com/nhibernate-development@googlegroups.com/msg00024.html – zoidbeck Jul 30 '09 at 22:44
This should be marked as answer, it's the first I've come to that really did the trick! – bastianwegge Dec 11 '13 at 15:56

score 3 · Answer 4 · edited Mar 13 '13 at 08:48

3

HQL:

from Demo d where :val in elements(d.Tags)

edited Mar 13 '13 at 08:48

Stefan Steinegger

63,782
15
129
193

answered Jul 31 '09 at 01:49

Ayende Rahien

22,925
1
36
41

I know that works. Above I acknowledge that it does. But I'm kicking and screaming about having to write a huge dynamic query based on my user's input. Thank you for looking at this though. I appreciate it Oren. – JohnRudolfLewis Jul 31 '09 at 02:27

score 2 · Answer 5 · edited May 23 '17 at 12:01

Switching to a class over a string is one compromise. Using HQL instead of ICriteria is another. There is a third compromise however... use custom SQL. Try this out.

var demos = Session.CreateCriteria<Demo>()
    .Add(Expression.Sql(
        "EXISTS (SELECT 1 FROM [Tags] custom_sql_t WHERE custom_sql_t.[DemoId] = {alias}.[Id] AND custom_sql_t.[Tag] = ?)",
        "a",
        NHibernateUtil.String))
    .List<Demo>();

This results in the follwing SQL being generated by NHibernate 2.1.2.4000...

exec sp_executesql N'SELECT this_.Id as Id2_0_, this_.Version as Version2_0_, this_.Name as Name2_0_ FROM Demo this_ WHERE EXISTS (SELECT 1 FROM [Tags] custom_sql_t WHERE custom_sql_t.[DemoId] = this_.[Id] AND custom_sql_t.[Tag] = @p0)',N'@p0 nvarchar(1)',@p0=N'a'

See this post for another example...

NHibernate - Querying from a collection of Value Types (non-Entity) to solve Select N+1

score 0 · Answer 6 · answered Dec 05 '16 at 10:31

This is possible by creating a separate criteria:

ICriteria demoCriteria = session.CreateCriteria<Demo>();
...
demoCriteria.Add(Restrictions...);
...
ICriteria tagCriteria = demoCriteria.CreateCriteria("Tags");
tagCriteria.Add(Restrictions.In("elements", new {"Tag1", "Tag2", ...}));

return demoCriteria.List<Demo>();

NHibernate How do I query against an IList property?

6 Answers6

17.1.4.1. Alias and property references

Linked