NHibernate 3.1 Linq with Contains and Any

[scjones]

Hi,

We are in the process of upgrading to NH3.1 which is going well -
everything is working as far as we can tell with existing code. One of
the motivations to move to NH3 from 2 has been to leverage the Linq
support and generally it is working really well. However I am
struggling with some more complex where clauses, specifically when I
want to check based on a sub-collection:

var results = from r in registrations
where (
from p in persons
where
p.ExplicitManagers.Any(m=>m.Manager==manager)
select p
).Contains(r.Registrant)
select r;

where the model is

p is a Person and a registration r has a registrant of type Person
p contains a collection of ExplicitManager associative entities which
hold a reference to another Person (manager).

note: registrations is an IQueryable<Registration>.Query() and persons
in an IQueryable<Person>.Query().

Essentially I am trying to restrict the registrations to where person1
is an explicit manager of p. I can do this via joins but not through
the Contains subquery. I get the following error:

"System.InvalidOperationException : Sequence contains more than one
matching element"

the reason for doing this as a sub-query is because ultimately I need
to externalise the logic for checking the managers to make it resuable
(it actually is more complex but I have simplified it for this example
because it is the Any within a Contains which is causing the grief).
Contains seems to work fine when not having a sub-query with Any.

Is this something I am doing wrong, or is it something unsupported or
a bug, and is there another way of achieving the same thing?

Many thanks for any help you can give.

[scjones]

I have found .Any works in place of .Contains and therefore this is
not such as pressing issue for me right now, e.g.

var results = from r in registrations
where

(from p in persons where

p.ExplicitManagers.Any(m=>m.Manager==manager) select p ).Any(p=>p ==
r.Registrant)
select r;

[scjones]

Actually I have found another related issue - this now works:

var results = from p in persons
where (
from r in persons
where
r.Supervisor == person1
select r
).Any(m => m == p)
select p.UserAccount.Name;


But if I move the inner part of the subquery to another method (to
make it reusable), it returns too many records (as in all persons and
not just the ones which meet the Any criteria).

Is this a bug?

[sbohlen]

No, its not a bog.

If your LINQ construct contains anything that cannot be interpreted by the database (e.g., a call to a .NET method of your own) then LINQ achieves the desired result by doing as much of the query as it can in the DB and then the rest of the work is applied to an in memory collection.  When the full query is in-lined, it can *all* be sent to the DB for execution but when you extract part of it into an arbitrary method the DB can no longer do all the work for you and some of the work needs to be applied after the DB returns the part it can understand.  This is standard behavior for all LINQ providers AFAIK.

HTH,
-Steve B.

[scjones]

Actually I think you are not entirely correct - if the method returns
an IQueryable it does this at the DB level:

//extract:
var query =
IoC.Resolve<IRepository<Registration>>().Query();

var results = from reg in query
where
staff(person1).Any(x=>x == reg.Registrant)
select reg;



foreach (var r in results)
{
Console.WriteLine(r.Registrant.UserAccount.Name);
}

}

private IQueryable<Person> staff(Person manager)
{
return
IoC.Resolve<IRepository<Person>>().Query().Where(p=>p.Supervisor==manager);
}


Even though staff is a method call, the sub-query does indeed do the
querying at the database level and not in code:

select registrati0_.RegistrationId as Registra1_40_,
registrati0_.PersonId as PersonId40_, registrati0_.ActivityId as
ActivityId40_,
registrati0_.CurrentResultId as CurrentR5_40_,
registrati0_.Status as Status40_, registrati0_.IsApproved as
IsApproved40_,
registrati0_.RegistrationType as Registra8_40_,
registrati0_.ApprovalDecidedOn as Approval9_40_,
registrati0_.CancelledOn as Cancell10_40_,
registrati0_.RegisteredOn as Registe11_40_, registrati0_.Note as
Note40_, registrati0_.CostAmount as CostAmount40_,
registrati0_.CancelledBy as Cancell14_40_,
registrati0_.CreatedBy as CreatedBy40_, registrati0_.CurrencyId as
CurrencyId40_,
registrati0_.ModifiedBy as ModifiedBy40_, registrati0_.CreatedOn
as CreatedOn40_, registrati0_.ModifiedOn as ModifiedOn40_,
registrati0_.BookingId as BookingId40_,
registrati0_.ActivityFlags as Activit21_40_,
registrati0_.REGISTRATION_CLASS as REGISTRA2_40_

from Registration registrati0_
where exists (
select person1_.PersonId from Person person1_
where person1_.SupervisorId=@p0 and
person1_.PersonId=registrati0_.PersonId);

@p0 = c085cd44-c49d-4ee5-b695-9eef00881c6d [Type: Guid (0)]


The bug - in my opinion (in terms of normal programming practice) is
because if you name the linq variables the same in the subquery method
as those in the calling query, you get a naming clash (e.g. if I used
x=>x instead of p=>p in the staff method).