C# Linq Inner Join

Question

I want to select the persons only who are having pets.

when I execute the query

var query = from p in people
                        join
                        pts in pets
                        on p equals pts.Owner into grp
                        select new {grp=grp,PersonName=p.FirstName};

Person does not have pet also get selected.

My Lists are

Person[] prn = new Person[3];
prn[0] = new Person();
prn[0].FirstName = "Jon";
prn[0].LastName = "Skeet";

prn[1] = new Person();
prn[1].FirstName = "Marc";
prn[1].LastName = "Gravell";

prn[2] = new Person();
prn[2].FirstName = "Alex";
prn[2].LastName = "Grover";

List<Person> people = new List<Person>();

 foreach (Person p in prn)
 {
     people.Add(p);
 }

 Pet[] pt = new Pet[3];

 pt[0] = new Pet();
 pt[0].Name = "Zonny";
 pt[0].Owner = people[0];

pt[1] = new Pet();
pt[1].Name = "Duggie";
pt[1].Owner = people[0];

pt[2] = new Pet();
pt[2].Name = "Zoggie";
pt[2].Owner = people[1];

List<Pet> pets=new List<Pet>();
 foreach(Pet p in pt)
 {
    pets.Add(p);
 }

Answer 1

That's because you're using join ... into which does a group join. You just want a normal join:

var query = (from p in people
             join pts in pets on p equals pts.Owner
             select p).Distinct();

Alternatively, if you want the people with pets, and their owners, you could do something like:

var query = pets.GroupBy(pet => pet.Owner)
                .Select(x => new { Owner = x.Key, Pets = x.ToList() });

That will give a result where you can get each owner and their pets, but only for people who have pets.

If you want something else, let us know...

By the way, now would be a good time to learn about object and collection initializers. Here's a simpler way to initialize your people list, for example:

List<Person> people = new List<Person>
{
    new Person { FirstName = "Jon", LastName = "Skeet" },
    new Person { FirstName = "Marc", LastName = "Gravell" },
    new Person { FirstName = "Alex", LastName = "Grover" },
};

Much more compact :)

EDIT: A cross join is easy:

var query = from person in people
            from pet in pets
            select new { person, pet };

Left joins are effectively emulated using group joins. As it sounds like you've got C# in Depth, I suggest you read chapter 11 thoroughly :)

Answer 2

Here's a different way to do it, adding only one line:

var query = from p in people
            join pts in pets
            on p equals pts.Owner into grp
            where grp.Any()             // <--- added this
            select new {grp=grp,PersonName=p.FirstName};

Here I select the groups, as you do, but I added one line that selects only the groups that contain at least one element, and ignore the rest.

Answer 3

this can also be done using lambda expressions in a single line of code...

IEnumerable<Person> peopleWithPets = people.Where(x => pets.Any(y => y.Owner == x));