Queryable.Join with the WHERE clause using inner table?

Question

I'm trying to use the Queryable.Join method to join Lists of objects together, but I want to use the WHERE clause against the inner table. I cannot reverse the order of the join tables in this case. When I add a WHERE clause it only "sees" the fields in the outer table. Here's a sample that reproduces my problem:

using System;
using System.Collections.Generic;
using System.Linq;

namespace JoinsWithIQueriable
{
    internal class Program
    {
        class Person
        {
            public string Name { get; set; }
            public int Id { get; set; }
        }

        class Pet
        {
            public string Name { get; set; }
            public int WeightInLbs { get; set; }
            public Person Owner { get; set; }

            public Species PetSpecies { get; set; }

        }


        class Species
        {
            public string Name { get; set; }
        }


        static void Main(string[] args)
        {
            Species dog = new Species { Name = "Dog" };
            Species cat = new Species { Name = "Cat" };

            Person magnus = new Person { Name = "Hedlund, Magnus", Id = 1};
            Person terry = new Person { Name = "Adams, Terry", Id = 2 };
            Person charlotte = new Person { Name = "Weiss, Charlotte", Id = 3 };
            Person alergictopets = new Person { Name = "Scratchy, Itchy", Id = 4 };

            Pet barley = new Pet { Name = "Barley", WeightInLbs = 10, Owner = terry, PetSpecies = cat };
            Pet boots = new Pet { Name = "Boots", WeightInLbs = 11, Owner = terry, PetSpecies = cat };
            Pet whiskers = new Pet { Name = "Whiskers", WeightInLbs = 12, Owner = charlotte, PetSpecies = cat };
            Pet daisy = new Pet { Name = "Daisy", WeightInLbs = 40, Owner = magnus, PetSpecies = dog};



            List<Person> people = new List<Person> { magnus, terry, charlotte, alergictopets };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, daisy };
            List<Species> species = new List<Species> { dog, cat};

            // Join the list of Person objects and the list of Pet objects
            // to create a list of person-pet pairs where each element is
            // an anonymous type that contains the name of pet and the name
            // of the person that owns the pet.

            Console.WriteLine("query");

            var query = people.AsQueryable().Join(pets,
                person => person,
                pet => pet.Owner,
                (person, pet) =>
                    new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });

            foreach (var obj in query)
            {
                Console.WriteLine(
                    "Owner {0} - Pet {1} - Pet weight {2} ",
                    obj.OwnerName,
                    obj.Pet, 
                    obj.Weight.ToString());
            }

            /*
                This code produces the following output:

                Owner Hedlund, Magnus - Pet Daisy - Pet weight 40
                Owner Adams, Terry - Pet Barley - Pet weight 10
                Owner Adams, Terry - Pet Boots - Pet weight 11
                Owner Weiss, Charlotte - Pet Whiskers - Pet weight 12
            */


            Console.WriteLine("querywithwhere");

            //Want to filter for pets that weight less than 12 lbs
            var querywithwhere = people.AsQueryable()
                .Join(pets,
                person => person,
                pet => pet.Owner,
                (person, pet) =>
                    new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });

            foreach (var obj in querywithwhere)
            {
                Console.WriteLine(
                    "Owner {0} - Pet {1} - Pet weight {2} ",
                    obj.OwnerName,
                    obj.Pet,
                    obj.Weight.ToString());
            }

            Console.WriteLine("Press any key to continue...");
            Console.ReadLine();

        }
    }
}

Answer 1

Either you can filter the pets with containing the record with WeightInLbs < 12 before join with people

var querywithwhere = people.AsQueryable()
    .Join(pets.Where(x => x.WeightInLbs < 12),
        person => person,
        pet => pet.Owner,
        (person, pet) =>
            new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });

Or you can filter the record after joining with .Where() clause.

var querywithwhere = people.AsQueryable()
    .Join(pets,
        person => person,
        pet => pet.Owner,
        (person, pet) =>
            new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs })
    .Where(x => x.Weight < 12);