Querying on collection of strings with NHibernate

Question

Hi,

I have an "Item" class, and this class has a collection "Tags".

Item
    IList<string> Tags;

my DB looks like this:

Items
   Id

Tags
   ItemId
   TagName

I am trying to get all Items which have the tags "x" and "y". How can I do this with NHibernate (preferably with criteria API)? Is it even possible?

Thanks.

EDIT: can I do it without mapping the Tag object? It doesn't have ti be 1 query. Something like

1. var q = query that will return all id's of objects that have tag x or tag y".

2. var res = query that will return all Items with Id in ( q.Execute())

Answer 1

Try something like this:

session.CreateCriteria(typeof(Item))
    .CreateCriteria("Tags", global::NHibernate.SqlCommand.JoinType.InnerJoin)
    .Add(Expression.Eq("TagName", "x"))
    .Add(Expression.Eq("TagName", "y"))
    .List()

EDIT: Make sure you have a bi-directional association between Item and Tag. In Fluent, this would look something like:

public class ItemMap : ClassMap<Item>
{
    public ItemMap()
    {
        ...
        HasMany(x => x.Tags).Inverse();
        ...
    }
}

public class TagMap : ClassMap<Tag>
{
    public TagMap()
    {
        ...
        References<Item>(x => x.Item);
        ...
    }
}

Answer 2

I think the problem your getting is associated with this: https://www.hibernate.org/117.html#A2 - i.e. collections of strings/components can't be queried this way using the criteria API.

I worked around the problem using HQL, as stated in the linked FAQ:

session.CreateQuery("from Item item "
    + "where :x in elements(item.Tags) and :y in elements(item.Tags)")
    .SetString("x", X)
    .SetString("y", Y);

It appears to work as intended.

Answer 3

I ran across the same problem recently and really didn't find a good solution anywhere for my particular problem. In my solution I needed a mapped Tags object since it was a little more complicated. I'll describe what I did in case it helps anyone. Essentially this is the equivalent of the following sql:

SELECT * 
FROM Items I 
WHERE 2 = (
    SELECT COUNT(DISTINCT TagName) 
    FROM Tags T 
    WHERE T.ItemId = I.ID) 
      AND (T.TagName = 'X' OR T.TagName = 'Y')
)



NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Item),"I");

ICriterion tagCriteria = null;
foreach (string tag in tagNames) {
    ICriterion newCriteria = Expression.Eq("TagName", tag);
    if (tagCriteria == null) {
        tagCriteria = newCriteria;
    } else {
        tagCriteria = Expression.Or(tagCriteria, newCriteria);
    }
}
if (tagCriteria != null) {
    DetachedCriteria subCriteria = DetachedCriteria.For<Tags>("T");
    subCriteria.SetProjection(Projections.CountDistinct("TagName"))
        .Add(Expression.EqProperty("I.Id", "T.ItemId"))
        .Add(tagCriteria);
    criteria = criteria.Add(Subqueries.Eq(tagNames.Count,subCriteria ));
}
return criteria.List<Item>();