Nhibernate Tag Cloud Request

Question

It was a two-week battle for me so far without any luck. :(

Let me first state my purpose. To be able to search for objects labeled "foo" and "bar". Wouldn't you think it was too complicated?

I know that this can be easily done using HQL, but since it is a dynamically constructed 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; } 
} 

It is displayed as many-to-many because the Tag class is used for different types. Therefore, there is no bi-directional link.

, , . , Foos "" (TagId1) && & & "" (TagId3) .

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*/

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

DC, Foo, .

NH 2.0.1, , , RowCount Foo.

, 2.1.0, . , ProjectionCriteria DetachedCriteria, , DetachedCriteria.

, , , . . . , . , .

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]))

sql, ( , ).

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

, , " ". , /.

?

NHibernates , , . , , . NHibernates.

. - , 6 .

Answer 1

:

var dc = DetachedCriteria.For<Foo>( "f")
            .CreateCriteria("Tags", "t")
            .Add(Restrictions.InG("t.Id", idsIn))
            .SetProjection(Projections.SqlGroupProjection("{alias}.FooId", "{alias}.FooId having count(distinct t1_.TagId) = " + idsIn.Count,
                         new[] { "Id" }, 
                           new IType[] { NHibernateUtil.Int32 }));

- ( t1 _.TagId) - , DetachedCriteria - .

Answer 2

,

, db, - SQL- SQL, , ?

, , , Linq-2-SQL Linq-2-Entities, , , - , .