Nhibernate criteria API for many-to-many

Question

My object model is the following:

Item has many Tags and a Tag could belong to many Items

I'd like to execte the following query using criteria's.

SELECT * FROM Item item where item.Id in (Select it.ItemId from dbo.ItemToTags it where it.Tag_id = 'ONE') and item.Id in (Select it.ItemId from dbo.ItemToTags it where it.Tag_id = 'TWO')

Meaning I would like to give a collection of possible tags and then provide all items that have all of these tags:

I tried the following but I get not results :

CreateCriteria().CreateAlias("Tags", "Tags"); if (AndQuery) { foreach(var tag in Tags) { criteria.Add(Subqueries.PropertyEq("Tags.Id", DetachedCriteria.For().Add(Restrictions.Eq("Id", tag)) .SetProjection(Projections.Property("Id")))); } }

Any help is appreciated !

Answer 1

I'm not sure if your query could be translated as :

SELECT * FROM Item item 
join ItemToTags itt on itt.ItemId = item.Id
join Tags t on itt.TagId = t.Id
where t.Id in ('ONE','TWO')

If so, you should be able to do this :

CreateCriteria<Item>().CreateAlias('Tags','t')
   .Add(Restrictions.In('t.Id',new List<string>{'TWO','ONE'}).List<Item>();

That's assuming you have the tags collection mapped on the Item.

hth