LINQ Many to Many With In or Contains Clause (and a twist)

I have a many to many table structure called PropertyPets. It contains a dual primary key consisting of a PropertyID (from a Property table) and one or more PetIDs (from a Pet table).

Next I have a search screen where people can multiple select pets from a jquery multiple select dropdown. Let's say s开发者_StackOverflow社区omebody selects Dogs and Cats.

Now, I want to be able to return all properties that contain BOTH dogs and cats in the many to many table, PropertyPets. I'm trying to do this with Linq to Sql.

I've looked at the Contains clause, but it doesn't seem to work for my requirement:

var result = properties.Where(p => search.PetType.Contains(p.PropertyPets));

Here, search.PetType is an int[] array of the Id's for Dog and Cat (which were selected in the multiple select drop down). The problem is first, Contains requires a string not an IEnumerable of type PropertyPet. And second, I need to find the properties that have BOTH dogs and cats and not just simply containing one or the other.

Thank you for any pointers.

You can do this using a nested where clause.

You need to filter p.PropertyPets using contains - return all rows where PetID is in search.PetType.

Then only return rows from properties where all search id's have been found - eg number of rows >= number of serach id's

All together:

var result = from p in properties
             where p.PropertyPets.Where(c => search.PetType.Contains(c.PetID)).Count() >= search.PetType.Count()
             select p;

For the part where Contains requires a string would not be true, Contains should require an int if your search.PetType is int[]. That means that you need to "convert" p.PropertyPets into an int. To convert p.PropertyPets to IEnumerable<int> you need to select the PropertyID field: p.PropertyPets.Select(propertyPet => propertyPet.PropertyID), but that won't get you a single int as required but a whole bunch. (.First() would give you one int but not solve your problem.

What you really want to do is

var result = properties.Where(p =>
    search.PetType.Except(p.PropertyPets.Select(propertyPet => 
                            propertyPet.PropertyID)).Count() == 0);

But Except is not available in LINQ2SQL.

The best option I can find is to apply Contains for each item in search.PetType.

Something like this:

var result = properties;
foreach(var petType in search.PetType)
{
    result = from p in result
             where p.PropertyPets.Select(propertyPet =>
                   propertyPet.PropertyID).Contains(petType)
             select p;
}