LINQ to NHibernate: selecting entity which has a specific entity in a one to many association

Question

I would like to make this query:

Session.Linq<User>().Where(u => u.Payments.Count(p => p.Date != null) > 0);

In plain English I want to get all the users that has at least one payment with the date specified.

When I run the sample code I get a System.ArgumentException with the message:

System.ArgumentException : Could not find a matching criteria info provider to: this.Id = sub.Id

Do you know a solution to this problem?

It would also be very helpful if someone could provide the same query with the NHibernate Query by Criteria API.

Answer 1

I think something like it:

Customer customerAlias = null; 
criteria = CurrentSession.CreateCriteria(typeof(User), () => customerAlias);
if (searchCriteria.OrdersNumber.HasValue)
{
     ICriteria paymentsCriteria = criteria.CreateCriteria<Customer>(x => x.Payments);

     DetachedCriteria paymentsCount = DetachedCriteria.For<Payment>();
     paymentsCount.SetProjection(Projections.RowCount());
     paymentsCount.Add(SqlExpression.NotNull<Payment>(x => x.Date));
     paymentsCount.Add<Payment>(x => x.Customer.Id == customerAlias.Id);

     paymentsCriteria.Add(Subqueries.Gt(1, paymentsCount));
 }

 return criteria.List<User>();

Answer 2

I'm not sure if this will work in your particular case, but I would use the .Any() extension to clean up the linq query a bit; for example:

Session.Linq<User>().Where(u => u.Payments.Any(p => p.Date != null));