QueryOver collection contains all values

Question

Simplified domain:

public class MasterDocument {
    Guid ID;
    Program StorageCompartment;
    ISet<DocumentCompartment> Compartments;
}
public class Program {
    int ID;
    string GroupName;
}
public class DocumentCompartment {
    int ID;
    Program AssociatedCompartment;
    MasterDocument AssociatedDocument;
}
public class Document {
    Guid ID;
    MasterDocument MasterDocument;
    //Many more properties
}

I know this is somewhat convoluted, but the schema exists like this because of security concerns that we have dealt with by putting certain records (like Documents) into different DB's that correspond to the Program/Compartment (interchangeable terms) that they belong to. The tables for MasterDocument, Program, and DocumentCompartment are in a 'master' database, which contains information across all compartments, while several different databases will each contain their own Documents table. Anyways, on to the problem:

I'm trying to structure a query whereby I am passed a list of group names, and I want only the documents that have no associated compartment that is NOT contained in that list of group names.

As an example:
Doc1 is associated with compartment P1
Doc2 is associated with P2, P3, and P7
Doc3 is associated with P1 and P3
I want to check against groups: P1, P3, P4, P7 (these are the groups I have permission for)

I should get back Doc1 and Doc3, because I don't have permissions for P2, and Doc2 requires that. I was able to do this using the LINQ provider using the following query:

string[] groups = new[] { "P1", "P3", "P4", "P7" };
return Session.Query<Document>().Where(doc => doc.MasterDocument.Compartments.All(comp => groups.Contains(comp.AssociatedCompartment.GroupName));

(Also a note on the above: If I try to encapsulate that logic in the Document class and pass that method to the 'Where' method, e.g. return Session.Query().Where(doc => doc.CanAccess(groups)), then I get a System.NotSupportedException. I sort of understand why, but it would be great if there's a workaround for this.)

The resulting generating SQL looks like this:

exec sp_executesql 
N'select
doc.DocumentGuid as guid
from Documents doc 
where  not (exists 
(select comp.DocumentCompartmentID 
 from Master.MasterDocuments master, 
      Master.DocumentCompartments comp, 
      Master.Programs prog 
 where doc.DocumentGuid=master.DocumentGuid and 
       master.DocumentGuid=comp.DocumentGuid and 
       comp.CompartmentID=prog.ProgramID and  
       not (prog.ADGroupName in ('P1', 'P3', 'P4', 'P7'))
 ))',

I'm now trying to figure out how to do the same query using the NHibernate QueryOver syntax. Unfortunately, I don't have enough knowledge or experience with this to know how to write it. Any help with this would be greatly appreciated!

Andrew Whitaker Andrew Whitaker · Accepted Answer · 2014-01-14T15:36:38

I believe you want something like this:

IList<Guid> results = Session.QueryOver<Document>(() => documentAlias)
    .WithSubquery.WhereNotExists(
        QueryOver.Of<MasterDocument>()
                .Where(md => md.ID == documentAlias.MasterDocument.ID) // Not sure about this
            .JoinQueryOver(md => md.Compartments)
            .JoinQueryOver(cmp => cmp.AssociatedCompartment)
                .WhereNot(acmp => acmp.GroupName.IsIn(new[] { "P1", "P3", "P4", "P7" })))
    .Select(doc => doc.ID)
    .List<Guid>();

I'm not sure if the comparison between MasterDocument.ID and Document.MasterDocument.ID is correct where I've commented, but this should get you started in the right direction.

A few notes:

In general, you can't use LINQ syntax with QueryOver. They look similar but they are different querying technologies.
For a very basic introduction to QueryOver, check out this guide on nhibernate.info. Unfortunately this is about as good as you can get in terms of official documentation
This assumes a few things about your mappings based on the classes you presented.

QueryOver collection contains all values

1 Answers