NHibernate Many to Many Criteria Task Tag Example

Question

I have a simple many to many relationship set up between tasks and tags. A task may have many tags and a tag may be assigned to many tasks. I use three tables to manage this - Task, Tag, and TaskTag. So far so good.

I am trying to write a criteria query for the following example...

Find all tasks that are tagged with 'Apple' (id = 1718) AND 'Orange' (id = 1717)

I found the following example which I tried to follow.

NHibernate many-to-many criteria

// Create sample list of tag ids.
var tagIDs = new List<long>();
tagIDs.Add(1718);
tagIDs.Add(1717);

// Create criteria and detached criteria.
var criteria = Session.CreateCriteria<Task>();
var detachedCriteria = DetachedCriteria.For<Task>("t")
    .SetProjection(Projections.GroupProperty(Projections.Id()))
    .Add(Restrictions.Eq(Projections.Count(Projections.Id()), tagIDs.Count))
    .Add(Restrictions.EqProperty("t.ID", "ID"))
    .CreateCriteria("Tags")
    .Add(Restrictions.In("id", tagIDs.ToArray<long>()));
criteria.Add(Subqueries.Exists(detachedCriteria));

This is the SQL it produces...

SELECT this_.ID as ID4_0_, [MORE COLS HERE]
FROM dbo.[Task] this_
WHERE exists (
    SELECT this_0_.ID as y0_
    FROM dbo.[Task] this_0_
    inner join dbo.TaskTag tags3_ on this_0_.ID=tags3_.TaskID
    inner join dbo.[Tag] tag1_ on tags3_.TagID=tag1_.ID
    WHERE this_0_.ID = this_0_.ID
    and tag1_.ID in (2, 1718)                   <------ 1
    GROUP BY this_0_.ID
    HAVING count(this_0_.ID) = 1717             <------ 2
)

For some reason I cannot clearly understand, the parameters are getting misplaced.

1. Notice that it puts the count (2) in the "in" list incorrectly, and
2. It then puts the second tag id where the count should go.

When I modify the SQL to put the parameters in the correct locations, it works.

I must have the criteria incorrect, but I cannot see it.

I appreciate any help. Thanks!

Answer 1

Here is my solution

Entities:

public class TaskTest : Entity<TaskTest>
{
    public virtual string Name { get; set; }

    public virtual IList<TagTest> Tags { get; set; }

    public TaskTest()
    {
        Tags = new List<TagTest>();
    }
}

public class TagTest : Entity<TagTest>
{
    public virtual string Name { get; set; }
}

Do not take in the consideration Entity<>. Is is generic as we've implemented a IEquatable interface

Next Mappings:

public class TaskTestMap : EntityMap<TaskTest>
{
    public TaskTestMap()
    {
        Map(x => x.Name).Not.Nullable().Length(512);
        HasManyToMany(x => x.Tags).Table("TaskTag").Cascade.All();
    }
}

public class TagTastMap : EntityMap<TagTest>
{
    public TagTastMap()
    {
        Map(x => x.Name).Not.Nullable();
    }
}

I set cascade just to save tasgs on saving tasks.

The repository with my criteria:

public IList<TaskTest> GetTaskByTagIds(IList<long> tagIds)
    {
        DetachedCriteria exists = DetachedCriteria.For<TaskTest>("t")
            .CreateAlias("t.Tags", "tags")
            .Add(Restrictions.EqProperty("t.Id", "task.Id"))
            .Add(Restrictions.In("tags.Id", tagIds.ToArray()))
            .SetProjection(Projections.GroupProperty("t.Id"))
            .Add(Restrictions.Eq(Projections.Count("t.Id"), tagIds.Count));

        ICriteria criteria = GetSession().CreateCriteria<TaskTest>("task")
            .Add(Subqueries.Exists(exists));

        return criteria.List<TaskTest>();
    }

And the unit test which pass on my side:

    [Test]
    public void TestTaskTest()
    {
        //Insert data;

        var task1 = new TaskTest {Name = "task1"};
        var task2 = new TaskTest {Name = "task2"};
        var task3 = new TaskTest {Name = "task3"};

        var tag1 = new TagTest {Name = "tag1"};
        var tag2 = new TagTest {Name = "tag1"};
        var tag3 = new TagTest {Name = "tag1"};

        task1.Tags.Add(tag1);
        task1.Tags.Add(tag2);

        task2.Tags.Add(tag1);
        task2.Tags.Add(tag3);

        task3.Tags.Add(tag3);


        _repository.AddToSession(task1);
        _repository.AddToSession(task2);
        _repository.AddToSession(task3);

        FlushAndClearSession();

        //We will try to get all task which a taged with tag1 and tag2. The result should be task1 

        var tagsId = new List<long> {tag1.Id, tag2.Id};

        var result = _repository.GetTaskByTagIds(tagsId);

        Assert.That(result, Is.Not.Null);
        Assert.That(result, Is.Not.Empty);
        Assert.That(result.Count, Is.EqualTo(1));
        Assert.That(result[0].Name, Is.EqualTo("task1"));
    }

and the sql statement is:

    exec sp_executesql N'SELECT this_.Id as Id50_0_, this_.Version as Version50_0_, this_.Name as Name50_0_ 
    FROM [TaskTests] this_ 
    WHERE exists (
                    SELECT this_0_.Id as y0_ 
                    FROM [TaskTests] this_0_ 
                    inner join TaskTag tags3_ on this_0_.Id=tags3_.TaskTestId 
                    inner join [TagTests] tags1_ on tags3_.TagTestId=tags1_.Id 
                    WHERE this_0_.Id = this_.Id and tags1_.Id in (@p0, @p1) 
                    GROUP BY this_0_.Id 
                    HAVING count(this_0_.Id) = @p2)',
N'@p0 bigint,@p1 bigint,@p2 int',
@p0=9000,@p1=9001,@p2=2

Regards, /Ion