This has been a 2 week battle for me so far with no luck. :( 

Let me first state my objective. To be able to search entities which are tagged "foo" **and** "bar". Wouldn't think that was too hard right?

I know this can be done easily with HQL but since this is a dynamically built search query that is not an option. First some code:

```
public class Foo 
{ 
     public virtual int Id { get;set; } 
     public virtual IList<Tag> Tags { get;set; } 
} 

public class Tag 
{ 
     public virtual int Id { get;set; } 
     public virtual string Text { get;set; } 
} 

```

Mapped as a many-to-many because the Tag class is used on many different types. Hence no bidirectional reference.

So I build my detached criteria up using an abstract filter class. Lets assume for simplicity I am just searching for Foos with tags "Apples"(TagId1) && "Oranges"(TagId3) this would look something like.

**SQL:**

```
SELECT ft.FooId
FROM Foo_Tags ft
WHERE ft.TagId IN (1, 3)
GROUP BY ft.FooId
HAVING COUNT(DISTINCT ft.TagId) = 2; /*Number of items we are looking for*/

```

**Criteria**

```
var idsIn = new List<int>() {1, 3};
var dc = DetachedCriteria.For(typeof(Foo), "f"). 
           .CreateCriteria("Tags", "t") 
           .Add(Restrictions.InG("t.Id", idsIn)) 
           .SetProjection( Projections.ProjectionList() 
              .Add(Projections.Property("f.Id")) 
              .Add(Projections.RowCount(), "RowCount") 
              .Add(Projections.GroupProperty("f.Id"))) 
          .ProjectionCriteria.Add(Restrictions.Eq("RowCount", idsIn.Count)); 
} 
var c = Session.CreateCriteria(typeof(Foo)).Add(Subqueries.PropertyIn("Id", dc)) 

```

Basically this is creating a DC that projects a list of Foo Ids which have all the tags specified.

This compiled in NH 2.0.1 but didn't work as it complained it couldn't find Property "RowCount" on class Foo.

After reading [this](https://stackoverflow.com/questions/636925/nhibernate-projections-and-having-clause/637003#637003) post I was hopeful that this might be fixed in 2.1.0 so I upgraded. To my extreme disappointment I discovered that ProjectionCriteria has been removed from DetachedCriteria and I cannot figure out how to make the dynamic query building work without DetachedCriteria.

So I tried to think how to write the same query without needing the infamous Having clause. It can be done with multiple joins on the tag table. Hooray I thought that's pretty simple. So I rewrote it to look like this.

```
var idsIn = new List<int>() {1, 3};
var dc = DetachedCriteria.For(typeof(Foo), "f"). 
           .CreateCriteria("Tags", "t1").Add(Restrictions.Eq("t1.Id", idsIn[0]))
           .CreateCriteria("Tags", "t2").Add(Restrictions.Eq("t2.Id", idsIn[1]))

```

In a vain attempt to produce the below sql which would do the job (I realise its not quite correct).

```
SELECT f.Id
FROM Foo f
JOIN Foo_Tags ft1
ON ft1.FooId = f.Id
   AND ft1.TagId = 1
JOIN Foo_Tags ft2
ON ft2.FooId = f.Id
   AND ft2.TagId = 3

```

Unfortunately I fell at the first hurdle with this attempt, receiving the exception "Duplicate Association Path". Reading [around](http://derek-says.blogspot.com/2008/06/duplicate-association-path-bug-in.html) this seems to be an [ancient](http://opensource.atlassian.com/projects/hibernate/browse/HHH-879) and still very real bug/limitation.

What am I missing?

I am starting to curse NHibernates name at making what is you would think so simple and common a query, so difficult. Please help anyone who has done this before. How did you get around NHibernates limitations.

Forget reputation and a bounty. If someone does me a solid on this I will send you a 6 pack for your trouble.