Hibernate version: 2.0GA
How can I construct the following query using NH?
Get me all reports (type of link) that have all of the these tags: (expressed as all Links where there's not a Tag in the given list that is not a LinkTag for the Link).
Code:
select l.Id, l.Text
from Link l
where _discriminator = 'ReportLink'
and not exists
(select * from Tag t
where not exists
(select * from LinkTag lt
where lt.LinkFK = l.Id
and lt.TagFK = t.Id)
and t.Name in ('tag1', 'tag3'))
It's the classic all customers that have ordered all of these products, but in NH the LinkTag table isn't a first-class citizen, being there (at present) only to facilitate the many-to-many.
I'd prefer to use NHibernate.Linq before the Criteria API, before HQL, before SQL.
The 'obvious' Linq query did nothing, which is fine as I know it's experimental:
Code:
public IEnumerable<Report> ReportsHavingAllOfTags(IEnumerable<string> tags)
{
return Query
.Where(report => report.Tags.All(tag => tags.Contains(tag.Name)))
.OrderBy(report => report.Text);
}
Is the answer to change the many-to-many into one-to-many from each end and make the resolving table an entity in it's own right and use Subqueries.NotExists?
Does anyone know a clever solution?
Persistent types and mapping attributes:Link class (abbreviated for clarity):
Code:
[Class(Table = "Link", DiscriminatorValueObject = LinkDiscriminators.Link)]
[Discriminator(TypeType = typeof(string), Column = "_discriminator", Length = 50)]
public abstract partial class Link
{
protected ISet<Tag> tags = new HashedSet<Tag>();
[Id(1, Name = "Id", TypeType = typeof(int), UnsavedValueObject = 0)]
[Generator(2, Class = "native")]
public virtual int Id { get; set; }
/// <summary>
/// The text describing the link
/// </summary>
[Property(Column = "[Text]", Length = Constraints.DescriptionLength)]
public virtual string Text { get; set; }
/// <summary>
/// A set of tags on this link.
/// </summary>
[Set(10, Table = "LinkTag", Inverse = false, Lazy = true, Access = "nosetter.camelcase", Cascade = CascadeStyle.All)]
[Key(20, Column = "LinkFK", ForeignKey = "FK_LinkTag_Link")]
[ManyToMany(30, ClassType = typeof(Tag), ForeignKey = "FK_LinkTag_Tag"), Column(31, Name = "TagFK", NotNull = true)]
public virtual IEnumerable<Tag> Tags
{
get { return tags; }
}
}
Report class (abbreviated for clarity):
Code:
[Subclass(ExtendsType = typeof(Link), DiscriminatorValueObject = LinkDiscriminators.ReportLink)]
public partial class Report : Link
{
}
Tag class:
Code:
[Class(Table = "Tag")]
public class Tag
{
[Id(1, Name = "Id", TypeType = typeof(int), UnsavedValueObject = 0)]
[Generator(2, Class = "native")]
public virtual int Id { get; set; }
[Property(Length = Constraints.NameLength)]
public virtual string Name { get; set; }
}
Login
FAQ
Search