why doesnt' nhibernate support this "exists in list" syntax?

i have the following query and its failing in Nhibernate 3 LINQ witha a "Non supp开发者_如何学Corted" exception. Its similar to this question but this question was asked over a year ago so i am positive that the answer is out of date.

My DB tables are:

1. VacationRequest (id, personId)
2. VacationRequestDate (id, vacationRequestId)
3. Person (id, FirstName, LastName)

My Entities are:

1. VacationRequest (Person, IList)
2. VacationRequestDate (VacationRequest, Date)

Here is the query that is getting a "Non supported" Exception

 Session.Query<VacationRequestDate>()
   .Where(r => people
     .Contains(r.VacationRequest.Person, new PersonComparer()))
   .Fetch(r=>r.VacationRequest)
   .ToList();

is there a better way to write this that would be supported in Nhibernate?

fyi . .the PersonComparer just compared person.Id

nhibernate cannot translate your new PersonComparer() you should change it.

NHibernate does not understand the PersonComparer type, and thus cannot translate its usage to SQL.

Have you tried using the overload of Contains which does not accept a comparer? NHibernate should infer that you are comparing entity instances and properly use the ID in the SQL comparison (what else could it use?):

Session.Query<VacationRequestDate>()
    .Where(r => people.Contains(r.VacationRequest.Person))
    .Fetch(r => r.VacationRequest)
    .ToList();

Also, keep in mind that within a single NHibernate session, the same entity instance is always returned for the same ID. This means that person1 == person1 should always be true if both Person instances were retrieved from the same session. This carries through relationships as well, so vacationRequest1.Person == vacationRequest2.Person will also be true within the same session if both vacation requests have the same person ID.

This means you can probably get rid of PersonComparer entirely unless you are mixing entities from multiple sessions through caching.

For starters it cant execute the code against that in your db.

Well here are my suggestions on how to go about this

var peopleIds= people.Select(x=>x.Id);
Session.Query<VacationRequestDate>()
    .Where(r => peopleIds.Contains(r.VacationRequest.PeopleId)).ToList()
    .Where(x=>people.Contains(r.VacationRequest,new         
     PersonComparer()).Select(r=>r.VacationRequest).ToList()

If you think this isnt wha tyou want to do then I would suggest you try your hand at Criteria queries or HQL and you can get exactly what you want from the db.

You can also use your comparer as follows. I like to make mine generic. You can make it string comparison too and you will add your logic of ignore case etc here.

public class ComparerByIntId<T> : IEqualityComparer<T>
    where T : class, IIntegerIdentifiable
{
    public bool Equals(T x, T y)
    {
        if (x == null && y == null)
            return true;

        if (x == null || y == null)
            return false;

        return x.Id == y.Id;
    }

    public int GetHashCode(T obj)
    {
        return base.GetHashCode();
    }
}

Now when you run your query it will be as follows:

Session.Query<VacationRequestDate>()
   .Where(r => people
   .Contains(r.VacationRequest.Person, new ComparerByIntId<Person>()))
   .Select(r=>r.VacationRequest)
   .ToList();

Hope that helps