NHibernate How do I query against an IList<string> 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.

Erudition

Answer 1

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.

Erudition

There is a way doing this by criteria api. I packed it in a separate answer. – Weismannism

Answer 2

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>();

Anyone

Answer 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>();

Gamine

That also results in the error message: collection was not an association: Demo.Tags – Erudition

You haven't specified any table for your bag<Tag>. I don't know this kind of mapping. Is this intended? – Gamine

Makes no difference if I specify the table name, or let it use the default (same as the property name in this case). – Erudition

Okay, finally saw what your problem is. Tags is a List<string>, not a List<Tag>. You can not do this using CriteriaAPI. See this thread: mail-archive.com/[email protected]/… – Gamine

This should be marked as answer, it's the first I've come to that really did the trick! – Nunley

Answer 4

HQL:

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

Folketing

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. – Erudition

Answer 5

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

Mandal

Answer 6

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>();

Weismannism