CODEWITHSUNDEEP
c#linqlinq-to-sql
Chris
Chris

Reputation: 33

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 somebody 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.

Upvotes: 3

Views: 1669

Answers (2)

Albin Sunnanbo
Albin Sunnanbo

Reputation: 47058

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

Upvotes: 0

Geoff Appleford
Geoff Appleford

Reputation: 18832

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;

Upvotes: 5

Related Questions