Entity Framework IN Object About Navigation Properties

Question

I have an IQueryable object that I am trying to dynamically add to Where clauses, this works fine for columns in the Listing object, but now conditionally I would like to add an IN clause to the navigation property (ListingAmenities), which has ListID, AmenityID columns

I have a critiera.AmenityID that may contain convenience. I like to filter the results.

I am trying to achieve the following if there are any amenityID in my array

select * from Listings l inner join ListingAmenities a on l.ListingID = a.ListingID where a.AmenityID IN(1,2,3) 

here is my code (I am using EF5)

 if (criteria.AmenityIDs.Count > 0) { listings = listings.Where(x => x.ListingAmenities.Any(y => y.AmenityID == criteria.AmenityIDs)); } 

which of course does not work. It is important to note that I am adding these WHERE clauses dynamically, so I am creating an IQueryable object

Answer 1

Enumerable.Contains converts to IN in SQL, so you can use:

 if (criteria.AmenityIDs.Count > 0) { listings = listings.Where(x => x.ListingAmenities .Any(y => criteria.AmenityIDs.Contains(y.AmenityID))); } 

Be careful if the collection of AmenityIDs very long, because Contains has performance issues for large collections . But 100 elements or so should not be a problem.