CODEWITHSUNDEEP
nhibernatequeryover
Vadim Rybak
Vadim Rybak

Reputation: 1697

Nhibernate Where Exists queryOver

I have the following objects

public class Document
{
    public Guid Id { get; set; }
    public ISet<Tag> Tags { get; set;}
    ...
}

public class Tag 
{
    public Guid Id { get; set;} 
}

This is a many to many relationship but I do not have an intermediate DocumentTag object. I am trying to use QueryOver syntax to return all documents that have all of a set of tags. Here is what i have so far.

Guid[] tagsThatMustMatch = ...;
var result = Session.QueryOver<Document>()
               .WithSubquery
               .WhereExists(QueryOver.Of<Tag>().Where(t => tagsThatMustMatch.Contains(t.Id))
               .List()

I am having trouble expressing the requirement. I understand that I have to dynamically construct a query which will have a WhereExists for each tag that must match. These WhereExists need to be anded. The other problem that I am having is that the WhereExists method needs to take in a DetachedQuery. But I have no way to constraining it to only look at the tags that this document has not all the tags.

Are there any examples out there where I can see how I can do something like this?

Upvotes: 2

Views: 6443

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123901

Using IN - no need ot adjust Entity(ies) definition

Let's firstly declare your subquery this way:

Guid[] tagsThatMustMatch = ...;

var subQuery = QueryOver.Of<Tag>()
 .WhereRestrictionOn(t => t.Id).IsIn(tagsThatMustMatch)
  .Select(t => t.Id); // tag ID projection

Having detached criteria, we can use them in a QueryOver syntax like this:

var query = session.QueryOver<Document>()
  .JoinQueryOver<Tag>(d => d.Tags)
  .Where(Subqueries.PropertyIn("Id", subQuery.DetachedCriteria)) // Tag.Id
  // .TransformUsing(Transformers.DistinctRootEntity) // distinct
  ;

EXTEND:

Using EXISTS - will require adjustment in our Entity Domain Model

As Diego Jancic pointed out in his comment, EXISTS could be more effective... but only if there is reference from Tag to Document

It could be either the reference

public class Tag 
{
    public Guid Id { get; set;} 
    public Document Document { get; set; }
}

or at least the Guid representing the Doucment id

public class Tag 
{
    public Guid Id { get; set;} 
    public Guid DocumentId { get; set; }
}

Let's assume the first, then we can use subquery like this

Guid[] tagsThatMustMatch = ...;

// Essential alias of the parent Document
Document document = null;

var subQuery = QueryOver.Of<Tag>()
 .Where(t => t.Document.Id == document.Id) // or t.DocumentId = document.Id
 .WhereRestrictionOn(t => t.Id).IsIn(tagsThatMustMatch)
 .Select(t => t.Id); // tag ID projection

This is the only way, how the subquery would properly return true only on tags which relate to document. The exist query would than look like this

var query = session
    .QueryOver<Document>(() => document) // alias used in subquery
    .WithSubquery                        // needed to filter by parent id
        .WhereExists(subQuery)
    ...

Upvotes: 7

Related Questions