score:2

Accepted answer

Using Queryover the solution should look like,

Tag tagAlias = new Tag();
Post postAlias = new Post();

Tag tagAliasInner = new Tag();
Post postAliasInner = new Post();

var subQuery = QueryOver.Of(() => postAliasInner)
    .JoinAlias(() => postAliasInner.Tags, () => tagAliasInner)
    .Where(Restrictions.EqProperty(Projections.Property(() => postAliasInner.Id),
        Projections.Property(() => postAlias.Id)))
    .Where(Restrictions.In(Projections.Property(() => tagAliasInner.Id), ids.ToArray()))
    .Select(Projections.Count(Projections.Property(() => tagAliasInner.Id)));

var query = session.QueryOver(() => postAlias)
    .JoinAlias(() => postAlias.Tags, () => tagAlias)
    .Where(Restrictions.In(Projections.Property(() => tagAlias.Id), ids.ToArray()))
    .WithSubquery.WhereValue(ids.Count).Eq<Post>(subQuery);

var results = query.List();

This results in SQL,

SELECT this_.Id as Id3_1_,
 this_.Title as Title3_1_,
 tags3_.Post_id as Post1_,
 tagalias1_.Id as Tag2_,
 tagalias1_.Id as Id5_0_,
 tagalias1_.Text as Text5_0_ 
FROM "Post" this_
 inner join PostTag tags3_ on this_.Id=tags3_.Post_id 
 inner join "Tag" tagalias1_ on tags3_.Tag_id=tagalias1_.Id 
WHERE tagalias1_.Id in (?, ?) 
 and ? = (SELECT count(tagaliasin1_.Id) as y0_ 
            FROM "Post" this_0_
            inner join PostTag tags3_ on this_0_.Id=tags3_.Post_id 
            inner join "Tag" tagaliasin1_ on tags3_.Tag_id=tagaliasin1_.Id 
            WHERE this_0_.Id = this_.Id and tagaliasin1_.Id in (?, ?))

score:2

LINQ solution (nhibernate should be able to translate it)

var tags = new[] { 1 , 2 };

var postIds = PostTags
    .Where(pt => tags.Contains(pt.TagId))
    .GroupBy(pt => pt.PostId)
    .Where(g => g.Count() == tags.Length)
    .Select(g => g.Key);

SQL Solution:

SELECT PostId
FROM (
    SELECT COUNT(*) AS count, PostId
    FROM [PostTag]
    WHERE TagId IN (1, 2) --List of tags
    GROUP BY PostId
    ) as t1
WHERE [t1].[count] = 2 --Length of list

Explanation: We filter PostTag to only include the tags we care about. Then we group by post. If the count of the grouping equals the length of the tag list, then the post contains all tags.


Related Query

More Query from same tag