NHibernate QueryOver value collection

Question

I have a project using NH 3.1 and have been using the QueryOver syntax for everything thus far.

One aspect of this project lives in a organization-wide database that I have read-only access to and is using a completely differently DBMS (Oracle vs MSSQL). So I store references from my objects (Foos) to their objects (Bars) using a standard many-to-many table

FooBars
FooID int not null PK
BarID int not null PK

And my domain object, instead of having a Iset<Bar> instead has an ISet<int> BarIDs which is manually mapped to the FooBars table. This prevents NH from trying to do the impossible and join all the way over to the Bars table (I can use a BarRepository.Get() to retrieve the details of the Bars later, if I need them, and in this case, I wouldn't, because I just need the IDs to filter the list of objects returned).

Given IList<int> SelectedBars how can I write a QueryOver<Foo> where BarIDs contains any element in SelectedBars?

SQL something like

...FROM foos INNER JOIN foobars on foo.fooID = foobars.fooID WHERE barID IN ( ... )

Answer 1

It is not possible with QueryOver. Two years ago, I had a similar question about filtering value collections. (Note: QueryOver is based on Criteria API).

I'm not 100% sure, but it probably works with HQL. It is much more powerful.

You may include an SQL statement into the QueryOver criteria.

I don't really understand why you don't map it as a list of entities. There is lazy loading to avoid unnecessary loading - although there are some trade offs sometimes. You can access the ID of NH proxies without hitting the database. Mapping ids makes usually life much harder.

Answer 2

Try:

session.QueryOver<Foo>()
       .JoinQueryOver(x => x.FooBars)
       .WhereRestrictionOn(x => x.BarId).IsIn( ... )

Answer 3

So 3 years later, I'm back to report how I did solve this.

public class Foo :Entity {
    public virtual ISet<FooBar> BarIDs { get; protected internal set; }
 } ...

public class FooBar :Entity {
    protected internal FooBar() { }
    protected internal FooBar(Foo f, int BarID) { ... }
    public virtual Foo Foo { get; protected internal set; }
    public virtual int BarID { get; protected internal set; }
}

This is basically what Stefan suggested, and what's hinted at in the related post. You just have to eat the overhead of writing an extra entity and referencing it. Remember that I'm storing BarIDs instead of full Bar objects, because I'm dealing with a hard boundary between two databases: the Bars are stored in an entirely different database on a different platform than the Foos. Otherwise of course, you're far better off just telling Foo that is has an ISet<Bar>.

Finding Foos by SelectedBarIDs is then easy, much like Thilak suggested:

session.QueryOver<Foo>().JoinQueryOver<FooBar>(f => f.BarIDs).
    WhereRestrictionOn(b => b.BarID).IsIn(...)...

It's an interesting problem, working across the database boundary like this. I can't say I like having to do it, but if someone else is going to take the time to maintain a list of Bars and make it available for my use, it would be a giant waste of resources for me to do the same. So a small inefficiency in having the wrapper class is a very easy cost to justify.