I have entity A which has an IList of B called Bs and B has an IList of C called Cs.
I want to search for all A's which have at least 5 C's in them. So I went and wrote
using (var s = this._sessionFactory.OpenSession())
{
IQueryable<A> q = s.Linq<A>();
// some code...
if (range.Min.HasValue)
q = q.Where(a => a.Bs.Sum(b => b.Cs.Count) >= range.Min.Value);
// some code...
return q.Select(b=>b).ToArray();
}
However upon executing the code (and having Min specified in the range variable) I get the following exception :
NHibernate.QueryException : could not resolve property: Cs of: A
Why does it look for the B's property on A? The mappings seem to be right though :
The (Fluent) mapping on A says :
//...
HasMany(a => a.Bs)
.Table("Bs")
.KeyColumn("IdA")
.Cascade.AllDeleteOrphan()
.Inverse()
.Not.LazyLoad();
//...
and on the mapping on B says :
//...
HasMany(b => b.Cs)
.Table("Cs")
.KeyColumn("IdB")
.Cascade.AllDeleteOrphan()
.Inverse()
.Not.LazyLoad();
References(b => b.A, "IdA")
.Not.LazyLoad();
//...
finally on the mapping on C :
References(c => c.B, "IdB").Not.LazyLoad();
LINQ to NHibernate is great for making simple queries easy to do. However, when you need to do complex queries (such as this one) it is often better to switch to the criteria API or HQL. You have 3 querying methods at your disposal, use them all.
That being said once NHibernate 3.0 is released it may be possible to do this query using LINQ.
You can't do it with LINQ to NHibernate 2.x