Many-To-Many LINQ "IN" Query

Question

Edit: Both the answers below work. My problem was due to using the NHibernate LINQ provider like this:

from parks in Session.Linq<Park>()

instead of like this:

from parks in Session.Linq<Park().AsEnumerable()

---

I have a class called Park which has an of Amenities. I want to create a LINQ query that returns all Park objects which contain every Amenity in a list. So given:

List<Park> Parks(IList<Amenity> amenities)
{
   // I want a query that would look like this (if this worked)
   // return all Park objects that have all of the given amenities

  var query = from parks in db.Parks
              where parks.Amenities.Contains(amenities)
              select parks;
}

This query:

var query = from parks in Session.Linq<Park>()
    where amenities.All(a => parks.Amenities.Contains(a))
    select parks;

doesn't work.

Here's more of my code for context:

Mapping classes(I"m using Fluent NHibernate)

public ParkDBMap()
{
 Id(x => x.ParkId).Column("ParkId").GeneratedBy.HiLo("0").UnsavedValue(0);
 Map(x => x.Name, "Name");
 this.HasManyToMany<Amenity>(x => x.Amenities)
 .Table("ParksMaps_ParkAmenities")
.Cascade.SaveUpdate();
}


public AmenityDBMap()
{
 Id(x => x.AmenityId).Column("AmenityId").GeneratedBy.HiLo("0").UnsavedValue(0);
 Map(x => x.Name, "Name");
}

Test method:

public void ListParksByAmenity()
{
// Create Parks
int parkCount = 10;
CreateParks(parkCount);

// Create Amenities
Amenity restrooms = new Amenity();
restrooms.Name = "Restrooms";
ParksRepos.SaveAmenity(restrooms);

Amenity tennis = new Amenity();
tennis.Name = "Tennis Courts";
ParksRepos.SaveAmenity(tennis);

Amenity dogs = new Amenity();
dogs.Name = "Dogs Allowed";
ParksRepos.SaveAmenity(dogs);

// Add amenities to parks
IList<Park> parks = ParksRepos.Parks();

parks[0].AddAmenity(dogs);
parks[0].AddAmenity(tennis);
parks[0].AddAmenity(restrooms);
ParksRepos.SavePark(parks[0]);

parks[4].AddAmenity(tennis);
parks[4].AddAmenity(restrooms);
ParksRepos.SavePark(parks[4]);

parks[9].AddAmenity(restrooms);
ParksRepos.SavePark(parks[4]);

IList<Amenity> amenityList = new List<Amenity>() { restrooms};
List<Park> restroomsParks = ParksRepos.Parks(amenityList);

// three parks have restrooms
Assert.AreEqual(1, restroomsParks.Count);
Assert.AreEqual(parks[0].Name, restroomsParks[0].Name);

amenityList = new List<Amenity>() { dogs, tennis, restrooms };
List<Park> allAmenities = ParksRepos.Parks(amenityList);

// only one park has all three amenities
Assert.AreEqual(3, allAmenities.Count);

}

I have three tables. A "Parks" table, an "Amenities" table, and a many-to-many table that has two columns, a park id and an amenity id.

I'm having trouble wrapping my head around this. Anyone have any suggestions?

Answer 1

List<Park> Parks(IList<Amenity> amenities)
{   
  var query = from parks in db.Parks
              where amenities.All(a => parks.Amenities.Where(sa => sa.ID == a.ID).Count() == 1)
              select parks;
}

Answer 2

This will work .It will return an IEnumerable . To convert to List you will need to do a ToList() Note I Assume that Park.Amenity is a List

 var x = from Park p in db.Parks where amenities.Except(p.Amenity).Count() == 0 select p;