NHibernate Linq : Contains Statement

Question

As an example, let's say you have a class like such

**Person**
int PersonID
string PersonName
BusinessLocation Locations

**BusinessLocation**
string city
string state
List<int> ZipCodes

(saying that the locations may exist in multiple zipcodes)
(also ignoring that zipcodes should be strings instead of ints, this is just an example)

Say that the locations of the Businesses exist in multiple zipcodes.

Now I am trying to pull back all the people in the person table, given a business zipcode.

For example, I want all the people who have a zipcode of 32567.

(Given a list of IDs, this works, I am trying to do the opposite, given one ID, I want a list of people)

public Person GetPersonsByBusinessZipCode(int zipcode)
{
    List<Person> personList = this.GetAllQueryable().Where(x => x.Locations.ZipCodes.Contains(zipcode)).ToList();
}

This is Mapped Like such in Fluent.

HasMany<int>(x => x.ZipCodes)
   .Table("BusinessLocationsZipCodes")
   .KeyColumns.Add("BusinessLocationID")
   .Inverse()
   .Element("ZipCode")
   .AsBag()
   .Cascade.None()
   .Cache.ReadOnly();

BusinessLocationZipCodes is just a reference table alluding that a BusinessLocation can have multiple ZipCodes, hence the HasMany.

Knowing that the reverse works, if I am given a list of ZipCodes and I am trying to find BusinessLocations contained in the list of zipcodes works (as long as the mapping is to a zipcode and not a List of zipcodes). Now I'm just trying to find the BusinessLocations given a zipcode.

If anyone has an answer, I would appreciate it.

Answer 1

Linq provider shouldn't matter much as long as you're using System.Linq.Queryable and the IQueryable interface.

I believe you are looking for the Queryable.Any method.

Persons.Where(p => p.Locations.ZipCodes.Any(z => z == zipCode))

Or, if your Persons have many Locations:

IQueryable<Person> query =
  from p in Persons
  where
  (
    from l in p.Locations
    from z in l.ZipCodes
    select z
  ).Any(z => z == zipCode)
  select p;

Answer 2

What's the exception? That should work correctly using the latest NHibernate build (2.1.2.400).

// The following query: (never mind the Spanish)

var transporte = // some entity;
var solicitud  = IQueryable<Solicitud>
                   .Where(x => x.SolicitudesDeTransporte.Contains(transporte)).ToList();

Generates:

SELECT this_.Id as Id6_0_,
       /* etc... */
FROM   Solicitud this_
WHERE  this_.Id in (SELECT this_0_.Id as y0_
                    FROM   Solicitud this_0_
                           left outer join Solicitud_Transporte solicitude1_
                             on this_0_.Id = solicitude1_.Id_Solicitud
                    WHERE  solicitude1_.Id = 1 /* :p0 */)

Answer 3

Paco, apparently I screwed up because I had two accounts floating out here that I did not know about, so this is my OpenID one. Anyhow I am not sure of the NullReferenceException, it dives down into NHibernate namespaces with no link back to any of the classes I am working with. But I only get the NullReferenceException when I try to dive into it using Contains. As I state above, I do not get the NullReferenceException if I am just bring back the list of ZipCodes. So my mapping works, but it can not seem to return me a List of BusinessLocations based on the Contains statement.