用于匹配所有标签的 NHibernate 查询 [英] NHibernate query for matching all tags

问题描述

Here are my relevant classes:

public class Item {
    public virtual int Id { get; protected set; }
    public virtual IList<Tag> Tags { get; set; }
}

public class Tags {
    public virtual int Id { get; protected set; }
    public virtual string Name { get; set; }
    public virtual IList<Item> Items { get; set; }
}

These are mapped with a many to many association. The intermediate table is named ItemsToTags.

Here's the question:

Given a list of strings, how do I create an NHibernate query that returns all Items that have all the Tags with Names matching all the strings in the given list?

This is the function signature:

IList<Item> GetItemsWithTags(IList<string> tagNames);

I need something like:

from Item item
where !tagsNames.Except(
    from item.Tags select item.Tags.Name
).Any()
select item

Thanks in advance for any help.

解决方案

You can definitely do this with HQL; I've successfully tested it.

var items = session.CreateQuery("SELECT i.Id FROM Item i JOIN i.Tags tags WHERE tags.Name IN(:tags) GROUP BY i HAVING COUNT(DISTINCT tags) = :tagCount")
        .SetParameterList("tags", tagNames)
        .SetInt32("tagCount", tagNames.Count)
        .List();

That will get you a list of Item IDs which you can use to get the Items. The GROUP BY and HAVING combination may be inefficient on some DBMS though. There is another query method using iterative joins that may be more efficient on certain DBMS but I can't get it to work (may not be possible at all with Criteria). If I ever do, I'll update my answer.

这篇关于用于匹配所有标签的 NHibernate 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！