How to compare two lists for likeness in a Queryover

Question

I have a data structure that looks something like this:

public class Foo{
   public Bar Property;
}
public class Bar{
   public List<Baz> BazItems;
}
public class Baz{
   public long EntityKey;
}

In my code, I have a List<long> ValidKeys of entity keys that should correspond with Foo.Bar.bazItems. I'm trying to write a QueryOver statement to compare all Foo.Bar.BazItems against my list of valid keys. I want only the Foo items who have an exact match of .Bar.bazItems to the list of ValidKeys (no more no fewer).

I'm not sure how to accomplish this. I need something like:

fooRepo.QueryOver<Foo>()
    .Where(f => f.Bar.BazItems.compareAgainst(ValidKeys))

At this point I'm not sure where to go. I need to then iterate over each BazItems.EntityKeys to compare each one against my ValidKeys but nothing I have found on QueryOver statements allows this. If this is possible, any help would be appreciated.

http://stackoverflow.com/questions/12795882/quickest-way-to-compare-two-list — MethodMan, Sep 11 '15 at 20:20
This looks promising http://stackoverflow.com/questions/5408781/nhibernate-using-queryover-with-where-in — wooters, Sep 11 '15 at 21:43

score 0 · Answer 1 · answered Sep 14 '15 at 06:28

Assuming there are no duplicates in Bart.Bazlist, you can count the matches and filter by that.

List<long> validKeys = ...;
Bar barAlias = null

// get the count of matching Attributes
var subquery = QueryOver.Of<Bar>()
    .Where(p = > p.Id == barAlias.Id)
    .JoinQueryOver<Baz>(p => p.BazList)
        .WhereRestrictionOn(baz => baz.Id).In(validKeys))
    .Select(Projections.RowCount());

var matchesQuery = QueryOver.Of(() => barAlias)
    .WithSubquery.WhereValue(validKeys.Count).Eq(subquery)
    ..Select(Projections.Id());

var results = sessioni.QueryOver<Foo>()
    .WithSubquery.WhereProperty(foo => foo.Bar.Id).In(matchesQuery)
    .List();

Note: it might be shortend by one subquery

How to compare two lists for likeness in a Queryover

1 Answers1