LINQ Many for many with In or Contause (and a twist)

Question

I have many, many tabular structures called PropertyPets. It contains a double primary key consisting of a PropertyID (from the property table) and one or more PetID (from the pet table).

Next, I have a search screen in which people can select multiple pets from the jquery multiple select drop-down list. Let's say someone chooses dogs and cats.

Now I want to be able to return all properties containing the BOTH of dogs and cats in many PropertyPets tables. I am trying to do this from Linq to Sql.

I looked at the Contains suggestion, 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 array of int [] identifiers for the dog and cat (which was selected from the drop-down list of several items). First problem: Contains requires the string to not be an IEnumerable of PropertyPet type. And secondly, I need to find properties that have BOTH dogs and cats, and not just just one or the other.

Thanks for any pointers.

Answer 1

You can do this using a nested where clause.

You need to filter p.PropertyPetswith contains- return all rows where it PetIDis in search.PetType.

Then we return only the rows from propertieswhere the entire search identifier is found - for examplenumber of rows >= number of serach id's

Together:

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

Answer 2

, Contains string, true, Contains int, search.PetType int[]. , "" p.PropertyPets int. p.PropertyPets IEnumerable<int>, PropertyID: p.PropertyPets.Select(propertyPet => propertyPet.PropertyID), int, , . (.First() int, .

,

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

Except LINQ2SQL.

, , - Contains search.PetType.

- :

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;
}