Help with linq query. many to many

Question

I have a collection(people) that has a many-to-many reference to another collection(dogs). Suspend your disbelief on how there can be more than one people per dog. People just contains member which is an List<Dog>

I would like to select all the people where the people have a certain property(specified in an IList) and pets have a certain property(specified in an IList).

E.g. I have an IList (used for this query only) with the dog’s property value.

public enum EnumLikesToBite
{
No,
Yes,
Sometimes
}

IList <<EnumLikesToBite>> listDogsMayBite = 
{ { EnumLikesToBite.Yes},
{ EnumLikesToBite.Sometimes}};

Then another list for the peoples property:

public enum EnumKeepsPetWith
{
Chain,
String,
Rubberband
}
IList <EnumKeepsPetWith> listPeopleWhoDontRestrainDog = 
{ { EnumKeepsPetWith.String },
{ EnumKeepsPetWith.Rubberband}};

How can I query out all the people who have a dog that may bite and don’t restrain dog. Like this pseudo code:

Var result = from p in People where p.KeepsPet in listPeopleWhoDontRestrainDog and dog.LikesToBite in listDogsMayBite.

Result has all the people. Of course if I could get all the dogs who may bite under those people that would be great.

Answer 1

List<int> mayBite = new List<int>()
{
  (int) EnumLikesToBite.Yes,
  (int) EnumLikesToBite.Maybe
}

List<int> poorRestraint = new List<int>()
{
  (int) EnumKeepsPetWith.String,
  (int) EnumKeepsPetWith.RubberBand
}

IQueryable<Person> query =
  from p in db.People
  where poorRestraint.Contains(p.KeepsPetWith)
  where p.DogPeople.Any(dp => mayBite.Contains(dp.Dog.DoesBite))
  select p;

---

var query =
  from p in db.People
  where poorRestraint.Contains(p.KeepsPetWith)
  let bitingDogs =
    from dp in p.DogPeople
    let d = dp.Dog
    where mayBite.Contains(d.DoesBite)
  where bitingDogs.Any()
  select new {Person = p, BitingDogs = bitingDogs.ToList()};

Answer 2

Maybe this code will help.. One of the possible solution are:

   var result =
        peoples.Where(y => dontRestrainDog.Contains(y.KeepsPetWith) && y.Dogs.Any(x => dogsMayBite.Contains(x.LikesToBite))).ToList();
    result.ForEach(y => y.Dogs = y.Dogs.Where(x => dogsMayBite.Contains(x.LikesToBite)).ToList());

which you can see an example of here:

    class Program
    {
        static void Main(string[] args)
        {
            IList<EnumLikesToBite> dogsMayBite = new List<EnumLikesToBite>
                                                     {
                                                         { EnumLikesToBite.Yes }, { EnumLikesToBite.Sometimes }
                                                     };
            IList<EnumKeepsPetWith> dontRestrainDog = new List<EnumKeepsPetWith>
                                                          {
                                                              { EnumKeepsPetWith.String }, { EnumKeepsPetWith.Rubberband }
                                                          };

            var peoples = new List<People>();
            var dogs = new List<Dog>();
            Random gen = new Random(2);

            for(int i = 0; i < 10; i++)
            {
                People p = new People
                               {
                                   PeopleId = i,
                                   KeepsPetWith = (EnumKeepsPetWith) (gen.Next(10)%3),
                                   Dogs = new List<Dog>()
                };
                Dog d = new Dog
                            {
                                DogId = i,
                                LikesToBite = (EnumLikesToBite) (gen.Next(10)%3),
                                Peoples = new List<People>()
                            };
                peoples.Add(p);
                dogs.Add(d);
            }
            for(int i = 0; i < 10; i++)
            {
                for(int j = 0; j < 10; j++)
                {
                    if (gen.Next(10)>7)
                    {
                        peoples[i].Dogs.Add(dogs[j]);
                    }
                    if (gen.Next(10)>7)
                    {
                        dogs[i].Peoples.Add(peoples[j]);
                    }
                }
            }
            PrintDogs(dogs);
            PrintPeoples(peoples);

            var result =
                peoples.Where(y => dontRestrainDog.Contains(y.KeepsPetWith) && y.Dogs.Any(x => dogsMayBite.Contains(x.LikesToBite))).ToList();
            result.ForEach(y => y.Dogs = y.Dogs.Where(x => dogsMayBite.Contains(x.LikesToBite)).ToList());

            Console.WriteLine("===================");
            PrintPeoples(result);
            Console.ReadLine();
        }

        private static void PrintPeoples(List<People> peoples)
        {
            Console.WriteLine("=Peoples=");
            foreach (var people in peoples)
            {
                Console.WriteLine("Id: {0}", people.PeopleId);
                Console.WriteLine("KeepsPetWith: {0}", people.KeepsPetWith);
                Console.WriteLine("Dogs: ");
                foreach (var dog in people.Dogs)
                {
                    Console.Write("{0}, ", dog.DogId);
                }
                Console.WriteLine();
            }
        }

        private static void PrintDogs(List<Dog> dogs)
        {
            Console.WriteLine("=Dogs=");
            foreach (var dog in dogs)
            {
                Console.WriteLine("Id: {0}", dog.DogId);
                Console.WriteLine("LikesToBite: {0}", dog.LikesToBite);
                Console.WriteLine("Peoples: ");
                foreach (var people in dog.Peoples)
                {
                    Console.Write("{0}, ", people.PeopleId);
                }
                Console.WriteLine();
            }
        }
    }

    public class People
    {
        public int PeopleId { get; set; }
        public EnumKeepsPetWith KeepsPetWith { get; set; }
        public IList<Dog> Dogs { get; set; }
    }

    public class Dog
    {
        public int DogId { get; set; }
        public EnumLikesToBite LikesToBite { get; set; }
        public IList<People> Peoples { get; set; }
    }

    public enum EnumLikesToBite
    {
        No,
        Yes,
        Sometimes
    }

    public enum EnumKeepsPetWith
    {
        Chain,
        String,
        Rubberband
    }